如何从C#执行多条PostgreSQL语句完成迁移前数据库初始化操作

1. 如何解决CREATE DATABASE cannot be executed from a function报错问题 #

这个报错是PostgreSQL的原生设计限制，没有可在应用代码中使用的规避方案：

• CREATE DATABASE命令不允许在事务块、自定义函数、匿名DO块中执行，和你的语法写法无关，不存在能把「检查库是否存在+创建库」逻辑封装到单个SQL块中执行的方案。
• 仅在psql命令行脚本场景下，可以通过元命令实现单脚本逻辑，示例如下：

SELECT 'CREATE DATABASE database_name'
WHERE NOT EXISTS (SELECT datname FROM pg_database WHERE datname = 'database_name')\gexec

该语法仅能在psql交互环境或psql执行的脚本中运行，无法在C#应用的普通SQL查询接口中调用，因此你预期的方案1在当前应用场景下无法实现。

2. 第二种实现方案是否属于不规范的实现方式 #

第二种方案是完全规范的标准实现，是该场景下的通用做法：

• EF Core内置的context.Database.EnsureCreated()方法内部就是完全一致的逻辑：先连接到PostgreSQL默认的postgres系统库，查询目标库是否存在，不存在就单独执行CREATE DATABASE命令。
• 现有代码只需要注意两个细节即可直接使用：
  • 初始连接字符串必须指定连接到postgres默认库，不能填写目标库，否则目标库不存在时会直接连接失败
  • 执行该逻辑的数据库账号需要拥有CREATEDB权限

补充优化建议 #

你希望减少C#判断逻辑的需求可以在后续步骤中实现：用户创建、权限授予操作没有事务限制，可以封装到单个SQL的DO块中执行，完整流程示例如下：

// 第一步：检查并创建数据库
using (var sysConn = new NpgsqlConnection("连接到postgres默认库的连接串"))
{
    sysConn.Open();
    var checkDbSql = "SELECT 1 FROM pg_database WHERE datname='database_name'";
    using var checkCmd = new NpgsqlCommand(checkDbSql, sysConn);
    var dbExists = checkCmd.ExecuteScalar();
    
    if(dbExists == null)
    {
        using var createCmd = new NpgsqlCommand("CREATE DATABASE database_name", sysConn);
        createCmd.ExecuteNonQuery();
    }
}

// 第二步：检查创建用户并授权
using (var targetConn = new NpgsqlConnection("连接到目标库的连接串"))
{
    targetConn.Open();
    var initUserSql = @"
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'app_user') THEN
        CREATE ROLE app_user WITH LOGIN PASSWORD '你的用户密码';
    END IF;
END
$$;
GRANT CONNECT ON DATABASE database_name TO app_user;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_user;
";
    using var userCmd = new NpgsqlCommand(initUserSql, targetConn);
    userCmd.ExecuteNonQuery();
}

// 第三步：执行EF迁移
context.Database.Migrate();

内容的提问来源于stack exchange，提问作者Bunnynut