• Stars
    star
    1,048
  • Rank 43,968 (Top 0.9 %)
  • Language
    JavaScript
  • License
    MIT License
  • Created over 13 years ago
  • Updated 3 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

SQL generation for node.js

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.

Build Status

install

$ npm install sql

use

//require the module
var sql = require('sql');

//(optionally) set the SQL dialect
sql.setDialect('postgres');
//possible dialects: mssql, mysql, postgres (default), sqlite

//first we define our tables
var user = sql.define({
  name: 'user',
  columns: ['id', 'name', 'email', 'lastLogin']
});

var post = sql.define({
  name: 'post',
  columns: ['id', 'userId', 'date', 'title', 'body']
});

//now let's make a simple query
var query = user.select(user.star()).from(user).toQuery();
console.log(query.text); //SELECT "user".* FROM "user"

//something more interesting
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();

//query is parameterized by default
console.log(query.text); //SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) AND ("user"."id" = $2)) OR (("user"."name" = $3) AND ("user"."id" = $4)))

console.log(query.values); //['boom', 1, 'bang', 2]

//queries can be named
var query = user.select(user.star()).from(user).toNamedQuery('user.all');
console.log(query.name); //'user.all'

//how about a join?
var query = user.select(user.name, post.body)
  .from(user.join(post).on(user.id.equals(post.userId))).toQuery();

console.log(query.text); //'SELECT "user"."name", "post"."body" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'

//this also makes parts of your queries composable, which is handy

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));

//and now...compose...
var friendsWhoHaveLoggedInQuery = user.from(userToFriends).where(friends.lastLogin.isNotNull());
//SELECT * FROM "user"
//LEFT JOIN "friendship" ON ("user"."id" = "friendship"."userId")
//LEFT JOIN "user" AS "friends" ON ("friendship"."friendId" = "friends"."id")
//WHERE "friends"."lastLogin" IS NOT NULL

var friendsWhoUseGmailQuery = user.from(userToFriends).where(friends.email.like('%@gmail.com'));
//SELECT * FROM "user"
//LEFT JOIN "friendship" ON ("user"."id" = "friendship"."userId")
//LEFT JOIN "user" AS "friends" ON ("friendship"."friendId" = "friends"."id")
//WHERE "friends"."email" LIKE %1

//Using different property names for columns
//helpful if your column name is long or not camelCase
var user = sql.define({
  name: 'user',
  columns: [{
      name: 'id'
    }, {
      name: 'state_or_province',
      property: 'state'
    }
  ]
});

//now, instead of user.state_or_province, you can just use user.state
console.log(user.select().where(user.state.equals('WA')).toQuery().text);
// "SELECT "user".* FROM "user" WHERE ("user"."state_or_province" = $1)"

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:

// autogenerated by node-sql-generate v0.0.1 on Tue May 21 2013 01:04:12 GMT-0700 (PDT)
var sql = require('sql');

/**
 * SQL definition for database.bar
 */
exports.bar = sql.define({
    name: 'bar',
    columns: [
        'id',
        'foo_id'
    ]
});

/**
 * SQL definition for database.foo
 */
exports.foo = sql.define({
    name: 'foo',
    columns: [
        'id',
        'field_1',
        'foo_bar_baz'
    ]
});

/**
 * Adding a column to an existing table:
 */
var model = sql.define({ name: 'foo', columns: [] });
model.addColumn('id');

// If you try to add another column "id", node-sql will throw an error.
// You can suppress that error via:
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

More Repositories

1

node-postgres

PostgreSQL client for node.js.
JavaScript
12,166
star
2

node-pg-copy-streams

COPY FROM / COPY TO for node-postgres. Stream from one database to another, and stuff.
JavaScript
331
star
3

node-pg-query-stream

Query results from node-postgres as a readable (object) stream
JavaScript
311
star
4

node-pg-types

Type parsing for node-postgres
JavaScript
270
star
5

node-forky

Forky manages your cluster module stuff for you so you don't have to pull your hair out.
JavaScript
254
star
6

node-pg-native

Native (C/C++) bindings to PostgreSQL with sync and async options.
JavaScript
247
star
7

node-pg-pool

A connection pool for node-postgres
JavaScript
180
star
8

jade-mode

Emacs major mode for jade template highlighting
Emacs Lisp
156
star
9

node-domain-middleware

Middleware to attach your express app's request & response to a domain. No more process.uncaughtException nonsense.
JavaScript
120
star
10

node-libpq

Simple, low level native bindings to PostgreSQL's libpq from node.js
JavaScript
112
star
11

node-postgres-docs

Documentation for node-postgres
JavaScript
97
star
12

node-pg-query

Simple helper to run queries with node-postgres
JavaScript
79
star
13

node-pg-cursor

Query cursor extension for node-postgres
JavaScript
78
star
14

node-okay

bubble errors back up your big ol' nested callback chain
JavaScript
69
star
15

node-postgres-pure

node-postgres without any of the C/C++ stuff
JavaScript
59
star
16

node-pdf-text

extract text from a pdf as an array of text blocks
JavaScript
46
star
17

node-omf

