Using an ORM to access your Postgres database

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

• Kysely
• Prisma
• Drizzle (Coming Soon)

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

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"
     previewFeatures = ["jsonProtocol"]
   }
    
   datasource db {
     provider = "postgresql"
     url = env("POSTGRES_PRISMA_URL") // uses connection pooling
     directUrl = env("POSTGRES_URL_NON_POOLING") // uses a direct connection
     shadowDatabaseUrl = env("POSTGRES_URL_NON_POOLING") // used for migrations
   }
    
   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: 'elliott@vercel.com',
       },
     });
    
     const users = await prisma.user.findMany();
   }

5. Whenever you make changes to your prisma schema, you must run prisma generate to update the generated type generations, which live in the node_modules/.prisma/client directory.

   Prisma generate

   prisma generate

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

We recommend using the POSTGRES_URL_NON_POOLING Environment Variable instead.

See the Prisma docs to learn more.

To use Prisma Migrate, you must connect to Vercel Postgres with the POSTGRES_URL_NON_POOLING environment variable. Prisma migrations do not work with pooled connections. This environment variable can also be added to your client via the directUrl configuration property in your schema.prisma file.

We are working on adding support for using Drizzle with Vercel Postgres.