CODEWITHSUNDEEP
knex.js
Bryce York
Bryce York

Reputation: 1056

Can you create views using Knex.js defined by raw SQL?

Writing a migration to create a view based on raw SQL is failing silently. No error message occurs but nothing is created in the DB.

Code below using a generic query (actual query uses window functions, hence the need for raw SQL).

const DAILY_SESSIONS_QUERY = `select session_ids from sessions group by days`;

export async function up(knex: Knex): Promise<void> {
  knex.schema.createViewOrReplace('daily_sessions_view', function (view) {
    knex.raw(DAILY_SESSIONS_QUERY);
  })
}

Upvotes: 2

Views: 2100

Answers (1)

Bryce York
Bryce York

Reputation: 1056

Ended up figuring it out and landed on the following:

const DAILY_SESSIONS_QUERY = `(select session_ids from sessions group by days) as q`;

export async function up(knex: Knex): Promise<void> {
    return knex.schema.createViewOrReplace('daily_sessions_view', function (view) {
        view.columns(['session_ids']);
        view.as(knex.select('*').fromRaw(DAILY_SESSIONS_QUERY));
    })
}

Upvotes: 3

Related Questions