๐ข๏ธ Database Development
A comprehensive guide to working with the database layer in NestFlux using Postgres and Drizzle ORM.
๐ฏ Development Philosophyโ
NestFlux's database layer is built around modern development practices:
- Type Safety: Full TypeScript integration with compile-time validation
- Schema-First: Define your database structure in code, not SQL
- Migration-Based: Version-controlled database changes
- Developer Experience: Intuitive API with excellent tooling support
- Code Organization: Structured approach to table definitions and relationships
๐๏ธ Database Architectureโ
The database layer is organized within the server project (apps/server
) with a clear structure:
apps/server/src/database/
โโโ schemas/
โ โโโ main.schema.ts # Main schema export file
โ โโโ main/
โ โโโ tables/
โ โโโ identity/ # Folder organization
โ โโโ user.table.ts
โ โโโ ...
โโโ services/ # Database service layer
โโโ migrations/ # Generated migration files
๐ Working with Tablesโ
Creating New Tablesโ
To add a new table to your database schema:
- Create the table file in the appropriate directory under
apps/server/src/database/schemas/main/tables/
- Define the table structure using Drizzle's Postgres table syntax with appropriate column types, constraints, and relationships
- Export the table and types for TypeScript inference
- Add the export to
main.schema.ts
to include it in the database schema - Generate and apply migration using
pnpm db:generate
followed bypnpm db:migrate
Adding Columnsโ
To add new columns to existing tables:
- Modify the table definition by adding the new column definitions
- Consider default values for existing records if the column is NOT NULL
- Add appropriate constraints such as unique, foreign key, or check constraints
- Generate migration to create the ALTER TABLE statements
- Review the generated SQL to ensure it matches your expectations
- Apply the migration to update your database structure
Modifying Columnsโ
To change existing column properties:
- Update the column definition with new type, length, or constraint specifications
- Consider data compatibility - ensure existing data can be converted to the new format
- Plan data migration if the change requires transforming existing values
- Generate migration which will create appropriate ALTER TABLE statements
- Test thoroughly especially when changing data types or adding NOT NULL constraints
Removing Columnsโ
To remove columns from existing tables:
- Remove the column definition from the table schema
- Consider data preservation - ensure you don't need the data or have backed it up
- Update application code to remove any references to the deleted column
- Generate migration which will create DROP COLUMN statements
- Apply with caution as this operation is irreversible and will permanently delete data
Foreign Key Relationshipsโ
When working with relationships between tables:
- Define foreign key columns with matching data types to the referenced primary key
- Add foreign key constraints using Drizzle's foreignKey syntax
- Specify cascade behavior for updates and deletes (CASCADE, RESTRICT, SET NULL)
- Ensure referenced tables exist before creating the relationship
- Consider index creation on foreign key columns for performance
โ ๏ธ Important: Always consider data migration implications when making structural changes in production.
๐ Migration Commandsโ
Drizzle provides two main commands for schema management:
pnpm db:generate
โ
Purpose: Generates migration files based on schema changes.
When to use:
- After modifying any schema files in
src/database/schemas/
- When adding new tables, columns, or relationships
- When changing existing table structures
What it does:
- Compares current schema with existing migrations
- Creates new migration files in
apps/server/drizzle/
- Generates SQL DDL statements for the changes
# From project root
pnpm db:generate
# Or from server directory
cd apps/server && pnpm db:generate
Example Output:
โ Generated migration file: 0004_amazing_spiderman.sql
pnpm db:migrate
โ
Purpose: Applies pending migrations to your database.
When to use:
- After running
pnpm db:generate
- When setting up a fresh database
- After pulling changes that include new migrations
What it does:
- Executes SQL migration files against your database
- Updates the database structure to match your schema
- Tracks which migrations have been applied
# From project root
pnpm db:migrate
# Or from server directory
cd apps/server && pnpm db:migrate
Example Output:
โ Applied migration: 0004_amazing_spiderman.sql
Database is up to date!
๐จ Best Practicesโ
Schema Organizationโ
- Group related tables in folders (e.g.,
identity/
,blog/
,commerce/
) - Use descriptive names for tables and columns
- Follow naming conventions: snake_case for database, camelCase for TypeScript
- Keep table files focused - one table per file
Migration Safetyโ
- Always review generated migrations before applying
- Test migrations on development data first
- Plan for rollback scenarios in production
- Never edit existing migration files - create new ones
Performance Considerationsโ
- Add indexes for frequently queried columns
- Use appropriate column types and lengths
- Consider partitioning for very large tables
- Avoid nullable foreign keys when possible
Development vs Productionโ
- Staging: Always use migrations to test the deployment process
- Production: Only apply well-tested migrations with proper backup procedures
๐ง Troubleshootingโ
Common Issuesโ
Schema Export Errors:
// Make sure all tables are exported in main.schema.ts
export { newTable } from "@database/schemas/main/tables/category/new.table";
Foreign Key Errors:
- Ensure referenced tables exist
- Check column types match between tables
- Verify foreign key constraints are properly defined
๐ Advanced Resourcesโ
This guide covers the fundamental patterns for database development in NestFlux. As your application grows, consider implementing additional patterns like database seeding, connection pooling optimization, and read/write replicas.