Seeding Database With Drizzle ORM

Database

This post assume using PostgreSQL for database, check orm.drizzle.team/docs/overview for more information how to connnect to your database.

Setup

Dependencies

npm i drizzle-orm pg dotenv
npm i -D drizzle-kit @types/pg esbuild-register @faker-js/faker

Environment Variables

# .env.development
DATABASE_URL=postgres://user:pass@127.0.0.1:5432/db

Drizzle Config

// drizzle.config.ts
import type { Config } from "drizzle-kit";
import * as dotenv from "dotenv";
dotenv.config({ path: "./.env.development" });

if (!("DATABASE_URL" in process.env))
        throw new Error("DATABASE_URL not found on .env.development");

export default {
        schema: "./src/db/schema.ts",
        out: "./src/db/migrations",
        driver: "pg",
        dbCredentials: {
                connectionString: process.env.DATABASE_URL,
        },
        strict: true,
} satisfies Config;

Schema

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

export const users = pgTable("users", {
        id: serial("id").primaryKey(),
        username: text("username"),
        email: varchar("email", { length: 256 }),
});

Scripts

// package.json
{
        "scripts": {
                "drizzle:generate": "drizzle-kit generate:pg",
                "drizzle:push": "drizzle-kit push:pg",
                "drizzle:seed": "node --loader esbuild-register/loader -r esbuild-register ./src/db/seed.ts"
        }
}

Migrations & Push

npm run drizzle:generate
npm run drizzle:push

Seed

// src/db/seed.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { users } from "./schema.ts";
import { faker } from "@faker-js/faker";
import * as dotenv from "dotenv";
dotenv.config({ path: "./.env.development" });

if (!("DATABASE_URL" in process.env))
        throw new Error("DATABASE_URL not found on .env.development");

const main = async () => {
        const client = new Pool({
                connectionString: process.env.DATABASE_URL,
        });
        const db = drizzle(client);
        const data: (typeof users.$inferInsert)[] = [];

        for (let i = 0; i < 20; i++) {
                data.push({
                        username: faker.internet.userName(),
                        email: faker.internet.email(),
                });
        }

        console.log("Seed start");
        await db.insert(users).values(data);
        console.log("Seed done");
};

main();

npm run drizzle:seed

Comments

[Spiff Jekey-Green]

Nice, I like that you did not do the db updates in the loop, something many will fail at.

[Ryan Lee]

WRONG!

[anasrin]

Which one is wrong ?

[Md A. Apu]

Nice write-up. I think tsx package is better suited for the task. Here's an example.

[Mohsin]

I dont see your scripts

[chanidog]

wouldnt mass updating the db like this mess up with its primary key sequence?

[chanidog]

my bad, it wouldnt since you are not setting the primary key thus its auto incrementing on every user you insert. in my case tho i was passing the primary key manually so the sequence was not auto incrementing and was never updated and stayed at the last id in my case 1. so when i went and added a record without an id i got this error:
⨯ NeonDbError: duplicate key value violates unique constraint "users_pkey"

because it was trying to insert the record with an id of 1 but i already added a record with an id of 1.

to fix this i ran a query to update the primary key sequence like this after my seed script

await db.execute(
      sql`SELECT setval(pg_get_serial_sequence('courses', 'id'), (SELECT COALESCE(MAX(id), 0) + 1 FROM courses));`,
    );