UXDL Docs
Backend

PostgreSQL + Drizzle

Node — Drizzle schema, type-safe queries, and migration workflow.

Drizzle ORM is the preferred ORM for new PostgreSQL services. It provides full type safety with minimal runtime overhead.

Install

bash
pnpm add drizzle-orm postgres
pnpm add -D drizzle-kit postgres @types/pg

Schema definition

typescript
// src/db/schema/users.schema.ts
import { pgTable, uuid, varchar, timestamp } from "drizzle-orm/pg-core";
 
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  cognitoSub: varchar("cognito_sub", { length: 255 }).notNull().unique(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});
 
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
typescript
// src/db/schema/projects.schema.ts
import { pgTable, uuid, varchar, timestamp } from "drizzle-orm/pg-core";
import { users } from "./users.schema";
 
export const projects = pgTable("projects", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  ownerId: uuid("owner_id")
    .references(() => users.id)
    .notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Connection

typescript
// src/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";
 
const client = postgres(process.env.DATABASE_URL!, { max: 10 });
export const db = drizzle(client, { schema });

Queries (type-safe)

typescript
// src/services/projects.service.ts
import { eq } from "drizzle-orm";
import { db } from "../db";
import { projects } from "../db/schema/projects.schema";
 
export async function listByOwner(ownerId: string) {
  return db.select().from(projects).where(eq(projects.ownerId, ownerId));
}
 
export async function create(data: { name: string; ownerId: string }) {
  const [project] = await db.insert(projects).values(data).returning();
  return project;
}

Migrations

typescript
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
 
export default defineConfig({
  schema: "./src/db/schema",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
});
bash
pnpm drizzle-kit generate    # Generate migration from schema changes
pnpm drizzle-kit migrate     # Apply migrations
pnpm drizzle-kit studio      # Visual DB browser

Scripts

json
{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio"
  }
}

Official documentation