How-to
5 min read

Using an ORM to access your Postgres database

Learn how to use your favorite ORM to access your database from your apps
Table of Contents

Vercel Postgres is available on Hobby and Pro plans

Vercel Postgres provides an SDK, but we recommend using an ORM for larger applications.

To get started using Vercel Postgres with your favorite ORM, follow the instructions in our Postgres quickstart.

If your ORM is featured on this page, read the corresponding section for the most optimal configuration instructions.

Kysely is a type-safe and autocomplete-friendly TypeScript SQL query builder. Use our Kysely template to deploy a Next.js project that connects to Vercel Postgres with Kysely now.

To use Kysely, follow these steps:

  1. Install the Kysely package:

    pnpm
    yarn
    npm
    pnpm i kysely @vercel/postgres-kysely
  2. Use the createKysely method from @vercel/postgres-kysely to create a client with a pooled connection

    import { createKysely } from '@vercel/postgres-kysely';
     
    interface Database {
      person: PersonTable;
      pet: PetTable;
      movie: MovieTable;
    }
     
    const db = createKysely<Database>();
     
    await db
      .insertInto('pet')
      .values({ name: 'Catto', species: 'cat', owner_id: id })
      .execute();
     
    const person = await db
      .selectFrom('person')
      .innerJoin('pet', 'pet.owner_id', 'person.id')
      .select(['first_name', 'pet.name as pet_name'])
      .where('person.id', '=', id)
      .executeTakeFirst();

Kysely supports all PostgreSQL commands. See Kysely's docs for a full reference of the methods used to send the commands.

Prisma is a next-gen ORM that includes a type-safe query builder, migration system, and database management interface. Use our Prisma template to deploy a Next.js project that connects to Vercel Postgres with Prisma now.

To use Vercel Postgres with Prisma, you must:

  1. Follow the instructions in our Postgres quickstart

  2. Install Prisma client and Prisma CLI:

    pnpm
    yarn
    npm
    pnpm i prisma @prisma/client
  3. Use your environment variables in your schema.prisma file as shown below:

    schema.prisma
    generator client {
      provider = "prisma-client-js"
    }
     
    datasource db {
      provider = "postgresql"
      // Uses connection pooling
      url = env("POSTGRES_PRISMA_URL")
      // Uses direct connection, ⚠️ make sure to keep this to `POSTGRES_URL_NON_POOLING`
      // or you'll have dangling databases from migrations
      directUrl = env("POSTGRES_URL_NON_POOLING")
    }
     
    model User {
      id        Int      @id @default(autoincrement())
      name      String
      email     String   @unique
      image     String
      createdAt DateTime @default(now())
    }
  4. Use @prisma/client to query your Vercel Postgres database

    import { PrismaClient } from '@prisma/client';
     
    const prisma = new PrismaClient();
     
    export default async function prismaExample() {
      const newUser = await prisma.user.create({
        data: {
          name: 'Elliott',
          email: 'xelliottx@example-user.com',
        },
      });
     
      const users = await prisma.user.findMany();
    }
  5. Whenever you make changes to your prisma schema, you must run a migration, then run prisma generate to update the generated type generations, which live in the node_modules/.prisma/client directory. You can do this by adding the following script to your package.json:

    package.json
    {
      "scripts": {
        "vercel-build": "prisma generate && prisma migrate deploy && next build"
      }
    }

When you connect with Prisma using the POSTGRES_PRISMA_URL environment variable, the parameters connect_timeout=10 and pgbouncer=true will be set.

See the Prisma docs to learn more.

See the Prisma Migrate docs to migrate your database.

Drizzle is a TypeScript ORM that enables developers to build type-safe SQL queries. It relies on zero dependencies, and uses a SQL-like syntax to offer a lower learning curve than other ORMs. Use our Drizzle template to deploy a Next.js project that connects to Vercel Postgres with Drizzle now.

To use Drizzle with Vercel Postgres, you must:

  1. Follow the instructions in the Postgres quickstart

  2. Install the drizzle-orm package to write queries, and the drizzle-kit package to define your schema and manage migrations:

    pnpm
    yarn
    npm
    pnpm i drizzle-orm
    pnpm
    yarn
    npm
    pnpm i -D drizzle-kit
  3. Create a schema.ts file for your database tables:

    drizzle/schema.ts
    import { drizzle } from 'drizzle-orm/vercel-postgres';
    import { sql } from '@vercel/postgres';
    import {
      pgTable,
      serial,
      text,
      timestamp,
      uniqueIndex,
    } from 'drizzle-orm/pg-core';
     
    export const UsersTable = pgTable(
      'users',
      {
        id: serial('id').primaryKey(),
        name: text('name').notNull(),
        email: text('email').notNull(),
        image: text('image').notNull(),
        createdAt: timestamp('createdAt').defaultNow().notNull(),
      },
      (users) => {
        return {
          uniqueIdx: uniqueIndex('unique_idx').on(users.email),
        };
      },
    );
  1. Install the @next/env package to load the environment variables outside of the Next.js runtime:

    pnpm
    yarn
    npm
    pnpm i @next/env
    Next.js (/app)
    Next.js (/pages)
    Other frameworks
    drizzle/envConfig.ts
    import { loadEnvConfig } from '@next/env';
     
    const projectDir = process.cwd();
    loadEnvConfig(projectDir);
  1. Create a drizzle.config.js file in the root of your project, import the environment variables config, add the path to your schema file, and your Vercel POSTGRES_URL connection string:

    drizzle.config.ts
    import '@/drizzle/envConfig';
    import { defineConfig } from 'drizzle-kit';
     
    export default defineConfig({
      schema: './drizzle/schema.ts',
      driver: 'pg',
      dbCredentials: {
        connectionString: process.env.POSTGRES_URL!,
      },
    });
  2. Use the drizzle-kit CLI to generate your tables:

    Terminal
    npx drizzle-kit push:pg

After running this command, you can view your tables in your Vercel dashboard, under the Storage tab. Follow the Drizzle-Kit docs to migrate and seed your database, and learn how to use the Drizzle-Studio.

  1. Lastly, create a new client to query your database:

    drizzle/db.ts
    import '@/drizzle/envConfig';
    import { drizzle } from 'drizzle-orm/vercel-postgres';
    import { sql } from '@vercel/postgres';
    import * as schema from './schema';
     
    export const db = drizzle(sql, { schema });
     
    export const getUsers = async () => {
      return db.query.users.findMany();
    };
Last updated on May 29, 2024