dev-resources.site

for different kinds of informations.

How to use ORMs (Prisma / Drizzle / Knex.js) in a TypeScript backend built with Encore.ts

Published at

11/25/2024

Using a Database with Encore

Encore treats databases as logical resources in your code and natively supports PostgreSQL databases. But let’s dive right into some code because that will speak for itself.

import { SQLDatabase } from "encore.dev/storage/sqldb";

const db = new SQLDatabase("url", { migrations: "./migrations" });

To create a database with Encore, import SQLDatabase from Encore’s encore.dev/storage/sqldb module and call new SQLDatabase, giving it a name and a path to our migrations folder. In the migrations folder Encore expects ordinary SQL files, which is how you define the database schema.

Encore automatically handles up migrations. Each up migration runs sequentially, expressing changes in the database schema from the previous migration. The first migration typically defines the initial table structure. For instance, the first migration file for a URL shortener service might look like this:

1_create_table.up.sql

CREATE TABLE url (
    id TEXT PRIMARY KEY,
    original_url TEXT NOT NULL
);

This is how our folder structure could look like with multiple migration files:

/my-app
├── encore.app                       // ... other top-level files
│
└── url                              // url shortener service
    ├── migrations                   // database migrations (directory)
    │   ├── 1_create_table.up.sql    // first migration file
    │   └── 2_add_field.up.sql       // second migration file
    ├── url.ts                       // url service code
    └── url.test.ts                  // tests for url service

With this code in place, Encore will automatically create the database using Docker when you run the command encore run locally.

We can now start querying and inserting data into the database. With the exec method we can write SQL code for inserting data.

import { api } from "encore.dev/api";
import { randomBytes } from "node:crypto";

interface URL {
  id: string; // short-form URL id
  url: string; // complete URL, in long form
}

interface ShortenParams {
  url: string; // the URL to shorten
}

// shorten shortens a URL.
export const shorten = api(
  { expose: true, auth: false, method: "POST", path: "/url" },
  async ({ url }: ShortenParams): Promise<URL> => {
    const id = randomBytes(6).toString("base64url");
    await db.exec`
        INSERT INTO url (id, original_url)
        VALUES (${id}, ${url})
    `;
    return { id, url };
  },
);

We can even make use of template literals when writing our SQL query, allowing easy use of placeholder parameters. Encore takes care of the escaping here so there is no risk of SQL injections.

The get endpoint takes a user ID as input. We are using the queryRow method, which returns a single row. We again use a template literal to send in the ID. If no rows are found we return an error, otherwise we get the original_url from the row.

import { api, APIError } from "encore.dev/api";

// Get retrieves the original URL for the id.
export const get = api(
  { expose: true, auth: false, method: "GET", path: "/url/:id" },
  async ({ id }: { id: string }): Promise<URL> => {
    const row = await db.queryRow`
        SELECT original_url
        FROM url
        WHERE id = ${id}
    `;
    if (!row) throw APIError.notFound("url not found");
    return { id, url: row.original_url };
  },
);

Encore CLI comes with tools for you to interact with the locally running database. You can for example run encore db shell to open a psql shell to the database.

When you start your app using encore run you get access to the Local Development Dashboard. From here you can easily call your endpoints. Each call to your application results in a trace that you can inspect to see the API requests but also the database calls.

ORM

And if you don’t want to write SQL code by hand you can use ORMs like Prisma, Drizzle, or Knex if you prefer.

An ORM stands for Object–relational mapping, what this basically means is that we are using the power of object oriented programming to define a layer on top of our database.

An ORM abstracts away the communication with the database. It can speed up development and one nice thing is that you get type-safety when working with your database.

But using an ORM is not a given and you should think about if it suites your project because it can be really hard to move away from an ORM once you have committed.

But with that said, let’s look at how to use Prisma, one of the most popular ORMs for Node.js, together with Encore.

Prisma

Let’s take a look at how our schema.prisma could look like when integrating with Encore.

generator client {
  provider = "prisma-client-js"
  binaryTargets = ["native", "debian-openssl-3.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = "<paste shadow db connection string here>"

}

model User {
  id      Int      @id @default(autoincrement())
  name    String
  surname String
  age Int
}

For the datasource we need to set the provider and the url to the shadow database. Prisma requires a "shadow database" for certain operations, essentially it's a second, temporary, database that is created and deleted automatically. Encore comes with built-in support for shadow databases, and by configuring Prisma to operate on Encore's shadow database, Encore.ts and Prisma won't interfere with each other.

To get the Encore shadow URI we run the following command encore db conn-uri encore_prisma_test --shadow in the terminal. Then we paste that value into the schema file. It will look something like this: "postgresql://prisma-3wv2:[email protected]:9500/encore_prisma_test?sslmode=disable".

Now we are ready to create a database and a PrismaClient:

import { SQLDatabase } from "encore.dev/storage/sqldb";
import { PrismaClient } from "@prisma/client";

// Define a database named 'encore_prisma_test', using the database migrations
// in the "./prisma/migrations" folder (where prisma will generate their migrations).
// Set `source` to `prisma` to let Encore know that the migrations are generated by Prisma.
const DB = new SQLDatabase('encore_prisma_test', {
  migrations: {
    path: './prisma/migrations',
    source: 'prisma',
  },
});

// Setup prisma client with connection string
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: DB.connectionString,
    },
  },
});

