Drizzle with Neon Postgres

This tutorial demonstrates how to use Drizzle ORM with Neon Postgres database. If you do not have an existing Neon account, sign up here.

This guide assumes familiarity with:
  • You should have installed Drizzle ORM and Drizzle kit. You can do this by running the following command:
npm
yarn
pnpm
bun 
npm i drizzle-orm
npm i -D drizzle-kit
npm
yarn
pnpm
bun 
npm i @neondatabase/serverless
  • You should have installed the dotenv package for managing environment variables.
npm
yarn
pnpm
bun 
npm i dotenv

Setup Neon and Drizzle ORM

Create a new Neon project

Log in to the Neon Console and navigate to the Projects section. Select a project or click the New Project button to create a new one.

Your Neon projects come with a ready-to-use Postgres database named neondb. We’ll use it in this tutorial.

Setup connection string variable

Navigate to the Connection Details section in the project console to find your database connection string. It should look similar to this:

postgres://username:[email protected]/neondb

Add the DATABASE_URL environment variable to your .env file, which you’ll use to connect to the Neon database.

DATABASE_URL=NEON_DATABASE_CONNECTION_STRING

Connect Drizzle ORM to your database

Create a db.ts file and set up your database configuration:

src/db.ts 
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import { config } from "dotenv";

config({ path: ".env" });

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

Create tables

Create a schema.ts file and declare your tables:

src/schema.ts 
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:

drizzle.config.ts 
import { config } from 'dotenv';
import { defineConfig } from "drizzle-kit";

config({ path: '.env' });

export default defineConfig({
  schema: "./src/schema.ts",
  out: "./migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_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 "public"."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
💡
Push command is good for situations where you need to quickly test new schema designs or changes in a local development environment, allowing for fast iterations without the overhead of managing migration files.

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 db.ts, schema definitions in schema.ts, and a migration script in migrate.ts file which is responsible for applying migrations that stored in the migrations directory.

📦 <project root>
 ├ 📂 src
 │  ├ 📜 db.ts
 │  └ 📜 schema.ts
 │  └ 📜 queries.ts
 ├ 📂 migrations
 │  ├ 📂 meta
 │  │  ├ 📜 _journal.json
 │  │  └ 📜 0000_snapshot.json
 │  └ 📜 0000_dry_richard_fisk.sql
 ├ 📜 .env
 ├ 📜 drizzle.config.ts
 ├ 📜 package.json
 └ 📜 tsconfig.json

Query examples

For instance, we create src/queries.ts file with the following queries.

Insert data

Read more about insert query in the documentation.

src/queries.ts 
import { db } from './db';
import { InsertUser, usersTable } from './schema';

export async function createUser(data: InsertUser) {
  await db.insert(usersTable).values(data);
}

Select data

Read more about select query in the documentation.

src/queries.ts 
import { asc, between, count, eq, getTableColumns, sql } from 'drizzle-orm';
import { db } from './db';
import { InsertUser, 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.

src/queries.ts 
// imports

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.

src/queries.ts 
// imports

export async function deleteUser(id: SelectUser['id']) {
  await db.delete(usersTable).where(eq(usersTable.id, id));
}
Benchmarks
Extension
Studio
Studio Package
Turso
Payload
Xata
Neon
Nuxt
Hydra
Become a Gold Sponsor