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
• How to install
• Full Example
• Establish Connection
• Specify the table name
• Get the connection object
• End Connection
• Select Statement
  • Select All Columns
  • Select Specific Columns
  • Select Distinct Values
  • Select With Alias Name
  • Select With Aggregate Functions
• Where Clause
  • And - Or - Not
  • Like - In - Between
  • Is Null - Is Not Null
• Order By - Limit
• Group By - Having
• Joins (inner, left, right, full, cross)
• Union - Union All
• Insert
• Update
• Truncate
• Delete

Features

Supports MySQL and PostgreSQL: The query builder is fully compatible with both MySQL and PostgreSQL databases, offering seamless integration for your projects.

Comprehensive SQL Coverage: Equipped with a variety of methods to handle the majority of SQL statements, simplifying complex operations and reducing boilerplate code.

Chaining Style for Readability: Methods are designed to be chainable, allowing you to write concise and easily readable queries.

Built-in Security Measures: Utilizes placeholders and value filters to prevent SQL injection, ensuring your application remains secure and robust.

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 establish a connection, simply pass an object containing the database details to the class. This object allows you to define the connection driver, specify the target table, and provide the database connection credentials.

The query builder supports both MySQL and PostgreSQL drivers. You can set the desired driver using the "es_driver" property by choosing one of the following options: mysql-client, mysql-pool, pg-client, or 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
    }
})

Learn about the differences between pool and client connection.
https://node-postgres.com/features/pooling

Specify the Table Name

To define the table name, you can include it directly in the connection object using the es_table property, as shown below:

{ es_table: "tablename" }

Alternatively, you can dynamically set the table name using the 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

The connect property is a key feature that allows you to interact seamlessly with the client and pool connections of MySQL and PostgreSQL drivers. It provides access to the database driver's connection object, enabling you to execute SQL queries, manage connections, and perform other database operations efficiently.

Here’s an example demonstrating how to use the connect property:

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

Closing the Database Connection

It is advisable to close the database connection once you have finished executing queries. This helps free up system resources and prevents potential issues such as connection leaks.

To close the connection, use the 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) => 
{
    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: For insert, delete and update you must call the save() method at the end to execute the qurey.