string replacements in migrations

[TimoStolz]

I'd like to replace some strings when deploying migrations. This could be handy for schema qualifiers in PostgreSQL, as an example.

Let's say, I want to deploy the migration

create table public.users (
   id varchar primary key,
   username varchar
);

However, I'd like to create the users table in the schema my_app, in order to separate it from other tables in public, that I don't want to share with some application or user.

I know, that I can use psql variables for this, and that is what I am currently doing. However, there are some shortcomings with this approach:

• Some IDEs have good support for SQL (with database-aware code completion, error checking when specifying foreign key constraints, type awareness when coding stored procedures, ...)—but they break, if I include psql specific syntax like :VAR, \set, \if and so on (I'd love to use DataGrip by JetBrains)
• It get's cumbersome to use psql variables in stored procedures, because the body of stored procedures is basically a string.

create function public.my_account()
returns public.users
as $body$
  select * from public.users where id = current_setting('jwt.claims.sub', true)
$body$
language sql
stable;

In order to replace the public within $body$, I'd have to do something like this:

select format(
  $block$
    create function %1$I.my_account()
    returns %1$I.users
    as $body$
      select * from %1$I.users where id = current_setting('jwt.claims.sub', true)
    $body$
    language sql
    stable;
  $block$,
  :'app_schema'
)\gexec

This is the same as above, but replaces public with the content of the psql variable :'app_schema'. Unfortunately, the code is much less readable, and obviously, there is no longer IDE support for such things, even syntax highlighting is mostly broken.

Therefore, I'd love to see configurable string replacements, e.g. a file

public -> my_app_schema

[theory]

Some syntax highlighters recognize variables. Here, for example, is how VS Code formats a psql variable in SQL mode:

Otherwise, it can be helpful to use the quoted syntax for psql variables, since syntax highlighters usually know what to do with them. Here's VS Code again:

I just started to type up wish for the use of (https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT) for functions; I was not aware of the \gexec syntax. NEAT! But I think the other thing you can do is set search_path before creating the function, and the function will be created with that search path in place. Check this out:

CREATE SCHEMA :"schema";

SET search_path = :"schema";

CREATE FUNCTION foo() RETURNS TEXT LANGUAGE SQL AS $$
    SELECT current_setting('search_path')
$$ ;

\df foo
SELECT foo();

If I run it with the :schema set, note that the output has the proper schema, both where the function lives and in the search path when the function runs:

$ psql --set schema=blah -f try.sql
BEGIN
CREATE SCHEMA
SET
CREATE FUNCTION
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
 blah   | foo  | text             |                     | func

 foo  
------
 blah

[TimoStolz]

I don't want to start a debate about IDEs. VSCode has good syntax support for various SQL dialects, that's right, but in my opinion, DataGrip is better at code completion, even with type awareness.

The use of SET search_path, in combination with CREATE FUNCTION has a major drawback, unfortunately. The function body is evaluated with the runtime search_path, rather than the search_path it was defined with. Thus, when you change the search path from definition to execution, the function will no longer find the referenced tables—or even worse, it will resolve tables with the same name, but in another schema. (Well, that might be considered a feature. It depends on the use case.)

create schema if not exists :"schema";

set search_path = :"schema";

create table if not exists users (
   id varchar primary key,
   username varchar
);

insert into users (id, username) 
values ('my-user-id', 'me')
on conflict do nothing;

create or replace function my_account()
returns users
as $body$
  select * from users where id = current_setting('jwt.claims.sub', true)
$body$
language sql
stable;

select set_config('jwt.claims.sub', 'my-user-id', false);

select * from :"schema".my_account();

-- when we reset the runtime search_path, ...
reset search_path;

-- ...the function fails with `ERROR: relation "users" does not exist`
select * from :"schema".my_account();

When using schema-qualified identifiers in the function body, we avoid this problem. Well, there is a third option:

create or replace function my_account()
returns users
as $body$
  select * from users where id = current_setting('jwt.claims.sub', true)
$body$
language sql
stable
set search_path = :"schema";

We can define the function with a specific search_path. That works for resolving the names, no matter the current search_path, but has two other drawbacks:

• You can no longer expect unqualified identifiers to be resolved in the runtime search_path.
• You loose performance, because your function call can no longer be inlined (https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions) This is especially important, if your function is executed several times per query, for example with lateral joins. (I have this use case a lot.)

[TimoStolz]

Imagine, the code would look like this:

create or replace function "$schema".my_account()
returns "$schema".users
as $body$
  select * from "$schema".users where id = current_setting('jwt.claims.sub', true)
$body$
language sql
stable;

This would be valid SQL, working with all IDEs. And even better, you can execute it, even without changing "$schema" to another value, because the literal "$schema" would be a valid schema name, at least in PostgreSQL.

During development, I would use "$schema" as literal schema name, because then, I have DataGrip's code completion, when referring tables or functions in later migrations. Code completion is not possible with psql variables for current IDEs, because up to now, they are not smart enough to infer their value from environment files.

[theory]

Yeah, setting search_path in the CREATE FUNCTION statement is the way to go; it's exactly what it was designed to solve. Having unqualified identifiers ignore the runtime search path is a security feature in my mind. But I agree it makes certain things more difficult, and just using the qualifications directly in the function is even more secure.

The challenge with implementing a templating solution over SQL is that it has to have a general solution to avoid conflicts. Leaving it up to the native database client, which understands its own SQL syntax flavors, avoids the whole issue.

Have you thought about wrapping psql in a script that does a bit of preprocessing using something like envsubst and then calls the real psql? Then you can set engine.pg.client to your wrapper script? It would do something like:

• Take as its first argument a path to a file setting variables
• Load those variables
• Parse the options to figure out what file will be sent to the database (sqitch always specifies the file via the --file option)
• Run the file through envsubst and write it out to a temporary location
• Replace the file in the options with the temp file
• Pass all remaining arguments to a call to psql
• Delete the temp file

Another approach might be to do some preprocessing as part of a build process, e.g., to run all your deploy/revert/verify files through some templating system and rewrite them for a particular environment (dev, stg, prd, whatever) before distributing them.

I'm not saying we'd never support our own variables in files, but up to now we have left files entirely under the control of the native database clients. Doing string migration would be somewhat of a philosophical change, and would need to be designed and implemented very carefully.

Personally, I'd like to see this kind of support added to psql.

[TimoStolz]

Dear @theory, thank you very much for the detailed explanation regarding engine.pg.client. It helped me to come up with a wrapper script that I'd like to share. Maybe, it's useful to somebody else.

psql-wrapper.sh

#!/usr/bin/env bash

declare -a modifiedArguments=("$@")
declare tmpFile=$(mktemp)
declare originalFile

# cleanup on exit
cleanup() { 
  # for debugging:
  #echo "arguments: ${modifiedArguments[@]}"
  #cat $tmpFile

  # delete the temporary file at exit
  rm -f "$tmpFile"
}

trap cleanup EXIT

# find and modify the --file option to point at $tmpFile
for index in "${!modifiedArguments[@]}"
do
  if [[ "${modifiedArguments[index]}" == "--file" ]]
  then
    originalFile="${modifiedArguments[$((index+1))]}"
    modifiedArguments[$((index+1))]=$tmpFile
    break
  fi
done

# replace environment variables in the migration file
envsubst < "$originalFile" > "$tmpFile"

# call psql
psql "${modifiedArguments[@]}"

[theory]

Cool!