tgriesser/knex — pull request #252 by tgriesser (2014-04-22 16:57:05+00:00)
Title: Upcoming release: 0.6.0

Still need to fix up the migrations and clean up a few other things, but I wanted to create a ticket that details the new minor release so people can take a look and give any feedback, because the internal changes are pretty significant and exciting.

The library everything will still take the "Batteries Included" approach: calling `Knex.initialize(config)` will make sure everything is taken care of for you, the entire library has been refactored such that a "client" object is now the central piece of the library, with a set of constructors attached to it, independent to each client instance (i.e. `client.QueryBuilder`):

Always loaded:

- **QueryBuilder**
- **QueryCompiler**
- **Formatter**
- **Transaction**
- **Raw**

Added conditionally if you've specified a connection:
- **Runner**
- **Pool**

Added conditionally if any of the schema methods are called:

- **SchemaBuilder**
- **SchemaCompiler**
- **TableBuilder**
- **TableCompiler**
- **ColumnBuilder**
- **ColumnCompiler**

...and in SQLite3, where anything related to DDL is a complete mess:

- **SQLite3_DDL**

Added conditionally if the migration utilities are used:
- **Migrator**

It helps makes things super obvious as to where features should be added, and what the model should be when adding new clients. Also, since each client has independent constructors, it's totally fine to write plugins/modify things as needed without potentially running into issues with others' instances.

Some of the main issues that have come up deal with raw queries and the fact that you can't specify bound parameters when injecting raw statements within a knex query. The solution to this was pretty in-depth, it involved rewriting most of the structure of the entire builder, each call to a query builder pushes the arguments onto a `statements` array, rather than try to add the bindings as the methods are called.

This allows us to do things like:

```js
knex.whereIn('id', knex.raw('(select * from table where id = ?)', [binding])).orWhere('id', 22)
```

or to re-use queries by placing them in other queries (see #162 for a neat use):

```js
var q1 = knex.select('*').from('table').where('id', binding);
knex.whereIn('id', q1).orWhere('id', 22);
```

This works because everything now follows a "Builder" -> "Compiler" sequence, where the query is first built, and then "compiled", where the compiler uses its own formatter object, keeping track of the position of the bindings as the query is compiled rather than as it is built.

This new structure also make it super straightforward on how to add new features, for example facilities for view creation. In fact I've taken the liberty of putting a viewbuilder.js & viewcompiler.js in the schema directory, they're empty, anyone can feel free to take that one on :)

Also, as you may have noticed, all methods can be chained from the `knex` object, and don't need to start with `knex(tableName)` though that behavior is still supported, `knex.insert(values).into(table)` or `knex.table(name).update(values)` or `knex.select(columns).from(table)` all work as you'd expect, making query composition a nicer experience.

There are also major number of unit tests added, adapted from laravel's suite :)

The new structure should also make it really obvious how to add additional adapters, @tybenz if you want to take a look for mssql, that'd be great. There's also a number of unit tests to start you off that are commented out from laravel's suite.

##### Other features:
- an explicit connection can be passed for any query #56
- drop column / alter column for sqlite3 (quite a pain)
- all schema actions are run sequentially on the same connection if chained, e.g.:

```js
knex.schema.createTable(args)
.createTable(args)
.dropTable(table)
```
- schema actions can now be wrapped in a transaction
- `.references(tableName.columnName)` as shorthand for `.references(columnName).inTable(tableName)`
- `.join('table.column', 'otherTable.column')` as shorthand for `.join('table.column', '=', 'otherTable.column')`
- Streams should also be supported for selects, passing through to the streaming capabilities of node-mysql and node-postgres... haven't tried them yet though

##### Deprecated:
- `join`'s with the trailing argument, e.g. `join('table.id', '=', 'otherTable.table_id', 'left outer')` are now `.leftOuterJoin('table.id', '=', 'otherTable.table_id')` or more succinctly `.leftOuterJoin('table.id', 'otherTable.table_id')`
- `whereRaw`, `orWhereRaw`, `havingRaw`, `orHavingRaw` are now just `.where(knex.raw(rawQuery, bindings))`, etc.

##### Still left to-to:
- Tests for stream
- Fix the migration utilities, figure out a better api / best practice of doing those
- Standardize debugging things with the new runner #167, errors, how to show all queries executed from a single action
- Ensuring all of the recent bugfixes surrounding operators are preserved, a few more tests/tickets to add things for
- Check that websql works
- Get a browser build together; one for websql, one with everything for demo'ing query building in the browser... which will be a cool feature for the docs.

A [simple bench](https://github.com/tgriesser/knex/blob/e74f43cfe57ab27b02250948f8706d16c5d821b8/test/bench/index.js#L27) against building a simple query with a few where's showed about a 4x improvement from 0.5

Interested to hear any questions/thoughts people have on this, feel free to take a look through and let me know what you think. Hoping to get this out by the end of the month.

/cc @tkellen and @neilk