Drizzle with PostgreSQL Setup

Setting up Drizzle for PostgreSQL involves several steps, including setting up the environment, defining schemas, configuring it, and updating our database.

Drizzle has less fluff than most ORMs, which makes the initial setup a little longer than other ORMs, but when it's done, it's worth it.

But don't worry; follow these steps, and you'll be up and running:

Installation

In your project, you'll need to install the necessary packages:

npm i drizzle-orm postgres
npm i -D drizzle-kit

This installs both Drizzle and the Postgres client and then Drizzle Kit so we can sync our changes with the database.

Configuring Drizzle

Next, add a drizzle.config.ts file to the root of your project to specify your schema and output directory for migrations:

import "dotenv/config";
import type {Config} from "drizzle-kit";
export default {
  schema: "./db/schema.ts", // Your schema location
  out: "./drizzle", // Where our migrations will be outputted
  driver: "pg", // PostgreSQL driver
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

I'm using an environment variable to pass in the connection string to connect to our database.

Connect Drizzle to Your Database

Let's create a little database utility that we can import and use wherever in our project:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const queryClient = postgres(process.env.DATABASE_URL!);
// We will use the exported variable to query our db:
export const db = drizzle(queryClient);

Creating a Schema

Define your database schema in a schema.ts file.

I've added mine to a db folder.

Here, you'll define your tables and fields using Drizzle's syntax. For instance, to define a user table, you might write:

import { serial, text, timestamp, pgTable } from "drizzle-orm/pg-core";

export const users = pgTable("user", {
  id: serial("id"),
  name: text("name"),
  email: text("email"),
});

This creates a table with fields for the id, name, and email fields.

Now let's push these changes to our database.

Push your schema changes directly to the database without generating any migrations files using the following command:

npx drizzle-kit push:pg

Now, we can write queries using Drizzle ORM:

Example Operations

With Drizzle, it's easy and very similar to SQL to query data.

For example, to insert a new user, you might use:

// Our client we created earlier:
import { db } from "./client";
// Import the schema we want to operate on:
import { users } from "./db/schema";

export const createUser = async (name: string, email: string) => {
    const response = await db
      .insert(users)
      .values({ name, email })
      .returning({ newUserId: users.id });
    return response;
  };

And to fetch all users, you could write:

import { db } from "./client";
import { users } from "./schema";

export const getUsers = async () => {
  const response = await db.select().from(users);
  return response;
};

I hope these operations give you a taste of how easy it is to work with Drizzle.

For more detailed instructions and advanced configurations, refer to the Drizzle ORM documentation and additional resources provided (link here.