Skip to content

Use Drizzle ORM with Appwrite Postgres_

Connect Drizzle ORM to Appwrite Postgres, query data with TypeScript, and apply SQL migrations as your schema changes.

Atharva Deosthale

Developer Advocate

8 min read

ORMs are a popular way to work with databases through application code. Appwrite Postgres fits into that workflow with a standard PostgreSQL connection string. With Drizzle ORM, you can write typed queries in TypeScript and manage schema migrations through Drizzle Kit.

This guide provisions a database and adds a books table. A second migration adds a column while preserving the existing records.

Provision Postgres

In your project at appwrite.io, open Databases → Create database. Under Native databases, select PostgreSQL.

Create database screen with PostgreSQL under Native databases
Create database screen with PostgreSQL under Native databases

Enter Bookshelf as the name and leave Database ID auto-generated. Select the Starter tier for this example.

Keep Replica count at 0, Enable PITR off, and Daily backups selected. Review the price in Database summary, then click Create database. See the PostgreSQL quick start for provisioning options.

Connect Drizzle

After provisioning, open Credentials → DSN → Copy. Change the URL's port from 6432 to 5432, keeping the other values unchanged. We recommend this direct endpoint for migrations that rely on session settings or session-level locks. The pooler on port 6432 uses transaction mode by default, so transactions can use different sessions. See connection pooling.

Database connection details with the password hidden
Database connection details with the password hidden

In a TypeScript project using ES modules, install the dependencies:

Bash
npm install drizzle-orm@0.45.2 pg@8.23.0 dotenv@17.4.2
npm install --save-dev drizzle-kit@0.31.10 tsx@4.23.13 typescript@7.0.2 @types/node@22.20.1 @types/pg@8.23.1

Set the connection string in .env and exclude that file from Git:

.env
DATABASE_URL="<YOUR_DIRECT_POSTGRESQL_CONNECTION_STRING>"

Create src/db.ts:

TypeScript
import 'dotenv/config'
import { drizzle } from 'drizzle-orm/node-postgres'
import pg from 'pg'


if (!process.env.DATABASE_URL) {
  throw new Error('Set DATABASE_URL in .env')
}


export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
})


export const db = drizzle({ client: pool })

Create the table with Drizzle Kit

With Drizzle, you can define tables and columns in TypeScript. Drizzle Kit turns that code into SQL migrations.

Define the books table in src/schema.ts:

TypeScript
import { boolean, pgTable, text } from 'drizzle-orm/pg-core'


export const books = pgTable('books', {
  isbn: text('isbn').primaryKey(),
  title: text('title').notNull(),
  author: text('author').notNull(),
  read: boolean('read').notNull().default(false),
})

Configure Drizzle Kit in drizzle.config.ts:

TypeScript
import 'dotenv/config'
import { defineConfig } from 'drizzle-kit'


if (!process.env.DATABASE_URL) {
  throw new Error('Set DATABASE_URL in .env')
}


export default defineConfig({
  dialect: 'postgresql',
  schema: './src/schema.ts',
  out: './drizzle',
  dbCredentials: { url: process.env.DATABASE_URL },
})

Generate the SQL migration:

Bash
npx drizzle-kit generate --name=create_books

Review drizzle/0000_create_books.sql, then apply it:

Bash
npx drizzle-kit migrate

generate writes SQL from schema changes. migrate applies pending migrations and records them in Postgres. Commit the drizzle directory, including its metadata.

Verify the table in Appwrite

In Appwrite, select Data, choose the public schema, and refresh the table list. Open books and select Columns to see the four columns defined in your schema.

The books table in Appwrite with isbn, title, author, and read columns
The books table in Appwrite with isbn, title, author, and read columns

Insert and query books

Create src/seed.ts:

TypeScript
import { db, pool } from './db.js'
import { books } from './schema.js'


try {
  const inserted = await db
    .insert(books)
    .values([
      {
        isbn: '9780441478125',
        title: 'The Left Hand of Darkness',
        author: 'Ursula K. Le Guin',
      },
      { isbn: '9780807083697', title: 'Kindred', author: 'Octavia E. Butler' },
      {
        isbn: '9780061054884',
        title: 'The Dispossessed',
        author: 'Ursula K. Le Guin',
      },
    ])
    .onConflictDoNothing({ target: books.isbn })
    .returning({ title: books.title })


  console.log(`Inserted ${inserted.length} books.`)
} finally {
  await pool.end()
}

Run the seed:

Bash
npx tsx src/seed.ts

The first run inserts three books. onConflictDoNothing makes subsequent runs skip existing ISBNs.

Create src/unread.ts:

TypeScript
import { asc, eq } from 'drizzle-orm'
import { db, pool } from './db.js'
import { books } from './schema.js'


try {
  const unread = await db
    .select({ title: books.title, author: books.author })
    .from(books)
    .where(eq(books.read, false))
    .orderBy(asc(books.title))


  console.table(unread)
} finally {
  await pool.end()
}

It returns Kindred, The Dispossessed, and The Left Hand of Darkness, ordered by title. Drizzle infers the result's title and author types from the schema.

Add a column through a migration

Replace src/schema.ts with this version to add a nullable rating:

TypeScript
import { boolean, integer, pgTable, text } from 'drizzle-orm/pg-core'


export const books = pgTable('books', {
  isbn: text('isbn').primaryKey(),
  title: text('title').notNull(),
  author: text('author').notNull(),
  read: boolean('read').notNull().default(false),
  rating: integer('rating'),
})

Generate the next migration:

Bash
npx drizzle-kit generate --name=add_rating

The generated drizzle/0001_add_rating.sql contains:

SQL
ALTER TABLE "books" ADD COLUMN "rating" integer;

Apply it and rerun the query:

Bash
npx drizzle-kit migrate
npx tsx src/unread.ts

The same three books remain. In Appwrite, refresh Columns to see rating; the existing rows have null in that column.

Books table columns after the second migration, including the nullable integer rating column
Books table columns after the second migration, including the nullable integer rating column

Update and query the new column

Create src/rate.ts to mark Kindred as read, assign a rating, and query rated books:

TypeScript
import { desc, eq, isNotNull } from 'drizzle-orm'
import { db, pool } from './db.js'
import { books } from './schema.js'


try {
  const updated = await db
    .update(books)
    .set({ read: true, rating: 5 })
    .where(eq(books.isbn, '9780807083697'))
    .returning({ title: books.title, rating: books.rating })


  console.log('Updated:', updated)


  const rated = await db
    .select({ title: books.title, rating: books.rating })
    .from(books)
    .where(isNotNull(books.rating))
    .orderBy(desc(books.rating))


  console.table(rated)
} finally {
  await pool.end()
}

Run it, then check the unread list:

Bash
npx tsx src/rate.ts
npx tsx src/unread.ts

The rated query returns Kindred with a rating of 5. The unread query returns the two Ursula K. Le Guin books. Refresh Rows in Appwrite to inspect the saved values.

Books table in Appwrite with Kindred marked as read and rated 5
Books table in Appwrite with Kindred marked as read and rated 5

Use Drizzle in your application

Drizzle brings your database schema into TypeScript, so you can write typed queries and catch type errors during development. Drizzle Kit produces SQL migrations you can review and commit alongside your application code. With Appwrite managing Postgres, you can use this workflow in your own production applications.

As your schema grows, you can test migrations on a database branch before applying them to production.

Resources

PostgreSQL quick start

Drizzle integration guide

PostgreSQL connection options

Discord community

Read next

Ready to build?_

Get startedView pricing