Using Postgres functions, like NOW(), in query helpers

[jamiesyme]

Consider the code:

const fields = {
  name: 'John Doe',
  created_at: ...
};
await sql`
  INSERT INTO users ${sql(fields)}
`;

What would I use for created_at to send NOW()?

I know I could probably use the following, but my hope is to keep the SQL compact with the dynamic query helper:

await sql`
  INSERT INTO users (
    name,
    created_at
  ) VALUES (
    ${fields.name},
    NOW()
  )
`;

[porsager]

That's a really good use case 😊

I think I would like it to work like below, but that is not supported currently, so I'll see if I can get that into 2.0.

const fields = {
  name: 'John Doe',
  created_at: sql`now()`
}
await sql`
  INSERT INTO users ${sql(fields)}
`

For now, how about setting default now() in your schema on the created_at column?

[jamiesyme]

Awesome! That looks like an intuitive approach to me.

I thought about the default now() solution, but it doesn't work for setting updated_at columns, unfortunately. I think I'll just use new Date on the application-side for the now; it shouldn't be an issue for my app :)

Thanks!

[porsager]

Ah yes, there are many other use cases where this usage would be great. Just yesterday I wanted to use a custom type in the same way.

Wrt. updated_at I'd put that in a before update trigger in the database though 😅

[porsager]

Fyi or for anyone else curious, it's basically just:

create or replace function updated_at() returns trigger as
$$
begin
  new.updated_at := now();
  return new;
end;
$$ language plpgsql;

-- and then use that trigger on your table
create trigger updated_at before update on users for each row execute procedure updated_at()

[jamiesyme]

Yup, good call on the trigger. Works great! I was worried about losing the timestamp of user edits during db migrations if I used a trigger, but I think I'll introduce a separate column to track user edits if/when I need to.

I think there may be a typo in your code tho (create function updated_at() vs execute procedure changed_at()).

[porsager]

Oh, you're right :) The db I'm working on at the moment uses changed_at, so I guess it's in my fingers ;)

With regards to migrations you can disable triggers globally during the migration.

[jamiesyme]

Ahh, cool! I'm still new to triggers, so that's good to know.

[lishine]

Fyi or for anyone else curious, it's basically just:

create or replace function updated_at() returns trigger as
$$
begin
  new.updated_at := now();
  return new;
end;
$$ language plpgsql;

-- and then use that trigger on your table
create trigger updated_at before update on users for each row execute procedure updated_at()

Could you do this with this lib?

I am trying to this

import postgres from 'postgres'

const sql = postgres()

async function run() {
    try {
        await sql.begin(async (sql) => {
            await sql`
                CREATE TABLE public.users (
                    id serial NOT NULL UNIQUE,
                    created_at timestamptz NOT NULL DEFAULT now(),
                    updated_at timestamptz NOT NULL DEFAULT now(),
                    email text NOT NULL,
                    password text NOT NULL,
                    salt text NULL,
                    PRIMARY KEY (id),
                    UNIQUE(id),
                    UNIQUE(email)
                );`

            await sql`
                CREATE OR REPLACE FUNCTION public.set_current_timestamp_updated_at()
                RETURNS
                    TRIGGER AS $$
                DECLARE
                    _new record;
                BEGIN
                    _new := NEW;
                    _new.updated_at = NOW();
                    RETURN _new;
                END;
                $$ LANGUAGE plpgsql;

                CREATE TRIGGER set_public_users_updated_at
                BEFORE
                    UPDATE ON public.users
                FOR EACH ROW
                EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
                COMMENT
                    ON TRIGGER set_public_users_updated_at ON public.users 
                IS 'trigger to set value of column updated_at to current timestamp on row update';`
        })
    } catch (error) {
        console.log(error)
    } finally {
        await sql.end()
    }
}

run()

But it says - cannot insert multiple commands into a prepared statement.
Already divided into two parts. I don't know if it works at all if divided more...

[jamiesyme]

I believe you have to divide the second part into CREATE OR REPLACE FUNCTION ... and CREATE TRIGGER ....

