⬆️ ⬇️

Jii: Full Query Builder for Node.js with API from Yii 2

Introduction



Hello to all habrovchanam, fans of Yii and Node.js. Why are the PHP framework and server-side JavaScript lovers combined?

Because Yii is now available in JavaScript (for both Node.js and the browser)!



In this article, we will look at the Query Builder, which fully preserves the API from Yii2 and works on Node.js.

Query Designer is only one of the implemented parts of Jii (not to be confused with Yii), in this article I will not specifically consider the framework as a whole, because it can also be used in parts.



Jii

What is Jii?



Jii is a component MVC JavaScript framework that repeats the architectural solutions of the legendary PHP framework Yii 2, in most cases retaining its API. Hence the origin of the name Jii - JavaScript Yii.



Installation



Jii and its parts are distributed as npm manager packages. You can install it with one command:

')

npm install jii jii-model jii-ar-sql 


After installation, you have access to the namespace and Jii class, this is the only entry point to all Jii classes.

The jii package is the basic jii package, which just declares the namespace and the Jii class and returns it. All other parts of Jii (including jii-ar-sql ) are also put as packages, but they only fill the base namespace.



Spoiler
As you can see, in the name of the package there is the letter ar . Yes, this is Active Record with API from Yii2, it is already written and covered with a bunch of tests. I will describe it in the following articles. For now we will consider only its Query Builder.



All the examples that will be described below assume that MySQL, Node.js is installed, and something like this:



 var Jii = require('jii-ar-sql'); var db = new Jii.sql.mysql.Connection({ host: '127.0.0.1', database: 'example', username: 'root', password: '', charset: 'utf8' }); db.open().then(function() { (new Jii.sql.Query()) .from('user') .where({last_name: 'Smith'}) .count('*', db) .then(function(count) { console.log('Records count:', count); db.close(); }); }); 


Database Access Objects



These objects implement an interface through which you can send queries to the database and receive responses in a specific format. They are used by query designers and Active Record.



Each of the data access objects accesses the DBMS through drivers, which are different for each database. All of them implement a single API that allows you to change the DBMS.



At the moment, the MySQL access object is implemented, which uses the driver package from

npm mysql . Support for other DBMS is planned and planned in the future.



Creating a database connection





