sql - 在存储过程后面抽象我们的数据库有哪些优点和缺点？

Question

在工作中，我们目前有一个 PostgreSQL 数据库，我们通过一些 Perl 绑定(bind)来访问它以访问数据库并编码对 Perl 类型的响应。这工作正常，但由于各种原因，我们对 Perl 越来越不满意。我们一直在考虑的一种选择是将此 API 中的大部分工作作为 plpgsql 存储过程转移到数据库本身。

简要示例

例如，我们可能在数据库中有以下内容:

-- This matches our 'Entity::Artist' object
CREATE TYPE loaded_artist (
  artist_id uuid,
  revision_id integer,
  artist_tree_id integer,
  name text,
  sort_name text,
  artist_type_id integer,
  -- etc
);

-- This gets the latest 'master' version of an artist and joins in basic data
-- from the artist tree
CREATE FUNCTION get_latest_artist_by_mbid(in_mbid UUID)
RETURNS SETOF loaded_artist AS $$
  BEGIN
    RETURN QUERY
    SELECT
      artist_id, revision_id, artist_tree_id, name.name,
      sort_name.name AS sort_name, artist_type_id
    FROM artist
    JOIN artist_revision USING (artist_id)
    JOIN artist_tree USING (artist_tree_id)
    JOIN artist_data USING (artist_data_id)
    WHERE artist.master_revision_id = revision_id
      AND artist_id = in_mbid;
  END;
$$ LANGUAGE 'plpgsql';

现在我们当前的 Perl API 可以有效地简化为以下内容:# 在 Perl 中

package Data::Artist;
sub get_latest_by_mbid {
    my ($self, $mbid) = @_;
    return $self->new_from_row(
        $self->sql->select_single_row_hash(
            'SELECT * FROM get_latest_artist_by_mbid(?)',
            $mbid));
}

这合理吗？

从表面上看，我喜欢这个。我们:

• 放弃 Perl，但不要改用其他语言。这意味着我们可以将我们的实际应用程序转移到 Python/ future 的任何东西，并且我们的大部分 API 已经完成。
• 通过指定诸如 RETURNS SETOF loaded_artist 之类的内容，从 PostgreSQL 获得额外的类型安全性
• 仍然通过 PGTAP 进行单元测试和其他内容。

有几个缺点:

• 可能会缩短开发周期，因为我们现在必须替换数据库中的函数。这不是世界末日，但这有效地在我们的工作流程中引入了一个以前没有的“编译”步骤。
• 版本控制可能更困难，但肯定有办法做到这一点

有没有人做过这样的工作？您会鼓励这样做，还是充满危险？

---

脚注:关于我们案例的更多信息

这是一个开源网站。我们分发我们数据库的转储供人们导入到 PostgreSQL 数据库中。我们没有计划在短期内放弃 PG，因此与数据库无关的决定并不真正适用于我们。我们是一个非常小的团队(2 个付费开发人员，更多开源贡献者)，这让我们在部署策略方面非常灵活。

Answer 1

优点:

• 数据库架构/布局/存储更改对应用程序完全隐藏；
• 您有一个统一的 API 来处理数据库；
• 您可以对数据库中完成的所有操作进行大量记录，包括所有 SELECT 查询。

缺点:

• 对优秀 DBA 的需求增加；
• 对数据库开发人员的需求增加，他们充分了解数据库如何处理数据以及数据库端过程如何工作；
• 数据库端和应用程序端团队之间需要更多的协调；
• ORM 集成困难；
• 使用存储过程限制了数据库优化的可能性，一些查询(尤其是报告)会带来性能问题，最好改用 View ，因为优化器可以将谓词下推到 View 中并正确利用索引。

最佳组合是在数据库端实现大量业务逻辑，而不仅仅是包装函数。

模式版本控制是可能的。对配置表中的数据进行版本控制更加棘手。在我参与的其中一个项目中，这是通过为我们处理这部分的外部工具(基于 perl)完成的:

• 首先将数据加载/提取到中间表；
• 然后分析 RI 约束和所有可能的违规行为；
• 可以在将数据加载到实时表之前对其进行操作；
• 可以一次性定义和提取生成多个表的业务对象；
• 存在多种处理匹配实体的方法，例如:覆盖、合并、复制。

我们正在对提取文件(纯 SQL)进行版本控制，并在安装脚本中有一个特殊步骤来加载新配置。