1 | # node-sql
|
2 | _sql string builder for node_ - supports PostgreSQL, mysql, Microsoft SQL Server, Oracle and sqlite dialects.
|
3 |
|
4 | Building SQL statements by hand is no fun, especially in a language which has clumsy support for multi-line strings.
|
5 |
|
6 | So let's build it with JavaScript.
|
7 |
|
8 | Maybe it's still not fun, but at least it's _less not fun_.
|
9 |
|
10 | [![Build Status](https://secure.travis-ci.org/brianc/node-sql.png)](http://travis-ci.org/brianc/node-sql)
|
11 |
|
12 | ## install
|
13 |
|
14 | ```sh
|
15 | $ npm install sql
|
16 | ```
|
17 |
|
18 | ## use
|
19 |
|
20 | ```js
|
21 | //require the module
|
22 | var sql = require('sql');
|
23 |
|
24 | //(optionally) set the SQL dialect
|
25 | sql.setDialect('postgres');
|
26 | //possible dialects: mssql, mysql, postgres (default), sqlite
|
27 |
|
28 | //first we define our tables
|
29 | var user = sql.define({
|
30 | name: 'user',
|
31 | columns: ['id', 'name', 'email', 'lastLogin']
|
32 | });
|
33 |
|
34 | var post = sql.define({
|
35 | name: 'post',
|
36 | columns: ['id', 'userId', 'date', 'title', 'body']
|
37 | });
|
38 |
|
39 | //now let's make a simple query
|
40 | var query = user.select(user.star()).from(user).toQuery();
|
41 | console.log(query.text); //SELECT "user".* FROM "user"
|
42 |
|
43 | //something more interesting
|
44 | var query = user
|
45 | .select(user.id)
|
46 | .from(user)
|
47 | .where(
|
48 | user.name.equals('boom').and(user.id.equals(1))
|
49 | ).or(
|
50 | user.name.equals('bang').and(user.id.equals(2))
|
51 | ).toQuery();
|
52 |
|
53 | //query is parameterized by default
|
54 | 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)))
|
55 |
|
56 | console.log(query.values); //['boom', 1, 'bang', 2]
|
57 |
|
58 | //queries can be named
|
59 | var query = user.select(user.star()).from(user).toNamedQuery('user.all');
|
60 | console.log(query.name); //'user.all'
|
61 |
|
62 | //how about a join?
|
63 | var query = user.select(user.name, post.body)
|
64 | .from(user.join(post).on(user.id.equals(post.userId))).toQuery();
|
65 |
|
66 | console.log(query.text); //'SELECT "user"."name", "post"."body" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
|
67 |
|
68 | //this also makes parts of your queries composable, which is handy
|
69 |
|
70 | var friendship = sql.define({
|
71 | name: 'friendship',
|
72 | columns: ['userId', 'friendId']
|
73 | });
|
74 |
|
75 | var friends = user.as('friends');
|
76 | var userToFriends = user
|
77 | .leftJoin(friendship).on(user.id.equals(friendship.userId))
|
78 | .leftJoin(friends).on(friendship.friendId.equals(friends.id));
|
79 |
|
80 | //and now...compose...
|
81 | var friendsWhoHaveLoggedInQuery = user.from(userToFriends).where(friends.lastLogin.isNotNull());
|
82 | //SELECT * FROM "user"
|
83 | //LEFT JOIN "friendship" ON ("user"."id" = "friendship"."userId")
|
84 | //LEFT JOIN "user" AS "friends" ON ("friendship"."friendId" = "friends"."id")
|
85 | //WHERE "friends"."lastLogin" IS NOT NULL
|
86 |
|
87 | var friendsWhoUseGmailQuery = user.from(userToFriends).where(friends.email.like('%@gmail.com'));
|
88 | //SELECT * FROM "user"
|
89 | //LEFT JOIN "friendship" ON ("user"."id" = "friendship"."userId")
|
90 | //LEFT JOIN "user" AS "friends" ON ("friendship"."friendId" = "friends"."id")
|
91 | //WHERE "friends"."email" LIKE %1
|
92 |
|
93 | //Using different property names for columns
|
94 | //helpful if your column name is long or not camelCase
|
95 | var user = sql.define({
|
96 | name: 'user',
|
97 | columns: [{
|
98 | name: 'id'
|
99 | }, {
|
100 | name: 'state_or_province',
|
101 | property: 'state'
|
102 | }
|
103 | ]
|
104 | });
|
105 |
|
106 | //now, instead of user.state_or_province, you can just use user.state
|
107 | console.log(user.select().where(user.state.equals('WA')).toQuery().text);
|
108 | // "SELECT "user".* FROM "user" WHERE ("user"."state_or_province" = $1)"
|
109 | ```
|
110 |
|
111 | There are a __lot__ more examples included in the [test/dialects](https://github.com/brianc/node-sql/tree/master/test/dialects) folder. We encourage you to read through them if you have any questions on usage!
|
112 |
|
113 | ## from the command line
|
114 | You can use the [sql-generate module](https://github.com/tmont/node-sql-generate)
|
115 | to automatically generate definition files from a database instance. For example,
|
116 | running `node-sql-generate --dsn "mysql://user:password@host/database"` will generate
|
117 | something similar to:
|
118 |
|
119 | ```javascript
|
120 | // autogenerated by node-sql-generate v0.0.1 on Tue May 21 2013 01:04:12 GMT-0700 (PDT)
|
121 | var sql = require('sql');
|
122 |
|
123 | /**
|
124 | * SQL definition for database.bar
|
125 | */
|
126 | exports.bar = sql.define({
|
127 | name: 'bar',
|
128 | columns: [
|
129 | 'id',
|
130 | 'foo_id'
|
131 | ]
|
132 | });
|
133 |
|
134 | /**
|
135 | * SQL definition for database.foo
|
136 | */
|
137 | exports.foo = sql.define({
|
138 | name: 'foo',
|
139 | columns: [
|
140 | 'id',
|
141 | 'field_1',
|
142 | 'foo_bar_baz'
|
143 | ]
|
144 | });
|
145 |
|
146 | /**
|
147 | * Adding a column to an existing table:
|
148 | */
|
149 | var model = sql.define({ name: 'foo', columns: [] });
|
150 | model.addColumn('id');
|
151 |
|
152 | // If you try to add another column "id", node-sql will throw an error.
|
153 | // You can suppress that error via:
|
154 | model.addColumn('id', { noisy: false });
|
155 | ```
|
156 |
|
157 | Read the module's documentation for more details.
|
158 |
|
159 | ## contributing
|
160 |
|
161 | We __love__ contributions.
|
162 |
|
163 | node-sql wouldn't be anything without all the contributors and collaborators who've worked on it.
|
164 | If you'd like to become a collaborator here's how it's done:
|
165 |
|
166 | 1. fork the repo
|
167 | 2. `git pull https://github.com/(your_username)/node-sql`
|
168 | 3. `cd node-sql`
|
169 | 4. `npm install`
|
170 | 5. `npm test`
|
171 |
|
172 | 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.
|
173 | My email address is on my github profile and also on every commit I contributed in the repo.
|
174 |
|
175 | 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.
|
176 |
|
177 | __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__
|
178 |
|
179 | 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.
|
180 |
|
181 | Usually after a few high-quality pull requests and friendly interactions we will gladly share collaboration rights with you.
|
182 |
|
183 | After all, open source belongs to everyone.
|
184 |
|
185 |
|
186 | ##license
|
187 | MIT
|