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

In this post, I’ll walk you through how to work with ORMs like and .

We will look at how to create and manage Databases when running your application locally but also how to go about getting an app using databases deployed to the cloud.

Video version:

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  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 };

  },

);

 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 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 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 . 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 .

Other related posts