Database Integration

1import { createApp, createDatabaseAdapter, MySQLAdapter, PostgreSQLAdapter, SQLiteAdapter, z, validate } from '@morojs/moro';
2
3const app = createApp({
4  cors: true,
5  compression: true,
6  helmet: true
7});
8
9// Example 1: Using the factory function (recommended)
10const sqliteDb = createDatabaseAdapter('sqlite', {
11  filename: 'app.db',
12  memory: false
13});
14
15// Example 2: Direct instantiation for more control
16const mysqlDb = new MySQLAdapter({
17  host: 'localhost',
18  port: 3306,
19  user: 'root',
20  password: 'password',
21  database: 'myapp',
22  connectionLimit: 10
23});
24
25// Initialize database connection
26async function initializeDatabase() {
27  try {
28    await sqliteDb.connect();
29    
30    // Create tables if they don't exist
31    await sqliteDb.query(`
32      CREATE TABLE IF NOT EXISTS users (
33        id INTEGER PRIMARY KEY AUTOINCREMENT,
34        name TEXT NOT NULL,
35        email TEXT UNIQUE NOT NULL,
36        age INTEGER NOT NULL,
37        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
38      )
39    `);
40    
41    console.log('Database initialized successfully');
42  } catch (error) {
43    console.error('Database initialization failed:', error);
44  }
45}
46
47// User schema for validation
48const UserSchema = z.object({
49  name: z.string().min(2).max(50),
50  email: z.string().email(),
51  age: z.number().int().min(18).max(120)
52});
53
54// Use database adapter in routes
55app.get('/users', async (req, res) => {
56  try {
57    const users = await sqliteDb.query('SELECT * FROM users ORDER BY created_at DESC');
58    return { 
59      success: true, 
60      data: users,
61      adapter: 'SQLite'
62    };
63  } catch (error) {
64    res.status(500);
65    return { 
66      success: false, 
67      error: 'Failed to fetch users'
68    };
69  }
70});
71
72app.post('/users', 
73  validate({ body: UserSchema }),
74  async (req, res) => {
75    try {
76      const userData = req.body;
77      const newUser = await sqliteDb.insert('users', userData);
78      
79      res.status(201);
80      return { 
81        success: true, 
82        data: newUser,
83        message: 'User created successfully'
84      };
85    } catch (error) {
86      res.status(500);
87      return { 
88        success: false, 
89        error: 'Failed to create user'
90      };
91    }
92  }
93);
94
95// Initialize and start
96initializeDatabase().then(() => {
97  app.listen(3000, () => {
98    console.log('Server running on http://localhost:3000');
99  });
100});

1import { createApp, createDatabaseAdapter, MySQLAdapter, PostgreSQLAdapter } from '@morojs/moro';
2
3const app = createApp({
4  cors: true,
5  compression: true
6});
7
8// Primary database (PostgreSQL)
9const primaryDb = createDatabaseAdapter('postgresql', {
10  host: 'localhost',
11  port: 5432,
12  user: 'postgres',
13  password: process.env.DB_PASSWORD,
14  database: 'myapp_primary',
15  connectionLimit: 20
16});
17
18// Analytics database (separate PostgreSQL instance)
19const analyticsDb = new PostgreSQLAdapter({
20  host: 'analytics.example.com',
21  port: 5432,
22  user: 'analytics_user',
23  password: process.env.ANALYTICS_DB_PASSWORD,
24  database: 'myapp_analytics',
25  connectionLimit: 10
26});
27
28// SQLite for local caching
29const cacheDb = createDatabaseAdapter('sqlite', {
30  filename: 'cache.db',
31  memory: false
32});
33
34// Initialize all database connections
35async function initializeDatabases() {
36  try {
37    await Promise.all([
38      primaryDb.connect(),
39      analyticsDb.connect(),
40      cacheDb.connect()
41    ]);
42    
43    console.log('All databases connected successfully');
44  } catch (error) {
45    console.error('Database initialization failed:', error);
46    throw error;
47  }
48}
49
50// Use specific database adapters in routes
51app.get('/users/:id', async (req, res) => {
52  try {
53    const { id } = req.params;
54    
55    // Query primary database
56    const user = await primaryDb.queryOne(
57      'SELECT * FROM users WHERE id = ?',
58      [parseInt(id)]
59    );
60    
61    if (!user) {
62      res.status(404);
63      return { success: false, error: 'User not found' };
64    }
65    
66    // Query analytics database
67    const analytics = await analyticsDb.query(
68      'SELECT * FROM user_analytics WHERE user_id = ?',
69      [parseInt(id)]
70    );
71    
72    // Check cache database
73    const cachedData = await cacheDb.queryOne(
74      'SELECT data FROM user_cache WHERE user_id = ? AND expires_at > datetime("now")',
75      [parseInt(id)]
76    );
77    
78    return {
79      success: true,
80      data: {
81        user,
82        analytics,
83        cached: cachedData ? JSON.parse(cachedData.data) : null
84      }
85    };
86  } catch (error) {
87    res.status(500);
88    return {
89      success: false,
90      error: 'Failed to fetch user data'
91    };
92  }
93});
94
95// Initialize and start
96initializeDatabases().then(() => {
97  app.listen(3000);
98});

