MQL - Node.js Functional SQL Query Builder & ORM
EN | KR
Features
- Tagged template literal
- No models.
- Only need functions and javascript data types.
- Promises
- No cost for converting to JSON.
- More freedom in using SQL syntax.
- Preventing SQL-injection attacks.
- Easy to use the latest operators provided in databases.
- Simple transaction API.
- No models for Associations.
- Designed to work well with PostgreSQL, MySQL.
Overview
Installation
npm i mql2
Connect
PostgreSQL
const { PostgreSQL } = require('mql2');
const { CONNECT } = PostgreSQL;
const POOL = CONNECT({
host: 'localhost',
user: 'username',
password: '1234',
database: 'dbname'
});
PostgreSQL Connection option
MQL 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.
MySQL
const { MySQL } = require('mql2');
const { CONNECT } = MySQL;
const POOL = CONNECT({
host: 'localhost',
user: 'username',
password: '1234',
database: 'dbname'
});
MySQL Connection option
MQL 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.
Simple query
const { QUERY } = POOL;
const id = 10;
const posts = await QUERY `SELECT * FROM posts WHERE id = ${id}`;
Subquery, Join
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.
Ready to be used
const POOL = CONNECT();
const {
VALUES, IN, NOT_IN, EQ, SET, COLUMN, CL, TABLE, TB, SQL, MQL_DEBUG,
QUERY,
ASSOCIATE,
LJOIN,
TRANSACTION
} = POOL;
Helper-Function
EQ
const users = await QUERY `SELECT * FROM users WHERE ${EQ({
email: 'dev@marpple.com',
password: '1234'
})}`;
IN
const users = await QUERY `SELECT * FROM users WHERE ${IN('id', [15, 19, 20, 40])}`;
NOT_IN
const users = await QUERY `SELECT * FROM users WHERE ${NOT_IN('id', [2, 4])} ORDER BY ID LIMIT 3`;
VALUES
const post = { user_id: 10, body: 'hoho' };
await QUERY `
INSERT INTO posts ${VALUES(post)}
`;
await QUERY `
INSERT INTO coords ${VALUES([
{x: 20},
{y: 30},
{x: 10, y: 20}
])}`;
SET
await QUERY `
UPDATE posts ${SET({ body: 'yo!', updated_at: new Date() })} WHERE id = ${post.id}
`;
COLUMN, CL
COLUMN == CL;
await QUERY `
SELECT
${COLUMN('id', 'bb as cc', 't2.name', 't2.name as name2', { a: 'c' }, { 't3.a': 'd' })}
...
`;
TABLE, TB
TABLE == TB;
await QUERY `
SELECT
...
FROM ${TABLE('t1')}, ${TABLE('tt as t2')}
`;
Associations
Common use
ASSOCIATE uses Connection pool.
const { ASSOCIATE } = POOL;
const posts = await ASSOCIATE `
posts
- user
< comments
- user
`;
posts[0].body;
posts[0]._.user.name
posts[0]._.comments[0].body
posts[0]._.comments[0]._.user.name
-
of - user
refers to "Belongs to", <
of < user
refers to "Has many".
Polymorphic
await ASSOCIATE `
posts
- user
p - photo
p < photos
< comments
p < photos
`;
p -
refers to Polymorphic + Has one, p <
refers to Polymorphic + Has many.
Many to many
const books = await ASSOCIATE `
books
x authors
`;
books[0]._.authors[0].name;
const authors = await ASSOCIATE `
authors
x books ${{ xtable: 'books_authors' }}
`;
authors[0]._.books[0].title;
Option
ASSOCIATE `
posts
- user
< comments
- user
p < likes
- user
p < likes
- user
x tags
`;
ASSOCIATE `
posts ${SQL `WHERE is_hidden = false ORDER BY id DESC LIMIT ${10}`}
- user
< comments ${{
column: COLUMN('body', 'updated_at')
}}
- user
p < likes
- user
p < likes
- user
x tags
`;
const posts = await ASSOCIATE `
posts ${{
table: 'articles'
}}
- user ${{
left_key: 'writer_id',
key: 'member_id',
table: 'members'
}}
< comments ${{
key: 'article_id'
}}
- user ${{
left_key: 'writer_id',
key: 'member_id',
table: 'members'
}}
p < likes ${{
poly_type: { parent_name: 'comments' },
key: 'parent_id'
}}
p < likes ${{
poly_type: { parent_name: 'articles' },
key: 'parent_id'
}}
x tags ${{
left_key: 'id',
left_xkey: 'article_id',
xtable: 'tags_articles',
xkey: 'tag_name',
key: 'name'
}}
`;
If you use VIEW in databases, it's much easier. Then, you don't need to insert all correct column and table names.
ROW_NUMBER + PARTITION (PostgreSQL)
You can set the row_number
option to fetch only up to four comments each post. Internally use ROW_NUMBER
and PARTITION
.
ASSOCIATE `
posts ${SQL `WHERE is_hidden = false ORDER BY id DESC LIMIT ${10}`}
< comments ${{
row_number: [4, SQL `id DESC`]
}}
`
Hook
You can add virtual columns, sorting, filtering and etc by using Hook.
When all the datas are gathered below “posts”, Hook is executed.
const users = await ASSOCIATE `
users ${{hook: users => users.map(u =>
Object.assign({}, u, { _popular: !!u._.posts.find(p => p._is_best) })
)}}
< posts ${{hook: posts => posts.map(
p => Object.assign({}, p, { _is_best: p._.comments.length > 1 }))}}
- user
< comments
- user
`;
users[0]._popular;
users[0]._.posts[0]._is_best;
users[0]._.posts[1]._is_best;
ASSOCIATE_MODULE
ASSOCIATE
allows you to modularize options for reuse. ASSOCIATE_MODULE
in the function to be passed to ASSOCIATE
.
Post.rights = () => ASSOCIATE_MODULE `
- user
< comments ${{
row_number: [4, SQL `id DESC`]
}}
- user
p < likes
- user
p < likes
- user
x tags
`;
ASSOCIATE `
posts ${SQL `WHERE is_hidden = false ORDER BY id DESC LIMIT ${10}`}
${Post.rights}
`;
Use currying to pass arguments.
Post.rights = (limit = 4) => () => ASSOCIATE_MODULE `
- user
< comments ${{
row_number: [limit, SQL `id DESC`]
}}
- user
p < likes
- user
p < likes
- user
x tags
`;
ASSOCIATE `
posts ${SQL `WHERE is_hidden = false ORDER BY id DESC LIMIT ${10}`}
${Post.rights(6)}
`;
Transaction
const { PostgreSQL } = require('mql2');
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();
DEBUG
MQL_DEBUG.LOG = true;
QUERY `SELECT ${"hi~"} as ho`;