sql

node-sql

sql string builder for node - supports PostgreSQL, mysql, Microsoft SQL Server, Oracle and sqlite dialects.

Building SQL statements by hand is no fun, especially in a language which has clumsy support for multi-line strings.

So let's build it with JavaScript.

Maybe it's still not fun, but at least it's less not fun.

install

$ npm install sql

use

var sql = require('sql'); sql.setDialect('postgres'); var user = sql.define({  name: 'user',  columns: ['id', 'name', 'email', 'lastLogin']}); var post = sql.define({  name: 'post',  columns: ['id', 'userId', 'date', 'title', 'body']}); var query = user.select(user.star()).from(user).toQuery();console.log(query.text);  var query = user    .select(user.id)    .from(user)    .where(      user.name.equals('boom').and(user.id.equals(1))    ).or(      user.name.equals('bang').and(user.id.equals(2))    ).toQuery(); console.log(query.text);  console.log(query.values);  var query = user.select(user.star()).from(user).toNamedQuery('user.all');console.log(query.name);  var query = user.select(user.name, post.body)  .from(user.join(post).on(user.id.equals(post.userId))).toQuery(); console.log(query.text);   var friendship = sql.define({  name: 'friendship',  columns: ['userId', 'friendId']}); var friends = user.as('friends');var userToFriends = user  .leftJoin(friendship).on(user.id.equals(friendship.userId))  .leftJoin(friends).on(friendship.friendId.equals(friends.id)); var friendsWhoHaveLoggedInQuery = user.from(userToFriends).where(friends.lastLogin.isNotNull()); var friendsWhoUseGmailQuery = user.from(userToFriends).where(friends.email.like('%@gmail.com')); var user = sql.define({  name: 'user',  columns: [{      name: 'id'    }, {      name: 'state_or_province',      property: 'state'    }  ]}); console.log(user.select().where(user.state.equals('WA')).toQuery().text);

There are a lot more examples included in the test/dialects folder. We encourage you to read through them if you have any questions on usage!

from the command line

You can use the sql-generate module to automatically generate definition files from a database instance. For example, running node-sql-generate --dsn "mysql://user:password@host/database" will generate something similar to:

var sql = require('sql'); exports.bar = sql.define({    name: 'bar',    columns: [        'id',        'foo_id'    ]}); exports.foo = sql.define({    name: 'foo',    columns: [        'id',        'field_1',        'foo_bar_baz'    ]}); var model = sql.define({ name: 'foo', columns: [] });model.addColumn('id'); model.addColumn('id', { noisy: false });

Read the module's documentation for more details.

contributing

We love contributions.

node-sql wouldn't be anything without all the contributors and collaborators who've worked on it. If you'd like to become a collaborator here's how it's done:

1. fork the repo
2. git pull https://github.com/(your_username)/node-sql
3. cd node-sql
4. npm install
5. npm test

At this point the tests should pass for you. If they don't pass please open an issue with the output or you can even send me an email directly. My email address is on my github profile and also on every commit I contributed in the repo.

Once the tests are passing, modify as you see fit. Please make sure you write tests to cover your modifications. Once you're ready, commit your changes and submit a pull request.

As long as your pull request doesn't have completely off-the-wall changes and it does have tests we will almost always merge it and push it to npm

If you think your changes are too off-the-wall, open an issue or a pull-request without code so we can discuss them before you begin.

Usually after a few high-quality pull requests and friendly interactions we will gladly share collaboration rights with you.

After all, open source belongs to everyone.

license

MIT

‏Source www.npmjs.com