database

Database

Find the schema here: schema.sql

Commands

Run from the root directory:

• DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:migrate:create <name> - Create a new database migration template
• DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:migrate - Run database migrations
• DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:migrate:down - Rollback database migrations
• DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:reset - Reset database
• DATABASE_URL=postgres://localhost:5432/monorepo-scaffold pnpm --filter @app/db db:seed - Seed database with initial data (default tenant and permissions)

Create a new migration

1. Run db:migrate:create <myname>
2. Edit the file it creates, always add an Up and Down migration.
3. Run db:migrate

Note:

• Do not generate types.gen.ts files yourself - they'll be auto generated.
• Never alter an OWNER in a migration.

Using Kysely Type Helpers

When working with database types in TypeScript, always use Kysely's type helper utilities for proper type safety:

1. Selectable - Use when selecting/reading data from the database:

   import type { Selectable } from 'kysely'
   import type { User } from '@app/db/types'
   
   // Function that returns user data from DB
   async function getUser(id: string): Promise<Selectable<User> | null> {
     return await db.selectFrom('users').where('id', '=', id).selectAll().executeTakeFirst()
   }
   
   // Component props
   interface UserProfileProps {
     user: Selectable<User>
   }
   

2. Insertable - Use when inserting data into the database:

   import type { Insertable } from 'kysely'
   import type { Project } from '@app/db/types'
   
   // Function parameters for creating records
   async function createProject(data: Insertable<Project>): Promise<Selectable<Project>> {
     return await db.insertInto('projects').values(data).returningAll().executeTakeFirstOrThrow()
   }
   
   // Partial inserts with required fields
   async function createUser(data: Partial<Insertable<User>> & { email: string; tenantId: string }) {
     return await db.insertInto('users').values({
       emailVerified: false,
       status: 'active',
       ...data,
     }).returningAll().executeTakeFirstOrThrow()
   }