1 | 'use strict';
|
2 | const pg = require('pg');
|
3 |
|
4 |
|
5 |
|
6 |
|
7 |
|
8 | let postgres = {
|
9 |
|
10 | connect: function(connection) {
|
11 | return new Promise((resolve, reject) => {
|
12 | pg.connect(connection, function(err, client, done) {
|
13 | if(err) {
|
14 | reject(err);
|
15 | }
|
16 | resolve({ client: client, done: done });
|
17 | });
|
18 | });
|
19 | },
|
20 |
|
21 | query: function(client, done, sql) {
|
22 | return new Promise((resolve, reject) => {
|
23 | client.query(sql, function(err, results) {
|
24 | done();
|
25 | if(err) {
|
26 | reject(err);
|
27 | }
|
28 | resolve(results);
|
29 | });
|
30 | });
|
31 | }
|
32 |
|
33 | };
|
34 |
|
35 |
|
36 |
|
37 |
|
38 |
|
39 | const STRING = 'string';
|
40 | const OBJECT = 'object';
|
41 | const NUMBER = 'number';
|
42 | const BOOLEAN = 'boolean';
|
43 |
|
44 |
|
45 |
|
46 |
|
47 |
|
48 | let token = {
|
49 | table : (table) => {
|
50 | return `"${table}"`;
|
51 | },
|
52 | columnSingle: (column) => {
|
53 | return `${(column !== '*') ? '"' + column + '"' : column}`;
|
54 | },
|
55 | columnTable: (table, column) => {
|
56 | return `${token.table(table)}.${token.column(column)}`;
|
57 | },
|
58 | column: (column) => {
|
59 | if(typeof column === OBJECT) {
|
60 | return `${token.columnTable(column[0], column[1])}`;
|
61 | }
|
62 | else if(typeof column === STRING) {
|
63 | return `${token.columnSingle(column)}`;
|
64 | }
|
65 | },
|
66 | expression: (column, operator, value) => {
|
67 | return `${token.column(column)} ${operator} ${token.value(value)}`;
|
68 | },
|
69 | expressionColumn: (column, operator, value) => {
|
70 | return `${token.column(column)} ${operator} ${token.column(value)}`;
|
71 | },
|
72 | value : (value) => {
|
73 | switch(typeof value) {
|
74 | case STRING:
|
75 | return `'${value.replace(/\'/g, "''")}'`;
|
76 | case NUMBER:
|
77 | return `${value}`;
|
78 | case BOOLEAN:
|
79 | return `${value}`;
|
80 | }
|
81 | },
|
82 | };
|
83 |
|
84 |
|
85 |
|
86 |
|
87 |
|
88 | let SQL = function(settings) {
|
89 |
|
90 |
|
91 |
|
92 |
|
93 |
|
94 | let query = [];
|
95 |
|
96 | let container = {
|
97 | table: '',
|
98 | distinct: false,
|
99 | };
|
100 |
|
101 |
|
102 |
|
103 |
|
104 |
|
105 | this.table = (table) => {
|
106 | container.table = token.table(table);
|
107 | return {
|
108 | distinct: this.distinct,
|
109 | select: this.select,
|
110 | insert: this.insert,
|
111 | update: this.update,
|
112 | delete: this.delete
|
113 | };
|
114 | };
|
115 |
|
116 | this.distinct = () => {
|
117 | container.distinct = true;
|
118 | return {
|
119 | select: this.select,
|
120 | insert: this.insert,
|
121 | update: this.update,
|
122 | delete: this.delete
|
123 | };
|
124 | };
|
125 |
|
126 |
|
127 |
|
128 |
|
129 |
|
130 | this.begin = () => {
|
131 | query.push(`begin`);
|
132 | return {
|
133 | exec: this.exec
|
134 | };
|
135 | };
|
136 |
|
137 | this.commit = () => {
|
138 | query.push(`commit`);
|
139 | return {
|
140 | exec: this.exec
|
141 | };
|
142 | };
|
143 |
|
144 | this.rollback = () => {
|
145 | query.push(`rollback`);
|
146 | return {
|
147 | exec: this.exec
|
148 | };
|
149 | };
|
150 |
|
151 |
|
152 |
|
153 |
|
154 |
|
155 | this.select = (columns) => {
|
156 | columns = columns.map((column) => token.column(column));
|
157 | query.push(`select ${((container.distinct) ? 'distinct ' + columns.join(', ') : columns.join(', ') )} `);
|
158 | query.push(`from ${container.table}`);
|
159 | return {
|
160 | sql: this.sql,
|
161 | where: this.where,
|
162 | limit: this.limit,
|
163 | offset: this.offset,
|
164 | orderBy: this.orderBy,
|
165 | innerJoin: this.innerJoin,
|
166 | exec: this.exec
|
167 | };
|
168 | };
|
169 |
|
170 | this.insert = (data) => {
|
171 | let columns = [];
|
172 | let values = [];
|
173 | query.push(`insert into ${container.table}`);
|
174 | data.map((cols) => {
|
175 | let vals = [];
|
176 | for(let col in cols) {
|
177 | let c = token.column(col);
|
178 | let found = columns.find((column) => column === c);
|
179 | if(!found) columns.push(c);
|
180 | vals.push([token.value(cols[col])]);
|
181 | }
|
182 | values.push(`(${vals.join(', ')})`);
|
183 | });
|
184 | query.push(`(${columns.join(', ')}) values ${values.join(' ')}`);
|
185 | return {
|
186 | sql: this.sql,
|
187 | returning: this.returning,
|
188 | exec: this.exec
|
189 | };
|
190 | };
|
191 |
|
192 | this.update = (data) => {
|
193 | let columns = [];
|
194 | let values = [];
|
195 | query.push(`update ${container.table} set ${columns.join(', ')}`);
|
196 | for(let col in data) {
|
197 | columns.push(token.column(col));
|
198 | values.push(token.value(data[col]));
|
199 | }
|
200 | query.push(`(${columns.join(', ')}) = (${values.join(', ')})`);
|
201 | return {
|
202 | sql: this.sql,
|
203 | where: this.where,
|
204 | exec: this.exec
|
205 | };
|
206 | };
|
207 |
|
208 | this.delete = () => {
|
209 | query.push(`delete from ${container.table}`);
|
210 | return {
|
211 | sql: this.sql,
|
212 | where: this.where,
|
213 | exec: this.exec
|
214 | };
|
215 | };
|
216 |
|
217 |
|
218 |
|
219 |
|
220 |
|
221 | this.join = (table) => {
|
222 | query.push(`join ${token.table(table)}`);
|
223 | return {
|
224 | sql: this.sql,
|
225 | on: this.on
|
226 | };
|
227 | };
|
228 |
|
229 | this.innerJoin = (table) => {
|
230 | query.push(`inner join ${token.table(table)}`);
|
231 | return {
|
232 | sql: this.sql,
|
233 | on: this.on
|
234 | };
|
235 | };
|
236 |
|
237 | this.outerJoin = (table) => {
|
238 | query.push(`outer join ${token.table(table)}`);
|
239 | return {
|
240 | sql: this.sql,
|
241 | on: this.on
|
242 | };
|
243 | };
|
244 |
|
245 | this.on = (columnA, operator, columnB) => {
|
246 | query.push(`on (${token.expressionColumn(columnA, operator, columnB)})`);
|
247 | return {
|
248 | sql: this.sql,
|
249 | where: this.where,
|
250 | whereIn: this.whereIn,
|
251 | limit: this.limit,
|
252 | offset: this.offset,
|
253 | orderBy: this.orderBy,
|
254 | returning: this.returning,
|
255 | exec: this.exec
|
256 | };
|
257 | };
|
258 |
|
259 |
|
260 |
|
261 |
|
262 |
|
263 | this.where = (column, operator, value) => {
|
264 | if(column, operator, value) {
|
265 | query.push(`where ${token.expression(column, operator, value)}`);
|
266 | }
|
267 | else if(column, !operator, !value) {
|
268 | query.push(`where ${token.column(column)}`);
|
269 | }
|
270 | return {
|
271 | sql: this.sql,
|
272 | and: this.and,
|
273 | or: this.or,
|
274 | in: this.in,
|
275 | limit: this.limit,
|
276 | offset: this.offset,
|
277 | orderBy: this.orderBy,
|
278 | returning: this.returning,
|
279 | exec: this.exec
|
280 | };
|
281 | };
|
282 |
|
283 | this.and = (column, operator, value) => {
|
284 | if(column, operator, value) {
|
285 | query.push(`and ${token.expression(column, operator, value)}`);
|
286 | }
|
287 | else if(column, !operator, !value) {
|
288 | query.push(`and ${token.column(column)}`);
|
289 | }
|
290 | return {
|
291 | sql: this.sql,
|
292 | and: this.and,
|
293 | or: this.or,
|
294 | in: this.in,
|
295 | limit: this.limit,
|
296 | offset: this.offset,
|
297 | orderBy: this.orderBy,
|
298 | exec: this.exec
|
299 | };
|
300 | };
|
301 |
|
302 | this.or = (column, operator, value) => {
|
303 | if(column, operator, value) {
|
304 | query.push(`or ${token.expression(column, operator, value)}`);
|
305 | }
|
306 | else if(column, !operator, !value) {
|
307 | query.push(`or ${token.column(column)}`);
|
308 | }
|
309 | return {
|
310 | sql: this.sql,
|
311 | and: this.and,
|
312 | or: this.or,
|
313 | in: this.in,
|
314 | limit: this.limit,
|
315 | offset: this.offset,
|
316 | orderBy: this.orderBy,
|
317 | exec: this.exec
|
318 | };
|
319 | };
|
320 |
|
321 | this.in = (sql) => {
|
322 | query.push(`in (${sql})`);
|
323 | return {
|
324 | sql: this.sql,
|
325 | where: this.where,
|
326 | exec: this.exec
|
327 | };
|
328 | };
|
329 |
|
330 | this.limit = (limit) => {
|
331 | query.push(`limit ${limit}`);
|
332 | return {
|
333 | sql: this.sql,
|
334 | offset: this.offset,
|
335 | orderBy: this.orderBy,
|
336 | exec: this.exec
|
337 | };
|
338 | };
|
339 |
|
340 | this.offset = (offset) => {
|
341 | query.push(`offset ${offset}`);
|
342 | return {
|
343 | sql: this.sql,
|
344 | limit: this.limit,
|
345 | orderBy: this.orderBy,
|
346 | exec: this.exec
|
347 | };
|
348 | };
|
349 |
|
350 | this.orderBy = (column, order) => {
|
351 | query.push(`order by ${token.column(column)} ${order}`);
|
352 | return {
|
353 | sql: this.sql,
|
354 | limit: this.limit,
|
355 | orderBy: this.orderBy,
|
356 | exec: this.exec
|
357 | };
|
358 | };
|
359 |
|
360 | this.returning = (columns) => {
|
361 | columns = columns.map((column) => token.column(column));
|
362 | query.push(`returning ${columns.join(', ')}`);
|
363 | return {
|
364 | sql: this.sql,
|
365 | exec: this.exec
|
366 | };
|
367 | };
|
368 |
|
369 |
|
370 |
|
371 |
|
372 |
|
373 | this.sql = () => {
|
374 | const sql = `${query.join(' ')}`;
|
375 | if(settings.debug) {
|
376 | console.log('\n\n', sql);
|
377 | }
|
378 | return sql;
|
379 | };
|
380 |
|
381 |
|
382 |
|
383 |
|
384 |
|
385 | this.exec = () => {
|
386 | let sql = this.sql();
|
387 | return new Promise((resolve, reject) => {
|
388 | if(settings.transaction) {
|
389 | if(settings.debug) {
|
390 | console.log('\n\n', 'using transaction');
|
391 | }
|
392 | settings.transaction.query(sql, function(err, results) {
|
393 | if(err) {
|
394 | reject(err);
|
395 | } else {
|
396 | resolve(results.rows);
|
397 | }
|
398 | });
|
399 | } else {
|
400 | if(settings.debug) {
|
401 | console.log('\n\n', 'using transaction');
|
402 | }
|
403 | let client, done;
|
404 | postgres.connect(settings.connection).then((response) => {
|
405 | client = response.client;
|
406 | done = response.done;
|
407 | return postgres.query(client, done, sql);
|
408 | })
|
409 | .then((results) => {
|
410 | resolve(results.rows);
|
411 | })
|
412 | .catch((err) => {
|
413 | reject(err);
|
414 | });
|
415 | }
|
416 | });
|
417 | };
|
418 | };
|
419 |
|
420 |
|
421 |
|
422 |
|
423 |
|
424 | let Querier = (connection, config) => {
|
425 |
|
426 |
|
427 | let settings = {
|
428 | connection: '',
|
429 | debug: (config && config.debug) ? config.debug : false
|
430 | };
|
431 |
|
432 |
|
433 | settings.connection = `postgres://${connection.user}:${connection.password}@${connection.host}/${connection.database}`;
|
434 |
|
435 |
|
436 | let client, done;
|
437 |
|
438 |
|
439 | return {
|
440 | table: function(table) {
|
441 | return new SQL({
|
442 | connection: settings.connection,
|
443 | debug: settings.debug,
|
444 | transaction: client
|
445 | }).table(table);
|
446 | },
|
447 | transaction() {
|
448 | return postgres.connect(settings.connection).then((response) => {
|
449 | client = response.client;
|
450 | done = response.done;
|
451 | return new SQL({
|
452 | transaction: client,
|
453 | debug: settings.debug
|
454 | }).begin().exec();
|
455 | });
|
456 | },
|
457 | rollback: function() {
|
458 | return new SQL({
|
459 | transaction: client,
|
460 | debug: settings.debug
|
461 | }).rollback().exec().then(() => {
|
462 | done();
|
463 | client = null;
|
464 | done = null;
|
465 | });
|
466 | },
|
467 | commit: function() {
|
468 | return new SQL({
|
469 | transaction: client,
|
470 | debug: settings.debug
|
471 | }).commit().exec().then(() => {
|
472 | done();
|
473 | client = null;
|
474 | done = null;
|
475 | });
|
476 | }
|
477 | };
|
478 |
|
479 | };
|
480 |
|
481 | module.exports = Querier; |
\ | No newline at end of file |