How to Integrate Drizzle ORM with Astro: Complete Guide
Step-by-step guide to integrating Drizzle ORM with your Astro website. Setup, configuration, and best practices.
How to Integrate Drizzle ORM with Astro: Complete Guide
Drizzle ORM is a lightweight, type-safe SQL toolkit for TypeScript that generates zero runtime overhead. Unlike heavier ORMs that abstract away SQL entirely, Drizzle lets you write queries that map directly to SQL while giving you full TypeScript autocompletion and type checking. Combined with Astro's server-side capabilities, it provides a solid data layer for dynamic websites and applications.
This guide covers setting up Drizzle ORM in an Astro project from scratch, defining schemas, running queries, and handling migrations.
Prerequisites
Before getting started, you will need:
- Node.js 18+ installed
- An existing Astro project (v3.0+ recommended)
- A supported database: PostgreSQL, MySQL, or SQLite
- Basic TypeScript knowledge
- Astro configured with an SSR adapter (Node, Vercel, Cloudflare, etc.)
This guide uses PostgreSQL as the database, but Drizzle supports all three databases with the same API patterns.
Installation
Install Drizzle ORM, the Drizzle Kit CLI for migrations, and the PostgreSQL driver:
npm install drizzle-orm postgres
npm install -D drizzle-kit
If you are using MySQL or SQLite instead, swap postgres for mysql2 or better-sqlite3 respectively.
Make sure your Astro project has an SSR adapter installed:
npx astro add node
Configuration
Environment Variables
Add your database connection string to .env:
DATABASE_URL=postgresql://username:password@localhost:5432/your_database
Drizzle Configuration File
Create drizzle.config.ts in your project root. This tells Drizzle Kit where your schema files are and how to connect to the database for migrations:
// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
schema: './src/db/schema.ts',
out: './drizzle',
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});
Defining Your Schema
Create a schema file that describes your database tables using Drizzle's TypeScript-first approach:
// src/db/schema.ts
import { pgTable, serial, text, timestamp, boolean } from 'drizzle-orm/pg-core';
export const posts = pgTable('posts', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
slug: text('slug').notNull().unique(),
content: text('content'),
published: boolean('published').default(false),
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at').defaultNow(),
});
export const categories = pgTable('categories', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
slug: text('slug').notNull().unique(),
});
Creating the Database Client
Set up a reusable database client that your Astro pages and API routes can import:
// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
const client = postgres(import.meta.env.DATABASE_URL);
export const db = drizzle(client, { schema });
Running Migrations
Generate migration files from your schema and apply them:
npx drizzle-kit generate
npx drizzle-kit migrate
This creates SQL migration files in the ./drizzle folder and runs them against your database. You can also use npx drizzle-kit push during development to push schema changes directly without generating migration files.
Common Patterns
Querying Data in Astro Pages
Import the database client and use Drizzle's query builder in your page frontmatter:
---
// src/pages/blog.astro
import { db } from '../db';
import { posts } from '../db/schema';
import { eq, desc } from 'drizzle-orm';
const allPosts = await db
.select()
.from(posts)
.where(eq(posts.published, true))
.orderBy(desc(posts.createdAt));
---
<html>
<body>
<h1>Blog</h1>
{allPosts.map((post) => (
<article>
<h2><a href={`/blog/${post.slug}`}>{post.title}</a></h2>
<time>{post.createdAt?.toLocaleDateString()}</time>
</article>
))}
</body>
</html>
Every query is fully typed. Your editor will autocomplete column names and catch type errors at build time.
API Routes with Mutations
Create API endpoints that insert or update data:
// src/pages/api/posts.ts
import type { APIRoute } from 'astro';
import { db } from '../../db';
import { posts } from '../../db/schema';
export const POST: APIRoute = async ({ request }) => {
const body = await request.json();
const newPost = await db.insert(posts).values({
title: body.title,
slug: body.slug,
content: body.content,
}).returning();
return new Response(JSON.stringify(newPost[0]), {
status: 201,
headers: { 'Content-Type': 'application/json' },
});
};
Relational Queries
Drizzle supports relational queries when you define relations in your schema. This lets you fetch related data in a single query without manual joins:
// src/db/schema.ts (add relations)
import { relations } from 'drizzle-orm';
export const postsRelations = relations(posts, ({ one }) => ({
category: one(categories, {
fields: [posts.categoryId],
references: [categories.id],
}),
}));
Troubleshooting
"Cannot find module 'postgres'" at runtime: Make sure you installed the postgres package (not pg). Drizzle's postgres-js adapter requires the postgres npm package specifically.
Type errors on schema columns: Drizzle infers types from your schema definition. If you added a column but see type errors, regenerate your migration files and make sure the schema file is saved before running queries.
Migrations not applying: Check that your DATABASE_URL in the environment matches what is in drizzle.config.ts. The config file uses process.env while Astro pages use import.meta.env, so both need to be set.
Connection pool exhaustion: The postgres driver manages its own connection pool. In serverless environments, set a lower pool size: postgres(url, { max: 5 }) to avoid exceeding your database's connection limit.
Slow development workflow: Use drizzle-kit push during development instead of generate + migrate. It applies schema changes directly and is faster for iterating.
Conclusion
Drizzle ORM brings type-safe database access to Astro without the overhead of traditional ORMs. The schema-as-code approach means your TypeScript types always match your database structure, catching errors before they reach production. Combined with Astro's hybrid rendering, you can build data-driven pages that are fast to develop and reliable in production. Start with a simple schema, use push during development, and switch to proper migrations when you are ready to deploy.
Related Articles
How to Use Algolia with Astro: Complete Guide
Step-by-step guide to integrating Algolia with your Astro website.
How to Integrate Auth0 with Astro: Complete Guide
Step-by-step guide to integrating Auth0 with your Astro website. Setup, configuration, and best practices.
How to Use AWS Amplify with Astro: Complete Guide
Step-by-step guide to integrating AWS Amplify with your Astro website.