DEV Community

Jessica Alves
Jessica Alves

Posted on

Using uuid in Knex.js and PostgreSQL

I've been working on a personal project using Node.js and PostgreSQL. I also decided to use Knex.js (a SQL query builder) to run migrations and build schemas. So when creating a table which I wanted to replace a simple incremental id with uuid I had to search a little bit more than just reading Knex and Postgres documentation. I found a few comments on GitHub issues and Stack OverFlow answers that were very helpful and decided to share this short post in case someone faces the same situation.

Scrolling the documentation

So that's the relevant piece of documentation from Knex related to using uuid when creating a schema in Knex migrations:

Image description

Although there is no example of how to use it, I also found the documentation related to the uuid-ossp extension in Postgres, which has a function called uuid_generate_v4(). And that's the extension and function you should use to write your schema in Knex when creating a table that has an uuid column.

How to use it

So after creating a new Knex migration, let's say you want to create a new table whose primary key will be a uuid column.
Here's an example of how to do it:



import { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
    await knex.schema.raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
        .createTable("table_name", (table) => {
            table.uuid("id", {primaryKey: true}).defaultTo(knex.raw("uuid_generate_v4()"));

//scheme continuation...

    });
}


Enter fullscreen mode Exit fullscreen mode

Note: as stated in Knex documentation you can set the useBinaryUuid to true so that binary(16) is used instead of the default char(36).
Like that:



table.uuid("id", {primaryKey: true, useBinaryUuid: true}).defaultTo(knex.raw("uuid_generate_v4()"));


Enter fullscreen mode Exit fullscreen mode

Also note that the notNullable() is not necessary in this case since we're setting the primaryKey option to true, which in Postgres doesn't allow nullable values.

Top comments (1)

Collapse
 
diogocavilha profile image
Diogo Alexsander Cavilha

Hey, good explanation! Thanks for sharing!
Another way to do that is by using the knex utility functions.

So you could achieve the same result using:

.defaultTo(knex.fn.uuid());
Enter fullscreen mode Exit fullscreen mode

Docs: knexjs.org/guide/utility.html#uuid

This way we get independent from database functions.