第 14 課：資料庫驅動程式和連線池

Node.js 核心：從基礎到高級

第 4 部分：無框架構建

亞洲開發網

1.PostgreSQL（頁）

import pg from 'pg'

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
})

// Query
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [userId])
const user = rows[0]

// Parameterized query — chống SQL injection
const result = await pool.query(
  'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
  [name, email]
)

// Transaction
const client = await pool.connect()
try {
  await client.query('BEGIN')
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, fromId])
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, toId])
  await client.query('COMMIT')
} catch (err) {
  await client.query('ROLLBACK')
  throw err
} finally {
  client.release()
}

2.MySQL（mysql2）

import mysql from 'mysql2/promise'

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 20,
})

const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [userId])

// Prepared statements
const [result] = await pool.execute(
  'INSERT INTO users (name, email) VALUES (?, ?)',
  [name, email]
)

3.SQLite（更好的sqlite3）

import Database from 'better-sqlite3'

const db = new Database('app.db', { verbose: console.log })

// Synchronous — nhanh nhất cho SQLite
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
  )
`)

const insert = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)')
const user = insert.run('Nguyen Van A', '[email protected]')

const getUser = db.prepare('SELECT * FROM users WHERE id = ?')
const row = getUser.get(1)

// Transaction
const insertMany = db.transaction((users: { name: string; email: string }[]) => {
  for (const u of users) insert.run(u.name, u.email)
})
insertMany([{ name: 'B', email: '[email protected]' }, { name: 'C', email: '[email protected]' }])

4. 查詢產生器 (Knex.js)

import Knex from 'knex'

const knex = Knex({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: { min: 2, max: 20 },
})

// Query builder
const users = await knex('users')
  .select('id', 'name', 'email')
  .where('active', true)
  .orderBy('created_at', 'desc')
  .limit(10)

// Join
const orders = await knex('orders')
  .join('users', 'orders.user_id', 'users.id')
  .select('orders.*', 'users.name as user_name')
  .where('orders.status', 'pending')

5. 遷移

// migrations/20260101_create_users.ts
import type { Knex } from 'knex'

export async function up(knex: Knex) {
  await knex.schema.createTable('users', (table) => {
    table.increments('id').primary()
    table.string('name').notNullable()
    table.string('email').unique().notNullable()
    table.timestamps(true, true)
  })
}

export async function down(knex: Knex) {
  await knex.schema.dropTable('users')
}

npx knex migrate:latest
npx knex migrate:rollback
npx knex seed:run

下一篇： 快取、佇列和後台作業 — Redis、BullMQ、cron。