Comprehensive comparison for Database technology in Web Development applications

const express = require('express');
const mariadb = require('mariadb');
const bcrypt = require('bcrypt');
const jwt = require('jsonwebtoken');

const app = express();
app.use(express.json());

// Create connection pool with best practices
const pool = mariadb.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'webapp',
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME || 'ecommerce',
  connectionLimit: 10,
  acquireTimeout: 30000,
  connectTimeout: 10000,
  idleTimeout: 600000
});

// User registration endpoint with transaction handling
app.post('/api/users/register', async (req, res) => {
  let conn;
  try {
    const { email, password, firstName, lastName } = req.body;

    // Input validation
    if (!email || !password || !firstName || !lastName) {
      return res.status(400).json({ error: 'All fields are required' });
    }

    if (password.length < 8) {
      return res.status(400).json({ error: 'Password must be at least 8 characters' });
    }

    conn = await pool.getConnection();
    await conn.beginTransaction();

    // Check if user already exists using prepared statement
    const existingUser = await conn.query(
      'SELECT id FROM users WHERE email = ? LIMIT 1',
      [email]
    );

    if (existingUser.length > 0) {
      await conn.rollback();
      return res.status(409).json({ error: 'User already exists' });
    }

    // Hash password with bcrypt
    const saltRounds = 12;
    const passwordHash = await bcrypt.hash(password, saltRounds);

    // Insert user with prepared statement to prevent SQL injection
    const result = await conn.query(
      `INSERT INTO users (email, password_hash, first_name, last_name, created_at, updated_at)
       VALUES (?, ?, ?, ?, NOW(), NOW())`,
      [email, passwordHash, firstName, lastName]
    );

    const userId = result.insertId;

    // Create user profile in separate table
    await conn.query(
      `INSERT INTO user_profiles (user_id, email_verified, account_status)
       VALUES (?, false, 'active')`,
      [userId]
    );

    // Commit transaction
    await conn.commit();

    // Generate JWT token
    const token = jwt.sign(
      { userId, email },
      process.env.JWT_SECRET || 'your-secret-key',
      { expiresIn: '24h' }
    );

    res.status(201).json({
      message: 'User registered successfully',
      userId,
      token
    });

  } catch (error) {
    if (conn) {
      await conn.rollback();
    }
    console.error('Registration error:', error);
    res.status(500).json({ error: 'Internal server error' });
  } finally {
    if (conn) {
      conn.release();
    }
  }
});

// Graceful shutdown
process.on('SIGINT', async () => {
  await pool.end();
  process.exit(0);
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});