// Select all users
const allUsers = prisma.user.findMany();

We create a database like we did earlier, but this time we get the connectionString and pass that in when creating the PrismaClient. Now we can use the prisma object to query and update data in the database.

Run npx prisma migrate dev to create new migrations if you have made any changes to the schema.prisma file. The migration files will be automatically applied by Encore.

We also add the following NPM script that will run after installing our dependencies so that Prisma sets everything up in our CI pipeline when deploying:

{
  "scripts": {
    "postinstall": "npx prisma generate --schema=users/prisma/schema.prisma"
  },
}

Drizzle

Let’s take a look at another popular ORM, Drizzle. In the database.ts file we again create a database and pass in the connectionString to drizzle:

// database.ts
import { api } from "encore.dev/api";
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { drizzle } from "drizzle-orm/node-postgres";
import { users } from "./schema";

// Create SQLDatabase instance with migrations configuration
const db = new SQLDatabase("test", {
  migrations: {
    path: "migrations",
    source: "drizzle",
  },
});

// Initialize Drizzle ORM with the connection string
const orm = drizzle(db.connectionString);

// Query all users
await orm.select().from(users);

In the Drizzle config we specify the migrations folder, our schema file and that we are using a PostgreSQL database:

// drizzle.config.ts
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: 'migrations',
  schema: 'schema.ts',
  dialect: 'postgresql',
});

And this is what a Drizzle schema file looks like:

// schema.ts
import * as p from "drizzle-orm/pg-core";

export const users = p.pgTable("users", {
  id: p.serial().primaryKey(),
  name: p.text(),
  email: p.text().unique(),
});

Run the npx drizzle-kit generate command to generate migrations. Migrations are automatically applied when you run your Encore application, so you don’t need to run drizzle-kit migrate or any similar commands manually.

Knex

Knex is a “SQL query builder”, which is somewhere in between an ORM and writing raw SQL queries:

// site.ts
import { SQLDatabase } from "encore.dev/storage/sqldb";
import knex from "knex";

// Create SQLDatabase instance with migrations configuration
const SiteDB = new SQLDatabase("siteDB", {
  migrations: "./migrations",
});

// Initialize Knex with the database connection string
const knexDB = knex({
  client: "pg",
  connection: SiteDB.connectionString,
});

// Define the Site interface
export interface Site {
  id: number;
  url: string;
}

// Query builder for the "site" table
const Sites = () => knexDB<Site>("site");

// Example queries

// Query all sites
await Sites().select();

// Query a site by id
await Sites().where("id", id).first();

// Insert a new site
await Sites().insert({ url: params.url });

We create a database like we did earlier, and pass in the connectionString to the database when creating a new Knex client. We also need to specify that we are using PostgreSQL. Then we can create a query builder by using the knexDB object, passing in the type of the object we are storing (Site) and the table name ("site").

Now we can use the Sites object to query and update data in the database.

A query builder is easier to set up but you don’t get the same level of autocompletion in your editor as with an ORM.

Deploy

So, how do we deploy this application? Well you can build your application using encore build docker image, and you get it as a docker image you can deploy anywhere you want. You will need to supply a runtime configuration where you can specify how the application should connect to the infrastructure you are using, like databases and Pub/Sub. If you don’t feel like managing this stuff manually, you can use Encore's Cloud Platform which automates setting up the needed infrastructure in your cloud account on AWS or GCP, and it comes with built-in CI/CD so you just need to push to deploy. In cloud environments, Encore automatically injects the appropriate configuration to authenticate and connect to the database, so once the application starts up the database is ready to be used. The Platform also comes with monitoring, tracing, and automatic preview environments so you can test each pull request in a dedicated temporary environment.

Running the examples yourself

If you want to play around with this application yourself you can easily do so by installing Encore and then clone an example from our examples repo. You will need to have Docker desktop installed as that is needed to create databases locally.

Installation

macOS: brew install encoredev/tap/encore
Linux: curl -L https://encore.dev/install.sh | bash
Windows: iwr https://encore.dev/install.ps1 | iex

Cloning the ORM examples

Prisma

encore app create --example=ts/prisma

Drizzle

encore app create --example=ts/drizzle

Knex.js

encore app create --example=ts/knex

Wrapping up

⭐️ Support Encore by giving the project a star on GitHub.
If you have questions or want to share your work, join the developers hangout in Encore's community on Discord.