1// Database adapter provides these methods:
2
3// Connection management
4await db.connect();
5await db.disconnect();
6
7// Basic queries
8const users = await db.query('SELECT * FROM users');
9const user = await db.queryOne('SELECT * FROM users WHERE id = ?', [1]);
10
11// CRUD operations
12const newUser = await db.insert('users', {
13  name: 'John Doe',
14  email: 'john@example.com',
15  age: 30
16});
17
18const updatedUser = await db.update('users', 
19  { name: 'Jane Doe' }, 
20  { id: 1 }
21);
22
23const deletedCount = await db.delete('users', { id: 1 });
24
25// Transactions
26const result = await db.transaction(async (tx) => {
27  const user = await tx.insert('users', userData);
28  await tx.insert('user_profiles', { user_id: user.id, ...profileData });
29  return user;
30});
31
32// Raw SQL with parameters
33const complexQuery = await db.query(`
34  SELECT u.*, p.title, p.content 
35  FROM users u 
36  LEFT JOIN posts p ON p.author_id = u.id 
37  WHERE u.created_at > ? 
38  ORDER BY u.created_at DESC
39`, [new Date('2024-01-01')]);
40
41// Batch operations
42const users = await db.batchInsert('users', [
43  { name: 'User 1', email: 'user1@example.com' },
44  { name: 'User 2', email: 'user2@example.com' },
45  { name: 'User 3', email: 'user3@example.com' }
46]);

1// Available database adapters
2const adapters = {
3  // SQLite - Great for development and small applications
4  sqlite: createDatabaseAdapter('sqlite', {
5    filename: 'app.db',
6    memory: false // Set to true for in-memory database
7  }),
8  
9  // MySQL - Popular relational database
10  mysql: createDatabaseAdapter('mysql', {
11    host: 'localhost',
12    port: 3306,
13    user: 'root',
14    password: 'password',
15    database: 'myapp',
16    connectionLimit: 10
17  }),
18  
19  // PostgreSQL - Advanced relational database
20  postgresql: createDatabaseAdapter('postgresql', {
21    host: 'localhost',
22    port: 5432,
23    user: 'postgres',
24    password: 'password',
25    database: 'myapp',
26    connectionLimit: 10,
27    ssl: false // Set to true for production
28  })
29};
30
31// Or use direct instantiation for more control
32const customMysql = new MySQLAdapter({
33  host: 'localhost',
34  port: 3306,
35  user: 'root',
36  password: 'password',
37  database: 'myapp',
38  connectionLimit: 10,
39  acquireTimeout: 60000,
40  timeout: 60000
41});

