Drizzle with Vercel Postgres
This tutorial demonstrates how to use Drizzle ORM with Vercel Postgres. Vercel Postgres is a serverless SQL database designed to integrate with Vercel Functions and your frontend framework.
- You should have installed Drizzle ORM and Drizzle kit. You can do this by running the following command:
npm i drizzle-orm
npm i -D drizzle-kit
yarn add drizzle-orm
yarn add -D drizzle-kit
pnpm add drizzle-orm
pnpm add -D drizzle-kit
bun add drizzle-orm
bun add -D drizzle-kit
- You should have installed
dotenv
package for managing environment variables. Read more about this package here
npm i dotenv
yarn add dotenv
pnpm add dotenv
bun add dotenv
- You should have installed
@vercel/postgres
package. Read more about this package here
npm i @vercel/postgres
yarn add @vercel/postgres
pnpm add @vercel/postgres
bun add @vercel/postgres
Check Vercel documentation to learn how to connect to the database with Drizzle ORM.
Setup Vercel Postgres and Drizzle ORM
Create a new Vercel Postgres database
You can create new Vercel Postgres database in the dashboard.
Read Vercel Postgres documentation to learn how to create a new database.
Setup connection string variable
Navigate to your Vercel Postgres database and copy POSTGRES_URL
from .env.local
section.
Add POSTGRES_URL
to your .env.local
or .env
file.
POSTGRES_URL=<YOUR_DATABASE_URL>
Connect Drizzle ORM to your database
Create a index.ts
file in the src/db
directory and set up your database configuration:
import { sql } from '@vercel/postgres';
import { drizzle } from 'drizzle-orm/vercel-postgres';
import { config } from 'dotenv';
config({ path: '.env.local' }); // or .env
export const db = drizzle(sql);
Create tables
Create a schema.ts
file in the src/db
directory and declare your tables:
import { integer, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';
export const usersTable = pgTable('users_table', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
age: integer('age').notNull(),
email: text('email').notNull().unique(),
});
export const postsTable = pgTable('posts_table', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
content: text('content').notNull(),
userId: integer('user_id')
.notNull()
.references(() => usersTable.id, { onDelete: 'cascade' }),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at')
.notNull()
.$onUpdate(() => new Date()),
});
export type InsertUser = typeof usersTable.$inferInsert;
export type SelectUser = typeof usersTable.$inferSelect;
export type InsertPost = typeof postsTable.$inferInsert;
export type SelectPost = typeof postsTable.$inferSelect;
Setup Drizzle config file
Drizzle config - a configuration file that is used by Drizzle Kit and contains all the information about your database connection, migration folder and schema files.
Create a drizzle.config.ts
file in the root of your project and add the following content:
import { config } from 'dotenv';
import { defineConfig } from 'drizzle-kit';
config({ path: '.env.local' });
export default defineConfig({
schema: './src/db/schema.ts',
out: './migrations',
dialect: 'postgresql',
dbCredentials: {
url: process.env.POSTGRES_URL!,
},
});
Applying changes to the database
You can generate migrations using drizzle-kit generate
command and then run them using the drizzle-kit migrate
command.
Generate migrations:
npx drizzle-kit generate
These migrations are stored in the drizzle/migrations
directory, as specified in your drizzle.config.ts
. This directory will contain the SQL files necessary to update your database schema and a meta
folder for storing snapshots of the schema at different migration stages.
Example of a generated migration:
CREATE TABLE IF NOT EXISTS "posts_table" (
"id" serial PRIMARY KEY NOT NULL,
"title" text NOT NULL,
"content" text NOT NULL,
"user_id" integer NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "users_table" (
"id" serial PRIMARY KEY NOT NULL,
"name" text NOT NULL,
"age" integer NOT NULL,
"email" text NOT NULL,
CONSTRAINT "users_table_email_unique" UNIQUE("email")
);
--> statement-breakpoint
DO $$ BEGIN
ALTER TABLE "posts_table" ADD CONSTRAINT "posts_table_user_id_users_table_id_fk" FOREIGN KEY ("user_id") REFERENCES "users_table"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
Run migrations:
npx drizzle-kit migrate
Alternatively, you can push changes directly to the database using Drizzle kit push command:
npx drizzle-kit push
Basic file structure
This is the basic file structure of the project. In the src/db
directory, we have database-related files including connection in index.ts
and schema definitions in schema.ts
.
📦 <project root>
├ 📂 src
│ ├ 📂 db
│ │ ├ 📜 index.ts
│ │ └ 📜 schema.ts
├ 📂 migrations
│ ├ 📂 meta
│ │ ├ 📜 _journal.json
│ │ └ 📜 0000_snapshot.json
│ └ 📜 0000_watery_spencer_smythe.sql
├ 📜 .env.local
├ 📜 drizzle.config.ts
├ 📜 package.json
└ 📜 tsconfig.json
Query examples
For instance, we create src/db/queries
folder and separate files for each operation: insert, select, update, delete.
Insert data
Read more about insert query in the documentation.
import { db } from '../index';
import { InsertPost, InsertUser, postsTable, usersTable } from '../schema';
export async function createUser(data: InsertUser) {
await db.insert(usersTable).values(data);
}
export async function createPost(data: InsertPost) {
await db.insert(postsTable).values(data);
}
Select data
Read more about select query in the documentation.
import { asc, between, count, eq, getTableColumns, sql } from 'drizzle-orm';
import { db } from '../index';
import { SelectUser, postsTable, usersTable } from '../schema';
export async function getUserById(id: SelectUser['id']): Promise<
Array<{
id: number;
name: string;
age: number;
email: string;
}>
> {
return db.select().from(usersTable).where(eq(usersTable.id, id));
}
export async function getUsersWithPostsCount(
page = 1,
pageSize = 5,
): Promise<
Array<{
postsCount: number;
id: number;
name: string;
age: number;
email: string;
}>
> {
return db
.select({
...getTableColumns(usersTable),
postsCount: count(postsTable.id),
})
.from(usersTable)
.leftJoin(postsTable, eq(usersTable.id, postsTable.userId))
.groupBy(usersTable.id)
.orderBy(asc(usersTable.id))
.limit(pageSize)
.offset((page - 1) * pageSize);
}
export async function getPostsForLast24Hours(
page = 1,
pageSize = 5,
): Promise<
Array<{
id: number;
title: string;
}>
> {
return db
.select({
id: postsTable.id,
title: postsTable.title,
})
.from(postsTable)
.where(between(postsTable.createdAt, sql`now() - interval '1 day'`, sql`now()`))
.orderBy(asc(postsTable.title), asc(postsTable.id))
.limit(pageSize)
.offset((page - 1) * pageSize);
}
Alternatively, you can use relational query syntax.
Update data
Read more about update query in the documentation.
import { eq } from 'drizzle-orm';
import { db } from '../index';
import { SelectPost, postsTable } from '../schema';
export async function updatePost(id: SelectPost['id'], data: Partial<Omit<SelectPost, 'id'>>) {
await db.update(postsTable).set(data).where(eq(postsTable.id, id));
}
Delete data
Read more about delete query in the documentation.
import { eq } from 'drizzle-orm';
import { db } from '../index';
import { SelectUser, usersTable } from '../schema';
export async function deleteUser(id: SelectUser['id']) {
await db.delete(usersTable).where(eq(usersTable.id, id));
}