前端技术知识

Appearance

Node.js 数据库操作指南 ​

目录 ​

关系型数据库 ​

MySQL ​

1. 基本连接 ​

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

async function connectDB() {
  const connection = await mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "test",
  });

  return connection;
}

// 使用连接池
const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  password: "password",
  database: "test",
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

2. 基本操作 ​

javascript
async function databaseOperations() {
  const connection = await connectDB();

  try {
    // 创建表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 插入数据
    const [result] = await connection.execute(
      "INSERT INTO users (name, email) VALUES (?, ?)",
      ["John Doe", "john@example.com"]
    );
    console.log("Inserted ID:", result.insertId);

    // 查询数据
    const [rows] = await connection.execute(
      "SELECT * FROM users WHERE id = ?",
      [result.insertId]
    );
    console.log("User:", rows[0]);

    // 更新数据
    await connection.execute("UPDATE users SET name = ? WHERE id = ?", [
      "John Smith",
      result.insertId,
    ]);

    // 删除数据
    await connection.execute("DELETE FROM users WHERE id = ?", [
      result.insertId,
    ]);
  } catch (err) {
    console.error("Error:", err);
  } finally {
    await connection.end();
  }
}

3. 事务处理 ​

javascript
async function transactionExample() {
  const connection = await connectDB();

  try {
    await connection.beginTransaction();

    await connection.execute(
      "INSERT INTO accounts (user_id, balance) VALUES (?, ?)",
      [1, 1000]
    );

    await connection.execute(
      "UPDATE accounts SET balance = balance - ? WHERE user_id = ?",
      [100, 1]
    );

    await connection.execute(
      "UPDATE accounts SET balance = balance + ? WHERE user_id = ?",
      [100, 2]
    );

    await connection.commit();
  } catch (err) {
    await connection.rollback();
    throw err;
  } finally {
    await connection.end();
  }
}

PostgreSQL ​

1. 基本连接 ​

javascript
const { Pool } = require("pg");

const pool = new Pool({
  user: "postgres",
  host: "localhost",
  database: "test",
  password: "password",
  port: 5432,
});

2. 基本操作 ​

