goodybag / mongo-sql

An extensible SQL generation library for JavaScript with a focus on introspectibility

MoSQL - JSON to SQL

Put value and semantic meaning back into your queries by writing your SQL as JSON:

var builder = require('mongo-sql');

var usersQuery = {
  type: 'select'
, table: 'users'
, where: { $or: { id: 5, name: 'Bob' } }
};

var result = builder.sql(usersQuery);

result.values     // Array of values
result.toString() // Sql string value

Result:

select "users".* from "users" where "users.id" = $1 or "users"."name" = $2

Want to play around with the syntax? Check out the playground, documentation, and examples.

Installation:

Node.js:

npm install mongo-sql

Require.js:

jam install mongo-sql

Why JSON?

There are plenty of SQL building libraries that use a very imperative style of building SQL queries. The approach is linear and typically requires a bunch of function chaining. It removes your ability to use the query as a value and requires the library consumer to build their queries in large clumps or all at once. It's sometimes impossible with some of these libraries to reflect on the current state of the query programmatically. What columns have I added? Have I already joined against my groups table? MoSQL uses standard data structures to accomplish its query building, so you can figure out the state of the query at all times.

The reason we use standard JavaScript data structures is so everything is easily manipulated. Arrays are arrays and objects are objects. Everyone knows how to interface with them.

JSON is also a prime candidate for becoming a universally understood data representation. By using Javascript objects, we do not rule out the possibility of interoping with and porting to other languages.

It may not be as pretty as other libraries, but prettiness is not a design principle of this library. The design principles are:

Extensibility

If a feature is not supported, you should be able to add your own functionality to make it supported.

Semantic Value

The query should be represented in a manner that makes sense to developer and machine. The use of standard data structures allows the developer to use standard APIs to manipulate the query.

Examples

{
  type: 'create-table'
, table: 'jobs'
, definition: {
    id:         { type: 'serial', primaryKey: true }
  , user_id:    { type: 'int', references: { table: 'users', column: 'id' } }
  , name:       { type: 'text' }
  , createdAt:  { type: 'timestamp', default: 'now()' }
  }
}

Sorry, these are in no particular order.

• Simple select
• Simple insert
• Insert with values from a select
• Simple select with conditions
• Joins
• Various conditional stuff
• Not in sub-query
• Create view
• Multi-row inserts
• Ridiculous 'with' query with selecting JSON literal
• Various column selection methods
• Two different ways to specify a function
• Rename column
• Alias a table in select
• Drop table
• Create table
• Select distinc
• Update with increment
• Group by
• Various table specification methods
• Insert with sub-query as second value
• With sub-queries
• Adding a constraint with alter table
• Registering a conditional helper
• Updates increment decrement
• Expand foreign key array of integers to an array of JSON results
• Access Fields on HStore and JSON and casting
• Create Index

For even more examples, take a look at the ./tests directory.

How does it work?

Every MoSQL query has a query type specified that maps to a SQL string template. Query types are composed of various strings and query helpers whose output maps to functions.

So type: 'select' uses the query type defined as 'select'. Every other property in the query object maps to a query helper. The 'select' query type starts off like this:

{with} select {columns} {table}...

When you have the following query:

{ type: 'select', table: 'users' }

The table property is mapped to the table query helper.

98% of the functionality in MoSQL is defined through various helper interfaces. If the functionality you need doesn't exist, you can easily register your own behavior and keep on moving along. To see how all of the functionality was implemented, just check out the helpers folder. It uses the same API as library consumers to add its functionality.

Contributing

I will happily accept pull requests. Just write a test for whatever functionality you're providing. Coding style is an evolving thing here. I'll be JSHinting this repo soon and will make the coding style consistent when I do.

Developing

Mongo-sql development is done using Gulp. If you dont have gulp installed globally, install using npm install -g gulp. Then,

1. Install all development dependencies

npm install

2. Watch for source/spec files & run jshint/unit-test cases for changed files

gulp watch

3. Before committing changes, run full jshinting & unit-test cases for browserified version using default gulp target

gulp

Upgrading from 2.4.x to 2.5.x

There are two things you need to look out for:

Do not rely on adding parenthesis to strings (like in columns or returning helpers) in order to prevent MoSQL from attempting to quote the input. Instead use the expression query type:

// select something_custom - another_custom as "custom_result" from "users"
{
  type: 'select'
, table: 'users'
, columns: [
    { expression: 'something_custom - another_custom', alias: 'custom_result' }
  ]
}

If you were relying on expression objects without a type specified to be converted into a function type, this will no longer happen. Queries without types with expression specified in them will get converted to the new expression type.

mongo-sql's Issues

Proposal for multiple dialect support

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.

Parameterize nulls in updates and inserts

As @brianc pointed, doing plain-text null as a value will muck up query caching. Since say:

-- This is different
insert into users (first_name, last_name) values ($1, $2);
-- Than this
insert into users (first_name, last_name) values ($1, null);

This relates to #34

Type Casting

