knex raw query schema builder

Question

I would like use the knex raw for schema building. Mainly I want to use to create this session table because seems like knex doesn't have collate functionality

CREATE TABLE session (
    sid varchar NOT NULL COLLATE "default",
    sess json NOT NULL,
    expire timestamp(6) NOT NULL
)

WITH (OIDS=FALSE);

ALTER TABLE "session" ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;

I tried putting the entire thing in the quotes in knex.raw(), but it's giving me errors. What is the best strategy in this case?

Answer 1

The short answer is to use knex.schema.raw rather than knex.raw.

The raw below worked for me. I created an abbreviated version of the SQLs since I was getting SQLite Syntax errors while testing with SQLite. Also, note the use of .on('query-error',... which is helpful in returning SQL engine errors.

let rawCreate = 'CREATE TABLE session ( '+
    'sid varchar NOT NULL, sess json NOT NULL, '+
    'expire timestamp(6) NOT NULL );';
let rawCreateOriginal = 'CREATE TABLE session ( '+
    'sid varchar NOT NULL COLLATE "default", sess json NOT NULL, '+
    'expire timestamp(6) NOT NULL ) WITH (OIDS=FALSE);';

let rawAlter = 'ALTER TABLE "session" ADD KEY "sid" NOT DEFERRABLE INITIALLY IMMEDIATE;';
let rawAlterOriginal = 'ALTER TABLE "session" ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;';

return knex.schema.hasTable('session')
    .then(function(exists) {
        if (!exists) {
            return knex.schema.raw(rawCreate)
            .on('query-error', function(ex, obj) {
                console.log("DA.INIT81 query-error ex:", ex, "obj:", obj);
            }).then(function() {
                console.log("DA.INIT01 Created table session.");
            }).then(function() {
                return knex.schema.raw(rawAlter)
            }).then(function() {
                console.log("DA.INIT02 Altered table session.");
            });
        }
    })
    .catch((ex) => {
        console.error("DA.INIT88 Create or Alter table session is broken. ex:",ex);
    });

Hope this helps! Gary.