ORANGE MOCHA FRAPPUCCINO
JavaScript
40
star
18

node-relational

ORM with an emphasis on the O, the R, and the M.
CoffeeScript
37
star
19

node-nsqueue

node.js client for nsq
JavaScript
23
star
20

node-agi

node.js AGI (Asterisk Gateway Interface)
JavaScript
21
star
21

stylus-blueprint

Blueprint ported to stylus
JavaScript
17
star
22

node-deprecate

Mark a method as deprecated by printing a warning to the console the first time it's called
JavaScript
14
star
23

node-hacker-news-parser

Parses the insanely shitty html of hacker news comments into JSON
JavaScript
13
star
24

node-buffer-writer

A very fast buffer writer with expandable memory to reduce memory use
JavaScript
12
star
25

node-heroku-env

Because sometimes I wanna connect my local box to heroku postgres.
JavaScript
11
star
26

node-auto-deploy

my little auto-deploy script
JavaScript
11
star
27

node-querybox

A little helper to run queries stored in .sql files
JavaScript
10
star
28

node-packet-reader

JavaScript
10
star
29

indexeddb-performance-demo

Demo of simultaneous read/write speed vs. read only speed of indexeddb
JavaScript
8
star
30

node-ami

Asterisk AMI client for node.js
JavaScript
7
star
31

keeper

ORM for node.js
JavaScript
7
star
32

hipchat-eraser

Erase your 1 on 1 HipChat history
JavaScript
7
star
33

node-proxynova

node.js interface to the proxynova.com proxy list
JavaScript
6
star
34

vim-config

mah vim config
Vim Script
6
star
35

postgres-session

node-postgres backed session store for node.js express (connect middleware)
JavaScript
5
star
36

node-pg-parse-float

Add parse-float functionality back to node-postgres
JavaScript
5
star
37

upstart-scripts

Place to keep various upstart scripts so I stop forgetting how to make them
5
star
38

node-pg-pipe

Very efficient row by row streaming between PostgreSQL tables. Uses libpq.
JavaScript
4
star
39

node-pg-writable

Create a writable stream which very quickly writes an array of values to a table in postgres
JavaScript
4
star
40

fluxed

tiny flux implementation
JavaScript
3
star
41

bencher

node.js benchmarking & performance analysis utilities
JavaScript
3
star
42

node-array-reader

Search forwards and backwards in an array of strings
JavaScript
3
star
43

hyperterm-focus-reporting

Adds focus reporting to hyperterm for tmux & vim support
JavaScript
3
star
44

json-with-typed-arrays

JSON stringify/parse with typed array support
JavaScript
2
star
45

react-starter

Because setting this stuff up is a nightmare
JavaScript
2
star
46

node-kafka-protocol

JavaScript
2
star
47

node-logged

Simple logging for node.js
JavaScript
2
star
48

deadblackhearts.com

Band Website
Ruby
2
star
49

tailed

`tail -f` for node.js
JavaScript
2
star
50

node-pg-stream

A readable stream (objectmode) that gives you postgres rows
JavaScript
2
star
51

drain

A testing helper for node.js async code
2
star
52

unit.js

unit testing for javascript
JavaScript
2
star
53

node-ansible-tower-client

Simple client to ansible tower to run a job based on a template name
JavaScript
2
star
54

node-gelf-encode

Encode GELF json to proper GELF binary
JavaScript
1
star
55

myiptables

My iptables configuration file
1
star
56

memory-socket

In-memory node.js socket for use in testing
JavaScript
1
star
57

wasm-hello-world

HTML
1
star
58

git-nits

Some bits and bops I use to help me suck less at git
Shell
1
star
59

node-pg-copy-table

Copy tables from one database to another with streams and queries
JavaScript
1
star
60

node-dogpile

Dogpile cache an expensive async function
JavaScript
1
star
61

node-level-readable

Fast, efficient leveldb readable stream over the network
JavaScript
1
star
62

node-postgres-bench

Benchmark old & new node postgres
JavaScript
1
star
63

brianc.github.com

stuff stuff. gimme some stuff.
1
star
64

install-scripts

install scripts for cloud servers because chef is like whaaaa??
Shell
1
star
65

node-gelf-parser

Parse GELF binary using a through-stream
JavaScript
1
star
66

express-skeleton

Skeleton app for express with jade & sass
JavaScript
1
star
67

node-buffer-slice

slice a buffer into an array of sub-buffers
JavaScript
1
star
68

dotfiles

Mah .files
Lua
1
star
69

test-dir

Require directories full of files for testing.
JavaScript
1
star
70

proxied

A stream http proxy server with http request pause() support
CoffeeScript
1
star
71

pdbh

private.deadblackhearts.com
JavaScript
1
star
72

xo

kiss hug - exceptionally tiny event-bus framework
CoffeeScript
1
star
73

node-make-emitter

event emitter constructor factory
JavaScript
1
star
74

node-httper

Little wrapper around request to make consuming an external service a bit easier.
JavaScript
1
star
75

mvp.py

mvp.py
Python
1
star
76

node-pg-bench

node-postgres benchmarking utility
JavaScript
1
star