Node.js Functional SQL Query Builder & ORM
npm i fxsqlconst { PostgreSQL } = require("fxsql");
const { CONNECT } = PostgreSQL;
const POOL = CONNECT({
host: 'localhost',
user: 'username',
password: '1234',
database: 'dbname'
});FxSQL is built on node-postgres. The parameter of CONNECT function is the same as node-postgres’. You can read the detail of connection pool or connecting to DB on node-postgres’ site.
const { MySQL } = require("fxsql");
const { CONNECT } = MySQL;
const POOL = CONNECT({
host: 'localhost',
user: 'username',
password: '1234',
database: 'dbname'
});FxSQL is built on node-postgres. The parameter of CONNECT function is the same as the MySQL’. You can read the detail of connection pool or connecting to DB on MySQL's site.
POOL.END(); // Promiseconst { QUERY } = POOL;
const id = 10;
const posts = await QUERY `SELECT * FROM posts WHERE id = ${id}`;
// [{ id: 10, ... }]const type = 'TYPE1';
const limit = 10;
QUERY `
SELECT * FROM table1 WHERE table2_id IN (
SELECT id FROM table2 WHERE type = ${type} ORDER BY id DESC LIMIT ${limit}
)
`;
const status = 'STATUS1';
QUERY `
SELECT *
FROM table1 AS t1, table2 AS t2
WHERE t1.id = t2.table1_id AND t1.status = ${status}
ORDER BY id DESC
LIMIT 10
`;QUERY achieved from CONNECT uses a connection pool.
const POOL = CONNECT();
const {
VALUES, IN, NOT_IN, EQ, SET, COLUMN, CL, TABLE, TB, SQL, FxSQL_DEBUG,
QUERY,
ASSOCIATE,
LJOIN,
TRANSACTION,
END
} = POOL;const users = await QUERY `SELECT * FROM users WHERE ${EQ({
email: '[email protected]',
password: '1234'
})}`;
// [{ id: 15, email: '[email protected]', ... }]const users = await QUERY `SELECT * FROM users WHERE ${IN('id', [15, 19, 20, 40])}`;
// [{ id: 15, ...}, { id: 19, ...} ...]const users = await QUERY `SELECT * FROM users WHERE ${NOT_IN('id', [2, 4])} ORDER BY ID LIMIT 3`;
// [{ id: 1, ...}, { id: 3, ...}, { id: 5, ...}]const post = { user_id: 10, body: 'hoho' };
await QUERY `
INSERT INTO posts ${VALUES(post)}
`;
// INSERT INTO posts ("user_id", "body") VALUES (10, 'hohoho')
await QUERY `
INSERT INTO coords ${VALUES([
{x: 20},
{y: 30},
{x: 10, y: 20}
])}`;
// INSERT INTO coords ("x", "y") VALUES (20, DEFAULT), (DEFAULT, 30), (10, 20)await QUERY `
UPDATE posts ${SET({ body: 'yo!', updated_at: new Date() })} WHERE id = ${post.id}
`;
// UPDATE posts SET "body" = 'yo!', "updated_at" = '2018-08-28T23:18:13.263Z' WHERE id = 10COLUMN == CL; // true
await QUERY `
SELECT
${COLUMN('id', 'bb as cc', 't2.name', 't2.name as name2', { a: 'c' }, { 't3.a': 'd' })}
...
`;
// SELECT
// "id", "bb" AS "cc", "t2"."name", "t2"."name" AS "name2", "a" AS "c", "t3"."a" AS "d"
// ...TABLE == TB; // true
await QUERY `
SELECT
...
FROM ${TABLE('t1')}, ${TABLE('tt as t2')}
`;
// SELECT
// ...
// FROM "t1", "tt" AS "t2"ASSOCIATE uses Connection pool.
/*
* users
* - id
* - name
*
* posts
* - id
* - user_id
* - body
* comments
* - id
* - user_id
* - post_id
* - body
* */
const { ASSOCIATE } = POOL;
const posts = await ASSOCIATE `
posts
- user
$.id, posts)}) AND attached_type = 'posts';
// SELECT * FROM photos WHERE attached_id IN (${map($ => $.id, users)}) AND attached_type = 'users';
// SELECT * FROM photos WHERE attached_id IN (${map($ => $.id, comments)}) AND attached_type = 'comments';p - refers to Polymorphic + Has one, p users.map(u => Object.assign({}, u, { _popular: !!u._.posts.find(p => p._is_best) }) )}} posts.map( p => Object.assign({}, p, { _is_best: p._.comments.length > 1 }))}} - user ASSOCIATE_MODULE
id DESC]
}}ASSOCIATE posts ${SQLWHERE is_hidden = false ORDER BY id DESC LIMIT ${10}} ${Post.rights(6)} ;
## Transaction
```javascript
const { PostgreSQL } = require("fxsql");
const { CONNECT } = PostgreSQL;
const POOL = CONNECT({
host: 'localhost',
user: 'username',
password: '1234',
database: 'dbname',
charset: 'utf8'
});
const { TRANSACTION } = POOL;
const { QUERY, COMMIT, ROLLBACK } = await TRANSACTION();
await QUERY `
INSERT INTO posts ${VALUES(post)}
`;
await QUERY `
UPDATE posts ${SET({ body: 'yo!', updated_at: new Date() })} WHERE id = ${post.id}
`;
await ROLLBACK();FxSQL_DEBUG.LOG = true;
QUERY `SELECT ${"hi~"} as ho`;
// { text: 'SELECT $1 as ho', values: ['hi'] }No open issues yet, or sync has not completed.