knex / knex

A query builder for PostgreSQL, MySQL, CockroachDB, SQL Server, SQLite3 and Oracle, designed to be flexible, portable, and fun to use.

knex.js

A SQL query builder that is flexible, portable, and fun to use!

A batteries-included, multi-dialect (PostgreSQL, MySQL, CockroachDB, MSSQL, SQLite3, Oracle (including Oracle Wallet Authentication)) query builder for Node.js, featuring:

• transactions
• connection pooling
• streaming queries
• both a promise and callback API
• a thorough test suite

Node.js versions 12+ are supported.

• Take a look at the full documentation to get started!
• Browse the list of plugins and tools built for knex
• Check out our recipes wiki to search for solutions to some specific problems
• In case of upgrading from an older version, see migration guide

You can report bugs and discuss features on the GitHub issues page or send tweets to @kibertoad.

For support and questions, join our Gitter channel.

For knex-based Object Relational Mapper, see:

• https://github.com/Vincit/objection.js
• https://github.com/mikro-orm/mikro-orm
• https://bookshelfjs.org

To see the SQL that Knex will generate for a given query, you can use Knex Query Lab

Examples

We have several examples on the website. Here is the first one to get you started:

const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: './data.db',
  },
});

try {
  // Create a table
  await knex.schema
    .createTable('users', (table) => {
      table.increments('id');
      table.string('user_name');
    })
    // ...and another
    .createTable('accounts', (table) => {
      table.increments('id');
      table.string('account_name');
      table.integer('user_id').unsigned().references('users.id');
    });

  // Then query the table...
  const insertedRows = await knex('users').insert({ user_name: 'Tim' });

  // ...and using the insert id, insert into the other table.
  await knex('accounts').insert({
    account_name: 'knex',
    user_id: insertedRows[0],
  });

  // Query both of the rows.
  const selectedRows = await knex('users')
    .join('accounts', 'users.id', 'accounts.user_id')
    .select('users.user_name as user', 'accounts.account_name as account');

  // map over the results
  const enrichedRows = selectedRows.map((row) => ({ ...row, active: true }));

  // Finally, add a catch statement
} catch (e) {
  console.error(e);
}

TypeScript example

import { Knex, knex } from 'knex';

interface User {
  id: number;
  age: number;
  name: string;
  active: boolean;
  departmentId: number;
}

const config: Knex.Config = {
  client: 'sqlite3',
  connection: {
    filename: './data.db',
  },
};

const knexInstance = knex(config);

try {
  const users = await knex<User>('users').select('id', 'age');
} catch (err) {
  // error handling
}

Usage as ESM module

If you are launching your Node application with --experimental-modules, knex.mjs should be picked up automatically and named ESM import should work out-of-the-box. Otherwise, if you want to use named imports, you'll have to import knex like this:

import { knex } from 'knex/knex.mjs';

You can also just do the default import:

import knex from 'knex';

If you are not using TypeScript and would like the IntelliSense of your IDE to work correctly, it is recommended to set the type explicitly:

/**
 * @type {Knex}
 */
const database = knex({
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    user: 'your_database_user',
    password: 'your_database_password',
    database: 'myapp_test',
  },
});
database.migrate.latest();

knex's Issues

Custom Errors

Implement custom Error that can be used for easier debugging, maintaining the query, bindings, and other information.

Insert causes error when no `id` field defined.

{ sql: 'insert into "users" ("first_name") values ($1) returning "id"',
  bindings: [ 'xxx' ],
  __cid: '__cid2' }

timers.js:103
            if (!process.listeners('uncaughtException').length) throw e;
                                                                      ^
error: column "id" does not exist

timestamp creates wrong default value

Knex.Schema.createTable('users', function(table){
  table.integer('id', 20).primary();
  table.string('name', 500);
  table.text('email', 200).unique();
  table.string('loginId', 200).unique();
  table.timestamp('createdAt');
  table.timestamp('updatedAt');
  // table.timestamps();
}).then(function(){
  console.log('Users Table is Created!');
})

This creates table fine but createdAt default value set to CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP and updatedAt to 0000-00-00 00:00:00

If I define one timestamp column then it sets default value wrong, if multiple then first column has wrong default value.

However, if I use table.timestamps() then everything looks fine. I just wanted to have camelCase column names..