javascript
async function postgresOperations() {
  const client = await pool.connect();

  try {
    // 创建表
    await client.query(`
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 插入数据
    const insertResult = await client.query(
      "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
      ["Jane Doe", "jane@example.com"]
    );
    const userId = insertResult.rows[0].id;

    // 查询数据
    const { rows } = await client.query("SELECT * FROM users WHERE id = $1", [
      userId,
    ]);
    console.log("User:", rows[0]);
  } catch (err) {
    console.error("Error:", err);
  } finally {
    client.release();
  }
}

NoSQL 数据库 ​

MongoDB ​

1. 基本连接 ​

javascript
const { MongoClient } = require("mongodb");

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

async function connectMongo() {
  try {
    await client.connect();
    return client.db("test");
  } catch (err) {
    console.error("MongoDB connection error:", err);
    throw err;
  }
}

2. 基本操作 ​

javascript
async function mongoOperations() {
  const db = await connectMongo();
  const users = db.collection("users");

  try {
    // 插入文档
    const insertResult = await users.insertOne({
      name: "John Doe",
      email: "john@example.com",
      age: 30,
      createdAt: new Date(),
    });
    console.log("Inserted ID:", insertResult.insertedId);

    // 查询文档
    const user = await users.findOne({ _id: insertResult.insertedId });
    console.log("User:", user);

    // 更新文档
    await users.updateOne(
      { _id: insertResult.insertedId },
      { $set: { age: 31 } }
    );

    // 删除文档
    await users.deleteOne({ _id: insertResult.insertedId });

    // 聚合查询
    const result = await users
      .aggregate([
        { $match: { age: { $gt: 25 } } },
        { $group: { _id: null, avgAge: { $avg: "$age" } } },
      ])
      .toArray();
  } catch (err) {
    console.error("Error:", err);
  } finally {
    await client.close();
  }
}

Redis ​

1. 基本连接 ​

javascript
const Redis = require("ioredis");

const redis = new Redis({
  host: "localhost",
  port: 6379,
  password: "password",
});

2. 基本操作 ​

javascript
async function redisOperations() {
  try {
    // 字符串操作
    await redis.set("user:1:name", "John Doe");
    const name = await redis.get("user:1:name");

    // 哈希表操作
    await redis.hset("user:1", {
      name: "John Doe",
      email: "john@example.com",
      age: "30",
    });
    const user = await redis.hgetall("user:1");

    // 列表操作
    await redis.lpush("messages", "Hello");
    await redis.rpush("messages", "World");
    const messages = await redis.lrange("messages", 0, -1);

    // 集合操作
    await redis.sadd("users:online", "user:1", "user:2");
    const onlineUsers = await redis.smembers("users:online");

    // 有序集合操作
    await redis.zadd("scores", 100, "user:1");
    const topScores = await redis.zrevrange("scores", 0, -1, "WITHSCORES");
  } catch (err) {
    console.error("Redis Error:", err);
  }
}

ORM 框架 ​

Sequelize ​

1. 基本设置 ​

javascript
const { Sequelize, DataTypes } = require("sequelize");

const sequelize = new Sequelize("database", "username", "password", {
  host: "localhost",
  dialect: "mysql",
});

// 定义模型
const User = sequelize.define("User", {
  name: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
  },
  age: {
    type: DataTypes.INTEGER,
  },
});

2. 基本操作 ​

javascript
async function sequelizeOperations() {
  try {
    // 同步模型到数据库
    await sequelize.sync();

    // 创建记录
    const user = await User.create({
      name: "John Doe",
      email: "john@example.com",
      age: 30,
    });

    // 查询记录
    const foundUser = await User.findOne({
      where: { email: "john@example.com" },
    });

    // 更新记录
    await user.update({ age: 31 });

    // 删除记录
    await user.destroy();

    // 关联查询
    const Post = sequelize.define("Post", {
      title: DataTypes.STRING,
      content: DataTypes.TEXT,
    });

    User.hasMany(Post);
    Post.belongsTo(User);

    const userWithPosts = await User.findOne({
      include: Post,
    });
  } catch (err) {
    console.error("Sequelize Error:", err);
  }
}

Prisma ​

1. Schema 定义 ​

prisma
// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  posts     Post[]
  profile   Profile?
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

2. 基本操作 ​

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

async function prismaOperations() {
  try {
    // 创建用户和关联数据
    const user = await prisma.user.create({
      data: {
        email: "john@example.com",
        name: "John Doe",
        posts: {
          create: {
            title: "Hello World",
            content: "This is my first post",
          },
        },
        profile: {
          create: {
            bio: "I am a software developer",
          },
        },
      },
      include: {
        posts: true,
        profile: true,
      },
    });

    // 查询带关联数据的用户
    const users = await prisma.user.findMany({
      include: {
        posts: true,
        profile: true,
      },
    });

    // 事务
    const [post, profile] = await prisma.$transaction([
      prisma.post.create({
        data: {
          title: "New Post",
          authorId: user.id,
        },
      }),
      prisma.profile.update({
        where: { userId: user.id },
        data: { bio: "Updated bio" },
      }),
    ]);
  } catch (err) {
    console.error("Prisma Error:", err);
  } finally {
    await prisma.$disconnect();
  }
}

最佳实践 ​

1. 连接池管理 ​

javascript
// MySQL 连接池
const pool = mysql.createPool({
  connectionLimit: 10,
  host: "localhost",
  user: "root",
  password: "password",
  database: "test",
});

// MongoDB 连接池
const client = new MongoClient(uri, {
  poolSize: 10,
  useNewUrlParser: true,
  useUnifiedTopology: true,
});

2. 错误处理 ​

javascript
// 通用数据库错误处理
class DatabaseError extends Error {
  constructor(message, originalError) {
    super(message);
    this.name = "DatabaseError";
    this.originalError = originalError;
  }
}

async function executeQuery(query, params) {
  try {
    const result = await pool.query(query, params);
    return result;
  } catch (err) {
    throw new DatabaseError("Database query failed", err);
  }
}

3. 事务管理 ​

javascript
async function transferMoney(fromId, toId, amount) {
  const connection = await pool.getConnection();

  try {
    await connection.beginTransaction();

    await connection.query(
      "UPDATE accounts SET balance = balance - ? WHERE id = ?",
      [amount, fromId]
    );

    await connection.query(
      "UPDATE accounts SET balance = balance + ? WHERE id = ?",
      [amount, toId]
    );

    await connection.commit();
  } catch (err) {
    await connection.rollback();
    throw new DatabaseError("Transaction failed", err);
  } finally {
    connection.release();
  }
}

4. 查询优化 ​

javascript
// 使用索引
await pool.query(`
  CREATE INDEX idx_users_email ON users(email);
`);

// 使用预处理语句
const stmt = await connection.prepare("SELECT * FROM users WHERE email = ?");

// 分页查询
async function getUsers(page = 1, limit = 10) {
  const offset = (page - 1) * limit;
  const [users, total] = await Promise.all([
    pool.query("SELECT * FROM users LIMIT ? OFFSET ?", [limit, offset]),
    pool.query("SELECT COUNT(*) as total FROM users"),
  ]);

  return {
    users: users[0],
    total: total[0][0].total,
    pages: Math.ceil(total[0][0].total / limit),
  };
}

5. 缓存策略 ​

javascript
const Redis = require("ioredis");
const redis = new Redis();

async function getCachedUser(id) {
  // 尝试从缓存获取
  const cachedUser = await redis.get(`user:${id}`);
  if (cachedUser) {
    return JSON.parse(cachedUser);
  }

  // 从数据库获取
  const user = await User.findByPk(id);
  if (user) {
    // 存入缓存
    await redis.set(
      `user:${id}`,
      JSON.stringify(user),
      "EX",
      3600 // 1小时过期
    );
  }

  return user;
}

6. 数据验证 ​

javascript
const Joi = require("joi");

const userSchema = Joi.object({
  name: Joi.string().min(3).max(30).required(),
  email: Joi.string().email().required(),
  age: Joi.number().integer().min(0).max(150),
});

async function createUser(userData) {
  // 验证数据
  const { error, value } = userSchema.validate(userData);
  if (error) {
    throw new ValidationError(error.details[0].message);
  }

  // 创建用户
  return await User.create(value);
}

7. 安全性 ​

javascript
// 参数化查询防止 SQL 注入
const result = await pool.query("SELECT * FROM users WHERE email = ?", [
  userInput,
]);

// 加密敏感数据
const bcrypt = require("bcrypt");

async function hashPassword(password) {
  const salt = await bcrypt.genSalt(10);
  return await bcrypt.hash(password, salt);
}

// 数据脱敏
function sanitizeUser(user) {
  const { password, ...sanitizedUser } = user;
  return sanitizedUser;
}