Proposal for multiple dialect support

[sudhakar]

Currently mosql has excellent support for pgsql covering most of the pgsql statements (which again is close to ANSI standard). It also provides helper methods to expand on the pgsql dialect. So new query-types, helper-methods & conditionals can be added to expand mosql capabilities (which BTW is a awesome feature).

My usecase for mosql is to use it for other DBs like MySQL, Vertica, HiveQL & Aerospike. It is possible to add additional query-types using mosql.queryTypes.add, but unfortunately it gets added to the global namespace.

I am not an expert on SQL's DSL, but from what I have explored it looks like these DBs support either subset of query-types or a superset with additional query-type. So if I want to add a select query type for HiveQL, then I may have to prefix it as hive-select & so on. Also features like row_to_json are specific to pgsql & its error to use it in mysql or in other dialects. It would be nice if each dialect is placed in their own namespace, so that all SQL like dialects can be supported easily.

To accommodate this, I propose following api changes.

1. Allow user to specify the dialect while importing mosql like

   var pgsql = require('mongo-sql')('pg');
   pgsql.registerQueryType(
     'select-one-user'
   , 'select {columns} from users {joins} {where} limit 1'
   );

   This will add 'select-one-user' to pgsql dialect

2. Throw an exception if either requested query-type or helper-method is not registered for that dialect.

3. Allow query-types, helpers & conditionals in each dialect to introspect itself. For example,

     var pgsql = require('mongo-sql')('pg');
   
     // return list of all supported query-types
     pgsql.queryTypes.list
   
     // return list of all supported query-helpers
     pgsql.queryHelpers.list 
   
     // return list of query-helpers supported by 'alter-table' 
     // ie ['ifExists', 'only', 'table', 'action']
     pgsql.queryTypes.get('alter-table').queryHelpers 

   This will allow other libraries to interactively suggest the query-helpers based on query-type.

4. Move helpers directory to lib/pg-helpers & index.js to lib/mosql.js (So that gulp configuration can be simplified). New helpers can then be added to lib/hive-helpers & so on.

I am yet to get fully accustomed with codebase. So feel free to suggest changes & alternate approaches. Would love to hear your thoughts on this.

[jrf0110]

Multiple dialects is something I've always wanted to support, but never actually got around to working on. I might suggest a directory layout like:

- dialects/
  - global/
    - query-helpers/
      - tables.js
      - where.js
      ...
    - actions.js
    - ...
  - postgres/
    - query-helpers/
    ...
    index.js

Creating a dialect would be something like this:

// dialects/some-dialect/index.js
// Maybe we could just go off filename? Probably best to just specify
module.exports.name = 'some-dialect';

// When a dialect is initialized, it will be passed a fresh SQL builder instance
// The instance will have all of the existing methods MoSQL has now
// It will come pre-packaged with all of the helpers inside of `dialects/global`
// This will give the dialect a good starting point, but will also allow it to
// override conflicting behavior
module.exports.init = function( mosql ){
  // mosql.dialect === 'some-dialect'

  // At this point, we would register all of the helpers necessary
  [
    './conditional-helpers'
  , './query-types'
  , './query-helpers/limit'
  , './query-helpers/offset'
  , './query-helpers/order'
    ...
  ].forEach( function( module ){
    // Pass this instance to each helper module
    require( module )( mosql )
  })
};

Registering the dialect with mosql

var mosql = require('mongo-sql');
mosql.registerDialect( require('./dialects/some-dialect') );

var someDialect = mosql('some-dialect');

This would also allow simple dialect inheritance:

Dialect A:

module.exports.name = 'dialect-a';

module.exports.init = function( mosql ){
  mosql.registerQueryType( 'my-query-type', 'select {columns} from {table}' )
});

Dialect B:

module.exports.name = 'dialect-b';

module.exports.init = function( mosql ){
  // Register all of Dialect A's helpers onto Dialect B's instance
  require('../my-dialect-a').init( mosql );

  mosql.registerQueryType( 'my-query-type-1', 'select {columns} from {table} limit 1' )
});

OK - I need to get some more work done, but I'll keep on thinking about suggestions. There are a lot of cool things we can do here

[sudhakar]

