Database Migrations

Database Migrations

Manacore uses Drizzle ORM for database management with PostgreSQL.

Overview

Each backend service has its own database and schema:

Service	Database	Port
mana-core-auth	manacore	3001
chat	chat	3002
zitare	zitare	3007
contacts	contacts	3015
calendar	calendar	3014

Quick Commands

# Push schema changes (development)
pnpm --filter @chat/backend db:push

# Generate migration files
pnpm --filter @chat/backend db:generate

# Run migrations
pnpm --filter @chat/backend db:migrate

# Open Drizzle Studio (database GUI)
pnpm --filter @chat/backend db:studio

Development Workflow

For local development, use db:push to quickly sync schema changes:

1. Modify your schema

   Edit the schema file (e.g., src/drizzle/schema.ts):

   export const users = pgTable('users', {
     id: uuid('id').primaryKey().defaultRandom(),
     email: varchar('email', { length: 255 }).notNull().unique(),
     name: varchar('name', { length: 255 }),
     createdAt: timestamp('created_at').defaultNow(),
     // Add new field
     avatarUrl: varchar('avatar_url', { length: 500 }),
   });

2. Push changes

   pnpm --filter @chat/backend db:push

3. Verify in Studio

   pnpm --filter @chat/backend db:studio

Tip

db:push is fast for development but doesn’t create migration files. Use db:generate for production migrations.

Production Migrations

For production, use proper migration files:

1. Generate migration

   pnpm --filter @chat/backend db:generate

   This creates a timestamped SQL file in drizzle/migrations/.

2. Review migration

   Check the generated SQL:

   -- 0001_add_avatar_url.sql
   ALTER TABLE "users" ADD COLUMN "avatar_url" varchar(500);

3. Run migration

   pnpm --filter @chat/backend db:migrate

Schema Patterns

Basic Table

import { pgTable, uuid, varchar, timestamp, boolean } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  published: boolean('published').default(false),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

Foreign Keys

export const comments = pgTable('comments', {
  id: uuid('id').primaryKey().defaultRandom(),
  postId: uuid('post_id')
    .notNull()
    .references(() => posts.id, { onDelete: 'cascade' }),
  userId: uuid('user_id')
    .notNull()
    .references(() => users.id),
  content: text('content').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

Indexes

import { pgTable, uuid, varchar, index } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  organizationId: uuid('organization_id'),
}, (table) => ({
  emailIdx: index('users_email_idx').on(table.email),
  orgIdx: index('users_org_idx').on(table.organizationId),
}));

Enums

import { pgEnum } from 'drizzle-orm/pg-core';

export const roleEnum = pgEnum('role', ['user', 'admin', 'moderator']);

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  role: roleEnum('role').default('user'),
});

Drizzle Configuration

Each backend has a drizzle.config.ts:

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/drizzle/schema.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Connecting in Services

NestJS Module

import { DrizzleModule } from '@manacore/shared-drizzle';

@Module({
  imports: [
    DrizzleModule.forRootAsync({
      imports: [ConfigModule],
      useFactory: (config: ConfigService) => ({
        connectionString: config.get('DATABASE_URL'),
      }),
      inject: [ConfigService],
    }),
  ],
})
export class AppModule {}

Using in Services

import { Inject, Injectable } from '@nestjs/common';
import { DRIZZLE } from '@manacore/shared-drizzle';
import { eq } from 'drizzle-orm';
import { users } from '../drizzle/schema';

@Injectable()
export class UsersService {
  constructor(@Inject(DRIZZLE) private db: DrizzleDB) {}

  async findById(id: string) {
    const [user] = await this.db
      .select()
      .from(users)
      .where(eq(users.id, id));
    return user;
  }

  async create(data: { email: string; name: string }) {
    const [user] = await this.db
      .insert(users)
      .values(data)
      .returning();
    return user;
  }
}

Troubleshooting

”relation does not exist”

Schema not pushed:

pnpm --filter @chat/backend db:push --force

Migration conflicts

Reset and regenerate:

# Delete old migrations
rm -rf apps/chat/apps/backend/drizzle/migrations/*

# Regenerate
pnpm --filter @chat/backend db:generate

Connection refused

Check PostgreSQL is running:

pnpm docker:up
docker ps | grep postgres

Best Practices

1. Always review generated migrations before running in production
2. Use transactions for multi-step operations
3. Test migrations on a copy of production data
4. Keep schemas in sync across services that share data
5. Use db:push for development, db:migrate for production