I could see this working like this for columns:

{
  type: 'select'
, table: 'users'
, columns: [
    {
      name: 'some_col'
    , as: 'someCol'
    , cast: 'int'
    }
  ]
}

I think this will get us by for now.. but what about other situations like inside of conditionals?

select * from users where "user"."thing"::int = 7

That's trick because of the way the where is built. It makes these assumptions about cascading fields and helpers if the value is an object, so we can't really put meta information on objects. What we can do is make a conditional helper:

{
  type: 'select'
, table: 'users'
, where: {
    id: {
      $gt: 7
    , $cast: 'int'
    }
  }
}

This is a little funky semantically, but I think will be only way we can do it.

Check parameter types more often in helpers, throw helpful errors

Conditional Helpers: $exists, $notExists

Query Type Inheritance

It would be useful to be able to inherit a query type definition when creating new query types. Also, in some helpers, we check for the query type. If a user creates their own query type that derives from a select query, the helpers should treat it as a select if it select is an ancestor

MySQL query

Can this module build a MySQL query?

example:
{ type: 'select',
table: 'some_table',
where: { id: { '$gt': 1 } }
}

pg: {
text: 'select "some_table".* from "some_table" where "some_table"."id" > $1',
values: [ 1 ]
}

but can i receive MySQL query?

mysql: {
text: 'select some_table.* from some_table where some_table.id > ?',
values: [ 1 ]
}

if not .. can i replace $n values in query? thanks for help

Standardize value adding interface

There are some places where you can avoid adding a value to the array because of the user surrounded their string with '$' signs. This behavior isn't universal. If we standardize how we're adding values, we can make it universal.

limit and offset must be after group by

Expansions in Query Types

It would be nice to embed multiple query helpers in one. I can see this working in two different ways:

var mosql = require('mongo-sql');
mosql.registerQueryType(
  'some_select'
, 'select {columns} from {table} {joins} {where} {pagination}'
);

Where that would expand the {joins} helper to all of the join types: {leftJoin}, {innerJoin} etc. So that when you build your query object, you're still doing this:

{
...
, leftJoin: { /* ... */ }
}

Another way wouldn't actually require any change I think. You could just define a new query helper:

mosql.registerQueryHelper(
  'joins'
, function(joins, values, query){
    for (var key in joins){
      // Check existence first with .has()
      mosql.queryHelpers.get( key ).fn( joins[ key ], values, query);
    }
  }
);

Uncaught TypeError when query type has no helpers

This is because in query-builder, we do a type.match expecting an empty array, but it gets null instead.

Sub-query conditional helpers

not exists, exists... http://www.postgresql.org/docs/9.2/static/functions-subquery.html

Pretty much everything that I missed.

Define where query helpers should be added

If you add a new query helper, none of the default query types have it. We need a way to add it without overriding the whole damn thing.

var mosql = require('mongo-sql');

mosql.registerQueryHelper('something', function( something, values, query ){
  return 'something ' + something;
});

mosql.queryTypes.get( 'select' ).addQueryHelper( 'something', { after: 'columns' });

Proposal for gulp/browserify build system

Using browserify will help to reduce the boilerplate on source files. Combined with gulp, both the build & testing can be automated during development.

I am planning to hack around with mongo-sql for next couple of days. A better build system will help me(hopefully others aswell) to hack faster :)

If you are interested in this, let me know. I can make a PR later today.

Insert sub query

http://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already/13342031#13342031

Add Root API docs

Add Gruntfile + ESLint

Allow expressions in Group By clause

http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-GROUPBY

Over should be associated to a column

Missing alter table action: dropColumn tests

Expose registered query types

Currently we can register new querytypes using mosql.queryTypes.add() but there is no way to get the names of all registered query types. My proposal is to add mosql.queryTypes.keys property, so as to introspect queryTypes at runtime.

Dynamic expression embedding

Any value can actually be an expression.

We should be able to do something like:

{
  type: 'select'
, table: 'weather'
, where: {
    temp_lo: {
      type: 'select'
    , table: 'weather'
    , columns: [ { type: 'max', expression: 'temp_lo' } ]
    }
  }
}

I think what this entails is checking whether the value object has a type property and send it through the query builder rather than the conditional evaluator.

Create alter table withTimezone

Create Index query type

http://www.postgresql.org/docs/9.3/static/sql-createindex.html

helper: 'order' object syntax won't guaranteed order

Properties order in objects are not guaranteed in JavaScript
We can use an Array of Arrays instead of Object.

{
  type:     'select'
, table:    'users'

// current
, order:    { id: 'desc', name: 'asc' }
, order:    ['id desc', 'name asc']
, order:    'id desc'

//change into
,order: [['id', 'desc'], ['name', 'asc']]
,order: ['id', 'desc']
//
}

Conflict not working on mosql.j0.hn

The following example currently doesn't provide the correct output for queries using conflict

