知识库

Appearance

Return to top
On this page

Node.js 数据库集成

Node.js可以与多种数据库进行集成，包括关系型数据库和NoSQL数据库。本章详细介绍各种数据库的集成方式。

数据库驱动和ORM

MySQL集成

javascript
// 首先安装: npm install mysql2
const mysql = require('mysql2');

// 创建连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 获取Promise版本的连接
const promisePool = pool.promise();

// 查询示例
async function getUsers() {
  try {
    const [rows] = await promisePool.execute('SELECT * FROM users');
    return rows;
  } catch (err) {
    console.error('查询用户失败:', err);
    throw err;
  }
}

// 插入示例
async function createUser(userData) {
  try {
    const [result] = await promisePool.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [userData.name, userData.email]
    );
    return result.insertId;
  } catch (err) {
    console.error('创建用户失败:', err);
    throw err;
  }
}

// 使用示例
async function example() {
  try {
    const users = await getUsers();
    console.log('用户列表:', users);
    
    const newUserId = await createUser({ name: '张三', email: 'zhang@example.com' });
    console.log('新用户ID:', newUserId);
  } catch (err) {
    console.error('操作失败:', err);
  }
}

PostgreSQL集成

javascript
// 首先安装: npm install pg
const { Pool } = require('pg');

// 创建连接池
const pool = new Pool({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});

// 查询示例
async function getUsers() {
  try {
    const { rows } = await pool.query('SELECT * FROM users');
    return rows;
  } catch (err) {
    console.error('查询用户失败:', err);
    throw err;
  }
}

// 参数化查询
async function getUserById(id) {
  try {
    const { rows } = await pool.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return rows[0];
  } catch (err) {
    console.error('查询用户失败:', err);
    throw err;
  }
}

// 事务处理
async function transferMoney(fromUserId, toUserId, amount) {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    // 从源用户扣款
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE user_id = $2',
      [amount, fromUserId]
    );
    
    // 向目标用户加款
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE user_id = $2',
      [amount, toUserId]
    );
    
    await client.query('COMMIT');
    console.log('转账成功');
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('转账失败:', err);
    throw err;
  } finally {
    client.release();
  }
}

MongoDB集成

使用原生驱动

javascript
// 首先安装: npm install mongodb
const { MongoClient } = require('mongodb');

class MongoDBClient {
  constructor() {
    this.client = null;
    this.db = null;
  }
  
  async connect(uri, dbName) {
    try {
      this.client = new MongoClient(uri);
      await this.client.connect();
      this.db = this.client.db(dbName);
      console.log('MongoDB连接成功');
    } catch (err) {
      console.error('MongoDB连接失败:', err);
      throw err;
    }
  }
  
  async getUsers() {
    try {
      const collection = this.db.collection('users');
      const users = await collection.find({}).toArray();
      return users;
    } catch (err) {
      console.error('查询用户失败:', err);
      throw err;
    }
  }
  
  async createUser(userData) {
    try {
      const collection = this.db.collection('users');
      const result = await collection.insertOne(userData);
      return result.insertedId;
    } catch (err) {
      console.error('创建用户失败:', err);
      throw err;
    }
  }
  
  async updateUser(id, updateData) {
    try {
      const collection = this.db.collection('users');
      const result = await collection.updateOne(
        { _id: new ObjectId(id) },
        { $set: updateData }
      );
      return result.modifiedCount;
    } catch (err) {
      console.error('更新用户失败:', err);
      throw err;
    }
  }
  
  async close() {
    if (this.client) {
      await this.client.close();
    }
  }
}

// 使用示例
const db = new MongoDBClient();
await db.connect('mongodb://localhost:27017', 'myapp');

const users = await db.getUsers();
console.log('用户列表:', users);

使用Mongoose ODM

javascript
// 首先安装: npm install mongoose
const mongoose = require('mongoose');

// 连接到MongoDB
mongoose.connect('mongodb://localhost:27017/myapp', {
  useNewUrlParser: true,
  useUnifiedTopology: true
});

// 定义用户模式
const userSchema = new mongoose.Schema({
  name: { type: String, required: true },
  email: { type: String, required: true, unique: true },
  age: { type: Number, min: 0 },
  createdAt: { type: Date, default: Date.now },
  isActive: { type: Boolean, default: true }
});

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

