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
I know this, but i want ask to resolve use function in sqlite3 to use any where :) — Kuong Knight
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ö
yep, i added code and error. So do you have solution for this? — Kuong Knight
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

Mikael Lepistö Mikael Lepistö · Accepted Answer · 2017-01-14T12:08:26

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 Answers