Ability to have `uuid` for the primary key? about joist-orm HOT 10 CLOSED

This seems doable with probably not too many gyrations; are you thinking db-assigned uuids or client-assigned uuids?

If client-assigned uuids, the id params could be made required, so clients have to pass them when creating.

And the "assign ids" step in PostgresDriver would have to be skipped, based on a config flag of some sort.

Both code generation (i.e. via joist-codegen.json flag) and runtime (i.e. PostgresDriver via a constructor arg) would probably need to be told to use client-assigned ids.

Fwiw I am currently pretty sold on keeping the requirement for a single primary key field, and that the name of that field is id.

from joist-orm.

Hey - trying to find where we can do db-assigned uuids here. Is it just on migrations?

from joist-orm.

@mmahalwy what does the DDL of your table look like (just the id column)? Is it like this:

    contact_id uuid DEFAULT uuid_generate_v4 (),

From https://www.postgresqltutorial.com/postgresql-uuid/

from joist-orm.

CREATE TABLE "Application" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
)

from joist-orm.

We are also migrating this first:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

I know there's also:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

from joist-orm.

@mmahalwy so right now Joist is able to "cheat" a little bit insofar as saving new rows is a two step process:

1. Bulk access each of the sequences to get a new id for each new entity
2. Then do INSERTs with the ids already inserted

This is a little weird, but the reason we do it is b/c then the order of the INSERT statements doesn't matter. Like, if we did not does this, if I have a new book and a new author, I can't do INSERT INTO book (author_id) VALUES (...) before first doing the INSERT INTO author and getting back the id.

Granted, in this instance it seems trivial to know which table should "go first", but in large schemas and lots of FKs, it can be more difficult, and even impossible if two tables both depend on each other (i.e. a cycle in the FK relationships).

So, anyway, currently Joist does step 1 in this case:

https://github.com/stephenh/joist-ts/blob/main/packages/orm/src/drivers/PostgresDriver.ts#L308

My question is, for UUIDs, this assignNewIds doesn't work because you don't have sequences.

But, instead could we just create UUIDs directly in memory, and then do the INSERTs with the ids already populated, i.e. INSERT INTO authors (id, first_name) VALUES ('1234-1234-1234-1234', 'first')?

If so, that should make extending PostgresDriver to support UUIDs a lot easier, because you won't need to change the fundamental "step 1 assign/create ids", "step 2 do INSERTs w/already created ids".

from joist-orm.

@stephenh sorry I missed your last comment. I see – yeah the driver would need to be changed to support built-in UUID generation. We can have it be done in the driver to 'fake' it, as you've suggested, but I'd imagine folks may want this to be natively supported as joist may not be the only thing touching the db – just like folks want created at and updated at to auto populate.

from joist-orm.

I'd imagine folks may want this to be natively

Ah yeah, agreed; my assumption is that the db could have an id with a default uuid (for non-Joist / raw SQL clients), but Joist could still generate INSERTs with its own Joist/JS-generated uuids, and so be able to still up-front assign ids, and since Joist's INSERTs had an explicit id column in them, the db default would just be skipped / not applied.

from joist-orm.

I've start poking around at this in #223 . Besides just the feature itself, I'm also feeling out how to have a sane repository / test case setup, i.e. I'm trying out a new uuid-ids package that would be separate from the current large/covers-all-the-things integration-tests package.

So far the only thing I've done is add uuid as a database type, so that npm run codegen doesn't immediately blow up, but now there are compile errors (which is somewhat expected, I haven't changed anything in the codegen yet). So, dunno, probably ~4-5 rounds of "run it and see what breaks" and I'll keep seeing how it goes.

from joist-orm.

@mmahalwy I merged an initial version of this in #223; see the packages/tests/uuid-ids mini-project for an example of how the basics work. Note that I've only tested loading and savings, i.e. I've not tried queries yet (they should? work) and have not like ran the whole joist test suite against the "with uuid ids" codepaths (although I don't think that'd be necessary, but just fwiw / as a disclaimer).

Feel free to file any follow up issues around uuid keys, but I'll close this initial issue as fixed. Thanks!

from joist-orm.