Security News
tea.xyz Spam Plagues npm and RubyGems Package Registries
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
sequel-builder
Advanced tools
Ported query library from php yii2 to nodejs. currently only supporting mysql.
Readme
#Query Builder
This is an attempt to port Query builder from Yii2. MySQL is currently all that is supported at this time.
Query builder allows you to construct a SQL query in a programmatic and DBMS-agnostic way. Compared to writing raw SQL statements, using query builder will help you write more readable SQL-related code and generate more secure SQL statements.
Using query builder usually involves two steps:
Build a Query object to represent different parts (e.g. SELECT, FROM) of a SELECT SQL statement. Execute a query method (e.g. all()) of Query to retrieve data from the database. The following code shows a typical way of using query builder:
new Query()
.select(['id', 'email'])
.from('user')
.where({'last_name': 'Smith'})
.limit(10)
.all()
.then((rows) => {
});
The above code generates and executes the following SQL query, where the :last_name parameter is bound with the string 'Smith'.
SELECT `id`, `email`
FROM `user`
WHERE `last_name` = :last_name
LIMIT 10
Info: You usually mainly work with Query instead of QueryBuilder. The latter is invoked by the former implicitly when you call one of the query methods. QueryBuilder is the class responsible for generating DBMS-dependent SQL statements (e.g. quoting table/column names differently) from DBMS-independent Query objects.
To open a connection.
Query.connect({
"schemaType": "mysql",
"host": "xxxx",
"user": "xxxx",
"password": "xxxx",
"database": "xxxx"
});
To close the connection.
Query.end();
To build a Query object, you call different query building methods to specify different parts of a SQL query. The names of these methods resemble the SQL keywords used in the corresponding parts of the SQL statement. For example, to specify the FROM part of a SQL query, you would call the from() method. All the query building methods return the query object itself, which allows you to chain multiple calls together.
In the following, we will describe the usage of each query building method.
The select() method specifies the SELECT fragment of a SQL statement. You can specify columns to be selected in either an array or a string, like the following. The column names being selected will be automatically quoted when the SQL statement is being generated from a query object.
query.select(['id', 'email']);
// equivalent to:
query.select('id, email');
The column names being selected may include table prefixes and/or column aliases, like you do when writing raw SQL queries. For example,
query.select(['user.id AS user_id', 'email']);
// equivalent to:
query.select('user.id AS user_id, email');
To use aliases with normal selections you will need to convert array to object and use numeral indexes to specify that it will use the same name; otherwise the key becomes the alias.
query.select({'user_id': 'user.id', 0: 'email'});
If you do not call the select() method when building a query, * will be selected, which means selecting all columns.
Besides column names, you can also select DB expressions. You must use the array format when selecting a DB expression that contains commas to avoid incorrect automatic name quoting. For example,
query.select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);
As with all places where raw SQL is involved, you may use the DBMS agnostic quoting syntax for table and column names when writing DB expressions in select. You you may also select sub-queries. You should specify each sub-query in terms of a Query object. For example,
subQuery = new Query().select('COUNT(*)').from('user');
// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`
query = new Query().select({0: 'id', count: subQuery}).from('post');
To select distinct rows, you may call distinct(), like the following:
// SELECT DISTINCT `user_id` ...
query.select('user_id').distinct();
You can call addSelect() to select additional columns. For example,
query.select(['id', 'username'])
.addSelect(['email']);
The from() method specifies the FROM fragment of a SQL statement. For example,
// SELECT * FROM `user`
query.from('user');
You can specify the table(s) being selected from in either a string or an array. The table names may contain schema prefixes and/or table aliases, like you do when writing raw SQL statements. For example,
query.from(['public.user u', 'public.post p']);
// equivalent to:
query.from('public.user u, public.post p');
If you are using the array format, you can also use the array keys to specify the table aliases, like the following:
query.from({'u': 'public.user', 'p': 'public.post'});
Besides table names, you can also select from sub-queries by specifying them in terms of Query objects. For example,
subQuery = new Query().select('id').from('user').where('status=1');
// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u
query.from({'u': subQuery});
The where() method specifies the WHERE fragment of a SQL query. You can use one of the three formats to specify a WHERE condition:
String format is best used to specify very simple conditions or if you need to use builtin functions of the DBMS. It works as if you are writing a raw SQL. For example,
query.where('status=1');
// or use parameter binding to bind dynamic parameter values
query.where('status=:status', {':status': status});
// raw SQL using MySQL YEAR() function on a date field
query.where('YEAR(somedate) = 2015');
Do NOT embed variables directly in the condition like the following, especially if the variable values come from end user inputs, because this will make your application subject to SQL injection attacks.
// Dangerous! Do NOT do this unless you are very certain status must be an integer.
query.where("status=status");
When using parameter binding, you may call params() or addParams() to specify parameters separately.
query.where('status=:status')
.addParams({':status': status});
As with all places where raw SQL is involved, you may use the DBMS agnostic quoting syntax for table and column names when writing conditions in string format.
Hash format is best used to specify multiple AND-concatenated sub-conditions each being a simple equality assertion. It is written as an array whose keys are column names and values the corresponding values that the columns should be. For example,
// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))
query.where({
'status': 10,
'type': null,
'id': [4, 8, 15],
});
As you can see, the query builder is intelligent enough to properly handle values that are nulls or arrays.
You can also use sub-queries with hash format like the following:
userQuery = new Query().select('id').from('user');
// ...WHERE `id` IN (SELECT `id` FROM `user`)
query.where({'id': userQuery});
Using the Hash Format, this Query Builder internally uses parameter binding so in contrast to the string format, here you do not have to add parameters manually.
Operator format allows you to specify arbitrary conditions in a programmatic way. It takes the following format:
[operator, operand1, operand2, ...]
where the operands can each be specified in string format, hash format or operator format recursively, while the operator can be one of the following:
Using the Operator Format, Query Builder internally uses parameter binding so in contrast to the string format, here you do not have to add parameters manually.
You can use andWhere() or orWhere() to append additional conditions to an existing one. You can call them multiple times to append multiple conditions separately. For example,
var status = 10;
var search = 'search';
query.where({'status': status});
if (search.length > 0) {
query.andWhere(['like', 'title', search]);
}
If search is not empty, the following WHERE condition will be generated:
WHERE (`status` = 10) AND (`title` LIKE '%search%')
The orderBy() method specifies the ORDER BY fragment of a SQL query. For example,
// ... ORDER BY `id` ASC, `name` DESC
query.orderBy({
'id': SORT_ASC, // Needs to be imported
'name': SORT_DESC, //Needs to be imported
});
In the above code, the object keys are column names while the object values are the corresponding order-by directions. The constant SORT_ASC specifies ascending sort and SORT_DESC descending sort.
If ORDER BY only involves simple column names, you can specify it using a string, just like you do when writing raw SQL statements. For example,
query.orderBy('id ASC, name DESC');
You can call addOrderBy() to add additional columns to the ORDER BY fragment. For example,
query.orderBy('id ASC')
.addOrderBy('name DESC');
The groupBy() method specifies the GROUP BY fragment of a SQL query. For example,
// ... GROUP BY `id`, `status`
query.groupBy(['id', 'status']);
If GROUP BY only involves simple column names, you can specify it using a string, just like you do when writing raw SQL statements. For example,
query.groupBy('id, status');
You can call addGroupBy() to add additional columns to the GROUP BY fragment. For example,
query.groupBy(['id', 'status'])
.addGroupBy('age');
The having() method specifies the HAVING fragment of a SQL query. It takes a condition which can be specified in the same way as that for where(). For example
// ... HAVING `status` = 1
query.having({'status': 1});
Please refer to the documentation for where() for more details about how to specify a condition.
You can call andHaving() or orHaving() to append additional conditions to the HAVING fragment. For example,
// ... HAVING (`status` = 1) AND (`age` > 30)
query.having({'status': 1})
.andHaving(['>', 'age', 30]);
The limit() and offset() methods specify the LIMIT and OFFSET fragments of a SQL query. For example,
// ... LIMIT 10 OFFSET 20
query.limit(10).offset(20);
If you specify an invalid limit or offset (e.g. a negative value), it will be ignored.
The join() method specifies the JOIN fragment of a SQL query. For example,
// ... LEFT JOIN `post` ON `post`.`user_id` = `user`.`id`
query.join('LEFT JOIN', 'post', 'post.user_id = user.id');
The join() method takes four parameters:
You can use the following shortcut methods to specify INNER JOIN, LEFT JOIN and RIGHT JOIN, respectively.
For example,
query.leftJoin('post', 'post.user_id = user.id');
To join with multiple tables, call the above join methods multiple times, once for each table.
Besides joining with tables, you can also join with sub-queries. To do so, specify the sub-queries to be joined as Query objects. For example,
subQuery = Query().from('post');
query.leftJoin({'u': subQuery], 'u.id = author_id');
In this case, you should put the sub-query in an object and use the object key to specify the alias.
The union() method specifies the UNION fragment of a SQL query. For example,
var query1 = new Query()
.select("id, category_id AS type, name")
.from('post')
.limit(10);
var query2 = Query()
.select('id, type, name')
.from('user')
.limit(10);
query1.union(query2);
You can call union() multiple times to append more UNION fragments.
FAQs
Ported query library from php yii2 to nodejs. currently only supporting mysql.
The npm package sequel-builder receives a total of 1 weekly downloads. As such, sequel-builder popularity was classified as not popular.
We found that sequel-builder 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
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
Security News
As cyber threats become more autonomous, AI-powered defenses are crucial for businesses to stay ahead of attackers who can exploit software vulnerabilities at scale.
Security News
UnitedHealth Group disclosed that the ransomware attack on Change Healthcare compromised protected health information for millions in the U.S., with estimated costs to the company expected to reach $1 billion.