// CRUD操作示例
async function userOperations() {
  try {
    // 创建用户
    const newUser = new User({
      name: '张三',
      email: 'zhang@example.com',
      age: 25
    });
    
    const savedUser = await newUser.save();
    console.log('用户创建成功:', savedUser);
    
    // 查询用户
    const users = await User.find({ isActive: true });
    console.log('活跃用户:', users);
    
    // 更新用户
    const updatedUser = await User.findOneAndUpdate(
      { email: 'zhang@example.com' },
      { age: 26 },
      { new: true } // 返回更新后的文档
    );
    console.log('用户更新成功:', updatedUser);
    
    // 删除用户
    const deletedUser = await User.findOneAndDelete({ email: 'zhang@example.com' });
    console.log('用户删除成功:', deletedUser);
  } catch (err) {
    console.error('操作失败:', err);
  }
}

ORM集成

使用Sequelize

javascript
// 首先安装: npm install sequelize mysql2
const { Sequelize, DataTypes } = require('sequelize');

// 创建Sequelize实例
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql' // 或 'postgres', 'sqlite', 'mssql'
});

// 定义模型
const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  age: {
    type: DataTypes.INTEGER,
    validate: {
      min: 0,
      max: 150
    }
  }
}, {
  tableName: 'users',
  timestamps: true // 自动添加createdAt和updatedAt字段
});

// 同步数据库（创建表）
async function syncDatabase() {
  try {
    await sequelize.sync({ force: false }); // force: true 会删除现有表
    console.log('数据库同步成功');
  } catch (err) {
    console.error('数据库同步失败:', err);
  }
}

// CRUD操作
async function sequelizeOperations() {
  try {
    // 创建用户
    const newUser = await User.create({
      name: '李四',
      email: 'li@example.com',
      age: 30
    });
    console.log('用户创建成功:', newUser.toJSON());
    
    // 查询用户
    const users = await User.findAll({
      where: {
        age: {
          [Sequelize.Op.gte]: 18 // 年龄大于等于18
        }
      }
    });
    console.log('查询结果:', users.map(u => u.toJSON()));
    
    // 更新用户
    const [updatedRowsCount] = await User.update(
      { age: 31 },
      { where: { email: 'li@example.com' } }
    );
    console.log('更新行数:', updatedRowsCount);
    
    // 关联查询示例
    const Order = sequelize.define('Order', {
      productId: DataTypes.INTEGER,
      quantity: DataTypes.INTEGER
    });
    
    // 设置关联关系
    User.hasMany(Order);
    Order.belongsTo(User);
    
    const userWithOrders = await User.findOne({
      where: { email: 'li@example.com' },
      include: [Order]
    });
    console.log('用户及其订单:', userWithOrders.toJSON());
  } catch (err) {
    console.error('操作失败:', err);
  }
}

Redis集成

javascript
// 首先安装: npm install redis
const redis = require('redis');

class RedisClient {
  constructor() {
    this.client = null;
  }
  
  async connect() {
    try {
      this.client = redis.createClient({
        host: 'localhost',
        port: 6379
      });
      
      await this.client.connect();
      console.log('Redis连接成功');
    } catch (err) {
      console.error('Redis连接失败:', err);
      throw err;
    }
  }
  
  async set(key, value, expiration = 3600) {
    try {
      await this.client.set(key, JSON.stringify(value), {
        EX: expiration // 设置过期时间（秒）
      });
    } catch (err) {
      console.error('设置缓存失败:', err);
      throw err;
    }
  }
  
  async get(key) {
    try {
      const value = await this.client.get(key);
      return value ? JSON.parse(value) : null;
    } catch (err) {
      console.error('获取缓存失败:', err);
      throw err;
    }
  }
  
  async delete(key) {
    try {
      await this.client.del(key);
    } catch (err) {
      console.error('删除缓存失败:', err);
      throw err;
    }
  }
  
  async close() {
    if (this.client) {
      await this.client.quit();
    }
  }
}

// 使用Redis缓存优化数据库查询
class CachedUserService {
  constructor(db, redisClient) {
    this.db = db; // 数据库实例
    this.redis = redisClient;
  }
  
  async getUser(id) {
    // 首先尝试从缓存获取
    let user = await this.redis.get(`user:${id}`);
    
    if (!user) {
      // 缓存未命中，从数据库获取
      user = await this.db.getUserById(id);
      
      // 存入缓存，有效期1小时
      if (user) {
        await this.redis.set(`user:${id}`, user, 3600);
      }
    }
    
    return user;
  }
  
  async updateUser(id, updateData) {
    // 更新数据库
    const updatedUser = await this.db.updateUser(id, updateData);
    
    // 清除缓存
    await this.redis.delete(`user:${id}`);
    
    return updatedUser;
  }
}

数据库连接池管理

通用连接池配置

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

