CODEWITHSUNDEEP
node.jssqliteknex.jsbookshelf.js
Kuong Knight
Kuong Knight

Reputation: 115

Add function to sqlite3 using knex, bookshelf

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"

Upvotes: 1

Views: 1251

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19728

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
}),

Upvotes: 1

Related Questions