[lishine]

I believe you have to divide the second part into CREATE OR REPLACE FUNCTION ... and CREATE TRIGGER ....

Thank you very much. Indeed it solves it.
But the last part still makes a problem, please help:

            await sql`
                CREATE TRIGGER set_public_users_updated_at
                BEFORE
                    UPDATE ON public.users
                FOR EACH ROW
                EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
                COMMENT
                    ON TRIGGER set_public_users_updated_at ON public.users 
                IS 'trigger to set value of column updated_at to current timestamp on row update';`

Error: cannot insert multiple commands into a prepared statement

[lishine]

OK, I divided into this and it works!

            await sql`
                CREATE TRIGGER set_public_users_updated_at
                BEFORE UPDATE ON public.users
                FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();`
            await sql`
                COMMENT ON TRIGGER set_public_users_updated_at ON public.users 
                IS 'trigger to set value of column updated_at to current timestamp on row update';`

[hobberwickey]

I understand you're planning a fix for this in 2.0, but if anyone needs a slightly hacky fix in the meantime I solved this by just modifying the internal addValue function slightly, and used a typed value with a type of -1. It's unescaped, and therefore unsafe, but presumably the only use for this would be to use postgres functions/values anyway right?

function addValue(x, xargs, types) {
    const type = getType(x), 
          i = type.type === -1 ? types.length : types.push(type.type)

    if (i > 65534)
      throw errors.generic({ message: 'Max number of parameters (65534) exceeded', code: 'MAX_PARAMETERS_EXCEEDED' })

    if (type.type === -1) {
      return type.value
    } else {
      xargs.push(type)
      return '$' + i
    }
  }

In my case I needed to upsert a value into a table with a serial primary key which I was able to do with this addValue function and this query.

INSERT INTO projects (
	"id",
	"owner_id",
	"name"
) VALUES (
	${ project.id || {type: -1, value: "DEFAULT"} },
	${ project.owner_id },
        ${ project.name }
)
ON CONFLICT ON CONSTRAINT projects_pkey
DO UPDATE SET
       "name" = ${ project.name }

Perhaps something like this could be used internally, and the interface could be cleaned up by adding some function like
sql.raw(str) which just returns {type: -1, value: str}

[Minigugus]

What about something like:

import { call, values } from 'sqlt';

const fields = {
  name: 'John Doe',
  created_at: sql`now()`,
  // OR
  //created_at: call('now'),
}
await sql`
INSERT INTO users ${values([fields])}
`

// INSERT INTO users VALUES ($0, now());

I've built a proof of concept around sql tagged template string if you're interested: https://github.com/Minigugus/sqlt/blob/37b13a1cc4dc2eb6b4272117624f786889edc0b8/src/index.test.ts#L126-L139
I hope this is like a preview of what postgres could become.

[anazzani]

I thought about the default now() solution, but it doesn't work for setting updated_at columns

Sorry but what do you mean exactly? It seems to work just fine for me, you just have to make sure the field is NOT present in the object you're saving.

Am I missing something?

[anazzani]

I thought about the default now() solution, but it doesn't work for setting updated_at columns

Sorry but what do you mean exactly? It seems to work just fine for me, you just have to make sure the field is NOT present in the object you're saving.

Am I missing something?

Never mind, I was referring to the INSERT, but you were talking about the UPDATE: apologies.

[bas080]

Another helper I can think of is md5 hash: ${sql({hash: sql.md5(some_string)})}

[porsager]

@bas080 Just use the built in PostgreSQL function ;)

sql`select md5(${ 'some string })`

[bas080]

Agreed, but the places where the helpers apply is less because these functions aren't supported. I have had cases where I had quite some properties but couldn't use the helper because one value required md5... I'm fine with typing a bit more

[porsager]

Ah I see - that makes sense.. With the upcoming api you'll be able to make your own functions like this:

const md5 = x => sql`md5(${ x })` 

await sql`
  select ${ md5('some value') } as hash
`