【node.js】数据库与存储

个人主页：Guiat
归属专栏：node.js

正文

1. 数据库概述

Node.js应用程序中的数据库存储是构建持久化、可扩展应用的关键组件。Node.js可以连接各种类型的数据库系统，从关系型到NoSQL，满足不同的应用需求。

1.1 数据库在Node.js中的作用

• 持久化存储应用数据
• 提供高效的数据查询和检索
• 确保数据完整性和一致性
• 支持复杂的业务逻辑实现
• 优化应用性能和可扩展性

1.2 Node.js支持的数据库类型

Node.js可以与多种数据库类型无缝集成：

2. 关系型数据库集成

2.1 MySQL与Node.js

2.1.1 安装MySQL驱动

npm install mysql2

2.1.2 建立连接

const mysql = require('mysql2/promise');// 创建连接池
const pool = mysql.createPool({host: 'localhost',user: 'root',password: 'password',database: 'my_database',waitForConnections: true,connectionLimit: 10,queueLimit: 0
});async function queryDatabase() {try {// 获取连接const [rows, fields] = await pool.execute('SELECT * FROM users WHERE id = ?', [1]);console.log(rows);return rows;} catch (error) {console.error('数据库查询错误:', error);throw error;}
}

2.1.3 执行CRUD操作

// 插入数据
async function insertUser(name, email) {try {const [result] = await pool.execute('INSERT INTO users (name, email) VALUES (?, ?)',[name, email]);return result.insertId;} catch (error) {console.error('插入错误:', error);throw error;}
}// 更新数据
async function updateUser(id, name, email) {try {const [result] = await pool.execute('UPDATE users SET name = ?, email = ? WHERE id = ?',[name, email, id]);return result.affectedRows;} catch (error) {console.error('更新错误:', error);throw error;}
}// 删除数据
async function deleteUser(id) {try {const [result] = await pool.execute('DELETE FROM users WHERE id = ?', [id]);return result.affectedRows;} catch (error) {console.error('删除错误:', error);throw error;}
}

2.2 PostgreSQL与Node.js

2.2.1 安装pg驱动

npm install pg

2.2.2 建立连接

const { Pool } = require('pg');const pool = new Pool({user: 'postgres',host: 'localhost',database: 'my_database',password: 'password',port: 5432,max: 20, // 连接池最大连接数idleTimeoutMillis: 30000
});async function queryDatabase() {const client = await pool.connect();try {const result = await client.query('SELECT * FROM users WHERE id = $1', [1]);return result.rows;} finally {client.release(); // 释放连接回连接池}
}

2.2.3 使用事务

async function transferFunds(fromAccount, toAccount, amount) {const client = await pool.connect();try {await client.query('BEGIN');// 从一个账户扣款await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2',[amount, fromAccount]);// 向另一个账户存款await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2',[amount, toAccount]);await client.query('COMMIT');return true;} catch (error) {await client.query('ROLLBACK');console.error('事务失败:', error);throw error;} finally {client.release();}
}

2.3 SQLite与Node.js

2.3.1 安装sqlite3驱动

npm install sqlite3

2.3.2 建立连接和基本操作

const sqlite3 = require('sqlite3').verbose();// 打开数据库连接
const db = new sqlite3.Database('./mydatabase.sqlite', (err) => {if (err) {console.error('连接错误:', err.message);} else {console.log('已连接到SQLite数据库');}
});// 执行查询
function getUser(id) {return new Promise((resolve, reject) => {db.get('SELECT * FROM users WHERE id = ?', [id], (err, row) => {if (err) {reject(err);} else {resolve(row);}});});
}// 执行插入
function insertUser(name, email) {return new Promise((resolve, reject) => {db.run('INSERT INTO users (name, email) VALUES (?, ?)',[name, email],function(err) {if (err) {reject(err);} else {resolve(this.lastID); // 获取插入的ID}});});
}// 关闭数据库连接
function closeDatabase() {return new Promise((resolve, reject) => {db.close((err) => {if (err) {reject(err);} else {resolve();}});});
}

3. NoSQL数据库集成

3.1 MongoDB与Node.js

3.1.1 安装MongoDB驱动

npm install mongodb

3.1.2 建立连接

const { MongoClient } = require('mongodb');const uri = 'mongodb://localhost:27017';
const client = new MongoClient(uri);async function connectToMongo() {try {await client.connect();console.log('已连接到MongoDB');return client.db('mydatabase');} catch (error) {console.error('MongoDB连接错误:', error);throw error;}
}

3.1.3 CRUD操作

async function performOperations() {try {const db = await connectToMongo();const usersCollection = db.collection('users');// 插入文档const insertResult = await usersCollection.insertOne({name: 'John Doe',email: '[e-mail]',age: 30,createdAt: new Date()});console.log('插入的ID:', insertResult.insertedId);// 查询文档const user = await usersCollection.findOne({ name: 'John Doe' });console.log('找到的用户:', user);// 更新文档const updateResult = await usersCollection.updateOne({ name: 'John Doe' },{ $set: { age: 31 } });console.log('更新结果:', updateResult.modifiedCount);// 删除文档const deleteResult = await usersCollection.deleteOne({ name: 'John Doe' });console.log('删除结果:', deleteResult.deletedCount);} catch (error) {console.error('操作错误:', error);} finally {await client.close();}
}

3.1.4 使用Mongoose ODM

npm install mongoose

const mongoose = require('mongoose');// 连接到MongoDB
mongoose.connect('mongodb://localhost:27017/mydatabase', {useNewUrlParser: true,useUnifiedTopology: true
})
.then(() => console.log('MongoDB连接成功'))
.catch(err => console.error('MongoDB连接错误:', err));// 定义Schema
const userSchema = new mongoose.Schema({name: { type: String, required: true },email: { type: String, required: true, unique: true },age: { type: Number, min: 18, max: 100 },isActive: { type: Boolean, default: true },createdAt: { type: Date, default: Date.now }
});// 创建模型
const User = mongoose.model('User', userSchema);// 创建用户
async function createUser(userData) {try {const newUser = new User(userData);await newUser.save();return newUser;} catch (error) {console.error('创建用户错误:', error);throw error;}
}// 查询用户
async function findUsers(criteria = {}) {try {return await User.find(criteria).sort({ createdAt: -1 });} catch (error) {console.error('查询用户错误:', error);throw error;}
}// 更新用户
async function updateUser(id, updateData) {try {return await User.findByIdAndUpdate(id, updateData, { new: true, runValidators: true });} catch (error) {console.error('更新用户错误:', error);throw error;}
}// 删除用户
async function deleteUser(id) {try {return await User.findByIdAndDelete(id);} catch (error) {console.error('删除用户错误:', error);throw error;}
}

3.2 Redis与Node.js

3.2.1 安装Redis客户端

npm install redis

3.2.2 建立连接

const redis = require('redis');async function connectToRedis() {// 创建客户端const client = redis.createClient({url: 'redis://localhost:6379'});// 错误监听client.on('error', (err) => {console.error('Redis错误:', err);});// 连接Redisawait client.connect();console.log('已连接到Redis');return client;
}

3.2.3 基本操作

async function redisOperations() {const client = await connectToRedis();try {// 设置键值await client.set('user:1:name', 'John Doe');await client.set('user:1:email', '[e-mail]');await client.set('user:1:visits', '10');// 设置带过期时间的键值（60秒）await client.setEx('session:123', 60, 'active');// 获取值const name = await client.get('user:1:name');console.log('名称:', name);// 递增计数器const newVisits = await client.incr('user:1:visits');console.log('访问次数:', newVisits);// 哈希存储await client.hSet('user:2', {name: 'Jane Smith',email: '[e-mail]',age: '28'});// 获取哈希字段const userData = await client.hGetAll('user:2');console.log('用户数据:', userData);// 列表操作await client.lPush('recent_users', 'user:1');await client.lPush('recent_users', 'user:2');const recentUsers = await client.lRange('recent_users', 0, -1);console.log('最近的用户:', recentUsers);// 集合操作await client.sAdd('active_users', 'user:1', 'user:2');const isActive = await client.sIsMember('active_users', 'user:1');console.log('用户是否活跃:', isActive);// 删除键await client.del('session:123');// 检查键是否存在const exists = await client.exists('user:1:name');console.log('键存在:', exists);} finally {// 关闭连接await client.disconnect();}
}

3.2.4 使用Redis作为缓存

async function getUserWithCache(userId) {const client = await connectToRedis();try {// 尝试从Redis缓存获取const cachedUser = await client.get(`user:${userId}:data`);if (cachedUser) {console.log('缓存命中!');return JSON.parse(cachedUser);}console.log('缓存未命中，从数据库获取...');// 从数据库获取用户（示例）const user = await fetchUserFromDatabase(userId);// 存储到Redis缓存（10分钟过期）await client.setEx(`user:${userId}:data`, 600, JSON.stringify(user));return user;} finally {await client.disconnect();}
}// 模拟从数据库获取用户
async function fetchUserFromDatabase(userId) {// 这里应该是实际的数据库查询return { id: userId, name: 'Database User', email: '[e-mail]' };
}

3.3 Elasticsearch与Node.js

3.3.1 安装Elasticsearch客户端

npm install @elastic/elasticsearch

3.3.2 建立连接与基本操作

const { Client } = require('@elastic/elasticsearch');// 创建客户端
const client = new Client({ node: 'http://localhost:9200' });// 索引文档
async function indexDocument(doc) {try {const result = await client.index({index: 'products',body: doc});console.log('文档已索引:', result);return result;} catch (error) {console.error('索引错误:', error);throw error;}
}// 搜索文档
async function searchDocuments(query) {try {const result = await client.search({index: 'products',body: {query: {multi_match: {query: query,fields: ['name', 'description']}}}});console.log('搜索结果数量:', result.hits.total.value);return result.hits.hits.map(hit => ({id: hit._id,score: hit._score,...hit._source}));} catch (error) {console.error('搜索错误:', error);throw error;}
}// 使用示例
async function esExample() {// 索引产品await indexDocument({name: 'iPhone 13',description: '最新款苹果智能手机，搭载A15芯片',price: 799,category: 'electronics',tags: ['apple', 'smartphone', 'ios']});// 搜索产品const results = await searchDocuments('苹果 手机');console.log(results);
}

4. ORM和Query Builder

4.1 Sequelize ORM

4.1.1 安装Sequelize

npm install sequelize
npm install pg pg-hstore # PostgreSQL
# 或
npm install mysql2 # MySQL
# 或
npm install sqlite3 # SQLite

4.1.2 设置连接和模型

const { Sequelize, DataTypes } = require('sequelize');// 创建Sequelize实例
const sequelize = new Sequelize('database', 'username', 'password', {host: 'localhost',dialect: 'mysql', // 'postgres', 'sqlite', 'mariadb'pool: {max: 5,min: 0,acquire: 30000,idle: 10000},// SQLite专用// storage: './database.sqlite'
});// 定义模型
const User = sequelize.define('User', {// 模型属性firstName: {type: DataTypes.STRING,allowNull: false},lastName: {type: DataTypes.STRING},email: {type: DataTypes.STRING,allowNull: false,unique: true,validate: {isEmail: true}},age: {type: DataTypes.INTEGER,validate: {min: 18}},isActive: {type: DataTypes.BOOLEAN,defaultValue: true}
}, {// 其他模型选项timestamps: true, // 添加createdAt和updatedAtparanoid: true // 软删除（添加deletedAt而不是真删除）
});// 定义关联模型
const Post = sequelize.define('Post', {title: {type: DataTypes.STRING,allowNull: false},content: {type: DataTypes.TEXT},status: {type: DataTypes.ENUM('draft', 'published', 'archived'),defaultValue: 'draft'}
});// 定义模型关系
User.hasMany(Post);
Post.belongsTo(User);// 同步模型到数据库
async function syncDatabase() {try {await sequelize.sync({ force: false }); // force: true会删除现有表console.log('所有模型已同步');} catch (error) {console.error('同步错误:', error);}
}

4.1.3 CRUD操作

// 创建用户
async function createUser(userData) {try {const user = await User.create(userData);console.log('用户已创建:', user.toJSON());return user;} catch (error) {console.error('创建用户错误:', error);throw error;}
}// 查询用户
async function findUsers(options = {}) {try {const users = await User.findAll({where: options.where,attributes: options.attributes,include: options.include,order: options.order,limit: options.limit,offset: options.offset});return users;} catch (error) {console.error('查询用户错误:', error);throw error;}
}// 更新用户
async function updateUser(id, updateData) {try {const user = await User.findByPk(id);if (!user) {throw new Error('用户不存在');}const updatedUser = await user.update(updateData);return updatedUser;} catch (error) {console.error('更新用户错误:', error);throw error;}
}// 删除用户
async function deleteUser(id) {try {const user = await User.findByPk(id);if (!user) {throw new Error('用户不存在');}await user.destroy();return true;} catch (error) {console.error('删除用户错误:', error);throw error;}
}// 复杂查询示例
async function complexQueryExample() {try {// 查询活跃用户及其文章const activeUsers = await User.findAll({where: {isActive: true,age: {[Sequelize.Op.gte]: 21 // 大于等于21岁}},include: [{model: Post,where: {status: 'published'},required: false // LEFT OUTER JOIN}],order: [['lastName', 'ASC'],[Post, 'createdAt', 'DESC']],limit: 10});return activeUsers;} catch (error) {console.error('复杂查询错误:', error);throw error;}
}

4.2 TypeORM

4.2.1 安装TypeORM

npm install typeorm reflect-metadata
npm install pg # PostgreSQL
# 或其他数据库驱动

4.2.2 实体和连接配置

// entity/User.ts
import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from "typeorm";
import { Post } from "./Post";@Entity()
export class User {@PrimaryGeneratedColumn()id: number;@Column()firstName: string;@Column()lastName: string;@Column({ unique: true })email: string;@Column()age: number;@Column({ default: true })isActive: boolean;@Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP" })createdAt: Date;@OneToMany(() => Post, post => post.user)posts: Post[];
}// entity/Post.ts
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from "typeorm";
import { User } from "./User";@Entity()
export class Post {@PrimaryGeneratedColumn()id: number;@Column()title: string;@Column("text")content: string;@Column({type: "enum",enum: ["draft", "published", "archived"],default: "draft"})status: string;@Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP" })createdAt: Date;@ManyToOne(() => User, user => user.posts)user: User;
}// data-source.ts
import "reflect-metadata";
import { DataSource } from "typeorm";
import { User } from "./entity/User";
import { Post } from "./entity/Post";export const AppDataSource = new DataSource({type: "postgres",host: "localhost",port: 5432,username: "postgres",password: "password",database: "mydatabase",synchronize: true, // 在生产环境中不建议使用logging: true,entities: [User, Post],migrations: [],subscribers: []
});// 初始化数据源
export async function initializeDataSource() {try {await AppDataSource.initialize();console.log("数据源已初始化");} catch (error) {console.error("数据源初始化错误:", error);throw error;}
}

4.2.3 使用TypeORM进行操作

// user-service.ts
import { AppDataSource } from "./data-source";
import { User } from "./entity/User";
import { Post } from "./entity/Post";// 创建用户
export async function createUser(userData: Partial<User>): Promise<User> {const userRepository = AppDataSource.getRepository(User);const user = userRepository.create(userData);return await userRepository.save(user);
}// 查找用户
export async function findUserById(id: number): Promise<User | null> {const userRepository = AppDataSource.getRepository(User);return await userRepository.findOne({where: { id },relations: ["posts"]});
}// 更新用户
export async function updateUser(id: number, userData: Partial<User>): Promise<User | null> {const userRepository = AppDataSource.getRepository(User);await userRepository.update(id, userData);return await findUserById(id);
}// 删除用户
export async function deleteUser(id: number): Promise<boolean> {const userRepository = AppDataSource.getRepository(User);const result = await userRepository.delete(id);return result.affected ? true : false;
}// 复杂查询示例
export async function findActiveUsersWithPosts(): Promise<User[]> {const userRepository = AppDataSource.getRepository(User);return await userRepository.createQueryBuilder("user").leftJoinAndSelect("user.posts", "post", "post.status = :status", { status: "published" }).where("user.isActive = :isActive", { isActive: true }).andWhere("user.age >= :age", { age: 21 }).orderBy("user.lastName", "ASC").addOrderBy("post.createdAt", "DESC").take(10).getMany();
}

4.3 Knex Query Builder

4.3.1 安装Knex

npm install knex
npm install pg # PostgreSQL
# 或其他数据库驱动

4.3.2 建立连接和基本操作

const knex = require('knex')({client: 'pg', // 或 'mysql', 'sqlite3'connection: {host: '127.0.0.1',port: 5432,user: 'postgres',password: 'password',database: 'mydatabase'},pool: { min: 0, max: 7 }
});// 查询操作
async function getUsers() {try {return await knex('users').select('id', 'name', 'email').where('is_active', true).orderBy('created_at', 'desc').limit(10);} catch (error) {console.error('查询错误:', error);throw error;}
}// 插入操作
async function insertUser(userData) {try {const [id] = await knex('users').insert(userData).returning('id');return id;} catch (error) {console.error('插入错误:', error);throw error;}
}// 更新操作
async function updateUser(id, userData) {try {return await knex('users').where('id', id).update(userData);} catch (error) {console.error('更新错误:', error);throw error;}
}// 删除操作
async function deleteUser(id) {try {return await knex('users').where('id', id).del();} catch (error) {console.error('删除错误:', error);throw error;}
}// 事务示例
async function transferFunds(fromAccount, toAccount, amount) {try {return await knex.transaction(async (trx) => {// 从一个账户扣款await trx('accounts').where('id', fromAccount).decrement('balance', amount);// 向另一个账户存款await trx('accounts').where('id', toAccount).increment('balance', amount);// 记录交易await trx('transactions').insert({from_account: fromAccount,to_account: toAccount,amount: amount,transaction_date: new Date()});return true;});} catch (error) {console.error('事务错误:', error);throw error;}
}// 复杂查询示例
async function getUsersWithPosts() {try {// 多表联合查询return await knex('users as u').select('u.id','u.name','u.email',knex.raw('COUNT(p.id) as post_count')).leftJoin('posts as p', 'u.id', 'p.user_id').where('u.is_active', true).groupBy('u.id', 'u.name', 'u.email').having(knex.raw('COUNT(p.id) > ?', [0])).orderBy('post_count', 'desc');} catch (error) {console.error('复杂查询错误:', error);throw error;}
}

结语
感谢您的阅读！期待您的一键三连！欢迎指正！