Support for View Creation

[thefatoneinthecorner]

Hi,

Is there any support for view creation (analogous to createTableIfNotExists and friends)?

Many thanks,

-- Alastair

[KMayne]

+1 This would be very useful since Knex acts as an abstraction over different SQL syntaxes between our development (SQLite) and production (Postgres) databases.

[elhigu]

Sorry, no such support in knex so far. @KMayne you just need to create own query abstraction with knex.raw depending if it is ran on sqlite or pg.

[mashaalmemon]

@elhigu I was looking for the ability to have view creation/maintainence in migrations as well.

You mention that knex.raw can be used. Can you give a quick example of how to achieve this using knex.raw? I'm assuming you don't mean create the entire view with knex.raw, but just sub in 'create view' vs 'create table' somehow with knex.

We use postgres in our project.

[elhigu]

What kind of query you need to have generated?

[mashaalmemon]

Something like these (depending on the options we're looking to use at view creation time):

CREATE VIEW SELECT a.*, b.* WHERE a.b_id=b.id;
CREATE VIEW SELECT a.*, b.* WHERE a.b_id=b.id WITH LOCAL CHECK OPTION;
CREATE VIEW SELECT a.*, b.* WHERE a.b_id=b.id WITH CASCADED CHECK OPTION;

[elhigu]

This should work for your cases:

knex.schema.raw(`CREATE VIEW ? WITH CASCADED CHECK OPTION`, [
  knex.select('a.*', 'b.*').whereColumn('a.b_id','b.id')
]) 

[gpetrov]

Any news on views creation in the Knex API? Would be really nice to have an generic API integration instead of database dependent creation commands.

[kibertoad]

@gpetrov This functionality is still missing, but any contributions on that front would be dearly appreciated.

[gpetrov]

Maybe @rijkvanzanten can help - there is also a great need for query the views schema, next to creating and managing them. All databases, even SQLite have Views support and those are pretty powerful.

[kibertoad]

This is now available in 0.95.12-rc4