Managing Database Schemas and Queries with Drizzle ORM

Posted by on

 



Get Drizzle here: https://orm.drizzle.team/




Drizzle: A Lightweight, Type-Safe ORM That Loves SQL

Most real-world applications rely on relational databases like MySQL, PostgreSQL, and SQLite to store critical user data. But working with raw SQL can be painful—even life-threatening when you screw up. Over the decades, hundreds of libraries have been built to abstract away SQL code, hiding its complexity inside object-oriented programming languages. This technique is called Object Relational Mapping (ORM). While ORMs are great, they often bring unnecessary performance overhead and leaky abstractions because developers lose touch with the underlying SQL. Enter Drizzle—a modern ORM that takes a different approach.

What Is Drizzle?

Drizzle is a lightweight set of tools that makes type-safe object-relational mapping fun again. Instead of hiding SQL behind complex abstractions, Drizzle provides a TypeScript API that closely matches native underlying SQL code. This gives you the benefits of type safety and IntelliSense without sacrificing control or performance.

How Drizzle Works

When every database is different, how does Drizzle maintain such a clean and accurate API? Instead of jamming everything into a single interface, Drizzle provides dedicated adapters for each database (PostgreSQL, MySQL, SQLite, etc.), along with tools for popular cloud hosts like RDS and Neon.

If writing raw SQL-like queries feels too low-level for you, Drizzle also offers an optional Prisma-like query API that keeps data fetching and join code concise and readable.

Drizzle Toolkit

  • Drizzle Kit: A CLI for handling database migrations.
  • Drizzle Studio: A GUI you can run locally to manage your data.

Getting Started with Drizzle and Neon

To get started, you’ll need a relational database. Neon—a serverless Postgres platform with a generous free tier—is an excellent choice. It’s easy to use and even has a fast serverless driver with a Drizzle adapter.

Both Drizzle and Neon are designed to run on any JavaScript runtime (Node.js, Bun, Deno) as well as edge runtimes like Cloudflare Workers.

After installing Drizzle and your database driver, connect to the database in your TypeScript code. Then, define your schema using the pgTable function. Each table has a name and columns defined with constraints using JavaScript functions that match their SQL equivalents.

You can reference tables in other tables to create relationships with foreign keys. Optionally, use the relations function to simplify relational queries and joins.

Convert your TypeScript schema into PostgreSQL by running drizzle-kit generate. This creates migration files that you can apply to Neon. Now you’re ready to use Drizzle to insert and query data—with full type safety and intuitive relational support.

Conclusion

Drizzle offers a refreshing take on ORMs—embracing SQL instead of abstracting it away. With strong typing, dedicated adapters, and a powerful yet simple API, it brings joy back to database management.

Shout out to Neon for making this tutorial possible. Be sure to check out their Drizzle tutorial to learn more!




Getting Started with Drizzle ORM

If you're considering Drizzle ORM for your next project, this guide will give you the foundation you need. We’ll cover everything from defining schemas and generating migrations, to working locally, integrating with CI/CD pipelines, and deploying to production. Plus, we’ll explore how Neon fits into the workflow with features like database branching. Let’s dive in.

What is Drizzle ORM?

Drizzle ORM is a TypeScript-first ORM with three main components:

  • The ORM – offers both relational and SQL-like query APIs.
  • Drizzle Kit – the migration tool for generating and managing database migrations.
  • Drizzle Studio – a GUI for exploring your data.

Defining Schemas

In Drizzle, you define your database schema in TypeScript. For example, a users table can be created with columns like id, name, and email. You can also define relations such as posts belonging to users and comments belonging to both posts and users. While these relations are defined at the application level, any database-level constraints must be explicitly set in the schema itself.

Migrations with Drizzle Kit

Once your schema is defined, you use Drizzle Kit to generate migrations. These migrations translate TypeScript schema changes into SQL files. For example, adding createdAt and updatedAt fields to a table generates a new migration automatically. Migrations can then be applied locally, in staging environments, or in production.

Local Development Workflow

During development, you define schemas, generate migrations, and apply them to a local Postgres instance (or a Neon branch). When you’re ready, you push your code and migrations to version control, where they run in your CI/CD pipeline against a preview or staging database. Once merged, migrations are applied to your production database.

Querying Data

Drizzle offers two query styles:

  • SQL-like API – write familiar SQL-style queries with full TypeScript type safety.
  • Relational API – query across multiple related tables easily. For example, fetching posts with their authors and comments becomes a single, type-safe query.

Since queries are fully type-safe, any mismatch between schema and query parameters results in clear compile-time errors. This makes development faster and safer, without relying on code generation.

Why Use Drizzle with Neon?

Neon provides serverless Postgres with database branching, making it a natural fit with Drizzle. You can spin up branches for local development, preview deployments, and staging environments—all integrated into your workflow. Drizzle’s ability to plug into Neon’s serverless driver makes querying fast and seamless in serverless and edge environments.

