db-terminal v1.0.0

db-terminal

Write the same javascript code to execute CRUD operations on various regular and NoSQL databases. The module currently supports PostgreSQL, MySQL and MongoDB. But, there is an intention to include more databases.

To see a highlight of what can be achieved with this module, let us try to select all students who are named John from students table. For this, we write the following statements.

PostgreSQL and MySQL:	select * from students where name='John';
	
MongoDB:              db.students.find({ name: "John" })

To perform the same activity on any of the databases via this module, we use the following code:

			db.select({
				"table": "students",
				"where": "name='John'"
			});

Installation

Using npm

$ npm install db-terminal --save

Using yarn

$ yarn add db-terminal

Usage

To use this module, follow the procedures below:

1. Import: const terminal = require('db-terminal');

2. Instantiate the desired database:

   	const name = "postgresql"; // or "mysql" for MySQL, "mongodb" for MongoDB database
   	const db = terminal(name);  

3. Call the available functions The following functions can be called on the db instance created above. Calling any of these functions except the connect function is optional.

   • connect (required)
   • create
   • insert
   • select
   • update
   • delete
   • query
   • close (recommended)

   In addition to the above functions, the db instance has also the following property.

   • commands

The description of the above functions and command property is given below. Although collection is the appropriate term which describes a collection of data in NoSQL databases, the term table will be used for both of them. The properties of the arguments for each function are also explained where parameters within square brackets are optional. In addition, all the callback functions are called according to Node.js standard where err is passed as the first argument and response data, if any, is passed as the second argument.

🔷 connect(config :object , callback :function):

This function is used to create a connection with the preferred database.

Example

			const config = {
				"host": "localhost",
				"port": 5432,
				"user": "postgres",
				"password": "abc123",
				"database": "postgres"  
			};

			db.connect(config, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Connection successful.");
			});

🔷 create(params :object , callback :function):

This function is used to create a database or a table.

* - feature not supported in MongoDB

* - feature not supported in MongoDB

Example (database creation)

			const params = {
				"database": "mydb",
				"connlimit": 100,
			};

			db.create(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Database created.");
			});

Example (table creation)

			const params = {
				"table": "customers",
				"fields": ["id", "name", "email", "address"],
				"dataTypes": ["int", "varchar", "varchar", "text"],
					// 'address' field doesn't have constraint
				"constraints": ["NOT NULL", "NOT NULL", "NOT NULL", null],  
				"primaryKey": ["id"]
			};

			db.create(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Table created.");
			});

🔷 insert(params :object , callback :function):

This function is used to insert a row(s) or a column(s) into a table.

* - feature not supported in MongoDB

Example (row insertion)

			const params = {
				"table": "students",
				"item": "row",
				"fields": ["id", "name", "email"],
				"values": [1, "John Doe", "johndoe@provider.com"]
			};

			db.insert(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Row inserted.");
			});

Example (column insertion)

			const params = {
				"table": "customers",
				"item": "column",
				"fields": ["account no."],
				"dataTypes": ["int"],
				"constraints": ["UNIQUE"],  
			};

			db.insert(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Column inserted.");
			});

🔷 select(params :object , callback :function):

This function is used to fetch records from a database.

* - feature not supported in MongoDB

Example

			const params = {
				"table": "items",
				"fields": ["id", "price", "quantity"],
				"where": "price < 100.0",
				"orderBy": "quantity",
				"order": "ASC"
			};

			db.select(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log(res);
			});

🔷 update(params :object , callback :function):

This function is used to update existing records in a database.

Example

			const params = {
				"table": "employees",
				"fields": ["salary"],
				"values": [2000],
				"where": "department = 'frontend'",
			};

			db.update(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log(res);
			});

🔷 delete(params :object , callback :function):

This function is used to delete databases, tables, columns or rows.

* - feature not supported in MongoDB

Example (database deletion)

			db.delete({"database": "items", item: "database"}, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Database deleted");
			});

Example (table truncation)

			const params = {
				"table": "customers",
				"item": "table",
				"truncate": true
			};

			db.delete(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Table truncated");
			});

Example (column deletion)

			const params = {
				"table": "items",
				"item": "column",
				"fields": ["price"]
			};

			db.delete(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Column 'price' deleted");
			});

Example (row deletion)

			const params = {
				"table": "users",
				"item": "row",
				"fields": ["date"],
				"in": ["02/15/2020", "02/16/2020"]
			};

			db.delete(params, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log("Rows deleted");
			});

🔷 query(document :string | | object , callback :function):

Example (PostgreSQL and MySQL)

			const sql = "select id from students except select ineligible_ids from programming";
			db.query(sql, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log(res);
			});

Example (MongoDB)

			const doc = { 
				find: "items", 
				$db: "sales", 
				projection: { _id: 0, price: 1, quantity: 1 }, 
				sort: { price: -1} 
			};

			db.query(doc, (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log(res);
			});

🔷 close():

Example (MySQL or PostgreSQL)

			db.query("select * from customers", (err, res) => {
				if (err) {
					console.log("Error occured.");
					return;
				}
				console.log(res);
				db.close();
			});

🔷 commands:

Example

			db.commands.forEach((command) => {
				console.log(command); // logs each command
			});	

A sample command output for the above code may look like:

			{ sql:
			  	'UPDATE customers SET email = 'abc@def.com' , name = 'John Doe' WHERE id = 213',
			  nosql:
			    { 
			    	update: 'customers',
			      '$db': 'mydb',
			      ordered: false,
			      updates: [{ 
			        q: { id: 213 },  
			        u: { '$set': { email: 'abc@def.com', name: 'John Doe' } },
							upsert: true,
							multi: true 
						}] 
			   	} 
			 }

Issues or suggestions?

If you have any issues or if you want to suggest something, you can write it here.