Connect Postgres with Drizzle ORM

@ Database

Introduction

Communicate with relational databases via SQL can lead to development efficiency and security issues. Usually we choose a suitable ORM for better development experience, and Drizzle ORM🔗 is my go-to choice and starting point for working with ORMs. It has excellent support in the TypeScript ecosystem, and although it is relatively new, the community is active and it is worth trying out.

Why Choose Drizzle ORM?

The main reason is that compared to other ORMs like Prisma🔗, which can lead to Leaky abstraction🔗, Drizzle provides two forms of API for interacting with the database:

  • Query🔗: Highly abstracted and convenient to use
  • SQL like🔗: Minimal abstraction Query Builder; if you know SQL, you understand Drizzle.

It retains the user-friendly interface of traditional ORMs while offering a way of interacting that is closer to the underlying operations. This, in some ways, reduces the learning curve and avoids technology lock-in, allowing beginners to learn at an appropriate level of abstraction.

There are scary stories about Prisma producing inefficient queries: Migration Lesson: Don’t Use Prisma | Prime Reacts🔗. However, due to excessive abstraction layers, it becomes difficult to handle underlying issues when needed, potentially affecting system scalability and performance. The official Drizzle benchmarks🔗 might be worth checking out.

More benefits include: TypeScript support, no dependencies, full support for Serverless, support for multiple databases (such as PostgreSQL, MySQL, SQLite), and various cloud providers (like Neon, PlanetScale, etc.). More can be found in the official documentation: Why Drizzle🔗.

Drizzle and Postgres

Installing Dependencies

Assuming you have a TS project that needs to communicate with Postgres, first, install the necessary dependencies:

Terminal window
pnpm add drizzle-orm pg
pnpm add -D drizzle-kit tsx @types/pg dotenv

File Structure

The officially recommended file structure🔗 is as follows. In index.ts, Drizzle will interact with the database, but before that, many concepts and settings need to be prepared in advance.

📦 <project root>
 ├ 📂 drizzle
 ├ 📂 src
 │   ├ 📂 db
 │   │  └ 📜 schema.ts
 │   └ 📜 index.ts
 ├ 📜 .env
 ├ 📜 drizzle.config.ts
 ├ 📜 package.json
 └ 📜 tsconfig.json

Connecting to the Database

Add the database connection URL🔗 in .env, typically in the following format. You can refer to Getting Started with Docker and Building Postgres for details on how to start the Postgres server locally.

postgresql://alex:[email protected]/dbname
             └──┘ └───────┘ └─────────────────────────────────────────────┘ └────┘
              ʌ    ʌ          ʌ                                              ʌ
        role -│    │          │- hostname                                    │- database
                   │
                   │- password
DATABASE_URL=

In index.ts, use Drizzle to select the corresponding Postgres driver🔗 for the connection. Next, run the file: pnpm tsx src/index.ts, and you will connect to the database.

index.ts
import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';


const db = drizzle(process.env.DATABASE_URL!);
console.log(db)

Specifically, it communicates with the database through the node-postgres driver. You can refer to the official documentation: Database connection with Drizzle🔗.

Database First vs Code First

After connecting Drizzle ORM to Postgres, there are two development strategies to choose from for managing the mapping relationship between the database and code:

  • Database First
  • Code First

The difference between these two methods lies in whether the “database structure is defined first” or the “Drizzle schema is written first.” Each has its pros and cons and is suitable for different scenarios.

Database First

Design and establish the database first, then let the ORM tool generate the corresponding schema code based on the database structure. This approach is suitable for:

  • Existing databases or systems that need integration

  • Databases designed and maintained by third parties

  • Keeping the consistency of the database design

  • Advantages

    • Easier integration with existing databases
    • Clear database structure, can generate ER diagrams using tools
    • Easier to share databases with non-ORM applications

  • Disadvantages

    • Each change to the database requires manual updates to the schema code (or regeneration)
    • Development relies heavily on the speed of database adjustments

Code First

Write the schema code first, then let the ORM tool automatically create or synchronize the database structure based on the code. Suitable scenarios include:

  • New projects starting from scratch

  • Development teams leading the database structure design

  • Agile development with rapidly changing requirements

  • Advantages

    • Focus on code during the development process, making version control easier
    • Easier to perform automated migrations
    • No need to create a database first; able to focus on business logic development

  • Disadvantages

    • If the database is shared by multiple people, changes in structure need careful management.
    • Less suitable for integrating existing databases.

Performing Database Migration with Drizzle Kit

Terminal window
# Drizzle kit command examples
pnpm drizzle-kit generate
pnpm drizzle-kit migrate
pnpm drizzle-kit push
pnpm drizzle-kit pull
pnpm drizzle-kit check
pnpm drizzle-kit up
pnpm drizzle-kit studio

Drizzle migrations fundamentals🔗 outlines how to synchronize database and code using the drizzle-kit🔗 tool in different scenarios. For example, using the Code First development strategy, start by establishing the “schema code” and then synchronize it to the database.

Defining Database Schema in Code

src/db/schema.ts
import { integer, pgTable, varchar } from "drizzle-orm/pg-core";


export const usersTable = pgTable("users", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar({ length: 255 }).notNull(),
  age: integer().notNull(),
  email: varchar({ length: 255 }).notNull().unique(),
});

Drizzle Kit Configuration

Set up drizzle.config.ts for Drizzle Kit🔗 to understand the setting needed before migration.

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


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

Performing Migration

Terminal window
# drizzle-kit push directly pushes the structure and subsequent structural changes to the database, while skipping SQL file generation.
pnpm drizzle-kit push

Observing Database Changes

Drizzle Studio🔗 is a database preview tool still in beta.

Terminal window
pnpm drizzle-kit studio


Warning  Drizzle Studio is currently in Beta. If you find anything that is not working as expected or should be improved, feel free to create an issue on GitHub: https://github.com/drizzle-team/drizzle-kit-mirror/issues/new or write to us on Discord: https://discord.gg/WcRKz2FFxN


Drizzle Studio is up and running on https://local.drizzle.studio

Summary

Drizzle ORM is a lightweight, modern ORM tool with a good TypeScript experience. Whether you prefer a highly abstracted approach or want to retain flexibility close to SQL, it provides a flexible development experience.

Once you are familiar with the basic connection and migration processes, you can focus solely on query and schema design, which I believe will be quite manageable for developers familiar with SQL.

Further Reading