{
  type: 'insert'
, table: 'users'
, values: {
    name: 'Bob'
  , email: '[email protected]'
  }
, conflict: {
    target: {
      // When the email column raises a unique exception
      column: 'email'
    }
  , action: {
      // Go ahead and update the name to the name we tried to use
      update: { name: '$excluded.name$' }
      // Optionally, supply a condition for which resolve the conflict in this way
    , where: { id: { $lt: 100 } }
    }
  }
}

Also, no error is provided when unknown fields are added to objects, I think it would be very nice if instead of just dropping the conflict part (I'm assuming an old version is deployed) it would say Error: Unknown attribute "conflict".

Support distinct on( expression )

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

array length support

First of all, thanks for an awesome lib!

One small feature request that I would love to have would be to support the $size operator.

Some examples:

Empty array

{ where: { fruits: { $size: 0 } } }

coalesce(array_length(fruits, 1), 0) = 0

Non-empty array

{ where: { fruits: { $size: { $gt: 0 } } } }

coalesce(array_length(fruits, 1), 0) > 0

Exactly 8 entries

{ where: { fruits: { $size: 8 } } }

coalesce(array_length(fruits, 1), 0) = 8

fix $custom value interpolation

Editor area in online query preview tool goes off bottom of window.

You're adding a margin-top of 40px to accomodate the menu bar, but this pushes the editor area off the bottom of the page, making it hard to use with longer queries. I can fix this for you if you'll point me to the source.

Create Table

let gb admins change payment method on orders

convenient but this happens infrequently, I'd put it low priority

Conditional Helpers: $any, $all

Conditional Helper: $regex

Parameter prefixes

Is there any way to override the parameter prefixes for values from $1, $2, etc to ? Or have toString() return the actual pg statement?

Should use parenthesis for all sub-query expressions in columns

Document string behavior in `default` helper

Add better syntax for non-parameterized values in condition builder

The current syntax is to wrap the value in dollars. I think maybe making this double dollar would be good as postgres already supports this. However, It fucks up your value. It becomes a chore to check query.joins.group.id == '$otherTable.id' rather than simply query.joins.group.id === 'otherTable.id'

I thought about making a global property that will say, hey any descendents of me do not get parameterized. That syntax would look like:

var query = {
  type: 'select'
, table: ['users', 'groups']
, where: {
    $: { 'users.id': 'groups.userId' }
  , 'groups.name': 'admin'
  }
};

While this cleans the syntax a bit, it becomes just as difficult, if not harder to reason about. You would be like, query.where['users.id'] thinking you would find it.. but you don't know what objects are going to have a $ property.

Maybe we could have a completely separate where clause:

var query = {
  type: 'select'
, table: ['users', 'groups']
  // Not parameterized
, $where: {
    'users.id': 'groups.userId'
  }
  // Parameterized
, where: {
    'groups.name': 'admin'
  }
};

Aliased Joins

Like:

select users.*, row_to_json(c) as consumer from users
inner join (
  select * from consumers
) c on users.id = c.id
where users.id = 1

Column quoting: do not attempt on strings with single quotes

Add Pretty mode

Should be able to pass in an option to make the output pretty-printed

Syntax error at or near ")" for "$in" helper when list is empty

{
  type: 'select'
, table: 'users'
, where: { id: { $in: [] } }
}

The $in condition helper will cause syntax error because select * in XX where X in () is not support in postgresql.
There were some discussions in Postgresql mailing list.
http://postgresql.nabble.com/The-empty-list-td2147623.html

We can either

1. Just generate false instead of X in () when the list is empty
2. generate = any instead of in
   (see also http://stackoverflow.com/questions/30263671/postgresql-in-vs-any)

Order Query Helper needs some touching up

Right now, it just expects the user to enter valid strings for everything, like: ['id as', 'name desc']

Fix Columns stuff

Standardize the way columns are represented. I think array will be best.

• String is just column from the default table
• Object
  • If has type field, assume sub-query
  • If not and has name field, assume column
    • alias field allowed here

Add more semantic multi-column ordering syntax

[{ first_name: 'asc' }, { last_name: 'desc' }]

create index action and create-table option

Allow defining an index when creating a table and also create index query type.

Ignore undefined values in where objects

I think it would make sense for the where helper to filter out undefined values. Currently it throws TypeError: Cannot read property '0' of undefined.

e.g.

const filters = {
  age: { $gte: 40 }
}

const query = {
  type: 'select',
  table: 'person',
  where: {
    'person.id': filters.id,
    'person.name': filters.name,
    'person.age': filters.age
  }
}

When running the query this will fail since the where object will still have the keys person.id and person.age, but the value of them is undefined. This is a bit confusing since in javascript there can be both the absence of a key, or a key with the value undefined, and both of these cases will give back the value undefined.

I would be happy to submit a PR :)

Add real client-side testing

Allow selection of only values

Make `values` helper accept a flat array of values to be parameterized, add to select

It'd b nice to do something like this:

{
  type: 'select'
, values: [ 'a', 'b', 'c' ]
}

select $1, $2, $3;