Using an ORM to access your Postgres database
Learn how to use your favorite ORM to access your database from your appsVercel 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:
Install the Kysely package:
pnpm i kysely @vercel/postgres-kysely
Use the
createKysely
method from@vercel/postgres-kysely
to create a client with a pooled connectionimport { 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:
Follow the instructions in our Postgres quickstart
Install Prisma client and Prisma CLI:
pnpm i prisma @prisma/client
Use your environment variables in your
schema.prisma
file as shown below:schema.prismagenerator 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()) }
Use
@prisma/client
to query your Vercel Postgres databaseimport { 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(); }
Whenever you make changes to your prisma schema, you must run
prisma generate
to update the generated type generations, which live in thenode_modules/.prisma/client
directory.Prisma generateprisma 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.
Drizzle is a collection of front-end libraries that make writing decentralized applications easier and more predictable. 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:
- Follow the instructions in the Postgres quickstart
- Install the
drizzle-orm
package:pnpm i drizzle-orm
- Create a client that connects to your Vercel Postgres database with Drizzle:
import { drizzle } from 'drizzle-orm/vercel-postgres';
import { sql } from '@vercel/postgres';
import {
pgTable,
serial,
text,
timestamp,
uniqueIndex,
} from 'drizzle-orm/pg-core';
// Use this object to send drizzle queries to your DB
export const db = drizzle(sql);
// Create a pgTable that maps to a table in your DB
export const ExampleTable = 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),
};
},
);
export const getExampleTable = async () => {
const selectResult = await db.select().from(ExampleTable);
console.log('Results', selectResult);
};