Ubuntu 上 Node.js 连接数据库实操指南
一 准备与通用步骤
- 安装 Node.js 与 npm(建议优先使用 NVM 管理版本):
- 安装 NVM:curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.1/install.sh | bash
- 重新加载环境:source ~/.bash_profile
- 安装 Node:nvm install node
- 选择数据库并安装服务器(示例):
- MySQL:sudo apt update && sudo apt install mysql-server
- PostgreSQL:sudo apt install postgresql postgresql-contrib
- MongoDB:导入公钥、添加源、安装包并启动服务(详见下文示例)
- 创建数据库与用户(示例 SQL):
- CREATE DATABASE mydb;
- CREATE USER ‘myuser’@‘localhost’ IDENTIFIED BY ‘mypassword’;
- GRANT ALL PRIVILEGES ON mydb.* TO ‘myuser’@‘localhost’;
- FLUSH PRIVILEGES;
- 在项目中安装对应驱动(示例):
- MySQL:npm install mysql 或 mysql2
- PostgreSQL:npm install pg
- MongoDB:npm install mongodb 或 mongoose
- 运行应用:node app.js(建议配合 .env 管理配置与凭据)
二 连接 MySQL 示例
- 安装与初始化
- 安装驱动:npm install mysql2
- 建议准备 .env:DB_HOST=localhost DB_USER=myuser DB_PASS=mypassword DB_NAME=mydb
- 代码示例(回调风格)
- const mysql = require(‘mysql2’);
const connection = mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASS,
database: process.env.DB_NAME
});
connection.connect(err => {
if (err) throw err;
console.log(‘MySQL connected’);
connection.query(‘SELECT 1 + 1 AS result’, (err, results) => {
if (err) throw err;
console.log(‘Query result:’, results[0].result);
connection.end();
});
});
- 代码示例(Promise/async-await,推荐)
- const mysql = require(‘mysql2/promise’);
const pool = mysql.createPool({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASS,
database: process.env.DB_NAME,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
(async () => {
const [rows] = await pool.query(‘SELECT 1 + 1 AS result’);
console.log(‘MySQL connected, result:’, rows[0].result);
})();
- 提示:生产环境优先使用 连接池 与 参数化查询 防止 SQL 注入。
三 连接 PostgreSQL 示例
- 安装与初始化
- 安装驱动:npm install pg
- 切换到 postgres 用户并创建数据库与角色:
- sudo -u postgres psql
- CREATE USER app_user WITH PASSWORD ‘StrongPass!’;
- CREATE DATABASE app_db OWNER app_user;
- \q
- 代码示例(使用连接池)
- const { Pool } = require(‘pg’);
const pool = new Pool({
user: ‘app_user’,
host: ‘localhost’,
database: ‘app_db’,
password: ‘StrongPass!’,
port: 5432,
});
(async () => {
const { rows } = await pool.query(‘SELECT 1 + 1 AS result’);
console.log(‘PostgreSQL connected, result:’, rows[0].result);
})();
- 提示:PostgreSQL 默认使用 peer/ident 认证,本地开发可用本地用户映射或改为 md5 密码认证。
四 连接 MongoDB 示例
- 安装与初始化(Ubuntu 20.04 示例)
- 导入公钥:wget -qO - https://www.mongodb.org/static/pgp/server-6.0.asc | sudo apt-key add -
- 添加源:echo “deb [ arch=amd64,arm64 ] https://repo.mongodb.org/apt/ubuntu focal/mongodb-org/6.0 multiverse” | sudo tee /etc/apt/sources.list.d/mongodb-org-6.0.list
- 安装与启动:sudo apt update && sudo apt install -y mongodb-org
sudo systemctl start mongod && sudo systemctl enable mongod
- 代码示例(原生驱动)
- const { MongoClient } = require(‘mongodb’);
const uri = ‘mongodb://myuser:mypassword@localhost:27017/mydb’;
(async () => {
const client = new MongoClient(uri);
await client.connect();
console.log(‘MongoDB connected’);
const db = client.db(‘mydb’);
const col = db.collection(‘test’);
await col.insertOne({ name: ‘NodeJS’, ts: new Date() });
const docs = await col.find({}).toArray();
console.log(docs);
await client.close();
})();
- 代码示例(Mongoose ODM)
- npm install mongoose
- const mongoose = require(‘mongoose’);
(async () => {
await mongoose.connect(‘mongodb://myuser:mypassword@localhost:27017/mydb’);
console.log(‘MongoDB connected with Mongoose’);
const schema = new mongoose.Schema({ name: String, ts: Date });
const Model = mongoose.model(‘Test’, schema);
await Model.create({ name: ‘NodeJS’, ts: new Date() });
const docs = await Model.find();
console.log(docs);
await mongoose.disconnect();
})();
- 提示:MongoDB 默认监听 27017 端口,远程访问需配置 bindIp 与 防火墙。
五 常见问题与排查
- 服务未启动:sudo systemctl status mysql/mongod/postgresql;未运行则 sudo systemctl start …
- 认证失败:核对用户名、密码、主机与数据库;PostgreSQL 检查 pg_hba.conf 的认证方式
- 远程连接被拒:检查数据库 bind-address(如 0.0.0.0)、云安全组/防火墙放行对应端口(如 3306/5432/27017)
- 驱动与语法:MySQL 使用 mysql2/promise 更友好;PostgreSQL 使用 pg 的连接池;MongoDB 原生驱动与 Mongoose 二选一
- 连接泄漏:确保 connection.end()/client.close()/pool.end() 在异常与流程结束时执行(可用 try/finally 或框架中间件保证)