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// 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);

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]);