node establish sql
Elegant node.js query builder, provide an interface for mysql and postgresql drivers with appropriate methods to build SQL queries.
Content
Features
- Support MysqL and PostgreSQL.
- Provide diverse methods to cover the majority of SQL statements.
- Invoke methods in chaining style to make them readable and concise.
- Use placeholders and filters for query values to prevent SQL Injection.
Install via npm
npm i node_establish_sql
Full Example
const connection = require('node_establish_sql')
const database = new connection({
es_driver: 'mysql-pool',
es_table: 'tablename',
connect:
{
host: 'localhost',
user: 'user-name',
password: '12345',
database: 'database-name',
port: 3306
}
})
database.query((sql) =>
{
sql.all()
.where('id')
.between(5, 30)
.orderBy(['name'], 'ASC')
.limit(5)
.get((result) => {
console.log(result)
sql.close()
})
})
Establish Connection
To initialize connection just add an object with the database information to the class, you can through this object specify the connection driver, table and the database connection information.
The query builder supports mysql and postgresql database drivers, you can specify the driver through "es_driver" property by selecting one of these connection types ( mysql-client
- mysql-pool
- pg-client
- pg-pool
).
Example
const database = new connection({
es_driver: 'mysql-pool',
es_table: 'tablename',
connect:
{
host: 'localhost',
user: 'user-name',
password: '1234',
database: 'database-name',
port: 3306
}
})
Read about the different between pool and client connection.
https://node-postgres.com/features/pooling
Specify the table name
Like the previous example you can determine the name of the table within the connection object by provide the table name to the "es_table" properity {es_table: "tablename"}
or you can use table()
method.
Example
const database = new connection({
es_driver: 'mysql-pool',
es_table: 'your-table-name',
connect:
{
host: 'localhost',
user: 'user-name',
password: '1234',
database: 'database-name',
port: 3306
}
})
database.table('your-table-name')
Get the Connection Object
Use the connect
property to tap into the features of the client and pool connecitons of mysql and postgresql drivers, This property returns the connection object of the database driver.
Example
database.query((sql) =>
{
sql.connect
sql.connect.query('SELECT * FROM users', (err, res) => {
console.log(res)
})
sql.connect.destroy()
})
End Connection
It is advisable to end the database connection after you are done with the query and to do this you can use close()
method.
Example
database.query((sql) =>
{
sql.all()
.get((result) => {
console.log(result)
sql.close()
})
})
Select
The query builder provide three methods to select table columns.
Method | Describe | Parameters | Output |
---|
all() | This method selects all columns from the table. | no parameters needed | SELECT * |
select() | This method will help you to select specific columns from the table. | (array) the columns you need to select | SELECT columns |
distinct() | This method will return distinct (different) results | (array) the columns you need to select | SELECT DISTINCT columns |
Select all columns
database.query((sql) =>
{
sql.all()
.get((result) => {
console.log(result)
})
})
Select specific columns
database.query((sql) =>
{
sql.select(['column1', 'column2'])
.get((result) => {
console.log(result)
})
})
Select distinct values
database.query((sql) =>
{
sql.distinct(['column1', 'column2'])
.get((result) => {
console.log(result)
})
})
Select with alias name
database.query((sql) =>
{
sql.select([
'column1 AS col1',
'column2 AS col2'
])
.get((result) => {
console.log(result)
})
})
Select with aggregate functions
database.query((sql) =>
{
sql.select([
'COUNT(id) AS id_count',
'MAX(price) AS max_price',
'MIN(price) AS min_price',
'SUM(price) AS total_price'
])
.get((result) => {
console.log(result)
})
})
Where Clause
Adding where clause is very important to filter the columns.
Here are the available methods that will help you to build your Where condition.
Method | Describe | Parameters | Output |
---|
where() | Allow you to filter rows using where clause | (string) the column | WHERE column |
value() | Used to specify the operator and the value after where statement.
= < > <= >= <> != | (string) the operator (mixed) the value | = value |
Example
database.query((sql) =>
{
sql.all()
.where('column').value('>', 5)
.get((result) => {
console.log(result)
})
})
And Or Not
These operators are used to combine with where condition to get accurate results.
Method | Parameters | Output |
---|
and() | (string) column name | AND column |
or() | (string) column name | OR column |
whereNot() | (string) column name | WHERE NOT column |
Example
database.query((sql) =>
{
sql.all()
.where('column').value('=', 2)
.and('column2').value('=', 'value')
.get((result) => {
console.log(result)
})
sql.all()
.where('column').value('=', 2)
.or('column').value('=', 5)
.get((result) => {
console.log(result)
})
sql.all()
.whereNot('column').value('=', 20)
.get((result) => {
console.log(result)
})
})
Like In Between
Method | Parameters | Output |
---|
like() | (string) pattern | LIKE "%%" |
in() | (array) values | IN (1,2,3) |
between() | (mixed) value1 (mixed) value2 | BETWEEN value AND value |
Example
database.query((sql) =>
{
sql.all()
.where('column').like('%pattern%')
.get((result) => {
console.log(result)
})
sql.all()
.where('column').in([3, 0, 8])
.get((result) => {
console.log(result)
})
sql.all()
.where('column').between(5, 10)
.get((result) => {
console.log(result)
})
})
Is Null and Is Not Null
Example
database.query((sql) =>
{
sql.all()
.where('column').isNull()
.get((result) => {
console.log(result)
})
sql.all()
.where('column').isNotNull()
.get((result) => {
console.log(result)
})
})
Orderby and Limit
You can use orderBy()
and limit()
to sort data and retrieve limited records.
Method | Parameters | Output |
---|
orderBy() | (array) columns. (string) sort (DESC, ASC). | ORDER BY columns DESC |
limit() | (integer) records number. | LIMIT value |
Example
database.query((sql) =>
{
sql.all()
.orderBy(['id'])
.limit(5)
.get((result) => {
console.log(result)
})
})
Groupby and Having
Use groupBy()
and having()
to summarize the results and get statistical information.
Method | Parameters | Output |
---|
groupBy() | (array) columns. | GROUP BY columns |
having() | (string) the column. | HAVING column |
Example
database.query((sql) =>
{
sql.select(['COUNT(column) AS c'])
.groupBy(['column'])
.having('column').value('>', 5)
.get((result) => {
console.log(result)
})
})
Joins
Example
database.query((sql) =>
{
sql.all()
.innerJoin('table2').on('column1', 'column2')
.get((result) => {
console.log(result)
})
sql.all()
.leftJoin('table2').on('column1', 'column2')
.get((result) => {
console.log(result)
})
sql.all()
.rightJoin('table2').on('column1', 'column2')
.get((result) => {
console.log(result)
})
sql.all()
.fullJoin('table2').on('column1', 'column2')
.get((result) => {
console.log(result)
})
sql.all()
.crossJoin('table2')
.get((result) => {
console.log(result)
})
})
Union and Union All
Use Union and Union All Operators two combine the result of two tables.
Method | Parameters | Output |
---|
union() | (array) columns. (string) table. | UNION columns FROM table |
unionAll() | (array) columns. (string) table. | UNION ALL columns FROM table |
Example
database.query((sql) =>
{
sql.select(['column1', 'column2'])
.union(['column1', 'column2'], 'table2')
.get((result) => {
console.log(result)
})
sql.select(['column1', 'column2'])
.unionAll(['column1', 'column2'], 'table2')
.get((result) => {
console.log(result)
})
})
Insert
The query builder provide insert()
method to insert records into database table, The insert method accepts an object of column names and values.
Method | Describe | Parameters | Output |
---|
insert() | Generate sql insert statement. | (object) column and value | INSERT INTO table (columns) VALUES (values) |
Example
database.query((sql) =>
{
sql.insert({id: 20, name: 'ahmed'})
.save()
})
Update
To update existing records use update()
method, it accepts an object of column and value pairs indicating the columns to be updated.
Method | Describe | Parameters | Output |
---|
update() | Generate sql update statement. | (object) column and value | UPDATE table SET column = value |
Example
database.query((sql) =>
{
sql.update({
column1: 'value1',
column2: 'value2'
})
.where('column').value('=', 'value')
.save()
sql.update({column: 'value'})
.where('column').value('=', 'value')
.save()
})
Truncate
This method will truncate the selected table.
Example
database.query((sql) =>
{
sql.truncate().save()
})
Delete
You can use delete()
method to delete single or multiple records.
Method | Describe | Parameters | Output |
---|
delete() | Generate sql delete statement. | no parameters needed | DELETE FROM table |
Example
database.query((sql) =>
{
sql.delete()
.where('column').value('=', 'value')
.save()
sql.delete().where('column').in([9,7,8]).save()
})
Note: with insert, delete and update you should call save()
method at the end to execute the qurey.