Repository: knex/knex
Pull request #5617 (2023-07-01 21:11:33)

Add uuid helper function
Meant to be used in the schema creation:
```javascript
await knex.schema
    .createTable('users', table => {
      table.uuid('id').defaultTo(knex.fn.uuid());
      table.string('user_name');
    })
```

Can someone confirm that there is no uuid function in Redshift?

coveralls: 
[![Coverage Status](https://coveralls.io/builds/61110848/badge)](https://coveralls.io/builds/61110848)

coverage: 92.683% (-0.09%) from 92.774% when pulling **d5e31fe5b04e71b341a5e7c475fb96266e98b0dc on arbezerra:master** into **d9f333313b89581cad25bcf0905a4e3276cbe606 on knex:master**.

arbezerra: The coverage decreased because oracle and redshift aren't tested. Should I make a test to cover those lines or put a comment to ignore coverage?
OlivierCavadenti: > The coverage decreased because oracle and redshift aren't tested. Should I make a test to cover those lines or put a comment to ignore coverage?

Can you add test for OracleDB (same that Oracle) and for Redshift that test that the uuid function throw an Error with "Redshift does not have a uuid function" and it's seems good !

Can you also add a documentation ? 
https://github.com/knex/documentation
arbezerra: I think everything is ok now.
Opened PR knex/documentation#527

Patch:
diff --git a/lib/knex-builder/FunctionHelper.js b/lib/knex-builder/FunctionHelper.js
index d97bedcd..74abf94f 100644
--- a/lib/knex-builder/FunctionHelper.js
+++ b/lib/knex-builder/FunctionHelper.js
@@ -14,6 +14,32 @@ class FunctionHelper {
     return this.client.raw('CURRENT_TIMESTAMP');
   }
 
+  uuid() {
+    switch (this.client.driverName) {
+      case 'sqlite3':
+      case 'better-sqlite3':
+        return this.client.raw(
+          "(lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6))))"
+        );
+      case 'mssql':
+        return this.client.raw('(NEWID())');
+      case 'pg':
+      case 'pgnative':
+      case 'cockroachdb':
+        return this.client.raw('(gen_random_uuid())');
+      case 'oracle':
+      case 'oracledb':
+        return this.client.raw('(random_uuid())');
+      case 'mysql':
+      case 'mysql2':
+        return this.client.raw('(UUID())');
+      default:
+        throw new Error(
+          `${this.client.driverName} does not have a uuid function`
+        );
+    }
+  }
+
   uuidToBin(uuid, ordered = true) {
     const buf = Buffer.from(uuid.replace(/-/g, ''), 'hex');
     return ordered
diff --git a/types/index.d.ts b/types/index.d.ts
index 5f72e3fe..99a6f8c3 100644
--- a/types/index.d.ts
+++ b/types/index.d.ts
@@ -3192,6 +3192,7 @@ export declare namespace Knex {
 
   interface FunctionHelper {
     now(precision?: number): Raw;
+    uuid(): Raw;
     uuidToBin(uuid: string, ordered?: boolean): Buffer;
     binToUuid(bin: Buffer, ordered?: boolean): string;
   }


Test patch:
diff --git a/test/integration2/query/misc/additional.spec.js b/test/integration2/query/misc/additional.spec.js
index f05d4d2a..a9c51328 100644
--- a/test/integration2/query/misc/additional.spec.js
+++ b/test/integration2/query/misc/additional.spec.js
@@ -392,6 +392,54 @@ describe('Additional', function () {
           expect(knex.fn.now(6).toQuery()).to.equal('CURRENT_TIMESTAMP(6)');
         });
 
+        it('should allow using .fn.uuid to create raw statements', function () {
+          const expectedStatement = {
+            [drivers.MsSQL]: '(NEWID())',
+            [drivers.MySQL]: '(UUID())',
+            [drivers.MySQL2]: '(UUID())',
+            [drivers.Oracle]: '(random_uuid())',
+            oracle: '(random_uuid())',
+            [drivers.PostgreSQL]: '(gen_random_uuid())',
+            [drivers.PgNative]: '(gen_random_uuid())',
+            [drivers.SQLite]:
+              "(lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6))))",
+            [drivers.CockroachDB]: '(gen_random_uuid())',
+            [drivers.BetterSQLite3]:
+              "(lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6))))",
+          };
+
+          expect(knex.fn.uuid().prototype === knex.raw().prototype);
+
+          if (isRedshift()) {
+            expect(() => knex.fn.uuid().toQuery()).to.throw(
+              `${knex.client.driverName} does not have a uuid function`
+            );
+          } else {
+            expect(knex.fn.uuid().toQuery()).to.equal(
+              expectedStatement[knex.client.driverName]
+            );
+          }
+        });
+
+        it('should allow using .fn-methods to generate a uuid with select', async function () {
+          const uuid = await knex.select(knex.raw(knex.fn.uuid() + ' as uuid'));
+          expect(uuid[0].uuid).to.match(
+            /^[0-9A-F]{8}-[0-9A-F]{4}-[1-5][0-9A-F]{3}-[89AB][0-9A-F]{3}-[0-9A-F]{12}$/i
+          );
+        });
+
+        it('should allow using .fn-methods to be a default value', async function () {
+          await knex.schema.dropTableIfExists('default_uuid_table');
+          await knex.schema.createTable('default_uuid_table', (t) => {
+            t.uuid('uuid').defaultTo(knex.fn.uuid());
+          });
+          await knex('default_uuid_table').insert({});
+          const uuid = await knex('default_uuid_table').select('uuid');
+          expect(uuid[0].uuid).to.match(
+            /^[0-9A-F]{8}-[0-9A-F]{4}-[1-5][0-9A-F]{3}-[89AB][0-9A-F]{3}-[0-9A-F]{12}$/i
+          );
+        });
+
         it('should allow using .fn-methods to convert uuid to binary', function () {
           const originalUuid = '6c825dc9-c98f-37ab-b01b-416294811a84';
           const binary = knex.fn.uuidToBin(originalUuid);
diff --git a/test/unit/schema-builder/oracledb.js b/test/unit/schema-builder/oracledb.js
index 66795948..b33b039e 100644
--- a/test/unit/schema-builder/oracledb.js
+++ b/test/unit/schema-builder/oracledb.js
@@ -5,6 +5,7 @@ const { expect } = require('chai');
 const sinon = require('sinon');
 const Oracle_Client = require('../../../lib/dialects/oracledb');
 const knex = require('../../../knex');
+const FunctionHelper = require('../../../lib/knex-builder/FunctionHelper');
 const client = new Oracle_Client({ client: 'oracledb' });
 
 describe('OracleDb SchemaBuilder', function () {
@@ -1048,6 +1049,13 @@ describe('OracleDb SchemaBuilder', function () {
     expect(tableSql[0].sql).to.equal('alter table "users" add "foo" raw(16)');
   });
 
+  it('should allow using .fn.uuid to create raw statements', function () {
+    // Integration tests doesnt cover for oracle
+    const helperFunctions = new FunctionHelper(client);
+
+    expect(helperFunctions.uuid().toQuery()).to.equal('(random_uuid())');
+  });
+
   it('test set comment', function () {
     tableSql = client
       .schemaBuilder()
diff --git a/test/unit/schema-builder/redshift.js b/test/unit/schema-builder/redshift.js
index 0137fede..ee5f6a2f 100644
--- a/test/unit/schema-builder/redshift.js
+++ b/test/unit/schema-builder/redshift.js
@@ -7,6 +7,7 @@ let tableSql;
 
 const Redshift_Client = require('../../../lib/dialects/redshift');
 const knex = require('../../../knex');
+const FunctionHelper = require('../../../lib/knex-builder/FunctionHelper');
 const client = new Redshift_Client({ client: 'redshift' });
 
 const equal = require('assert').equal;
@@ -891,6 +892,14 @@ describe('Redshift SchemaBuilder', function () {
     );
   });
 
+  it('redshift doesnt have a uuid function', function () {
+    const helperFunctions = new FunctionHelper(client);
+
+    expect(() => helperFunctions.uuid()).to.throw(
+      `${client.driverName} does not have a uuid function`
+    );
+  });
+
   it('allows adding default json objects when the column is json', function () {
     tableSql = client
       .schemaBuilder()