Use when the user asks to create a new database table, add or modify columns, add indexes or relations, or generate/run database migrations. Use when editing files in backend/src/db/ or backend/framework/src/lib/db/. Use when the user mentions Drizzle ORM schema definitions.
This skill helps you create and manage database tables in the business-app using Drizzle ORM with PostgreSQL.
Use this skill when:
The project uses a dual-configuration setup:
backend/framework/ with prefix base_backend/src/db/schema.ts with prefix app_Add your table definition to backend/src/db/schema.ts:
import { pgBaseTable } from "@framework/lib/db/schema";
import { uuid, varchar, timestamp, index } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
export const myTable = pgBaseTable(
"my_table",
{
id: uuid("id")
.primaryKey()
.default(sql`gen_random_uuid()`),
tenantId: uuid("tenant_id")
.references(() => tenants.id, { onDelete: "cascade" })
.notNull(),
name: varchar("name", { length: 255 }).notNull(),
createdAt: timestamp("created_at", { mode: "string" })
.notNull()
.defaultNow(),
updatedAt: timestamp("updated_at", { mode: "string" })
.notNull()
.defaultNow(),
},
(table) => [
index("my_table_tenant_id_idx").on(table.tenantId),
]
);
If your table relates to other tables:
import { relations } from "drizzle-orm";
export const myTableRelations = relations(myTable, ({ one }) => ({
tenant: one(tenants, {
fields: [myTable.tenantId],
references: [tenants.id],
}),
}));
export type MyTableSelect = typeof myTable.$inferSelect;
export type MyTableInsert = typeof myTable.$inferInsert;
export const myTableSelectSchema = createSelectSchema(myTable);
export const myTableInsertSchema = createInsertSchema(myTable);
export const myTableUpdateSchema = createUpdateSchema(myTable);
Navigate to the backend directory and run:
cd backend
bun run generate
This generates migration files in backend/drizzle-sql/ for both framework and app schemas.
Run the migration to apply changes to the database:
bun run migrate
This applies migrations for both framework and app schemas.
Define enums before tables:
export const statusEnum = pgEnum("status", ["active", "inactive", "pending"]);
Always include onDelete behavior:
tenantId: uuid("tenant_id")
.references(() => tenants.id, { onDelete: "cascade" })
.notNull(),
Add indexes for frequently queried columns:
(table) => [
index("my_table_tenant_id_idx").on(table.tenantId),
index("my_table_name_idx").on(table.name),
]
Always include createdAt and updatedAt:
createdAt: timestamp("created_at", { mode: "string" })
.notNull()
.defaultNow(),
updatedAt: timestamp("updated_at", { mode: "string" })
.notNull()
.defaultNow(),
backend directory: The commands bun run generate and bun run migrate must be executed from the backend directoryapp_ prefix (defined in backend/src/db/index.ts)pgBaseTable from @framework/lib/db/schema which applies the base_ prefixbackend/drizzle-sql/ with timestamped names