PostgreSQL：pg 客户端

装

npm install pg

基础查询

import { Pool } from 'pg';

const pool = new Pool({
    host: 'localhost',
    port: 5432,
    user: 'app',
    password: 'secret',
    database: 'mydb',
});

// 或用连接字符串
const pool2 = new Pool({
    connectionString: process.env.DATABASE_URL,
});

const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [42]);
console.log(rows);

⚠ 永远用参数化查询（$1 $2）——别拼字符串，防 SQL 注入。

完整示例

import { Pool } from 'pg';

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 20,                         // 连接池大小
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
});

pool.on('error', err => console.error('Pool error:', err));

async function getUser(id) {
    const { rows } = await pool.query(
        'SELECT id, name, email FROM users WHERE id = $1',
        [id]
    );
    return rows[0] || null;
}

async function createUser(name, email) {
    const { rows } = await pool.query(
        'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
        [name, email]
    );
    return rows[0];
}

async function close() {
    await pool.end();
}

事务

async function transferMoney(fromId, toId, amount) {
    const client = await pool.connect();    // 从池里拿一个独占连接
    try {
        await client.query('BEGIN');
        await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
        await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
        await client.query('COMMIT');
    } catch (err) {
        await client.query('ROLLBACK');
        throw err;
    } finally {
        client.release();              // ★ 必须 release
    }
}

批量插入

// 慢：N 次查询
for (const user of users) {
    await pool.query('INSERT INTO users(name) VALUES($1)', [user.name]);
}

// 快：一次插入
const values = users.map((u, i) => `($${i + 1})`).join(',');
const params = users.map(u => u.name);
await pool.query(`INSERT INTO users(name) VALUES ${values}`, params);

// 更快：COPY（百万级数据）
import { from as copyFrom } from 'pg-copy-streams';
const stream = client.query(copyFrom('COPY users(name) FROM STDIN CSV'));
// pipe 数据流进去

JSON 字段

await pool.query('CREATE TABLE items (id serial, data jsonb)');

await pool.query(
    'INSERT INTO items(data) VALUES($1)',
    [{ name: 'Alice', tags: ['a', 'b'] }]
);

const { rows } = await pool.query(
    "SELECT * FROM items WHERE data->>'name' = $1",
    ['Alice']
);

pg 自动把 JS 对象转成 JSONB。

流式读大结果

import { Pool } from 'pg';
import QueryStream from 'pg-query-stream';

const client = await pool.connect();
const stream = client.query(new QueryStream('SELECT * FROM huge_table'));

for await (const row of stream) {
    process(row);
}

client.release();

现代替代：postgres.js

npm install postgres

import postgres from 'postgres';

const sql = postgres(process.env.DATABASE_URL);

// 简洁的 tagged template
const users = await sql`SELECT * FROM users WHERE id = ${42}`;
//                                                       ^^ 自动参数化（安全）

await sql`INSERT INTO users ${sql({ name: 'Alice', email: 'a@x' })}`;

await sql.end();

postgres.js 比 pg 更现代、性能更好——新项目可以直接用。

类型映射

⚠ bigint / numeric 默认是 string——避免精度问题。需要 number：

import pg from 'pg';
pg.types.setTypeParser(20, parseInt);   // BIGINT → number

坑

• 永远参数化查询——$1 $2，不要拼字符串
• 事务用 pool.connect() 拿独占连接（不是 pool.query）
• release() 必须调——否则连接泄漏
• 长查询要超时：SET statement_timeout = '5s'
• Postgres 默认连接数有限（100）—— 多实例时算好 max

下一篇：MongoDB。