[Adonis 5] Using UUID in the tables

[jrishabh55]

How to tell Model to use uuid instead of incrementing it.

In 4.* we do

class User extends Model {
  static get incrementing () {
    return false
  }
}

But what can be done for 5 ?

i'm using beforeCreate hook to set UUID

  @beforeCreate()
  public static async addUUID (user: User) {
    user.id = uuid()
  }

[wemersonrv]

I'm not start with Adonis 5 yet, but i think u don't need the incrementing method anymore... because now, each column now uses TypeScript @decorators, so try the @prepare decorator: https://preview.adonisjs.com/guides/models/columns#prepare instead using hooks:

export default class User extends BaseModel {
  @column({
    prepare: (value: string) => uuid()
  })
  public id: string
}

Edited: Sorry my bad... this is to transform data... for example. Encryption when creating AND updating with a new value. I think your code for @beforeCreate hook is right. Don't work?

[jrishabh55]

Prepare executes every time, Create / Update, i wouldn't wanna generate a new id every time, by following that example
i can do

@column({
    prepare: (value: string) => value ? value : uuid()
  })

But that still will be called every time.

Now my code beforeCreate works, it does create a record with UUID, but when we do user.create the return response gives me an incrementing ID instead of thar UUID, in database UUID is stored only, so like earlier it may be a local variable there.

[popovicieduard]

Instead of using id inside your databse, use uuid:

      table.uuid('uuid').index()

  @beforeCreate()
  public static async createUUID (model: Model) {
    model.uuid = uuid()
  }

Also, having uuid's as primary keys can slow down your database if there are a lot of records, In some of my apps, I use id's and uuid's.

[jrishabh55]

Instead of using id inside your databse, use uuid:

      table.uuid('uuid').index()

  @beforeCreate()
  public static async createUUID (model: Model) {
    model.uuid = uuid()
  }

Also, having uuid's as primary keys can slow down your database if there are a lot of records, In some of my apps, I use id's and uuid's.

that make sense, Someone mentioned that we should use UUID and not incremental IDs, i'm not sure how correct it is but the idea there was that incremental ids prevent parallel writes in a table, For a mysql table locks for a write and other write can only happen after the first one. but in case of UUID that is not the case. i might be totally off the mark on this

[popovicieduard]

by parallel writes you are referring to duplicate requests or? you can implement idempotentcy requests middleware to avoid duplicate inserts, and as for avoiding concurrency issues you can use transactions, Adonis Lucid supports transactions by default and you can make use of them when querying and persisting data to the database. you can read more treansactions on knex.js docs too.

And one more thing, I forgot to say that on the models you can serializeAs: 'id' the uuid column to get the desired json output

[jrishabh55]

That make sense, but what i was referring to is that the writes in database, will they happen in parallel or synchronously when using an increment instead of a UUID

[McSneaky]

What database are you using? If you are using Postgres then you can let PG generate UUIDs for you, like it would with auto incrementing IDs and there's no need to mess with hooks

UUIDv4 collision rate is so low you won't have to worry about it most likely. If I remember correctly you had to generate 1mil UUID / sec for 7 mil years to get first collision.

UUIDs are little bit slower. To search by ID 100 milion times it takes about 2 sec, while with UUIDs it takes about 4 sec in PG (on stupid small synthetic test)

UUIDs are bigger, and that's the main problem imo. If you are dealing with a lot of writes, like storing bilions of rows in DB you want to save every possible byte you can. You can save considerable amount of space when not using UUIDs

In most applications UUID upsides overweight the downsides by quite a lot. Like not leaking business stats with IDs (how many users you have in system, how many clients, how many invoices you have sent etc), protects from some ID based attacks etc.
You could make 2 sets of IDs, one for internal usage (joins etc) and other UUID that you expose to the world. But then you have to deal with 2 different kinds of IDs, which adds code complexity

[RodolfoSilva]

