Part 2: A Database With PostgreSQL and Drizzle

services:
  postgres:
    image: postgres:18-alpine
    environment:
      POSTGRES_USER: guild
      POSTGRES_PASSWORD: guild
      POSTGRES_DB: guild
    ports:
      - 127.0.0.1:5432:5432
    volumes:
      - postgres-data:/var/lib/postgresql

volumes:
  postgres-data:

PORT=4000
DATABASE_URL=postgres://guild:guild@localhost:5432/guild

allowBuilds:
  esbuild: true

import { index, integer, pgEnum, pgTable, text } from 'drizzle-orm/pg-core';

export const adventurerClass = pgEnum('adventurer_class', ['WARRIOR', 'MAGE', 'ROGUE', 'RANGER']);
export const questStatus = pgEnum('quest_status', ['OPEN', 'ACCEPTED', 'DONE']);

export const guilds = pgTable('guilds', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull(),
  motto: text(),
});

export const adventurers = pgTable(
  'adventurers',
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    name: text().notNull(),
    class: adventurerClass().notNull(),
    level: integer().notNull().default(1),
    guildId: integer()
      .notNull()
      .references(() => guilds.id),
  },
  (table) => [index().on(table.guildId)],
);

export const quests = pgTable(
  'quests',
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    title: text().notNull(),
    reward: integer().notNull(),
    status: questStatus().notNull().default('OPEN'),
    guildId: integer()
      .notNull()
      .references(() => guilds.id),
    assigneeId: integer().references(() => adventurers.id),
  },
  (table) => [index().on(table.guildId), index().on(table.assigneeId)],
);

export type GuildRow = typeof guilds.$inferSelect;
export type AdventurerRow = typeof adventurers.$inferSelect;
export type QuestRow = typeof quests.$inferSelect;

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

export const db = drizzle(process.env.DATABASE_URL!, {
  casing: 'snake_case',
  logger: process.env.LOG_SQL === 'true',
});

export type Database = typeof db;

import { defineConfig } from 'drizzle-kit';

process.loadEnvFile();

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

"db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:seed": "node --env-file=.env src/db/seed.ts"

CREATE TABLE "quests" (
	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "quests_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
	"title" text NOT NULL,
	"reward" integer NOT NULL,
	"status" "quest_status" DEFAULT 'OPEN' NOT NULL,
	"guild_id" integer NOT NULL,
	"assignee_id" integer
);

import { sql } from 'drizzle-orm';
import { db } from './index.ts';
import { adventurers, guilds, quests } from './schema.ts';

await db.execute(sql`truncate quests, adventurers, guilds restart identity cascade`);

const [ember, tide, moss] = await db
  .insert(guilds)
  .values([
    { name: 'Ember Lions', motto: 'First into the fire.' },
    { name: 'Tidecallers', motto: 'The sea remembers.' },
    { name: 'Moss Wardens', motto: 'Slow roots, strong trees.' },
  ])
  .returning();

const heroes = await db
  .insert(adventurers)
  .values([
    { name: 'Brakka', class: 'WARRIOR', level: 7, guildId: ember.id },
    { name: 'Ilse', class: 'MAGE', level: 5, guildId: ember.id },
    { name: 'Corin', class: 'ROGUE', level: 3, guildId: ember.id },
    { name: 'Nerys', class: 'MAGE', level: 8, guildId: tide.id },
    { name: 'Tamsin', class: 'RANGER', level: 4, guildId: tide.id },
    { name: 'Odo', class: 'WARRIOR', level: 2, guildId: tide.id },
    { name: 'Fen', class: 'RANGER', level: 6, guildId: moss.id },
    { name: 'Wren', class: 'ROGUE', level: 5, guildId: moss.id },
    { name: 'Hollis', class: 'MAGE', level: 3, guildId: moss.id },
  ])
  .returning();

const [brakka, ilse, , nerys, tamsin, , fen] = heroes;

await db.insert(quests).values([
  { title: 'Slay the cellar rats', reward: 20, guildId: ember.id, status: 'DONE', assigneeId: brakka.id },
  { title: 'Escort the spice caravan', reward: 120, guildId: ember.id, status: 'ACCEPTED', assigneeId: ilse.id },
  { title: 'Find the lost forge hammer', reward: 80, guildId: ember.id },
  { title: 'Tame the volcano drake', reward: 500, guildId: ember.id },
  { title: 'Chart the sunken reef', reward: 90, guildId: tide.id, status: 'ACCEPTED', assigneeId: tamsin.id },
  { title: 'Calm the storm spirit', reward: 300, guildId: tide.id, status: 'DONE', assigneeId: nerys.id },
  { title: 'Recover the pearl crown', reward: 250, guildId: tide.id },
  { title: 'Clear the harbour of krakenlings', reward: 150, guildId: tide.id },
  { title: 'Guard the seed vault', reward: 60, guildId: moss.id, status: 'ACCEPTED', assigneeId: fen.id },
  { title: 'Wake the old treant', reward: 400, guildId: moss.id },
]);

console.log('Seeded 3 guilds, 9 adventurers and 10 quests.');
await db.$client.end();