Schema Management

1// Organize schema files by version
2database/
3├── schema/
4│   ├── v1.0.0/
5│   │   ├── 001_users.sql
6│   │   └── 002_posts.sql
7│   ├── v1.1.0/
8│   │   ├── 003_add_user_roles.sql
9│   │   └── 004_posts_categories.sql
10│   └── current/
11│       └── schema.sql
12└── seeds/
13    ├── development.sql
14    └── production.sql
15
16// scripts/schema-manager.js
17import { createDatabaseAdapter } from '@morojs/moro';
18import fs from 'fs';
19import path from 'path';
20
21const db = createDatabaseAdapter('postgresql', {
22  host: 'localhost',
23  user: 'postgres',
24  password: process.env.DB_PASSWORD,
25  database: 'myapp'
26});
27
28export async function applySchema(version) {
29  await db.connect();
30  
31  const schemaDir = path.join('database/schema', version);
32  const files = fs.readdirSync(schemaDir).sort();
33  
34  for (const file of files) {
35    if (file.endsWith('.sql')) {
36      console.log(`Applying: ${file}`);
37      const sql = fs.readFileSync(path.join(schemaDir, file), 'utf8');
38      await db.query(sql);
39    }
40  }
41  
42  console.log(`Schema ${version} applied successfully`);
43  await db.disconnect();
44}

1// scripts/production-schema-update.js
2import { createDatabaseAdapter } from '@morojs/moro';
3
4const db = createDatabaseAdapter('postgresql', {
5  host: process.env.PROD_DB_HOST,
6  user: process.env.PROD_DB_USER,
7  password: process.env.PROD_DB_PASSWORD,
8  database: process.env.PROD_DB_NAME,
9  ssl: true
10});
11
12async function safeSchemaUpdate() {
13  await db.connect();
14  
15  try {
16    // Always use transactions for schema changes
17    await db.transaction(async (tx) => {
18      // Add new column safely
19      await tx.query(`
20        ALTER TABLE users 
21        ADD COLUMN IF NOT EXISTS phone VARCHAR(20)
22      `);
23      
24      // Add index if it doesn't exist
25      await tx.query(`
26        CREATE INDEX IF NOT EXISTS idx_users_phone 
27        ON users(phone) 
28        WHERE phone IS NOT NULL
29      `);
30      
31      // Update existing data if needed
32      await tx.query(`
33        UPDATE users 
34        SET updated_at = CURRENT_TIMESTAMP 
35        WHERE phone IS NULL
36      `);
37    });
38    
39    console.log('✅ Schema update completed successfully');
40  } catch (error) {
41    console.error('❌ Schema update failed:', error);
42    throw error;
43  } finally {
44    await db.disconnect();
45  }
46}
47
48// Run with proper error handling
49safeSchemaUpdate().catch((error) => {
50  console.error('Schema update failed:', error);
51  process.exit(1);
52});

1#!/bin/bash
2# backup-and-migrate.sh
3
4set -e  # Exit on any error
5
6echo "🗄️  Creating database backup..."
7pg_dump $DATABASE_URL > "backup_$(date +%Y%m%d_%H%M%S).sql"
8
9echo "✅ Backup created successfully"
10
11echo "🔄 Applying schema changes..."
12node scripts/schema-update.js
13
14echo "🏥 Testing application health..."
15curl -f http://localhost:3000/health || {
16  echo "❌ Health check failed after schema update"
17  echo "🔄 Consider restoring from backup"
18  exit 1
19}
20
21echo "✅ Schema update completed successfully"