1// Transaction handling with database adapters
2const TransferSchema = z.object({
3  fromUserId: z.number(),
4  toUserId: z.number(),
5  amount: z.number().positive()
6});
7
8app.post('/transfer', 
9  validate({ body: TransferSchema }),
10  async (req, res) => {
11    try {
12      const { fromUserId, toUserId, amount } = req.body;
13      
14      // Use database adapter transaction
15      const result = await primaryDb.transaction(async (tx) => {
16        // Check sender balance
17        const sender = await tx.queryOne(
18          'SELECT balance FROM accounts WHERE user_id = ? FOR UPDATE',
19          [fromUserId]
20        );
21        
22        if (!sender || sender.balance < amount) {
23          throw new Error('Insufficient funds');
24        }
25        
26        // Debit sender
27        await tx.query(
28          'UPDATE accounts SET balance = balance - ? WHERE user_id = ?',
29          [amount, fromUserId]
30        );
31        
32        // Credit receiver
33        await tx.query(
34          'UPDATE accounts SET balance = balance + ? WHERE user_id = ?',
35          [amount, toUserId]
36        );
37        
38        // Log transaction
39        const transaction = await tx.insert('transactions', {
40          from_user_id: fromUserId,
41          to_user_id: toUserId,
42          amount: amount,
43          created_at: new Date()
44        });
45        
46        return transaction;
47      });
48      
49      return {
50        success: true,
51        transaction: result,
52        message: 'Transfer completed successfully'
53      };
54    } catch (error) {
55      res.status(400);
56      return {
57        success: false,
58        error: error instanceof Error ? error.message : 'Transfer failed'
59      };
60    }
61  }
62);
63
64// Bulk operations with transactions
65app.post('/users/bulk', 
66  validate({ body: z.object({
67    users: z.array(z.object({
68      name: z.string(),
69      email: z.string().email(),
70      age: z.number()
71    })).min(1).max(10)
72  }) }),
73  async (req, res) => {
74    try {
75      const { users } = req.body;
76      
77      const result = await primaryDb.transaction(async (tx) => {
78        const createdUsers = [];
79        
80        for (const userData of users) {
81          const newUser = await tx.insert('users', userData);
82          createdUsers.push(newUser);
83        }
84        
85        return createdUsers;
86      });
87      
88      res.status(201);
89      return { 
90        success: true, 
91        data: result,
92        message: `${result.length} users created successfully`
93      };
94    } catch (error) {
95      res.status(500);
96      return { 
97        success: false, 
98        error: 'Failed to create users in bulk'
99      };
100    }
101  }
102);

1// Health check with database status
2app.get('/health', async (req, res) => {
3  try {
4    // Test database connection
5    await primaryDb.query('SELECT 1');
6    
7    return {
8      success: true,
9      status: 'healthy',
10      database: 'connected',
11      adapter: 'PostgreSQL',
12      timestamp: new Date().toISOString()
13    };
14  } catch (error) {
15    res.status(503);
16    return {
17      success: false,
18      status: 'unhealthy',
19      database: 'disconnected',
20      error: error instanceof Error ? error.message : String(error)
21    };
22  }
23});
24
25// Database adapter information endpoint
26app.get('/admin/db-info', async (req, res) => {
27  return {
28    success: true,
29    adapters: {
30      primary: {
31        type: 'PostgreSQL',
32        connected: primaryDb.isConnected(),
33        connectionLimit: 20
34      },
35      analytics: {
36        type: 'PostgreSQL', 
37        connected: analyticsDb.isConnected(),
38        connectionLimit: 10
39      },
40      cache: {
41        type: 'SQLite',
42        connected: cacheDb.isConnected(),
43        filename: 'cache.db'
44      }
45    }
46  };
47});
48
49// Graceful shutdown with database cleanup
50process.on('SIGINT', async () => {
51  console.log('\n🛑 Shutting down gracefully...');
52  try {
53    await Promise.all([
54      primaryDb.disconnect(),
55      analyticsDb.disconnect(),
56      cacheDb.disconnect()
57    ]);
58    console.log('All databases disconnected');
59    process.exit(0);
60  } catch (error) {
61    console.error('❌ Error during shutdown:', error);
62    process.exit(1);
63  }
64});