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

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. Install Prisma client and Prisma CLI:

   pnpm

   yarn

   npm

   pnpm i prisma @prisma/client

2. 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())
   }

3. 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: '[email protected]',
       },
     });
    
     const users = await prisma.user.findMany();
   }

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

2. Create a schema.ts file for your database tables:

   drizzle/schema.ts

   TypeScript

   TypeScriptJavaScript

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

   TypeScript

   TypeScriptJavaScript

   import '@/drizzle/envConfig';
   import { defineConfig } from 'drizzle-kit';
    
   export default defineConfig({
     schema: './drizzle/schema.ts',
     dialect: 'postgresql',
     dbCredentials: {
       url: process.env.POSTGRES_URL,
     },
   });

2. Use the drizzle-kit CLI to generate your tables:

   Terminal

   npx drizzle-kit push

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

   TypeScript

   TypeScriptJavaScript

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