Wonderful. Good to know that we are on the same page. Will think more about this & probably start prototying in some branch, so that we can discuss on the code(Oh yeah github is freaking awesome for these sort of stuff).

[sudhakar]

I am pretty much in sync with the proposed directory structure. For the new dialects, I dont fully agree on overriding the default for conflicting behaviour. Consider the example of AQL, a key-value store where only a very small subset of ANSI SQL is supported. So if we extend from ANSI SQL(assuming this is the global dialect), then we may have to remove more than 90% query-types & query-helpers which will be overkill.

If we decide to leave ANSI SQL dialect residue on top of AQL, then the library will allow users to create joins in AQL ending up in generating wrong statements for that dialect. In these kind of scenario's, mosql should throw error indicating the wrong behaviour.

I will come up with some prototype to explain the idea better. BTW, whats your plan on maintaining API compatibility with current version (hope you are ok to have some breaking changes here & there)

[jrf0110]

Dialects will definitely merit a major version bump (3.x.x), so, big changes to the API are fine :)

Perhaps then we don't worry about inheriting from globals for all dialects. Instead, we create an ansi-sql dialect that can optionally be inherited from (as I described in my previous post):

// PG Dialect
module.exports.name = 'postgres';

module.exports.init = function( mosql ){
  require('./dialects/ansi-sql').init( mosql );
  // PG specific stuff
};

[jrf0110]

Then, each MoSQL instance would come as a blank slate to be filled in

[sudhakar]

Yep. Only those required query types will be added to those dialects. Even the corresponding helpers set will start with a blank slate. One more idea that i would love to see in the core is to introspect each query type for its available helpers. Consider the following example

mosql.queryTypes.add(
  'select-one-user'
, 'select {columns} from users {joins} {where} limit 1'
);

Currently there's no way to get all the supported helpers for this query type. Although user can do a mosql.queryTypes.get('select-one-user') to get the template & parse it to get the supported helpers, it does certainly feel hacky.

[wmertens]

I would like this too, for sqlite. In particular, it has support for JSON but the syntax is a bit verbose (e.g. to get a field you must write json_extract(jsonField, '$.fieldname')) and thus it would be nice to use the psql-like syntax instead.

Since sqlite syntax is mostly the same as psql syntax, would there be a minimal way to achieve this, rather than the full-blown dialect setup?

[jrf0110]

@wmertens I think the proper solution would be to support multiple dialects. There's no easy to convert the pgsql "col"->"field" way of accessing json to sqlite. What you can do is use the $custom conditional helper:

{
  where: {
    $custom: [`json_extract(user, '$.name') = $1`, 'Bob']
  }
}

[wmertens]

Fair enough, thanks!

Actually, I wonder if there may be impedance mismatches between the mongo-sql API and how other databases work; perhaps supporting dialects is more trouble than it's worth…

[wmertens]

I suppose that a truly generic solution would be a convertor between a (simple) AST and SQL. Then each dialect can define the grammar of the dialect, and most json representations would be very similar. So no effort would be made to present a common language, but most SQL would look the same anyway. You could even write SQL, have it get converted to AST and pass it to other code that augments the SQL somehow before converting it back.

I say simple AST because whitespace and exact quoting is not important. SELECT count(*) as "c" FROM bar; would maybe become {type: "select", columns: [{type: "fn", name: "count", args: [{type: "star"}], alias: "c"}], from: {type: "id", name: "bar"}}. A bit uglier than the current representation, but still easy to process/generate, especially with helper functions and maybe syntactic sugar (the same example might become {$select: {$cols: [{$fn: {count: '*'}, alias: 'c'}], $from: "bar"}}.

See https://www.sqlite.org/syntaxdiagrams.html for getting a feeling for the size of that AST.

[jrf0110]

I suppose that a truly generic solution would be a convertor between a (simple) AST and SQL

and

You could even write SQL, have it get converted to AST and pass it to other code that augments the SQL somehow before converting it back

We're on the same page for what should be going into v2. The idea that MoSQL is simply an AST has been with for the past few years. Its place is understanding the structure and meaning of a sql statement, and a proper AST is the way to go there. Parsing, Transforming, and Generation will be the job of MoSQL in v2

I don't mind if it's uglier. We can build tools on top to make it nice :)