1 | # mesa
|
2 |
|
3 | [![Build Status](https://travis-ci.org/snd/mesa.png)](https://travis-ci.org/snd/mesa)
|
4 |
|
5 | **simple elegant sql for nodejs**
|
6 |
|
7 | mesa is not an orm. it aims to help as much as possible with the construction, composition and execution of sql queries
|
8 | while not restricting full access to the underlying database driver and database in any way.
|
9 |
|
10 | mesa builds on top of [mohair, a simple fluent sql query builder](https://github.com/snd/mohair).
|
11 |
|
12 | it adds the ability to run queries on connections, process query results, to declare and include
|
13 | associations (`hasOne`, `belongsTo`, `hasMany`, `hasManyThrough`) and more.
|
14 |
|
15 | mesa has been battle tested in a medium sized (8 heroku dynos) production environment
|
16 | for half a year.
|
17 |
|
18 | mesa uses criterion for sql-where-conditions.
|
19 | consult the [criterion readme](https://github.com/snd/criterion)
|
20 | and [mohair readme](https://github.com/snd/mohair) to get the full picture of what is possible with mesa.
|
21 |
|
22 | ### install
|
23 |
|
24 | ```
|
25 | npm install mesa
|
26 | ```
|
27 |
|
28 | ### use
|
29 |
|
30 | mesa has a fluent interface where every method returns a new object.
|
31 | no method ever changes the state of the object it is called on.
|
32 | this enables a functional programming style.
|
33 |
|
34 | #### require
|
35 |
|
36 | ```javascript
|
37 | var mesa = require('mesa');
|
38 | ```
|
39 |
|
40 | #### connections
|
41 |
|
42 | **mesa only works with [node-postgres](https://github.com/brianc/node-postgres) at the moment**
|
43 |
|
44 | tell mesa how to get a connection from the pool:
|
45 |
|
46 | ```javascript
|
47 | var pg = require('pg');
|
48 |
|
49 | var mesaWithConnection = mesa.connection(function(cb) {
|
50 | pg.connect('tcp://username@localhost/database', cb);
|
51 | });
|
52 |
|
53 | ```
|
54 |
|
55 | `mesaWithConnection` will now use the provided function to get connections
|
56 | for the commands you execute.
|
57 | these connections are under mesa's control.
|
58 | mesa will [properly call done()](https://github.com/brianc/node-postgres/wiki/pg#connectfunction-callback) on every connection it has obtained from the
|
59 | pool.
|
60 |
|
61 | #### tables
|
62 |
|
63 | specify the table to use:
|
64 |
|
65 | ```javascript
|
66 | var userTable = mesaWithConnection.table('user');
|
67 | ```
|
68 |
|
69 | #### command
|
70 |
|
71 | ##### insert
|
72 |
|
73 | ```javascript
|
74 | userTable.
|
75 | .attributes(['name'])
|
76 | .insert({
|
77 | name: 'alice'
|
78 | }, function(err, id) {
|
79 | });
|
80 | ```
|
81 |
|
82 | `attributes()` sets the properties to pick from data in the `create()` and `update()`
|
83 | methods. `attributes()` prevents mass assignment
|
84 | and must be called before using the `create()` or `update()` methods.
|
85 |
|
86 | ##### insert multiple records
|
87 |
|
88 | ```javascript
|
89 | userTable
|
90 | .attributes(['name'])
|
91 | .insertMany([
|
92 | {name: 'alice'},
|
93 | {name: 'bob'}
|
94 | ], function(err, ids) {
|
95 | });
|
96 | ```
|
97 |
|
98 | ##### insert with some raw sql
|
99 |
|
100 | ```javascript
|
101 | userTable.
|
102 | .attributes(['name', 'created'])
|
103 | .insert({
|
104 | name: 'alice',
|
105 | created: userTable.raw('NOW()')
|
106 | }, function(err, id) {
|
107 | });
|
108 | ```
|
109 |
|
110 | `raw()` can be used to inject arbitrary sql instead of binding a parameter.
|
111 |
|
112 | ##### delete
|
113 |
|
114 | ```javascript
|
115 | userTable.where({id: 3}).delete(function(err) {
|
116 | });
|
117 | ```
|
118 |
|
119 | see the [criterion readme](https://github.com/snd/criterion) for all the ways to
|
120 | specify where conditions in mesa.
|
121 |
|
122 | ##### update
|
123 |
|
124 | ```javascript
|
125 | userTable
|
126 | .where({id: 3})
|
127 | .where({name: 'alice'})
|
128 | .update({name: 'bob'}, function(err) {
|
129 | });
|
130 | ```
|
131 |
|
132 | multiple calls to `where` are anded together.
|
133 |
|
134 | #### query
|
135 |
|
136 | ##### find the first
|
137 |
|
138 | ```javascript
|
139 | userTable.where({id: 3}).first(function(err, user) {
|
140 | });
|
141 | ```
|
142 |
|
143 | ##### test for existence
|
144 |
|
145 | ```javascript
|
146 | userTable.where({id: 3}).exists(function(err, exists) {
|
147 | });
|
148 | ```
|
149 |
|
150 | ##### find all
|
151 |
|
152 | ```javascript
|
153 | userTable.where({id: 3}).find(function(err, user) {
|
154 | });
|
155 | ```
|
156 |
|
157 | ##### select, join, group, order, limit, offset
|
158 |
|
159 | ```javascript
|
160 | userTable
|
161 | .select('user.*, count(project.id) AS project_count')
|
162 | .where({id: 3})
|
163 | .where('name = ?', 'foo')
|
164 | .join('JOIN project ON user.id = project.user_id')
|
165 | .group('user.id')
|
166 | .order('created DESC, name ASC')
|
167 | .limit(10)
|
168 | .offset(20)
|
169 | .find(function(err, users) {
|
170 | });
|
171 | ```
|
172 |
|
173 | #### associations
|
174 |
|
175 | ##### has one
|
176 |
|
177 | use `hasOne` if the foreign key is in the other table (`addressTable` in this example)
|
178 |
|
179 | ```javascript
|
180 | var userTable = userTable.hasOne('address', addressTable, {
|
181 | primaryKey: 'id', // optional with default: 'id'
|
182 | foreignKey: 'user_id' // optional with default: userTable.getTable() + '_id'
|
183 | });
|
184 | ```
|
185 |
|
186 | the second argument can be a function which must return a mesa object.
|
187 | this can be used to resolve tables which are not yet created when the association
|
188 | is defined.
|
189 | it's also a way to do self associations.
|
190 |
|
191 | ##### belongs to
|
192 |
|
193 | use `belongsTo` if the foreign key is in the table that `belongsTo`
|
194 | is called on (`projectTable` in this example)
|
195 |
|
196 | ```javascript
|
197 | var projectTable = projectTable.belongsTo('user', userTable, {
|
198 | primaryKey: 'id', // optional with default: 'id'
|
199 | foreignKey: 'user_id' // optional with default: userTable.getTable() + '_id'
|
200 | });
|
201 | ```
|
202 |
|
203 | ##### has many
|
204 |
|
205 | use `hasMany` if the foreign key is in the other table (`userTable` in this example) and
|
206 | there are multiple associated records
|
207 |
|
208 | ```javascript
|
209 | var userTable = userTable.hasMany('projects', projectTable, {
|
210 | primaryKey: 'id', // optional with default: 'id'
|
211 | foreignKey: 'user_id' // optional with default: userTable.getTable() + '_id'
|
212 | });
|
213 | ```
|
214 |
|
215 | ##### has many through
|
216 |
|
217 | use `hasManyThrough` if the association uses a join table
|
218 |
|
219 | ```javascript
|
220 | var userProjectTable = mesaWithConnection.table('user_project');
|
221 |
|
222 | var userTable = userTable.hasManyThrough('projects', projectTable, userProjectTable,
|
223 | primaryKey: 'id', // optional with default: 'id'
|
224 | foreignKey: 'user_id', // optional with default: userTable.getTable() + '_id'
|
225 | otherPrimaryKey: 'id', // optional with default: 'id'
|
226 | otherForeignKey: 'project_id' // optional with default: projectTable.getTable() + '_id'
|
227 | });
|
228 | ```
|
229 |
|
230 | ##### including associated
|
231 |
|
232 | associations are only fetched if you `include` them:
|
233 |
|
234 | ```javascript
|
235 | userTable.includes({address: true}).find(function(err, users) {
|
236 | });
|
237 | ```
|
238 |
|
239 | includes can be nested arbitrarily deep:
|
240 |
|
241 | ```javascript
|
242 | userTable
|
243 | .includes({
|
244 | shipping_address: {
|
245 | street: true,
|
246 | town: true
|
247 | },
|
248 | billing_address: true,
|
249 | friends: {
|
250 | billing_address: true
|
251 | }})
|
252 | .find(function(err, users) {
|
253 | });
|
254 | ```
|
255 |
|
256 | ### advanced use
|
257 |
|
258 | ##### extending mesa's fluent interface
|
259 |
|
260 | every mesa object prototypically inherits from the object
|
261 | before it in the fluent call chain.
|
262 |
|
263 | this means that every mesa object is very lightweight since
|
264 | it shares structure with objects before it in the fluent call chain.
|
265 |
|
266 | it also makes it very easy to extend mesa's fluent interface:
|
267 |
|
268 | ```javascript
|
269 | var userTable = mesa.table('user');
|
270 |
|
271 | userTable.activeAdmins = function() {
|
272 | return this.where({visible: true, role: 'admin'});
|
273 | };
|
274 |
|
275 | userTable.whereCreatedBetween = function(from, to) {
|
276 | return this.where('created BETWEEN ? AND ?', from, to);
|
277 | };
|
278 |
|
279 | userTable
|
280 | .order('created DESC')
|
281 | .activeAdmins()
|
282 | .whereCreatedBetween(new Date(2013, 4, 10), new Date(2013, 4, 12))
|
283 | .find(function(err, users) {
|
284 | });
|
285 | ```
|
286 |
|
287 | ##### user controlled connections
|
288 |
|
289 | sometimes, when using a transaction, you need to run multiple commands over multiple tables on the
|
290 | same connection.
|
291 |
|
292 | use `getConnection()` to get a raw connection from mesa.
|
293 | you can then run arbitrary sql on that connection.
|
294 | use `connection()` with a connection object to
|
295 | tell mesa to explicitely use that connection instead of getting
|
296 | a new one from the pool:
|
297 |
|
298 | ```javascript
|
299 | userTable.getConnection(function(err, connection, done) {
|
300 | connection.query('BEGIN', function(err) {
|
301 | userTable
|
302 | // use the transactional connection explicitely
|
303 | .connection(connection)
|
304 | .insert({name: 'alice'}, function(err, id) {
|
305 |
|
306 | // run more commands in the transaction
|
307 | // possibly on other tables
|
308 |
|
309 | connection.query('COMMIT', function(err) {
|
310 | done();
|
311 | });
|
312 | });
|
313 | });
|
314 | });
|
315 | ```
|
316 |
|
317 | when you are done using the connection you need to call `done()` to
|
318 | tell node-postgres to return the connection to the pool.
|
319 | otherwise you will leak that connection, which is **very bad** since
|
320 | your application will run out of connections and hang.
|
321 |
|
322 | ### license: MIT
|