TypeORM : How to get next item using UUID?

Question

in Order class we have a UUID and other fields:

export class Order extends BaseEntity {
    @Field()
    @PrimaryGeneratedColumn("uuid")
    id: string;

    // other fields ...

}

the resolver could be sth like this:

 @Query(() => Order)
    async getNextOrder(
        @Arg("data")
        { orderId }: GetNextOrderInput,
    ) {
        return await Order.findOne(id:MoreThan(orderId);
    }

I know that it`s not working, So how to achieve the next item? is UUID time based and can be used as an index?

Amir Meyari · Accepted Answer · 2019-12-10T08:14:18.337

As I found @PrimaryGeneratedColumn("uuid") uses UUID v4 that is completely random and the v1 one is time-based as mentioned here, so no chance of ordering in the base of UUID v4. @Generated("increment") decorator is another option as mention in the doc but I had err and looks Postgres requires "increment" to be a primary key. so I prefer to tu use createdAt(of type Date)column for ordering and LEAD to get Id of next order:

PREPARE findNextId(uuid) AS(
    WITH producedNextIds AS(
         WITH ordersOfStore AS (
        SELECT
             "order"."id", "order"."createdAt"
         From
             "order"
         INNER JOIN "site" ON "order"."storeId" = "site"."id"
        )
        SELECT "id" , "createdAt",
            LEAD("id",1) OVER (
                ORDER BY "createdAt"
                ) next_id
        FROM
            ordersOfStore
    )
        SELECT
            "id" , "createdAt", next_id
        FROM producedNextIds
        WHERE
            "id" = ($1)
);
EXECUTE findNextId('cb5c5bf0-f781-46e1-9ae1-68c875bb3a56');

Any better solution would be appreciated!

score 0 · Answer 2 · answered Sep 05 '21 at 01:03

I stumbled upon the same problem. I am using postgres, and I wanted to use UUID for my primary keys, but I wanted them not to be totally random.

Besides the practical aspect, I found this post mentioning how totally random UUID keys might impact database storage.

To get TypeORM generating the PK using uuid_generate_v1mc instead of UUID v4, I added default: 'uuid_generate_v1mc()' to my migration:

await queryRunner.createTable(
  new Table({
    name: 'tags',
    columns: [
      {
        name: 'id',
        type: 'uuid',
        isPrimary: true,
        isGenerated: true,
        generationStrategy: 'uuid',
        default: 'uuid_generate_v1mc()',
      }
    ],
    // the rest of my table definition

This made the table creation look like this:

CREATE TABLE "tags" (
  "id" uuid NOT NULL DEFAULT uuid_generate_v1mc(),
  -- rest of my table definition

TypeORM : How to get next item using UUID?

2 Answers2