Use SQLite. But I thinks this will have the same result with other databases.

To reproduce the issue:

• clone my repository
• run: yarn
• run: yarn test

In my repository exists a test file to help you to reproduce the issue...

https://github.com/RodolfoSilva/demo-adonisjs-uuid-bug/blob/master/test/post.spec.ts

[RodolfoSilva]

Hey @thetutlage.

Have you able to reproduce the error and run the tests on my sample project?

[thetutlage]

Yup. Going to work on the fix :)

[RodolfoSilva]

Nice 😁 😜

[RodolfoSilva]

Fixed in 8.5.0.

adonisjs/lucid#605 (comment)

Thanks @thetutlage 😁

[RodolfoSilva]

Full example with UUID using Lucid ^8.5.0.

You will have to define a static property on your model for AdonisJS to use the local value.

import { BaseModel, beforeCreate, column } from '@ioc:Adonis/Lucid/Orm'
import { v4 as uuid } from 'uuid'

export default class Author extends BaseModel {
  public static selfAssignPrimaryKey = true

  @column({ isPrimary: true })
  public id: string

  @beforeCreate()
  public static async createUUID (model: Author) {
    model.id = uuid()
  }
}

[dajpes]

  public static selfAssignPrimaryKey = true

This is extremely important and should be on the docs

[RodolfoSilva]

This is very new @dajpes 😂 , but I agree. Can you make a PR with this? If you can't, I will try to do this latter.

[EDMONDGIHOZO]

this works for me, but i didn't use uuid as primary key,

[carlmathisen]

Thanks @dajpes, missing selfAssignPrimaryKey tripped me up for a few hours.

It is mentioned in the docs. For any future readers: https://docs.adonisjs.com/guides/models/introduction#selfassignprimarykey

[kennedyrs]

This is how to use uuid as primary key

First i create a migration to run berfore all others

export default class Extensions extends BaseSchema {
  public async up () {
    await this.db.rawQuery('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')
      .knexQuery
  }

  public async down () {}
}

Then my migrations can run uuids as primary key

export default class UsersSchema extends BaseSchema {
  protected tableName = 'users'

  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table
        .uuid('id')
        .primary()
        .defaultTo(this.db.rawQuery('uuid_generate_v4()').knexQuery)

      table.string('name', 255).notNullable()

    })
  }

  public async down () {
    this.schema.dropTable(this.tableName)
  }
}

My Model

import { DateTime } from 'luxon'
import Hash from '@ioc:Adonis/Core/Hash'

import {
  column,
  BaseModel,
} from '@ioc:Adonis/Lucid/Orm'

export default class User extends BaseModel {
  @column({ isPrimary: true })
  public id: string

  @column()
  public name: string

}

[RodolfoSilva]

Be careful, this approach will not work with automated test.

[gui8515]

Not work in MySQL, and func UUID() of MySQL doesn't work either

[gmoreiraGP]

'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'

I wasted half my day trying to work this out, you saved me.

Your solution was the best of all.

[McSneaky]

'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'

I wasted half my day trying to work this out, you saved me.

Your solution was the best of all.

I'd suggest to use this instead:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA pg_catalog VERSION "1.1"

Otherwise you might get into issues that uuid functions does not exist in DB, when doing some backup restoration / disaster recovery.
Difference is that regular CREATE EXTENSION makes uuid functions accessible only in given schema, but when switching schemas while doing some live patching / recovering with minimal downtime etc, then those functions won't be accessible in any other schemas nor DBs
Adding it to pg_catalog ensures it's uuid is accessible across schemas and DBs

[gmoreiraGP]

great, i've updated my code with the select part of the postgres schema table. tks.

[ghost]

I did this to make sure the uuid is also created when adding an entry to the db outside of adonis

 table
         .uuid('id')
         .unique()
         .index()
         .primary()
         .notNullable()
         .defaultTo(this.db.knexRawQuery('(UUID())'))