Knex ❤️ PSQL: updating timestamps like a pro
Tags: node, knex, psql, postgres
Published: 2020-02-25 14:22:51

---
title: Knex ❤️ PSQL: updating timestamps like a pro
published: true
description: How to use PSQL trigger functions in Knex.js to keep updated_at in sync
tags: node, knex, psql, postgres
---

Knex.js is the most popular SQL query builder around and the go-to solution for most of us working with PostgreSQL. You can find dozens of articles on dev.to about how to get started, so I decided to focus on a more advanced and often overlooked topic on how to keep the `updated_at` fields *really* updated - automatically.

# What does table.timestamps() do?
If you read along the documentation, upon creating a new table you will probably write a migration like this:

```javascript
exports.up = function(knex) {
  return knex.schema.createTable('products', function(table) {
    table.increments('id').primary();
    table.string('name');
    table.timestamps(false, true);
  });
};
```

The `table.timestamps(false, true)` line adds `created_at` and `updated_at` columns on the table. Both columns default to being not null and using the current timestamp when `true` is passed as the second argument.
While it's sufficent for the `created_at` column, the `updated_at` will remain unchanged even after an update query executed and it's your responsibility to keep it in sync.

There is a good reason behind this behaviour: different SQL dialects - like MySQL - handle automatic updating [pretty well](https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html), but others, like PostgreSQL don't support it.

# What is a Trigger Procedure in PSQL?
Think of a trigger procedures like middlewares in `expressjs`. You have the opportunity to execute functions that modify the inserted values *before* actually committing the update. The `NEW` value holds the new database row for INSERT/UPDATE operations, so setting the `updated_at` field is really easy:

```sql
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
```

# Okay, okay, just give me the code already

First you need to create this trigger function in a migration using `knex.raw`:

```javascript
exports.up = function(knex) {
  return knex.raw(`
    CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER
    LANGUAGE plpgsql
    AS
    $$
    BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;
        RETURN NEW;
    END;
    $$;
  `);
};

exports.down = function(knex) {
  return knex.raw(`
    DROP FUNCTION IF EXISTS update_timestamp() CASCADE;
  `);
};
```

To make sure everything went fine execute the following query:

```sql
SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE routine_type='FUNCTION' AND specific_schema='public';

+------------------+---------------------------------------------+
| routine_name     | routine_definition                          |
|------------------+---------------------------------------------|
| update_timestamp |                                             |
|                  |     BEGIN                                   |
|                  |         NEW.updated_at = CURRENT_TIMESTAMP; |
|                  |         RETURN NEW;                         |
|                  |     END;                                    |
|                  |                                             |
+------------------+---------------------------------------------+
```

# But how to use the function?

This function alone does nothing, we also need to tell the database engine where and when to use it. The best place for this the upcoming migrations where you create a new table - going back to my first example the code will be this:

```javascript
const tableName = 'products';

exports.up = async function(knex) {
  await knex.schema.createTable(tableName, function(table) {
    table.increments('id').primary();
    table.string('name');
    table.timestamps(false, true);
  });

  await knex.raw(`
    CREATE TRIGGER update_timestamp
    BEFORE UPDATE
    ON ${tableName}
    FOR EACH ROW
    EXECUTE PROCEDURE update_timestamp();
  `);
};

exports.down = function(knex) {
  return knex.schema.dropTable(tableName);
};
```

If you run the `\d products` command, at the bottom of the table you will see that the trigger function will be executed on each row update on this table.

```sql
> \d products
+------------+--------------------------+--------------------------------------------------------+
| Column     | Type                     | Modifiers                                              |
|------------+--------------------------+--------------------------------------------------------|
| id         | integer                  |  not null default nextval('products_id_seq'::regclass) |
| name       | character varying(255)   |                                                        |
| created_at | timestamp with time zone |  not null default now()                                |
| updated_at | timestamp with time zone |  not null default now()                                |
+------------+--------------------------+--------------------------------------------------------+
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
Triggers:
    update_timestamp_on_products BEFORE UPDATE ON products FOR EACH ROW EXECUTE PROCEDURE update_timestamp()
```

As always, your likes and feedbacks are much appreciated!