Database Module Guide - Drizzle or Prisma for Express

Overview

Add production-ready PostgreSQL or MySQL database access to your backend with Drizzle or Prisma.

Install

npx zuro add database

Adds database scaffolding, installs dependencies, updates .env, and patches src/env.ts with DATABASE_URL.

What This Generates

src/
├ db/
│ ├ index.ts
│ └ schema/
│   └ index.ts     # Drizzle only
└ env.ts           # updated with DATABASE_URL schema

drizzle.config.ts  # Drizzle only
prisma/
└ schema.prisma    # Prisma only

src/db/index.ts: exports your database client (db).
src/db/schema/index.ts: Drizzle schema export file.
drizzle.config.ts: Drizzle migration config.
prisma/schema.prisma: Prisma datasource and client generator.
src/env.ts: DATABASE_URL validation.

Quick Example

npx zuro add database
# prompt: Which ORM? -> Drizzle or Prisma
# prompt: Which database dialect? -> PostgreSQL or MySQL
# prompt: Database URL -> (leave blank for default)

Drizzle usage:

import { db } from "../db";
import { sql } from "drizzle-orm";

const result = await db.execute(sql`select 1 as ok`);
console.log(result);

Prisma usage:

import { db } from "../db";

await db.$queryRaw`SELECT 1`;

Example result:

[{ "ok": 1 }]

How It Works

CLI prompts for ORM, dialect, and DATABASE_URL.
Dependencies are installed for the selected variant.
DB files are generated (src/db/*, config files).
.env and src/env.ts are updated.
You run migrations.
Use db in routes/services.

Configuration

DATABASE_URL=postgresql://postgres@localhost:5432/mydb

DATABASE_URL: Connection string used by Drizzle/Prisma client and migration tools.

MySQL example:

DATABASE_URL=mysql://root@localhost:3306/mydb

API Reference

No HTTP endpoints are generated by database.
Exports a database client from src/db/index.ts:
db (Drizzle): typed query builder for your SQL tables.
db (Prisma): PrismaClient instance alias.

Advanced Usage

Install a specific variant directly:

npx zuro add database-pg
npx zuro add database-mysql
npx zuro add database-prisma-pg
npx zuro add database-prisma-mysql

Define and export a Drizzle schema:

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

export const users = pgTable("users", {
  id: text("id").primaryKey(),
});

// src/db/schema/index.ts
export * from "./users";

Run migrations:

# Drizzle
npx drizzle-kit generate
npx drizzle-kit migrate

# Prisma
npx prisma migrate dev --name init
npx prisma generate

Switching dialect/ORM safely:

If an existing DB setup is detected, Zuro asks for confirmation and creates a backup in .zuro/backups/.

Example Use Cases

User authentication storage for users, sessions, and tokens.
Webhook event persistence for retries and idempotency.
Background jobs metadata and processing history.
File uploads metadata storage when using the uploads module.

Schema Design (Drizzle)

Define tables in src/db/schema/ and export them from src/db/schema/index.ts.

Create a users table:

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

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Export from the schema index:

// src/db/schema/index.ts
export * from "./users";

Re-run migrations after any schema change:

npx drizzle-kit generate
npx drizzle-kit migrate

Querying Data (Drizzle)

Insert a record:

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

await db.insert(users).values({
  id: crypto.randomUUID(),
  email: "ava@example.com",
  name: "Ava",
});

Select with a filter:

import { eq } from "drizzle-orm";

const user = await db.select().from(users).where(eq(users.email, "ava@example.com")).limit(1);

Update a record:

await db.update(users).set({ name: "Ava Smith" }).where(eq(users.id, "user_123"));

Delete a record:

await db.delete(users).where(eq(users.id, "user_123"));

Querying Data (Prisma)

Insert:

import { db } from "../db";

await db.user.create({
  data: { id: crypto.randomUUID(), email: "ava@example.com", name: "Ava" },
});

Find:

const user = await db.user.findUnique({ where: { email: "ava@example.com" } });

Troubleshooting

Cannot connect to database Verify your DATABASE_URL is correct and the database server is running:

# PostgreSQL — test connection
psql $DATABASE_URL -c "SELECT 1"

relation "users" does not exist You need to run migrations before querying:

npx drizzle-kit generate
npx drizzle-kit migrate

Column does not exist after schema change Generate and apply a new migration after every schema edit:

npx drizzle-kit generate  # creates a new migration file
npx drizzle-kit migrate   # applies it to the database

MySQL ER_NOT_SUPPORTED_AUTH_MODE error Your MySQL server may require the legacy auth plugin. Update your connection string or run:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Prisma client out of sync After schema changes, regenerate the Prisma client:

npx prisma generate

Database

On this page