Skip to content

Elegant node.js query builder for postgresql and mysql.

License

Notifications You must be signed in to change notification settings

91ahmed/nodeEstablishSQL

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

8 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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

// 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()
       })
})

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({
    // 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

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

// 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')

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) => 
{
    // 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()
})

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) => 
{
    // SELECT * FROM table
    sql.all()
       .get((result) => {
           console.log(result) // result

           sql.close() // close connection
       })
})

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) => 
{
    // SELECT * FROM table
    sql.all()
       .get((result) => {
           console.log(result) // result
       })
})

Select specific columns

database.query((sql) => 
{
    // SELECT column1, column2 FROM table
    sql.select(['column1', 'column2'])
       .get((result) => {
            console.log(result) // result
       })
})

Select distinct values

database.query((sql) => 
{
    // SELECT DISTINCT column1, column2 FROM table
    sql.distinct(['column1', 'column2'])
       .get((result) => {
           console.log(result) // result
       })
})

Select with alias name

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

Select with aggregate functions

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

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) => 
{
    // SELECT * FROM table WHERE column > 5
    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) => 
{
    // 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)
       })
})

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) => 
{
    // 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)
       })
})

Is Null and Is Not Null

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

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) => 
{
    // SELECT * FROM table ORDER BY id LIMIT 5
    sql.all()
       .orderBy(['id']) // default DESC
       .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) => 
{
    // 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)
       })
})

Joins

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

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) => 
{
    // 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)
       })
})

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) => 
{
    // INSERT INTO table (id, name) VALUES (20, "ahmed")
    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) => 
{
    // 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()
})

Truncate

This method will truncate the selected table.

Example

database.query((sql) => 
{
    // TRUNCATE TABLE tablename
    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) => 
{
    // 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.

About

Elegant node.js query builder for postgresql and mysql.

Resources

License

Stars

Watchers

Forks