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:
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...
});
}
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()"));
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)
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:
Docs: knexjs.org/guide/utility.html#uuid
This way we get independent from database functions.