npm install mysql2

const mysql = require('mysql2/promise');

// 创建连接池（推荐）
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'mydb',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  charset: 'utf8mb4',
});

// 基本查询
async function getUsers() {
  const [rows] = await pool.query('SELECT * FROM users WHERE status = ?', ['active']);
  return rows;
}

// 参数化查询（防止 SQL 注入）
async function getUserById(id) {
  const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [id]);
  return rows[0];
}

// 插入数据
async function createUser(user) {
  const [result] = await pool.execute(
    'INSERT INTO users (name, email, role) VALUES (?, ?, ?)',
    [user.name, user.email, user.role]
  );
  return { id: result.insertId, ...user };
}

// 事务
async function transferBalance(fromId, toId, amount) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    await conn.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
    await conn.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

npm install pg

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'password',
  database: 'mydb',
  max: 20,
});

// 基本查询
async function getUsers() {
  const { rows } = await pool.query('SELECT * FROM users WHERE status = $1', ['active']);
  return rows;
}

// PostgreSQL 特有功能
async function advancedQueries() {
  // JSON 查询
  const { rows: jsonRows } = await pool.query(
    `SELECT * FROM users WHERE metadata->>'role' = $1`,
    ['admin']
  );

  // UPSERT
  await pool.query(
    `INSERT INTO counters (name, value)
     VALUES ($1, 1)
     ON CONFLICT (name) DO UPDATE SET value = counters.value + 1`,
    ['page_views']
  );

  // RETURNING
  const { rows: [newUser] } = await pool.query(
    `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *`,
    ['Alice', '[email protected]']
  );

  // 递归查询（CTE）
  const { rows: tree } = await pool.query(
    `WITH RECURSIVE category_tree AS (
      SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
      UNION ALL
      SELECT c.id, c.name, c.parent_id
      FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
    )
    SELECT * FROM category_tree`
  );
}

npm install mongodb
# 或使用 Mongoose（ODM）
npm install mongoose

const { MongoClient } = require('mongodb');

const client = new MongoClient('mongodb://localhost:27017');

async function main() {
  await client.connect();
  const db = client.db('mydb');
  const users = db.collection('users');

  // 插入
  await users.insertOne({ name: 'Alice', age: 30, tags: ['admin'] });
  await users.insertMany([{ name: 'Bob' }, { name: 'Charlie' }]);

  // 查询
  const allUsers = await users.find({}).toArray();
  const admins = await users.find({ tags: 'admin' }).toArray();
  const paged = await users.find({})
    .sort({ age: -1 })
    .skip(0)
    .limit(10)
    .toArray();

  // 更新
  await users.updateOne(
    { name: 'Alice' },
    { $set: { age: 31 }, $push: { tags: 'vip' } }
  );

  await users.updateMany(
    { age: { $lt: 18 } },
    { $set: { status: 'minor' } }
  );

  // 删除
  await users.deleteOne({ name: 'Bob' });

  // 聚合管道
  const stats = await users.aggregate([
    { $match: { status: 'active' } },
    { $group: { _id: '$role', count: { $sum: 1 }, avgAge: { $avg: '$age' } } },
    { $sort: { count: -1 } },
  ]).toArray();

  // 索引
  await users.createIndex({ email: 1 }, { unique: true });
  await users.createIndex({ name: 'text' }); // 全文索引

  await client.close();
}

const mongoose = require('mongoose');

// 连接
await mongoose.connect('mongodb://localhost:27017/mydb');

// 定义 Schema
const userSchema = new mongoose.Schema({
  name: { type: String, required: true, trim: true },
  email: { type: String, required: true, unique: true, lowercase: true },
  age: { type: Number, min: 0, max: 150 },
  role: { type: String, enum: ['user', 'admin'], default: 'user' },
  tags: [String],
  profile: {
    bio: String,
    avatar: String,
  },
}, {
  timestamps: true, // 自动添加 createdAt, updatedAt
});

// 虚拟字段
userSchema.virtual('displayName').get(function() {
  return `${this.name} (${this.role})`);
});

// 中间件（钩子）
userSchema.pre('save', function(next) {
  // 保存前的逻辑
  next();
});

// 静态方法
userSchema.statics.findByEmail = function(email) {
  return this.findOne({ email: email.toLowerCase() });
};

// 实例方法
userSchema.methods.isAdmin = function() {
  return this.role === 'admin';
};

// 创建模型
const User = mongoose.model('User', userSchema);

// 使用
const user = await User.create({
  name: 'Alice',
  email: '[email protected]',
  age: 30,
});

const found = await User.findByEmail('[email protected]');
const admins = await User.find({ role: 'admin' }).select('name email');
const paged = await User.find({})
  .sort('-createdAt')
  .skip(0)
  .limit(10)
  .lean(); // 返回纯对象，提升性能

await user.save();
await User.deleteOne({ _id: user._id });

npm install prisma @prisma/client
npx prisma init

npx prisma migrate dev --name init
npx prisma generate

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

// 创建
const user = await prisma.user.create({
  data: { name: 'Alice', email: '[email protected]' },
});

// 查询
const users = await prisma.user.findMany({
  where: { name: { contains: 'Ali' } },
  include: { posts: true },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 10,
});

// 更新
await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Updated' },
});

// 删除
await prisma.user.delete({ where: { id: 1 } });

// 事务
await prisma.$transaction([
  prisma.user.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } }),
  prisma.user.update({ where: { id: 2 }, data: { balance: { increment: 100 } } }),
]);

await prisma.$disconnect();