pg-promise项目教程：实现SELECT后INSERT的原子操作-优快云博客

pg-promise项目教程：实现SELECT后INSERT的原子操作

pg-promise PostgreSQL interface for Node.js 项目地址: https://gitcode.com/gh_mirrors/pg/pg-promise

引言

在数据库操作中，"先查询后插入"是一个非常常见的业务场景。本文将详细介绍如何使用pg-promise库在PostgreSQL中实现这一模式的原子操作，包括传统方法和高性能的单查询方案。

业务场景分析

考虑这样一个典型需求：

首先尝试查询特定条件的记录
如果记录存在，返回其ID
如果不存在，则插入新记录并返回新ID

这种模式在用户系统、标签系统等场景中非常常见，比如根据用户名获取用户ID，如果用户不存在则自动创建。

基础表结构

我们先定义一个简单的用户表作为示例：

CREATE TABLE Users(
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE -- 用户名唯一
);

传统实现方案

基本实现

使用pg-promise的task方法可以确保整个操作在一个事务中完成：

function getInsertUserId(name) {
    return db.task('getInsertUserId', t => {
        return t.oneOrNone('SELECT id FROM Users WHERE name = $1', name, u => u && u.id)
            .then(userId => {
                return userId || t.one('INSERT INTO Users(name) VALUES($1) RETURNING id', name, u => u.id);
            });
    });
}

使用说明

调用这个函数非常简单：

getInsertUserId('张三')
    .then(userId => {
        console.log('获取到的用户ID:', userId);
    })
    .catch(error => {
        console.error('操作失败:', error);
    });

ES7 async/await版本

对于支持ES7的环境，可以使用更简洁的async/await语法：

async function getInsertUserId(name) {
    return db.task('getInsertUserId', async t => {
        const userId = await t.oneOrNone('SELECT id FROM Users WHERE name = $1', name, u => u && u.id);
        return userId || await t.one('INSERT INTO Users(name) VALUES($1) RETURNING id', name, u => u.id);
    });
}

高性能单查询方案

虽然上述方案可以工作，但它有两个潜在问题：

需要执行两次数据库查询，影响性能
在高并发场景下可能出现竞态条件

单查询的优势

将整个操作封装为单个SQL查询可以：

显著提高性能（只需一次数据库往返）
确保数据完整性（避免并发插入冲突）
减少网络开销

实现思路

PostgreSQL 9.5+可以使用ON CONFLICT语法实现：

INSERT INTO Users(name) 
VALUES($1) 
ON CONFLICT (name) 
DO UPDATE SET name = EXCLUDED.name 
RETURNING id;

对于旧版本，可以使用CTE（Common Table Expressions）实现类似功能：

WITH sel AS (
    SELECT id FROM Users WHERE name = $1
), ins AS (
    INSERT INTO Users(name)
    SELECT $1
    WHERE NOT EXISTS (SELECT 1 FROM sel)
    RETURNING id
)
SELECT id FROM sel
UNION ALL
SELECT id FROM ins;