class DatabasePool {
  constructor(config) {
    this.pool = mysql.createPool({
      host: config.host,
      user: config.user,
      password: config.password,
      database: config.database,
      waitForConnections: true,
      connectionLimit: config.connectionLimit || 10,
      queueLimit: config.queueLimit || 0,
      acquireTimeout: config.acquireTimeout || 60000,
      timeout: config.timeout || 60000,
      enableKeepAlive: true,
      keepAliveInitialDelay: 0
    });
  }
  
  async query(sql, params) {
    let connection;
    try {
      connection = await this.pool.getConnection();
      const [results] = await connection.execute(sql, params);
      return results;
    } catch (err) {
      console.error('数据库查询失败:', err);
      throw err;
    } finally {
      if (connection) {
        connection.release();
      }
    }
  }
  
  async transaction(transactionFn) {
    const connection = await this.pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const result = await transactionFn(connection);
      
      await connection.commit();
      return result;
    } catch (err) {
      await connection.rollback();
      throw err;
    } finally {
      connection.release();
    }
  }
  
  async close() {
    await this.pool.end();
  }
}

// 使用连接池
const dbConfig = {
  host: 'localhost',
  user: 'username',
  password: 'password',
  database: 'database',
  connectionLimit: 20
};

const dbPool = new DatabasePool(dbConfig);

// 在Express应用中使用
const express = require('express');
const app = express();

app.use((req, res, next) => {
  req.db = dbPool;
  next();
});

app.get('/users', async (req, res) => {
  try {
    const users = await req.db.query('SELECT * FROM users');
    res.json(users);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

数据库迁移

使用Knex.js进行迁移

javascript
// 首先安装: npm install knex mysql2
const knex = require('knex');

// 配置Knex
const db = knex({
  client: 'mysql2',
  connection: {
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'database'
  },
  migrations: {
    tableName: 'knex_migrations'
  }
});

// 创建迁移文件
async function createUsersTable() {
  // 检查表是否存在
  const exists = await db.schema.hasTable('users');
  
  if (!exists) {
    await db.schema.createTable('users', (table) => {
      table.increments('id').primary();
      table.string('name').notNullable();
      table.string('email').notNullable().unique();
      table.integer('age');
      table.boolean('is_active').defaultTo(true);
      table.timestamp('created_at').defaultTo(db.fn.now());
      table.timestamp('updated_at').defaultTo(db.fn.now());
    });
    
    console.log('users表创建成功');
  }
}

// 种子数据
async function seedUsers() {
  const users = [
    { name: '张三', email: 'zhang@example.com', age: 25 },
    { name: '李四', email: 'li@example.com', age: 30 },
    { name: '王五', email: 'wang@example.com', age: 35 }
  ];
  
  await db('users').insert(users);
  console.log('种子数据插入成功');
}

数据库性能优化

查询优化技巧

javascript
// 1. 使用索引
async function optimizedQueries(db) {
  // 创建索引
  await db.query('CREATE INDEX idx_users_email ON users(email)');
  await db.query('CREATE INDEX idx_users_age_active ON users(age, is_active)');
  
  // 使用索引查询
  const user = await db.query(
    'SELECT * FROM users WHERE email = ?',
    ['specific@example.com']
  );
}

// 2. 批量操作
async function batchOperations(db) {
  // 批量插入
  const users = [
    ['张三', 'zhang1@example.com'],
    ['李四', 'li1@example.com'],
    ['王五', 'wang1@example.com']
  ];
  
  await db.query(
    'INSERT INTO users (name, email) VALUES ?',
    [users]
  );
  
  // 批量更新
  await db.query(
    `UPDATE users 
     SET age = CASE id 
       WHEN ? THEN ?
       WHEN ? THEN ?
       WHEN ? THEN ?
     END
     WHERE id IN (?, ?, ?)`,
    [1, 25, 2, 30, 3, 35, 1, 2, 3]
  );
}

// 3. 分页查询
async function paginatedQuery(db, page = 1, limit = 10) {
  const offset = (page - 1) * limit;
  
  // 获取总数
  const [{ count }] = await db.query(
    'SELECT COUNT(*) as count FROM users'
  );
  
  // 获取分页数据
  const users = await db.query(
    'SELECT * FROM users LIMIT ? OFFSET ?',
    [limit, offset]
  );
  
  return {
    data: users,
    pagination: {
      page,
      limit,
      total: count,
      pages: Math.ceil(count / limit)
    }
  };
}

数据库连接安全

连接配置安全

javascript
// 配置文件
require('dotenv').config(); // npm install dotenv

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: false
  } : false
};

// 环境文件 (.env)
// DB_HOST=localhost
// DB_USER=your_username
// DB_PASSWORD=your_secure_password
// DB_NAME=your_database

Node.js提供了丰富的数据库集成选项，从简单的数据库驱动到功能完整的ORM，开发者可以根据项目需求选择合适的方案。