To access the database, you must create an instance of the Jii.sql.Connection connection. Then you need to open the connection to load the database schema and establish a permanent connection.



 var db = new Jii.sql.mysql.Connection({ host: '127.0.0.1', database: 'example', username: 'root', password: '', charset: 'utf8' }); db.open().then(function() { // ... }); 


If you are creating a Jii application, then it is more convenient to set this connection in the application configuration as a component of the application available via `Jii.app.db`.



 module.exports = { // ... components: { // ... db: { className: 'Jii.sql.mysql.Connection', host: '127.0.0.1', database: 'example', username: 'root', password: '', charset: 'utf8', } }, // ... }; 


Execute SQL queries



When you have an instance of connecting to a database, you can execute a SQL query by following these steps:

  1. Create an instance of Jii.sql.Command with plain SQL;
  2. Add parameters to the request, if necessary;
  3. Call one of the Jii.sql.Command methods.


Consider a few examples of sample data from the database:



 var db = new Jii.sql.mysql.Connection(...); db.open().then(function() { //   ,       , //    -   ,   -     //  .       . db.createCommand('SELECT * FROM post') .queryAll() .then(function(posts) { }); //  ,     (  ) //  `null`    db.createCommand('SELECT * FROM post WHERE id=1') .queryOne() .then(function(post) { }); //  ,     (  ) //       db.createCommand('SELECT title FROM post') .queryColumn() .then(function(titles) { }); //  . `null`    db.createCommand('SELECT COUNT(*) FROM post') .queryScalar() .then(function(count) { }); }); 


Adding Parameters



When creating a command with parameters, you should always add parameters via calls to the `bindValue` or` bindValues` methods to prevent SQL injection attacks. For example:



 db.createCommand('SELECT * FROM post WHERE id=:id AND status=:status') .bindValue(':id', request.id) .bindValue(':status', 1) .queryOne() .then(function(post) { }); 


Execution of queries not on sampling data



Requests to change data must be performed using the `execute ()` method:



 db.createCommand('UPDATE post SET status=1 WHERE id=1') .execute(); 


The Jii.sql.Command.execute () method returns an object containing information with the result of the queries. Each of the access objects can add specific parameters to it, but the minimum set of parameters in it is as follows:



For INSERT, UPDATE and DELETE queries, instead of writing ordinary SQL queries, you can call

Jii.sql.Command.insert () , Jii.sql.Command.update () , Jii.sql.Command.delete () methods for creating

corresponding SQL. These methods will correctly escape the names of tables, columns, and parameter values.



 // INSERT (table name, column values) db.createCommand().insert('user', { name: 'Sam', age: 30 }).execute().then(function(result) { // result.affectedRows // result.insertId }); // UPDATE (table name, column values, condition) db.createCommand().update('user', {status: 1}, 'age > 30').execute(); // DELETE (table name, condition) db.createCommand().delete('user', 'status = 0').execute(); 


You can also call Jii.sql.Command.batchInsert () to insert multiple lines into one query, this will be more

effective in terms of performance:



 // table name, column names, column values db.createCommand().batchInsert('user', ['name', 'age'], { ['Tom', 30], ['Jane', 20], ['Linda', 25], }).execute(); 


Database schema change



Jii DAO provides a set of methods for modifying a database schema:



An example of using these methods:



 // CREATE TABLE db.createCommand().createTable('post', { id: 'pk', title: 'string', text: 'text' }); 


You can also get information about the table through the Jii.sql.Connection.getTableSchema () method .



 table = db.getTableSchema('post'); 


The method returns a Jii.sql.TableSchema object that contains information about the table columns, primary keys, foreign keys, and so on. All this data is used mainly in the query designer and Active Record to simplify working with the database.



Query Builder







Query Designer uses Database Access Objects, which allows you to build SQL queries in JavaScript. Query Designer improves the readability of SQL code and allows you to generate more secure queries to the database.



Using the query designer is divided into 2 stages:

  1. Creating an instance of the Jii.sql.Query class to represent various parts of the SQL statement (for example, `SELECT`,` FROM`).
  2. Calling methods (for example, `all ()`) on the Jii.sql.Query statement to perform a database query and asynchronous data retrieval.


The following code shows the simplest way to use the query designer:



 (new Jii.sql.Query()) .select(['id', 'email']) .from('user') .where({last_name: 'Smith'}) .limit(10) .all() .then(function(rows) { // ... }); 


The above code will generate and execute the following SQL code, in which the `: last_name` parameter is associated with the value` `Smith'`.



 SELECT `id`, `email` FROM `user` WHERE `last_name` = :last_name LIMIT 10 


Making requests



To build a query, you need to call various methods of the Jii.sql.Query object, thereby filling various parts of the SQL command. Method names are similar to SQL statement names. For example, to specify `FROM`, you must call the` from () `method. All methods return the request object itself, which allows you to combine several calls together.



Next, we describe the use of each query design method.



Jii.sql.Query.select ()



The Jii.sql.Query.select () method defines the `SELECT` part of an SQL query. You can specify the columns that

will be selected.



 query.select(['id', 'email']); // : query.select('id, email'); 


Column names can include table names and / or column aliases.

For example,



 query.select(['user.id AS user_id', 'email']); // : query.select('user.id AS user_id, email'); 


You can pass an object where the keys are column aliases.

For example, the above code can be rewritten as follows:



 query.select({user_id: 'user.id', email: 'email'}); 


By default (even if you do not call the Jii.sql.Query.select () method), an asterisk `*` will be generated in the query

to select all columns.



In addition to column names, you can also specify SQL expressions. For example:



 query.select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']); 


Sub -queries are also supported, for this you need to pass the Jii.sql.Query object as one of the elements for selection.



 var subQuery = (new Jii.sql.Query()).select('COUNT(*)').from('user'); // SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post` var query = (new Jii.sql.Query()).select({id: 'id', count: subQuery}).from('post'); 


To add the word `` DISTINCT'` to the SQL query, you need to call the Jii.sql.Query.distinct () method:



 // SELECT DISTINCT `user_id` ... query.select('user_id').distinct(); 


You can also call the Jii.sql.Query.addSelect () method to add additional columns.



 query.select(['id', 'username']) .addSelect(['email']); 


Jii.sql.Query.from ()



The Jii.sql.Query.from () method fills the `FROM` fragment from the SQL query. For example:



 // SELECT * FROM `user` query.from('user'); 


Table names may contain prefixes and / or aliases of tables. For example:



 query.from(['public.user u', 'public.post p']); // : query.from('public.user u, public.post p'); 


When transferring an object, the object keys will be aliased tables.



 query.from({u: 'public.user', p: 'public.post'}); 


In addition, table names can contain subqueries - Jii.sql.Query objects.



 var subQuery = (new Jii.sql.Query()).select('id').from('user').where('status=1'); // SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u query.from({u: subQuery}); 


Jii.sql.Query.where ()



The Jii.sql.Query.where () method fills the `WHERE` section in the SQL statement. You can use several formats for specifying SQL expression conditions:



String format



The string format is very well suited for specifying simple conditions. The specified string is directly written to the SQL expression.



 query.where('status=1'); //     query.where('status=:status', {':status': status}); 


You can add parameters to the query through the Jii.sql.Query.params () or Jii.sql.Query.addParams () methods.



 query.where('status=:status') .addParams({':status': status}); 


Condition as an object (hash)



The object is best used to specify several combined (`AND`) sub-conditions, each of which has

simple equality. The object keys are the columns, and the values ​​are the corresponding values ​​passed to the condition.

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] }); 


The query designer is smart enough to properly handle `NULL` and arrays as values.



You can also use subqueries with a hash format:



 var userQuery = (new Jii.sql.Query()).select('id').from('user'); // ...WHERE `id` IN (SELECT `id` FROM `user`) query.where({id: userQuery}); 


Format with operator



This format allows you to set arbitrary conditions in software. The general format is:



 [operator, operand1, operand2, ...] 


where the operands can be a friend specified in the format of a string, an object, or with an operator. Operator can be one

of the following:



Adding conditions



You can use the Jii.sql.Query.andWhere () or Jii.sql.Query.orWhere () methods to add conditions to

existing request. You can call these methods several times, for example:



 var status = 10; var search = 'jii'; query.where({status: status}); if (search) { query.andWhere(['like', 'title', search]); } 


If `search` is not empty, then the above code will generate the following SQL query:



 ... WHERE (`status` = 10) AND (`title` LIKE '%jii%') 


Condition filtering



When building `WHERE` conditions based on user data, it is usually necessary to ignore empty

values. For example, in a search form that allows you to search by name and email, you need

ignore the field if the user has not entered anything into it. This can be done using the method

Jii.sql.Query.filterWhere () :



 //   username  email    query.filterWhere({ username: username, email: email, }); 


The differences between Jii.sql.Query.filterWhere () and Jii.sql.Query.where () is the first one to ignore

null values

A value is considered empty if it is `null`,` false`, an empty array, an empty string, or a string consisting only of spaces.


Like the Jii.sql.Query.andWhere () and Jii.sql.Query.orWhere () methods, you can use

Jii.sql.Query.andFilterWhere () and Jii.sql.Query.orFilterWhere () for adding additional conditions.



Jii.sql.Query.orderBy ()



The Jii.sql.Query.orderBy () method adds the `ORDER BY` part to the SQL query. For example:



 // ... ORDER BY `id` ASC, `name` DESC query.orderBy({ id: 'asc', name: 'desc', }); 


In the above code, the object keys are the column names, and the values ​​are the corresponding sort direction.



To add sorting conditions, use the Jii.sql.Query.addOrderBy () method.

For example:



 query.orderBy('id ASC') .addOrderBy('name DESC'); 


Jii.sql.Query.groupBy ()



The Jii.sql.Query.orderBy () method adds the `GROUP BY` part to the SQL query. For example,



 // ... GROUP BY `id`, `status` query.groupBy(['id', 'status']); 


If `GROUP BY` only includes simple column names, you can specify it using a string, just like in normal SQL. For example:



 query.groupBy('id, status'); 


You can use the Jii.sql.Query.addGroupBy () method to add additional columns to the `GROUP BY` part.

For example:



 query.groupBy(['id', 'status']) .addGroupBy('age'); 


Jii.sql.Query.having ()



The Jii.sql.Query.having () method defines the `HAVING` part of the SQL expression. This method works the same as the Jii.sql.Query.where () method. For example,



 // ... HAVING `status` = 1 query.having({status: 1}); 


Add additional conditions using the Jii.sql.Query.andHaving () or Jii.sql.Query.orHaving () methods.

For example:



 // ... HAVING (`status` = 1) AND (`age` > 30) query.having({status: 1}) .andHaving(['>', 'age', 30]); 


Jii.sql.Query.limit () and Jii.sql.Query.offset ()



The Jii.sql.Query.limit () and Jii.sql.Query.offset () methods populate the `LIMIT` and` OFFSET` SQL statements. For example:



 // ... LIMIT 10 OFFSET 20 query.limit(10).offset(20); 


If you pass incorrect values ​​of `limit` and` offset`, they will be ignored.



Jii.sql.Query.join ()



The Jii.sql.Query.join () method fills the `JOIN` part of the SQL expression. For example:



 // ... LEFT JOIN `post` ON `post`.`user_id` = `user`.`id` query.join('LEFT JOIN', 'post', 'post.user_id = user.id'); 


The method has 4 parameters:



You can use the following methods to specify `INNER JOIN`,` LEFT JOIN` and `RIGHT JOIN` respectively.



For example,



 query.leftJoin('post', 'post.user_id = user.id'); 


To join multiple columns, you need to call the `join` methods several times.



In addition, you can attach sub-requests. In this case, you need to pass an object, where the key will be an alias of the join request. For example:



 var subQuery = (new Jii.sql.Query()).from('post'); query.leftJoin({u: subQuery}, 'u.id = author_id'); 


Jii.sql.Query.union ()



The Jii.sql.Query.union () method fills in the `UNION` part of the SQL query. For example,



 var query1 = (new Jii.sql.Query()) .select('id, category_id AS type, name') .from('post') .limit(10); var query2 = (new Jii.sql.Query()) .select('id, type, name') .from('user') .limit(10); query1.union(query2); 


You can call this method several times to add several `UNION` fragments.



Query methods



The Jii.sql.Query class provides a whole range of methods for various query results:



All of these methods return an instance of `Promise` for handling an asynchronous response.



For example:



 // SELECT `id`, `email` FROM `user` (new Jii.sql.Query()) .select(['id', 'email']) .from('user') .all().then(function(rows) { // ... }); // SELECT * FROM `user` WHERE `username` LIKE `%test%` (new Jii.sql.Query()) .from('user') .where(['like', 'username', 'test']) .one().then(function(row) { // ... }); 


All of these methods accept the optional `db` parameter representing Jii.sql.Connection . If this parameter is not

set, the application component `db` will be used to connect to the database. Below is another example of using the `count ()` method:



 // executes SQL: SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name (new Jii.sql.Query()) .from('user') .where({last_name: 'Smith'}) .count() .then(function(count) { // ... }) 


Indexes in the query results



When you call Jii.sql.Query.all () , it returns an array of strings, which are indexed by consecutive integers. But you can index them differently, for example, specific values ​​of a column or expression using the Jii.sql.Query.indexBy () method called before the Jii.sql.Query.all () method. In this case, the object will be returned.

For example:



 // returns {100: {id: 100, username: '...', ...}, 101: {...}, 103: {...}, ...} var query = (new Jii.sql.Query()) .from('user') .limit(10) .indexBy('id') .all(); 


To specify complex indexes, you can pass an anonymous function to the Jii.sql.Query.indexBy () method:



 var query = (new Jii.sql.Query()) .from('user') .indexBy(function (row) { return row.id + row.username; }).all(); 


The anonymous function takes the `row` parameter which contains the data of the current row and should return a row or a number that will be used as an index value (object key) for the current row.



At the end







Jii is an open source project, so I will be very happy if someone joins the development of Jii. affka@affka.ru.

Jii Active Record.



Source: https://habr.com/ru/post/260295/



All Articles