個人主頁：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: 'john@example.com',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', 'john@example.com');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: 'jane@example.com',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: 'user@example.com' };
}

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;}
}

結語
感謝您的閱讀！期待您的一鍵三連！歡迎指正！