dbqb
v1.0.50
Published
mysql query builder
Downloads
124
Maintainers
Readme
Examples
const dbqb = new DBQB({
/*
* Query = 'SHOW TABLES'
* ['table1', 'table2']
*/
getTables: () => string[],
/*
* Query = `SHOW FIELDS FROM ${table}`
* [
* { Field: 'idx', Type: 'int', Null: 'NO', Key: 'PRI', Default: '', Extra: 'auto_increment' },
* { Field: 'nick', Type: 'varchar(32)', Null: 'NO', Key: '', Default: '', Extra: '' },
* ]
*/
getFields: (table: string) => IFieldItem[]
});
// SELECT * FROM `user` WHERE id = 'test';
const selectQuery = await dbqb.selectQuery({
table: 'user',
where: {
id: 'test'
}
});
// SELECT COUNT(1) AS count FROM `user` WHERE adult_yn = 'Y';
const countQuery = await dbqb.countQuery({
table: 'user',
where: {
adult_yn: 'Y'
}
});
// INSERT INTO `user` SET id = 'test', nick = 'test';
const insertQuery = await dbqb.insertQuery({
table: 'user',
data: {
id: 'test',
nick: 'test'
}
});
// INSERT INTO `user` (id, nick) VALUES ('test', 'test'), ('test2', 'test2');
const insertAllQuery = await dbqb.insertAllQuery({
table: 'user',
data: [
{
id: 'test',
nick: 'test',
},
{
id: 'test2',
nick: 'test2',
}
]
});
// UPDATE `user` SET id = 'test' WHERE id = 'test2';
const updateQuery = await dbqb.updateQuery({
table: 'user',
set: {
id: 'test'
},
where: {
id: 'test2'
}
});
// INSERT INTO `user` SET id = 'test', nick = 'test' ON DUPLICATE KEY UPDATE nick = 'test';
const insertUpdateQuery = await dbqb.insertUpdateQuery({
table: 'user',
data: {
id: 'test',
nick: 'test'
},
set: {
nick: 'test'
}
});
// DELETE FROM `user` WHERE id = 'test';
const deleteQuery = await dbqb.deleteQuery({
table: 'user',
where: {
id: 'test'
}
});
WHERE / HAVING
where / whereOr / having / havingOr
// id = 'test' AND id != 'test' .....
const where = {
// id = 'test'
id: 'test',
// id != 'test'
'id !=': 'test',
// date >= '2022-12-03'
// `>=` `>` `<=` `<`
'date >=': '2022-12-03',
// BETWEEN
'date <=>': ['2022-12-01', '2022-12-31'],
// NOT BETWEEN
'date <!=>': ['2022-12-01', '2022-12-31'],
// idx IS NULL
idx: null,
// idx IS NOT NULL
'idx !=': null,
// id IN ('test', 'test2')
id: ['test', 'test2'],
// id NOT IN ('test', 'test2')
'id !=': ['test', 'test2'],
// nick LIKE 'test%'
'nick %': 'test%',
// nick NOT LIKE 'test%'
'nick !%': 'test%',
// ( nick = 'test' OR id = 'test' OR (adult_yn = 'Y' AND name = 'test2'))
[Symbol('OR')]: {
nick: 'test',
id: 'test',
[Symbol('AND')]: {
adult_yn: 'Y',
name: 'test2'
}
},
// `user`.`nick` = `user`.`name`
user: Symbol('user.name')
};
// id = 'test' OR nick = 'test' ...
const whereOr = {};
const query = await dbqb.selectQuery({
table: 'user',
where,
whereOr
});
// SELECT * FROM `user` WHERE id = 'test' AND nick != "test" AND (field1 = "123" OR field2 = "321");
const whereQuery = await dbqb.selectQuery({
table: 'user',
where: {
id: 'test'
},
sWhere: 'AND nick != "test" AND (field1 = "123" OR field2 = "321")'
});
// SELECT * FROM `user` WHERE id = nick AND nick = "test";
const bangQuery = await dbqb.selectQuery({
table: 'user',
where: {
'!id': 'name',
'!nick': '"test"'
}
});
LIMIT
// SELEC * FROM `user` WHERE adult_yn = 'Y' LIMIT 0, 10;
const limitQuery = await dbqb.selectQuery({
table: 'user',
where: {
adult_yn: 'Y'
},
offset: 0,
limit: 10
});
FIELD
// SELECT user.*, id, nick, name AS user_name, SUM(coin) AS coin_sum FROM `user`;
const fieldQuery = await dbqb.selectQuery({
table: 'user',
field: ['*', 'id', 'nick'],
fieldAs: {
name: 'user_name',
'SUM(coin)': 'coin_sum'
}
});
// SELECT COUNT(IF(adult_yn = "Y", 1, NULL) AS adult_count FROM `user`;
const fieldQuery2 = await dbqb.selectQuery({
table: 'user',
fieldAs: {
'!COUNT(IF(adult_yn = "Y", 1, NULL)': 'adult_count'
}
});
// SELECT (SELECT name FROM profile WHERE profile.user_idx = user.idx LIMIT 1) AS profile_name FROM `user`;
const fieldQuery3 = await dbqb.selectQuery({
table: 'user',
fieldAs: {
'!(SELECT name FROM profile WHERE profile.user_idx = user.idx LIMIT 1)': 'profile_name'
},
fieldQueryAs: [
[{
table: 'profile',
field: ['name'],
where: {
user_idx: Symbol('user.idx'),
},
}, 'profile_name2']
]
});
JOIN
LEFT / INNER / RIGHT / FULL OUTER
// SELECT profile.* FROM `profile` LEFT JOIN `user` ON `profile`.user_idx = `user`.idx;
const leftJoinQuery = await dbqb.selectQuery({
table: 'profile',
leftJoin: [
// 1
{
table: 'user',
on: '`profile`.user_idx = `user`.idx'
},
// 2
{
table: 'user',
on: {
idx: Symbol('profile.user_idx')
}
},
// 3
{
table: 'user',
on: 'profile.user_idx'
}
]
});
// SELECT user.id, user.name, profile.name AS profile_name FROM `user` INNER JOIN `profile` ON `profile`.user_idx = `user`.idx;
const innerJoinQuery = await dbqb.selectQuery({
table: 'user',
field: ['id', 'name'],
fieldAs: {
'profile.name': 'profile_name'
},
leftJoin: [
{
table: 'profile',
on: '`profile`.user_idx = `user`.idx'
}
]
});
GROUP BY
// SELECT * FROM `user` GROUP BY id, adult_yn;
const groupByQuery = await dbqb.selectQuery({
table: 'user',
groupBy: ['id', 'adult_yn']
});
SET
// UPDATE `user` SET login_date = '2000-11-01', login_count = login_count + 1 WHERE idx = 1;
// `+=`, `-=`
const setQuery = await dbqb.updateQuery({
table: 'user',
set: {
login_date: '2000-11-01',
'login_count +=': 1
},
where: {
idx: 1
}
});
ORDER BY
// SELECT * FROM `user` WHERE nick LIKE 'test%' ORDER BY login_date DESC, idx ASC;
const orderQuery = await dbqb.selectQuery({
table: 'user',
where: {
'nick %': 'test%'
},
orderBy: [
['login_date', 'DESC'],
['idx', 'DESC'],
]
});
// SELECT * FROM `user` WHERE nick LIKE 'test%' ORDER BY login_date DESC, idx ASC;
const orderQuery2 = await dbqb.selectQuery({
table: 'user',
where: {
'nick %': 'test%'
},
orderBy: {
login_date: 'DESC',
idx: 'ASC'
}
});
INDEX
// SELECT * FROM `user` USE INDEX (id_index) WHERE id = 'test';
const indexQuery = await dbqb.selectQuery({
table: 'user',
where: {
id: 'test'
},
// forceIndex, useIndex, ignoreIndex
useIndex: 'id_index'
});
SUB QUERY
// SELECT COUNT(1) FROM `user` AS `parent` WHERE 1 AND `parent`.`idx` = `board`.`user_idx` AND `parent`.`idx` != `p`.`user_idx` LIMIT 0, 1
const subQuery = await dbqb.selectQuery({
table: 'user',
as: 'parent',
parentTables: [
{table: 'board'},
{table: 'profile', as: 'p'}
],
field: [
'COUNT(1)'
],
where: {
idx: Symbol('board.user_idx'),
'idx !=': Symbol('p.user_idx')
},
limit: 1
});
PARTITION
// SELECT `user`.* FROM `user` PARTITION (p200101, p200102) WHERE 1 AND `user`.`email` = "[email protected]"
const partitionQuery = await dbqb.selectQuery({
table: 'user',
where: {
email: '[email protected]'
},
partition: ['p200101', 'p200102']
});
FOR UPDATE
// SELECT `user`.* FROM `user` WHERE 1 AND `user`.`email` = "[email protected]" FOR UPDATE
const forUpdateQuery = await dbqb.selectQuery({
table: 'user',
where: {
email: '[email protected]'
},
forUpdate: true // true / nowait / skip
});