In this post I will describe how you can have Postgres elegantly generate UUIDs when inserting rows, when you are using the npm package node-pg-migrate. There are multiple benefits of using UUID (Universally Unique IDentifier) as primary keys in your SQL tables, such as:

  • They are unique across tables, databases and servers which means you will never have problems with unique constraints when merging data.
  • They are non-guessable. It’s easy to guess that the resource /employee/101 exists if /employee/100 exists.

One of the cons of using UUIDs is that it’s tiresome having call a function to generate them whenvever you insert a record.

Enter uuid-ossp

The Postgres module uuid-ossp provides multiple functions for generating UUIDs using a number of standard algorithms. The module is included with most newer versions of Postgres so I will not go into detail about how to install it.

Enable uuid-ossp with Node-pg-migrate

The first thing you have to do to enable uuid-ossp with node-pg-migrate is to create a new migration using the cli:

node-pg-migrate create enable_uuid_ossp

Then you add this to your new migration file, which will enable the uuid-ossp extension when run:

enable_uuid_ossp.ts

import { MigrationBuilder } from "node-pg-migrate";

export async function up(pgm: MigrationBuilder): Promise<void> {
  pgm.createExtension("uuid-ossp", {
    ifNotExists: true
  });
}

export async function down(pgm: MigrationBuilder): Promise<void> {
  pgm.dropExtension("uuid-ossp");
}

Using uuid-ossp in your migration files

I can now use the extension in my migration files like this, to automactically generate a UUID in the ID column:

create_users_table.ts

import { MigrationBuilder, PgLiteral } from 'node-pg-migrate';

export async function up(pgm: MigrationBuilder): Promise<void> {
    pgm.createTable("users", {
        id: {
            type: "uuid",
            default: new PgLiteral("uuid_generate_v4()"),
            notNull: true,
            primaryKey: true
        },
        email: { type: "varchar(320)", notNull: true, unique: true },
        password: { type: "varchar(255)", notNull: true },
        firstName: { type: "varchar(80)", notNull: true },
        lastName: { type: "varchar(100)", notNull: true },
        created_at: {
            type: "timestamp",
            notNull: true,
            default: pgm.func("current_timestamp")
        }
    });
}

export async function down(pgm: MigrationBuilder): Promise<void> {
    pgm.dropTable("users");
}

Now, whenever I insert a row into the users table, Postgres will automatically be generating a UUID for the row. Great!