mql2
Advanced tools
Readme
npm i mql2
const { PostgreSQL } = require('mql2');
const { CONNECT } = PostgreSQL;
const POOL = await CONNECT({
host: 'localhost',
user: 'username',
password: '1234',
database: 'dbname'
});
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.
const { MySQL } = require('mql2');
const { CONNECT } = MySQL;
const POOL = await CONNECT({
host: 'localhost',
user: 'username',
password: '1234',
database: 'dbname'
});
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.
const { 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 = await CONNECT();
const = {
VALUES, IN, NOT_IN, EQ, SET, COLUMN, CL, TABLE, TB, SQL, MQL_DEBUG,
QUERY,
ASSOCIATE,
LJOIN,
TRANSACTION
} = POOL;
const users = await QUERY `SELECT * FROM users WHERE ${EQ({
email: 'dev@marpple.com',
password: '1234'
})}`;
// [{ id: 15, email: 'dev@marpple.com', ... }]
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])} LIMIT 3 ORDER BY ID`;
// [{ 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 = 10
COLUMN == 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
< 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".
/*
* photos
* - attached_type
* - attached_id
* */
await ASSOCIATE `
posts
- user
p - photo
p < photos
< comments
p < photos
`;
// SELECT * FROM photos WHERE attached_id IN (${map($ => $.id, posts)}) AND attached_type = 'photos';
// 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 < refers to Polymorphic + Has many.
/*
* books
* - id
*
* authors
* - id
* - name
*
* books_authors
* - author_id
* - book_id
* */
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].name;
/*
* If the tables are formed like the example below, the ASSOCIATE automatically creates the necessary table and column names for queries. the necessary names for the tables and columns for queries
* users
* - id
* posts
* - id
* - user_id
* comments
* - id
* - post_id
* - user_id
* likes
* - attached_type
* - attached_id
* - user_id
* posts_tags
* - post_id
* - tag_id
* tags
* - id
* */
ASSOCIATE `
posts
- user
< comments
- user
p < likes
- user
p < likes
- user
x tags
`;
/*
* You can select columns or add conditions.
* Even though you don’t select a foreign key or a primary key in the option like the below, they are included in ASSOCIATE.
* */
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
`;
/*
* If the names of the tables and columns does not follow the ASSOCIATE rules, you need to manually insert the correct names of the tables and columns.
* members
* - member_id
* articles
* - id
* - writer_id
* comments
* - id
* - article_id
* - writer_id
* likes
* - parent_name
* - parent_id
* - member_id
* tags_articles
* - article_id
* - tag_name
* tags
* - name
* */
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.
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; // true
users[0]._.posts[0]._is_best; // true
users[0]._.posts[1]._is_best; // false
const { PostgreSQL } = require('mql2');
const { CONNECT } = PostgreSQL;
const POOL = await 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();
MQL_DEBUG.LOG = true;
QUERY `SELECT ${"hi~"} as ho`;
// { text: 'SELECT $1 as ho', values: ['hi'] }
FAQs
query builder
The npm package mql2 receives a total of 101 weekly downloads. As such, mql2 popularity was classified as not popular.
We found that mql2 demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
A hospital in Mobile, Alabama, agreed to a settlement in a landmark ransomware death lawsuit, but is now reportedly reconsidering the agreement and refusing to pay.
Security News
A new report explores how advancements in LLMs are enhancing cyber threats, including polymorphic malware, personalized spearphishing, and the risk of hijacking customer service bots.
Security News
ESLint has approved an RFC that adds support for TypeScript configuration files, which is aimed at improving the developer experience and recognizing changes in the evolving JavaScript ecosystem.