Conclusion

Drizzle ORM gives you a modern, type-safe, and flexible way to work with relational databases in TypeScript. Combined with Neon, it supports workflows that scale from local development to production. Whether you prefer SQL-like syntax or a relational API, Drizzle keeps your queries safe, predictable, and efficient.

Have questions? Reach out in the Neon Discord community—we’d love to hear from you.


Considering Drizzle ORM for your next project? This guide walks you through the core concepts of defining your schema, generating migrations, writing type-safe queries, and integrating it all into a seamless local and CI workflow, with a special focus on using it with Postgres via Neon's serverless driver and branching capabilities.

The Three Pillars of Drizzle

Drizzle consists of three main components:

  • • Drizzle ORM: The core library offering both a relational and a SQL-like query API.
  • • Drizzle Kit: A companion CLI tool used to generate database migrations.
  • • Drizzle Studio: A GUI for exploring and managing the data inside your database.

The Development Workflow

The typical workflow when using Drizzle follows these steps:

  1. 1. Define Schema: You first define your database schema using TypeScript.
  2. 2. Generate Migration: From the TypeScript definition, Drizzle Kit generates the necessary SQL migration files.
  3. 3. Apply Locally: Apply the migration to your local Postgres instance or a Neon branch.
  4. 4. Preview & Deploy: In your CI/CD pipeline, apply the migration to a preview/staging database (e.g., a Neon branch) and finally to your production database upon merge.

Defining Your Schema

Schemas are defined in TypeScript using Drizzle's `pgTable` function. This provides full type safety from the very beginning without any code generation.

// Example schema definition for a 'users' table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

You can define foreign keys and relations directly in the schema, enabling powerful query capabilities later.

Generating & Running Migrations

After defining your schema, use Drizzle Kit to generate migrations. A script like `bun run db:generate` can execute the command:

drizzle-kit generate --schema=src/db/schema.ts

To apply the generated migrations, you use Drizzle's `migrate` function. A key strength of Drizzle is that it doesn't ship with its own database client; you bring your own. For Neon, you use the serverless driver.

import { drizzle } from 'drizzle-orm/neon-serverless';
import { migrate } from 'drizzle-orm/neon-serverless/migrator';

// Establish connection and run migrations
const db = drizzle(connection);
await migrate(db, { migrationsFolder: './src/db/migrations' });

Writing Type-Safe Queries

Drizzle offers two primary APIs for querying your database:

1. SQL-like API

This API provides a familiar, expressive way to write queries with full type safety and autocompletion.

// Inserting data is fully type-checked
await db.insert(users).values([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
]);

// Selecting data
const allUsers = await db.select().from(users);

2. Relational API

For more complex queries involving multiple tables, the relational API simplifies fetching nested data. This requires defining relations in your schema.

// Fetch all posts with their author and comments
const postsWithRelations = await db.query.posts.findMany({
  with: {
    author: true,
    comments: true,
  }
});

Conclusion

Drizzle ORM provides a robust, type-safe foundation for working with databases in TypeScript. Its intuitive schema definition, powerful migration tools, and flexible query APIs make it an excellent choice for modern application development. Combined with Neon's serverless Postgres and branching features, you can create a highly efficient development and deployment workflow for both preview environments and production.


does drizzle work well with vercel and supabase?

ChatGPT said:

Yes — Drizzle works really well with both Vercel and Supabase. Here’s how:

🔹 Vercel

  • Drizzle is lightweight and TypeScript-native, so it fits perfectly in a Next.js + Vercel environment.

  • You can use it with serverless/edge functions because Drizzle doesn’t bring its own heavy database driver — you choose the adapter (e.g. Neon’s serverless driver, pg, PlanetScale, etc.).

  • This makes it very efficient on Vercel’s serverless runtime.

  • Common setup: Next.js API routes or Route Handlers → import Drizzle → connect with a serverless Postgres (like Neon or Supabase).

🔹 Supabase

  • Supabase is just Postgres + auth + storage + realtime. Since Drizzle is schema-first and built for Postgres, it’s a great fit.

  • You can point Drizzle to your Supabase connection string, define schemas in TypeScript, and generate migrations.

  • Many devs use Drizzle for the ORM + migrations part and Supabase for database hosting, auth, and APIs.

  • Drizzle doesn’t conflict with Supabase’s JS client — you can even mix them (Drizzle for typed queries/migrations, Supabase client for auth and realtime).

👉 A typical modern stack is:

  • Frontend: Next.js on Vercel

  • Database: Supabase (hosted Postgres)

  • ORM: Drizzle (schema + queries + migrations)

That combo gives you typed queries, serverless-friendly connections, and a smooth DX.

Web Services

Comments

Post a Comment