This document details the database layer of the CityU Vet Sim project, focusing on Drizzle ORM, PostgreSQL, schema definitions, and migration procedures.
If you want to check the schema and tables, click here:
src/server/db/schema.ts)drizzle.config.ts)src/server/db/index.ts)Drizzle ORM is a TypeScript ORM used to interact with the PostgreSQL database. It provides a type-safe and performant way to define database schemas and perform queries, mutations, and migrations directly within TypeScript code.
The project uses PostgreSQL as its relational database. PostgreSQL is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance.
drizzle.config.ts)The drizzle.config.ts file configures the Drizzle Kit CLI for database operations.
schema: Points to the schema definition file (./src/server/db/schema.ts).dialect: Specifies the database dialect ("postgresql").dbCredentials.url: Retrieves the database connection URL from environment variables (env.DATABASE_URL).tablesFilter: Filters tables managed by Drizzle to those prefixed with cityu-vet-sim_.// drizzle.config.ts
import { type Config } from "drizzle-kit";
import { env } from "@/env";
export default {
schema: "./src/server/db/schema.ts",
dialect: "postgresql",
dbCredentials: {
url: env.DATABASE_URL,
},
tablesFilter: ["cityu-vet-sim_*"],
} satisfies Config;
src/server/db/index.ts)This file establishes and exports the Drizzle ORM database connection.
postgres library to connect to the PostgreSQL database.drizzle function initializes the ORM with the connection and the defined schema.// src/server/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { env } from "@/env";
import * as schema from "./schema";
const globalForDb = globalThis as unknown as {
conn: postgres.Sql | undefined;
};
const conn = globalForDb.conn ?? postgres(env.DATABASE_URL);
if (env.NODE_ENV !== "production") globalForDb.conn = conn;
export const db = drizzle(conn, { schema });
Drizzle Kit is used to manage database schema migrations. The package.json scripts provide commands for this:
"db:generate": Generates new migration files based on changes in src/server/db/schema.ts."db:migrate": Applies pending migrations to the database."db:push": Pushes schema changes directly to the database without generating migration files (primarily for development)."db:studio": Opens the Drizzle Studio UI for inspecting database data.Migration files are stored in the drizzle/ directory (e.g., drizzle/0000_violet_supreme_intelligence.sql). These files contain the SQL commands necessary to evolve the database schema over time. The drizzle/meta/_journal.json file tracks the applied migrations.