使用PostgreSQL函数实现关键业务逻辑

（本文由腾讯混元模型辅助编写）

在软件开发中，业务逻辑的实现位置一直是一个值得探讨的话题。传统的做法是将业务逻辑放在应用层，例如 Python、Java 等编程语言中。然而，将部分业务逻辑实现在数据库中，如使用 PostgreSQL 的 PL/pgSQL 函数，也有其独特的优势。本文将以一个具体的例子来说明在数据库中实现业务逻辑在效率、安全性和可维护性等方面的优势。

示例场景

假设我们有一个系统，需要限制不同级别的用户在指定时间段内使用某个功能的次数。我们可以根据用户的会员级别来控制使用上限。下面是一个使用 PostgreSQL 函数实现该逻辑的示例：

CREATE OR REPLACE FUNCTION public.update_usage(uid uuid, idx integer, lim integer, cnt integer)
 RETURNS TABLE(used integer, "limit" integer)
 LANGUAGE plpgsql
AS $function$
BEGIN
    -- 插入或更新数据，确保操作是原子的
    RETURN QUERY
    INSERT INTO usage (user_id, index, used, "limit")
    VALUES (uid, idx, cnt, lim)
    ON CONFLICT (user_id, index)
    DO UPDATE SET
        used = usage.used + EXCLUDED.used
        ,
        "limit" = EXCLUDED."limit"
    WHERE usage.used + EXCLUDED.used <= usage."limit"
    RETURNING usage.used, usage."limit";
END;
$function$;

效率优势

减少网络开销：在数据库中实现业务逻辑可以减少网络传输的数据量。例如，直接在数据库中进行插入或更新操作，而不是在应用层进行多次数据库交互。

安全性优势

原子性操作：使用数据库事务和锁机制，可以确保操作的原子性，避免并发问题。
权限控制：数据库层可以更细粒度地控制权限，确保只有授权的用户才能执行特定的操作。
防止 SQL 注入：在数据库层实现业务逻辑，可以使用参数化查询，有效防止 SQL 注入攻击。

可维护性优势

集中管理：将业务逻辑集中在数据库层，可以减少应用层的代码复杂度，便于维护。
一致性：数据库层的业务逻辑可以确保数据的一致性，避免因应用层逻辑不一致导致的数据错误。

对比示例

直接在 Python 代码中进行 SQL 查询

import psycopg2

def update_usage(uid, idx, lim, cnt):
    conn = psycopg2.connect(database="yourdb", user="youruser", password="yourpass", host="yourhost", port="yourport")
    cur = conn.cursor()

    try:
        cur.execute("BEGIN")
        cur.execute("INSERT INTO usage (user_id, index, used, limit) VALUES (%s, %s, %s, %s) ON CONFLICT (user_id, index) DO UPDATE SET used = usage.used + EXCLUDED.used, limit = EXCLUDED.limit WHERE usage.used + EXCLUDED.used <= usage.limit RETURNING usage.used, usage.limit", (uid, idx, cnt, lim))
        result = cur.fetchone()
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cur.close()
        conn.close()

    return result

直接调用 PostgreSQL 函数

以 supabase 为例。

from supabase import create_client
import os

SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_KEY = os.getenv("SUPABASE_KEY")

supabase = create_client(SUPABASE_URL, SUPABASE_KEY)


def update_usage(uid, idx, lim, cnt):
    result = supabase.rpc(
        "update_usage", {"uid": uid, "idx": idx, "lim": lim, "cnt": cnt}
    )
    return result

结论

在数据库中实现业务逻辑，如使用 PostgreSQL 的 PL/pgSQL 函数，可以在效率、安全性和可维护性等方面带来显著优势。通过减少网络开销、确保操作的原子性、细粒度的权限控制、防止 SQL 注入以及集中管理和一致性保证，数据库层的业务逻辑实现可以大大提升系统的整体性能和可靠性。当然，具体选择哪种方式还需根据实际业务需求和系统架构进行权衡。