Add function to sqlite3 using knex, bookshelf

Question

I use postgresql in development scope, it have function call "uuid_generate_v4()" In test scope i use sqlite3 but migrations code do not work because missing "uuid_generate_v4()". So can i reslove this proplem?

connection.schema.createTableIfNotExists('notification', (table) => {
  // UUID
  table.uuid('id').primary().defaultTo(connection.raw('uuid_generate_v4()'))

  // Adds a "created_at" and "updated_at" columns on the database,
  // setting these each to "dateTime" types.
  table.timestamps()

  table.string('type', 255)

  table.string('summary', 255)

  table.string('text', 255)

  table.string('actionText', 255)

  table.string('actionUrl', 255)

  table.uuid('recipient').references('user.id')
}),

failed with "create table if not exists "notification" ("id" char(36) default uuid_generate_v4(), "created_at" datetime, "updated_at" datetime, "type" varchar(255), "summary" varchar(255), "text" varchar(255), "actionText" varchar(255), "actionUrl" varchar(255), "recipient" char(36), foreign key("recipient") references "user"("id"), primary key ("id")) - SQLITE_ERROR: near "(": syntax error"

Switch your test environment to PostgreSQL. Developing and testing with different databases is sort of defeating the purpose of testing. — mu is too short, Jan 07 '17 at 07:39
I know this, but i want ask to resolve use function in sqlite3 to use any where :) — Kuong Knight, Jan 07 '17 at 12:08
If you add your failing migration code to the question, it would be easier to answer how to add SQL dialect specific implementation there so that it would generate uuid differently for sqlite. — Mikael Lepistö, Jan 07 '17 at 14:52
yep, i added code and error. So do you have solution for this? — Kuong Knight, Jan 07 '17 at 16:18
Generate the UUIDs in your code rather than asking the database to do. But this is the least of your problems, there are so many differences between SQLite and PostgreSQL that using SQLite to run your tests is, frankly, foolish. — mu is too short, Jan 07 '17 at 18:08
Possible duplicate of [Is there UID datatype in SQLITE if Yes then how to generate value for that](http://stackoverflow.com/questions/10104662/is-there-uid-datatype-in-sqlite-if-yes-then-how-to-generate-value-for-that) — mu is too short, Jan 07 '17 at 18:08

Mikael Lepistö · Accepted Answer · 2017-01-14T12:20:14.343

As @mu-is-too-short commented, by all means I don't recommend doing this, but this is how it can be done:

let uuidGenerationRaw = connection.client.config.client === 'sqlite3' ? 
  `(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))))` :
  `uuid_generate_v4()`;

connection.schema.createTableIfNotExists('notification', (table) => {
  table.uuid('id').primary().defaultTo(connection.raw(uuidGenerationRaw));
  // ... rest of the columns
}),

Add function to sqlite3 using knex, bookshelf

1 Answers1