Database
Schema Management
MoroJS doesn't include a built-in migration system. Instead, manage your database schema using your database's native tools or third-party migration libraries.
Schema Versioning with SQL Files
Schema Versioning with SQL Filestypescript
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}Schema Management Best Practices
- Version your schema files in source control
- Use CREATE TABLE IF NOT EXISTS for safety
- Test schema changes on development first
- Keep backups before schema changes
- Use transactions for multi-step changes
- Document schema changes and reasoning
Production Schema Management
Safe Production Schema Updatestypescript
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});Database Backup Before Changestypescript
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"