Cannot set not null in SQLite

Using knex 0.2.5:
schema:

knex.Schema.createTable('posts', function (t) {
            t.increments().primary();
            t.string('title');                         
            t.text('content', 'medium').nullable(); 
            ... 

output:

create table "posts" ("id" integer null primary key autoincrement, "title" varchar(255) null, "content" text null,...

Expectation: id and title should be not null, content should be null
Observation: all fields are null

Using knex 0.2.6 (which includes the change over to not null by default see #66)
schema:

knex.Schema.createTable('posts', function (t) {
            t.increments().primary();
            t.string('title').notNull();                         
            t.text('content', 'medium').nullable(); 

output:

create table "posts" ("id" integer primary key autoincrement not null, "title" varchar(255), "content" text,

Expectation: id and title should be not null, content should be null
Observation: all fields are null

As of 0c6dedc the explicit null has disappeared from the output, but specifying notNull() in the schema does not add not null.

Allow bindings for Knex.Raw

I see this was removed in 0.1.5.

Just curious as to why and if there is an alternative.

Knex.Builder.prototype.where should take Knex.Raw

I was surprised to find out that instead of calling .where and passing it a Knex.Raw object (like the example does with Builder.prototype.select) one would have to call whereRaw. I'd strongly suggest having where take a Raw object directly to be consistent.

Currently it results in a very weird query if you do so:

"value" = $2 and "client" = $3 and "_debug" = $4 and "_promise" = $5 and "debug" = (select *) and "exec" = (select *) and "then" = (select *) and "toString" = (select *) and "connection" = (select *) and "_connection" = $6 and "_setType" = (select *) and "_cleanBindings" = (select *) and "runQuery" = (select *) and "_source" = $7 and "toSql" = (select *)'

exec() with no callback throws TypeError: undefined is not a function

Consider the following simple queries:

Knex('table').where('id', 1).update({ value: "hello" }).exec();
Knex('table').where('id', 1).update({ value: "hello" }).then();

Query 1 succeeds, but then throws:

TypeError: undefined is not a function

Query 2 succeeds with no error.

Documentation: wrong mention of Knex.createTable

There's a reference to Knex.createTable(...) that is probably supposed to be Knex.Schema.createTable.

aftercolumn.after(field) 
Sets the column to be inserted after another, only used in MySql alter tables.

Knex.createTable('accounts', function() {
  t.increments().primary();
  t.string('email').unique();
});

Where chains - what mi doing wrong.

getEntryQuery()
    .where('entry_entry.id', entryId)
    .where('entry_entry.status', '<', '3')
    .whereRaw('( "debtor"."id" = '+userId+' or "lender"."id" = '+userId+')')

getEntryQuery()
    .where('entry_entry.id', entryId)
    .where('entry_entry.status', '<', '3')
    .where('debtor.id', userId)
    .orWhere('lender.id', userId)

The first example works fine, but the second fails. I can achieve the effect of the first example without method "whereRaw"?

Sqlite delete does not report the number affected rows

Sqlite delete does not report the number affected rows. It looks like sqlite3.js is using the all() method instead of run() when executing delete queries. Updating line 26 to the following fixes the problem:

var method = (builder.type === 'insert' || builder.type === 'update' || builder.type == 'delete') ? 'run' : 'all';

(I know I should probably learn to do a pull request but I've not used git before and don't feel like spending the time for a one line edit.)

Table name case is not honoured

camelCase is being converted to lowercase. I turned on debug and checked query generated has correct case. Column names are fine as per definition. Is there any configuration I'm missing?

Modifying columns

I think it is a reasonable expectation that it would be possible to:

• rename a column
• change the data type
• add or drop the null constraint
• change the default

And probably other things that I have not thought of.

I would imagine the syntax for doing this would be something like

 knex.Schema.table('table_name', function (t) {
        t.string('my_column').renameTo('something_else');
        t.string('my_column').changeTo('text');
        t.string('my_column').nullable() < adds nullable if not already present
        t.string('my_column').notNullable() < removes nullable if present
        t.string('my_column').defaultTo('whatever') < adds or updates the default
  });

Maybe there needs to be something extra in the chain, possibly at the knex.Schema.table level to indicate that this is a modify statement not an add.

I realise that this is tricky to implement across various databases, especially in SQLite where you have to create a whole new table, but in a system which requires migrations, without these tools it is going to be necessary to use knex.raw and write all the migrations for each DB supported, which sort of defeats the point of having a nice ORM especially one which is about to support migrations.

Strange Behaviuor when doing an insert

I've got a problem when I do an insert, but I do the same think in an update and it works perfectly.

I will try to explain: I'm using, as you suggested me, the Knex.Raw() to run Postgre functions for the updates and the inserts but I have problems with the insert, but not with the update:
if i try to add the following js I get an error:

brigadista.nombre = "DarthVader";
 brigadista.posicion = 'ST_Transform(ST_GeometryFromText(\'POINT(-0.475519999981581 38.69480999998364 600)\',4326),32630)'
 brigadista.alturaabsoluta = false;

I get the error:

{ nombre: 'DarthVader',
  posicion: 
   { value: 'ST_Transform(ST_GeometryFromText(\'POINT(-0.475519999981581 38.69480999998364 600)\',4326),32630)',
     client: 
      { name: 'main',
        debug: true,
        connectionSettings: [Object],
        grammar: [Object],
        schemaGrammar: [Object],
        pool: [Object] } },
  alturaabsoluta: false }
{ sql: 'insert into "brigadista" ("alturaabsoluta", "nombre", "posicion") values ($1, ST_Transform(ST_GeometryFromText(\'POINT(-0.475519999981581 38.69480999998364 600)\',4326),32630), $2) returning "id"',
  bindings: [ false, 'DarthVader' ],
  __cid: '__cid2' }

But if I send:

brigadista.alturaabsoluta = false;
brigadista.nombre = "DarthVader";
brigadista.posicion = 'ST_Transform(ST_GeometryFromText('POINT(-0.475519999981581 38.69480999998364 600)',4326),32630)'

{ alturaabsoluta: false,
  nombre: 'DarthVader',
  posicion: 
   { value: 'ST_Transform(ST_GeometryFromText(\'POINT(-0.475519999981581 38.69480999998364 600)\',4326),32630)',
     client: 
      { name: 'main',
        debug: true,
        connectionSettings: [Object],
        grammar: [Object],
        schemaGrammar: [Object],
        pool: [Object] } } }
{ sql: 'insert into "brigadista" ("alturaabsoluta", "nombre", "posicion") values ($1, $2, ST_Transform(ST_GeometryFromText(\'POINT(-0.475519999981581 38.69480999998364 600)\',4326),32630)) returning "id"',
  bindings: [ false, 'DarthVader' ],
  __cid: '__cid2' }

I think the error is because the properties are sorted but not the values. I'm sorry but I don't have enough time to test this week if the error occurs without Knex.Raw data.

enum datatype broken for postgres

Postgresql enum works slightly differently than mysql.

http://www.postgresql.org/docs/9.3/static/datatype-enum.html

Please make increments chain-able or accept length param

Currently it is hard coded to 11 and it is not chain-able so I can't do table.integer('id', 20).primary().increments().unique();

Thanks!

insert then delete doesn't work, delete then insert does work

Sorry, I wasn't sure if I should post this as an issue, or if I am just confused about something.

I want to delete a certain item in a database and then add another one. Deleting the item then adding it works, but not the reverse.

The following succeeds - the delete occurs, then the insert.

'use strict';

var db = require('./lib/db');

function createTile() {
  return db('tilestofetch')
    .idAttribute(null)
    .insert({tx: 100, ty: 101, zoom: 102});
}

function deleteTile(tile) {
  return db('tilestofetch').where(tile).delete();
}

var tile = {'tx': 10356, 'ty': 43106, 'zoom': 16 };

deleteTile(tile)
  .then(createTile)
  .then(console.log)
  .then(process.exit);

The following, which is exactly the same except the delete and create are reversed, does not work. Only the insert() succeeds.

'use strict';

var db = require('./lib/db');

function createTile() {
  var counter = parseInt(Date.now() / 1000, 10) % 10000;
  return db('tilestofetch')
    .idAttribute(null)
    .insert({tx: counter, ty: 101, zoom: 102});
}

function deleteTile(tile) {
  return db('tilestofetch').where(tile).delete();
}

var tile = {'tx': 10356, 'ty': 43106, 'zoom': 16 };

createTile()
  .then(deleteTile(tile))
  .then(console.log)
  .then(process.exit);

Transactions and Knex.Schema

How do you wrap a Knex.Schema.createTable or deleteTable in a transaction?

Architecting Knex to not store connection state itself

Hey,

I see you've architected Knex to use global data — that is store connections and instances and other data inside Knex.Instances. Why did you do it this way?

Why not have an initialize function (or something equivalent) that would return an instance of Knex that is self contained, thereby removing all persistence from the library with the whole named instance global data thing and hand it off to client code?

Postgres Incompatibility

We're using knex for our queries and are in the process of moving from mysql to postgres. Great library!

We ran into a compatibility problem when creating an empty record. In knex.js's runQuery method, this.toSql() generates the following for creating a new empty record:

insert into "owners" () values () returning "id"

but based on a quick (Google search)[http://www.postgresql.org/message-id/[email protected]], it should be:

insert into "owners" default values returning "id"

BTW, mysql generates acceptable code:

insert into `owners` () values ()

and I haven't tested sqlite3.

Is this something you could create a patch for? (I'm in crunch period and unfortunately do not have the time today to write a test, test on all sql variants, etc). If not, I can try to find time over the weekend.

Cheers!

Knex.Schema: Additional data types

Schema builder has commonly used data types but not all. Is there a way to create column with other data types that is not provided with knex (for example, LONGTEXT, NVARCHAR, BIGINT)?

Also, I have not found any document on adding foreign key. I checked the tests and found:

.references('id')
.inTable('test_table_two');

Can you please explain how this works. Does order of createTable definitions matter or Knex handles it internally while creating tables (much like Sequelize)?

Thanks!

Disconnecting and shutting down database connection

Hey,

I'm writing a cluster manager and zero downtime restart library and had a need to shut down the database connection pool that Knex set up. How would I do that?

I do see poolInstance.drain and destroyAllNow used in the base.js file. Are those meant to form the public interface for shutting down Knex?

Todo: More consistent use of promises in transactions, client libs.

Skipping connection pools or sharing them between multiple Knex instances

Hey,

I'm writing a library that would like to use Knex for its query building, but don't want to advertise that fact outside the library. I also don't want to everyone to run more than one connection pool.

How would you envision sharing connection pools between Knex and the outside world? Perhaps settle on some standard type of a pool or just a couple of functions with an agreed upon signature for getting a connection and releasing it?

Or perhaps ignore Knex's pooling at all and try to have it run the query directly on a Pg.Client instance given to it? Currently unfortunately they seem so coupled... Knex passing SQL arrays to multi query functions passing builders with SQL to client wrappers to... somethings.

Could you perhaps give an answer off the top of your head for now how to take a Knex built query and run it on the Pg.Client instance given to it, skipping all of the pooling and mooling? Thanks!

How to add a complex join (other than INNER)?

e.g.

a LEFT JOIN b ON b.a_id = a.id AND a.id = 17

using:

.join('publisher', function() {
  this
    .on('publisher.id', '=', 'school_publisher.publisher_id')
    .on('publisher.slug', '=', 'some-publisher-slug')
  ;
})

...always yields a default "INNER JOIN" in the query, and injecting "left" in all the usual spots accomplishes no change. The need for an outer/left join prevents the using of a where clause on this table, so a complex join is necessary.

`.insert()` modifies arrays that are passed in

If I do an .insert() and pass in an array of objects with the properties I want to insert, these objects get converted in place in the enclosing array, into arrays of name/value pairs that are used internally.

toString should quote and possibly escape

Hey,

I was writing a few tests and initially freaked out because the output of toString has no quotes around user supplied data. I realize Knex uses the database's own query parameters, but perhaps making toString match reality would be reasonable and helpful.

Error messages suppressed after Knex is accessed

After I access Knex, error messages are suppressed, making development pretty difficult. In this code the message "Polo" never appears, and more importantly there is no error message for fakeVar not existing
using node v0.10.8

var Knex = require('knex');

Knex.Initialize({
  client: 'mysql',
  connection: {
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'mydatabase',
    timezone: '-0700'
  }
});

Knex('mytable').select().exec(function(err,models) {
  console.log("Marco...");
  console.log(fakeVar);  // no error here, code does not exit
  console.log("Polo");  // never logs
});

Should knex intercept all exceptions when loading a client?

Earlier I had an sqlite problem which was masked by knex.

sqlite error:

{ [Error: Cannot find module './binding/Release/node-v11-darwin-x64/node_sqlite3.node'] code: 'MODULE_NOT_FOUND' }

knex error:

Error: sqlite3 is not a valid Knex client, did you misspell it?

Obviously this is a different error. I don't know how much of an edge case this is, though. Relevant code: https://github.com/tgriesser/knex/blob/master/knex.js#L129

The sort of error that code's looking for is:

{ [Error: Cannot find module './clients/missingclient.js'] code: 'MODULE_NOT_FOUND' }

I tried matching on the error code, but in this instance the error I got had the same error code anyway! If this is something you want fixed, I can do a PR that swaps the try/catch with fs.exists?

Not sure if it's worth it, but thought I'd give a heads up

-a

Test output organization makes it difficult to insert tests

Since the test output lookup uses:
out[type][label] || (out[type][label] = {});
where label is
var label = section + '.' + item;

It's hard to insert new tests in the middle (for example, to add another query in a related test group, because it requires renumber all subsequent output labels.

How about re-organization the output so each test sub-group is an array. i.e., instead of

{ "select.1": [res1...], "select.2": [res2...] }
do
{ "select": [ [res1...], [res2...] ] }

Then look up the output using:
((out[type] || 0 )[section] || 0 )[item]

This will make it easier to keep tests organized moving forward.

Sorry if I've missed a complication that does indeed make it necessary to use this naming structure.

Knex.Schema: How to create composite unique key?

For example,

ALTER TABLE users ADD UNIQUE KEY(LoginID, Email);
ALTER TABLE users ADD UNIQUE KEY(Email);
ALTER TABLE users ADD UNIQUE KEY(LoginID);

How do I do it with Knex.Schema.createTable

transactions broken with postgres

Hi I think there is something wrong with transactions at the moment. In knex.js method is called initTransaction, but in base.js in client folder it is startTransaction. I have tried to rename the method name, but then my postgres client is called with different scope -> this points to node.js top level object

Paging in different databases like MS SQL Server or Oracle

Hi,

Are your database adapters flexible enough so that you can generate sql select statements that have a complete different sql for paging like MS SQL Server and Oracle? I see your current adapters are only for very similar databases that support the LIMIT statement.

http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005
http://weblogs.asp.net/fbouma/archive/2007/05/21/api-s-and-production-code-shouldn-t-be-designed-by-scientists.aspx

Maybe you can post an MS SQL Server 2005+ adapter as well?

That will be awesome!

Constraint violations during commit are lost

I'm using postgres, and I've got all my foreign key constraints set up as DEFERRABLE INITIALLY DEFERRED. This means I don't need to ensure each query in the transaction is executed in the right order to satisfy foreign key constraints, so long as the transaction overall is valid. This means that the constraint violation isn't triggered on the original query, but rather on commit.

I'm finding that commits are failing but in my app (using Bookshelf), the transaction gets resolved and not rejected.

I can't see anyway of finding out whether the commit was successful or not... should commit return a promise?

Joined tables overwrite columns with the same name

Came upon this today and it's causing me quite a headache, I haven't found a good way of resolving this.

Okay, so take for instance that we create a simple OneToMany join:

  var posts = Knex('posts')
      .join('comments', 'comments.parent_id', '=', 'posts.id')
      .where({ 'posts.id': 1}).select()
      .then(function(result){
          res.send(result);
      });

The results come back as a flat tree. That makes sense, just like a regular SQL return (meaning that if you have 10 comments, you'll have 10 arrays with data from each comment + a copy of post data); however, if the posts table contains the column "name" and the comments table contains the column "name", the joined table (comments in this case) will overwrite the parent table (posts). Same goes for dates and a bunch of others.

For instance, if you just query for the post, you may get:

[ {
    "id": 1,
    "name": "My first post",
    "content": "Little johnny went to the woods...",
    "date": "1/29/2013",
    "category_id": 1
}]

When querying a single comment, you may get the following:

[{
    "id": 3
    "name": "AntJanus"
    "content": "Hi, this is my first comment",
    "date": "1/31/2013",
    "email": "[email protected]"
    "parent_id": "1"
}]

The resulting join will look like so:

[
    {
        "id": 3,
        "name": "AntJanus",
        "content": "Hi, this is my first comment",
        "date": "1/31/2013",
        "email": "[email protected]",
        "category_id": 1,
        "parent_id": 1
    },
    {
        "id": 4,
        "name": "Some Other",
        "content": "Hi, this is my second comment",
        "date": "2/1/2013",
        "email": "[email protected]",
        "category_id": 1,
        "parent_id": 1
    }
]

And it will completely overwrite original name and content of the post.

I haven't found a way around it with SQL other than using a select statement that aliases each individual column (there's no way to mass alias in SQL). However, on large and variable tables, this is not really an option.

sqlite3.js getCommandsByName

I was toying around with creating foreign keys and ran into the following...

In sqlite3.js, the getCommandsByName function is using _.where with a function iterator.

  // Get all of the commands with a given name.
  getCommandsByName: function(blueprint, name) {
    return _.where(blueprint.commands, function(value) { return value.name == name; });
  },

Shouldn't that be using either _.find or switch the iterator to a properties hash?

How to handle server disconnect/reconnect ?

Hello, after reading the doc and perform some search here I cannot find how to handle server disconnect and reconnect.

With raw MySQL lib it can be done with events listeners: on('error'), on('end') ...

The only way I know to get the connection is : Knex.client.getConnection().ten();

Any clue ?

Data type limitations and best practices documentation

I can start adding some documentation myself, but wanted to raise this as something to think about.

The joy of knex is abstracting away the pain of trying to support multiple databases, but I'm finding there isn't enough documentation around the datatypes to know what I can and cannot store in each one. I'm doing quite a bit of research to figure it out, and I'm thinking I won't be the only person who needs this information - so the basic limitations/expectations should probably be in the knex documentation perhaps with extended info on a wiki page detailing how different dbs will behave with different types.

Specifically, if I store a string, how long can it be? MySQL < 5.0.3 only allows 255 chars. MySQL >= 5.0.3 allows 65,535. SQLite is practically unlimited, PostGres is 1Gb.

Looking at the code I see:

string: function(column, length) {
      return this._addColumn('string', column, {length: (length || 255)});
},

So by default knex will limit my string to 255, which makes sense - it's the maximum safe value across the 3 DBs, but it doesn't constrain the length, so if I were to specify a string column with length 300, knex would attempt to create that as far as I can tell.

Equally if I'm working in SQLite, the length will be ignored, and I'll be able to save any length string. Knex doesn't yet do any sort of parsing / validation of data (I know it's in the pipeline, esp around dates) so even though it looks like I've declared a limit, my application actually has to manage this itself.

My main point is that it is not clear what the limitations, restrictions or behavioural differences of the different datatypes are across the DBs and that this would be useful documentation.

Also, as a future improvement it would be good to be able to rely upon knex to ensure that these datatypes behave reliably across different databases.

Adding a distinctOn method for cross database distinct values

PostgreSQL - DISTINCT ON (column)

MySQL / SQLite3 - GROUP BY column

MySQL test success depends on using InnoDB table type

In MySQL, InnoDB tables will increment the 'id' counter when inserts fail, but MyISAM tables do not. So, if the default settings for MySQL are to use MyISAM, a number of the select tests will fail, since they compare the ID.

I'm not sure what the best way to approach this would be... I only thought of the following three, but not sure which would be preferred:

1. Adding documentation in the tests directory mentioning the InnoDB requirement
2. Removing ID checks in tests
3. Adding a command specifying engine when creating tables in MySQL

Schema builder tests are an invalid example of API usage

I've been debugging an issue today, and stumbled across a quirk/feature of the when library which was causing the issue. The issue is also present in https://github.com/tgriesser/knex/blob/master/test/lib/schema.js

The problem is passing createTable (or any of the schema methods) to when.all/when.join is not a valid use of these when methods as they require promises not deferreds (from here). As such, even if createTable fails, when.all will not reject, it just times out eventually.

This makes it quite tricky to setup an array of createTable etc functions... I ended up proxying createTable with this:

function createTable(table, callback) {
    var deferred = when.defer();

    knex.Schema.createTable(table, callback)
        .then(deferred.resolve, deferred.reject);

    return deferred.promise;
}

I'm not familiar enough with when and knex to know if there is a neater way to do this, but as it's used in the knex tests I am assuming that other people will have made this mistake too. Perhaps the schema builder functions ought to return a promise?

How do write a subquery?

Hi. @tgriesser .
first of all congratulations on the superb job you all have been doing creating this tool. It looks amazing and it's working quite well.

for example (for postgres)

SELECT
   *
 FROM
   tbl_a LEFT join (
      SELECT 
          id, name 
      FROM 
           tbl_b
      GROUP BY 
           id, name
   )  AS tbl_b on tbl_a.id = tbl_b.id

now

Knex('tbl_a').select('*').join(
                Knex.Raw('(SELECT id, name FROM tbl_b GROUP BY id, name) AS tbl_b')
                ,'tbl_a.id', '=', 'tbl_b.id', 'LEFT');

I want to create without the Raw method.

Is it possible to other, using the USING?

thank you.

Error: getaddrinfo ENOTFOUND

I get this error when trying to do anything:

Error: getaddrinfo ENOTFOUND
    at errnoException (dns.js:37:11)
    at Object.onanswer [as oncomplete] (dns.js:124:16)

I have confirmed that I am passing the correct connection values, and I am not behind a proxy. OSX 10.8, using "pg"

Better handling of disconnects

Need to hook this up for each of the database drivers, having a good way to reject the promises when this happens, and smartly reconnect (refreshing the connection pools).

Primary Table can't be Subquery

Knex does not currently allow the primary table in a select query to be a query expression, although I believe it allows it for joined tables

Foreign Key

Thanks for a really nicely implemented project. Annotated source code is always a deity-send. I have a few questions:

Is there a way to create foreign keys to other tables? The source code has a function foreign with the comment "Specify a foreign key for the table." within the SchemaBuilder, but there doesn't seem to be a way to specify a table and column for the foreign key, and the documentation doesn't mention anything.

Is there a way to specify that two or more columns must be unique together?

Is there a way to define indexes on non-integer columns?

Thanks!

Adding upsert type capability

Brought up by @adamscybot in bookshelf/bookshelf#55 - this could be a nice feature to add.

0.2.6 = master?

If I do npm install knex I do not the version tagged at 0.2.6, I get a version which includes all of Sep 04's commits changing the api etc.

Enhancement Request: Schema.hasTable shouldn't fail on non-existence

For Schema.hasTable:

In order to tell the difference between a connection failure and an actual missing table, I have to either send a do nothing query first (select 'blah' as blah) or parse the failure for "does not exist".

What is your thought on changing Schema.hasTable to return a boolean and only fail if there's a problem running the query?

Run database functions from knex

Hi since yesterday :) I'm working with knex and I'm wondered, but... I have to run dbcomands when I insert, select or update the data.

I'm using Postgis the Geo extension for Postgre and I need to do the following for a field:

ST_Transform(ST_GeometryFromText("POINT(-0.475519999981581 38.69480999998364 600)",4326),32630)

but, when I use the knex platform and put the json inside the update the debug is:

{ nombre: 'arturo',
posicion: 'ST_Transform(ST_GeometryFromText("POINT(-0.475519999981581 38.69480999998364 600)",4326),32630)',
alturaabsoluta: true }
{ sql: 'update "brigadista" set "alturaabsoluta" = $1, "nombre" = $2, "posicion" = $3 where "nombre" = $4',
bindings:
[ true,
'arturo',
'ST_Transform(ST_GeometryFromText("POINT(-0.475519999981581 38.69480999998364 600)",4326),32630)',
'arturo' ],
__cid: '__cid2' }

And the debug is correc, because IS Text, but when it runs the query PostGIS doesen't understand that it's a function.

Well, the other option is to use the Raw in Knex, but maybe..

Thanks for all

Setting connection runtime options

Hey,

I need to SET a few PostgreSQL options right after connecting. Where have you envisioned such settings to take place?

I see getRawConnection in postgres.js is reponsible for creating a new connection — that could be a place for a temporary injection, but what are your thoughts for long term?