node_establish_sql
Advanced tools
Elegant node.js query builder, provide an interface for mysql and postgresql drivers with appropriate methods to build SQL queries.
npm i node_establish_sql
// require the package
const connection = require('node_establish_sql')
// Create connection
const database = new connection({
es_driver: 'mysql-pool', // specify the database driver
es_table: 'tablename', // specify the database table name
// provide your connection information
connect:
{
host: 'localhost',
user: 'user-name',
password: '12345',
database: 'database-name',
port: 3306
}
})
// build your SQL Query
database.query((sql) =>
{
sql.all()
.where('id')
.between(5, 30)
.orderBy(['name'], 'ASC')
.limit(5)
.get((result) => {
// Get the result
console.log(result)
// Close connection
sql.close()
})
})
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({
// database driver
// [mysql-client, mysql-pool, pg-client, pg-pool]
es_driver: 'mysql-pool',
// database table
es_table: 'tablename',
// Connection information
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
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
// specify the table name within the connection object
const database = new connection({
es_driver: 'mysql-pool', // database driver
es_table: 'your-table-name', // table name
// Connection information
connect:
{
host: 'localhost',
user: 'user-name',
password: '1234',
database: 'database-name',
port: 3306
}
})
// specify the table name through table method
database.table('your-table-name')
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) =>
{
// connection object
sql.connect
/** Some Examples **/
// Write sql query
sql.connect.query('SELECT * FROM users', (err, res) => {
console.log(res)
})
// end connection
sql.connect.destroy()
})
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) =>
{
// SELECT * FROM table
sql.all()
.get((result) => {
console.log(result) // result
sql.close() // close connection
})
})
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 |
database.query((sql) =>
{
// SELECT * FROM table
sql.all()
.get((result) => {
console.log(result) // result
})
})
database.query((sql) =>
{
// SELECT column1, column2 FROM table
sql.select(['column1', 'column2'])
.get((result) => {
console.log(result) // result
})
})
database.query((sql) =>
{
// SELECT DISTINCT column1, column2 FROM table
sql.distinct(['column1', 'column2'])
.get((result) => {
console.log(result) // result
})
})
database.query((sql) =>
{
// SELECT column1 AS col1, column2 AS col2 FROM table
sql.select([
'column1 AS col1',
'column2 AS col2'
])
.get((result) => {
console.log(result) // result
})
})
database.query((sql) =>
{
/**
SELECT
COUNT(id) AS id_count,
MAX(price) AS max_price,
MIN(price) AS min_price,
SUM(price) AS total_price
FROM table
**/
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) // result
})
})
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) =>
{
// SELECT * FROM table WHERE column > 5
sql.all()
.where('column').value('>', 5)
.get((result) => {
console.log(result)
})
})
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) =>
{
// SELECT * FROM table WHERE column = 2 AND column2 = 'value'
sql.all()
.where('column').value('=', 2)
.and('column2').value('=', 'value')
.get((result) => {
console.log(result)
})
// SELECT * FROM table WHERE column = 2 OR column = 5
sql.all()
.where('column').value('=', 2)
.or('column').value('=', 5)
.get((result) => {
console.log(result)
})
// SELECT * FROM table WHERE NOT column = 20
sql.all()
.whereNot('column').value('=', 20)
.get((result) => {
console.log(result)
})
})
| 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) =>
{
// SELECT * FROM table WHERE column LIKE '%pattern%'
sql.all()
.where('column').like('%pattern%')
.get((result) => {
console.log(result)
})
// SELECT * FROM table WHERE column IN (3,0,8)
sql.all()
.where('column').in([3, 0, 8])
.get((result) => {
console.log(result)
})
// SELECT * FROM table WHERE column BETWEEN 5 AND 10
sql.all()
.where('column').between(5, 10)
.get((result) => {
console.log(result)
})
})
Example
database.query((sql) =>
{
// SELECT * FROM table WHERE column IS NULL
sql.all()
.where('column').isNull()
.get((result) => {
console.log(result)
})
// SELECT * FROM table WHERE column IS NOT NULL
sql.all()
.where('column').isNotNull()
.get((result) => {
console.log(result)
})
})
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) =>
{
// SELECT * FROM table ORDER BY id LIMIT 5
sql.all()
.orderBy(['id']) // default DESC
.limit(5)
.get((result) => {
console.log(result)
})
})
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) =>
{
// SELECT COUNT(column) AS c FROM table GROUP BY column HAVING column > 5
sql.select(['COUNT(column) AS c'])
.groupBy(['column'])
.having('column').value('>', 5)
.get((result) => {
console.log(result)
})
})
Example
database.query((sql) =>
{
// SELECT * FROM table1 INNER JOIN table2 ON column1 = column2
sql.all()
.innerJoin('table2').on('column1', 'column2')
.get((result) => {
console.log(result)
})
// SELECT * FROM table1 LEFT JOIN table2 ON column1 = column2
sql.all()
.leftJoin('table2').on('column1', 'column2')
.get((result) => {
console.log(result)
})
// SELECT * FROM table1 RIGHT JOIN table2 ON column1 = column2
sql.all()
.rightJoin('table2').on('column1', 'column2')
.get((result) => {
console.log(result)
})
// SELECT * FROM table1 FULL OUTER JOIN table2 ON column1 = column2
sql.all()
.fullJoin('table2').on('column1', 'column2')
.get((result) => {
console.log(result)
})
// SELECT * FROM table1 CROSS JOIN table2
sql.all()
.crossJoin('table2')
.get((result) => {
console.log(result)
})
})
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) =>
{
// SELECT column1, column2 FROM table1 UNION column1, column2 FROM table2
sql.select(['column1', 'column2'])
.union(['column1', 'column2'], 'table2')
.get((result) => {
console.log(result)
})
// SELECT column1, column2 FROM table1 UNION ALL column1, column2 FROM table2
sql.select(['column1', 'column2'])
.unionAll(['column1', 'column2'], 'table2')
.get((result) => {
console.log(result)
})
})
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) =>
{
// INSERT INTO table (id, name) VALUES (20, "ahmed")
sql.insert({id: 20, name: 'ahmed'})
.save()
})
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) =>
{
// UPDATE table SET column1 = 'value1', column2 = 'value2' WHERE column = 'value'
sql.update({
column1: 'value1',
column2: 'value2'
})
.where('column').value('=', 'value') // condition
.save()
// UPDATE table SET column = 'value' WHERE column = 'value'
sql.update({column: 'value'})
.where('column').value('=', 'value')
.save()
})
This method will truncate the selected table.
Example
database.query((sql) =>
{
// TRUNCATE TABLE tablename
sql.truncate().save()
})
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) =>
{
// DELETE FROM table WHERE column = 'value'
sql.delete()
.where('column').value('=', 'value')
.save()
// DELETE FROM table WHERE column IN (9,7,8)
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.
FAQs
Elegant node.js query builder.
We found that node_establish_sql 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
GitHub removed 27 malicious pull requests attempting to inject harmful code across multiple open source repositories, in another round of low-effort attacks.
Security News
RubyGems.org has added a new "maintainer" role that allows for publishing new versions of gems. This new permission type is aimed at improving security for gem owners and the service overall.
Security News
Node.js will be enforcing stricter semver-major PR policies a month before major releases to enhance stability and ensure reliable release candidates.