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.
localhost.
port | number | The port number which the database server listens to.
user | string | The user name to which the database is associated.
password | string | A password to authenticate the user.
database | string | The name of the database which the user wants to operate on.
[keepAlive] | number | The number of milliseconds to keep the database active without a timeout. If not given, its value will be set to 10000.
database property is not specified in the params argument.
fields | array | The fields (column names) to be included in the table.
dataTypes | array | The data type of the table fields. Their order should correspond to the order of the fields property defined above. In MongoDB, this property is optional.
[constraints] | array | The constraints for the table fields. Their order should correspond to the order of the fields property defined above. If a field doesn't have a constraint, put its corresponding value to be null.
[defaults] * | array | Default values for the table fields. Their order should correspond to the order of the fields property defined above. If a field doesn't have a default value, put its corresponding value to be null.
[primaryKey] * | array | An array of primary key field names.
[foreignKey] * | array | An array of foreign key field names.
[unique] * | array | An array of field names which should have a unique value.
[inherits] * | array | An array table names from which the table will derive its properties.
[unlogged] * | boolean | Set this property to true to specify that the table is created as an unlogged table where data written to unlogged tables is not written to the write-ahead log but database operations will be faster.
[temporary] * | boolean | Set this value to true to specify that the table which will be created is temporary and will be deleted at the end of the current session or the current transaction based on the onCommit property below.
[onCommit] * | "PRESERVE ROWS", "DELETE ROWS", "DROP" | Specify this property to control the behavior of temporary tables at the end of a transaction block.
[tablespace] * | string | The table space to which the table belongs to.
``* - feature not supported in MongoDB``
row to insert a row and column to insert a column.
fields | array | The names of the fields (column names) for which the data will be inserted.
values | array | The values to be inserted. The order of the elements should correspond to the order of the fields property described above.
[multirow] | boolean | Set the value to true to enable multiple row insertion with a single query statement. If the value is set to true, the values property should be a 2D array where each internal array represents an array of values for a single row.
[sourceTable] | string | Use this property if the data to be inserted is fetched from another table. This property should be the name of the source table.
[sourceFields] | array | The fields in the sourceTable from which the data will be fetched. If the value is not specified, the array given as fields property will be used instead. If a selector has to be used during fetching, the selector applies only to the first element of this property.
[where] | string | A selector which is used to filter all rows in the sourceTable where the values of the first element of sourceFields pass a comparison given by this property.
[like] | string | A selector which is used to filter all rows in the sourceTable where the values of the first element of sourceFields fulfil a certain regular expression specified by this property.
[between] | array | An array of two elements ([minval, maxval]) which is used to select all rows from sourceTable where the values of the first element of sourceFields are between minval and maxval.
[in] | array | An array of pre-defined values which is used to select all rows from sourceTable where the value of the first element of sourceFields is among those pre-defined values.
row to insert a row and column to insert a column.
fields | array | The names of the fields (column names) for which the data will be inserted.
dataTypes * | array | An array of data type(s) of the the field(s) (columns) to be inserted. The order of the elements should correspond to the order of the fields property described above.
[defaults] | array | An array of default values of the the field(s) (columns) to be inserted. The order of the elements should correspond to the order of the fields property described above.
[constraints] * | array | An array of string constraints of the the field(s) (columns) to be inserted. The order of the elements should correspond to the order of the fields property described above. If a field doesn't have a constraint, put its corresponding value in this property to be null.
``* - feature not supported in MongoDB``
true to fetch only records which have distinct values.
[count] | string | A field name to determine the number of records in that particular field (column).
[avg] | string | A field name to determine the average of the values in that particular field (column).
[max] | string | A field name to determine the maximum value in that particular field (column).
[min] | string | A field name to determine the minimum value in that particular field (column).
[sum] | string | A field name to determine the sum of the values in that particular field (column).
[where] | string | A selector statement to filter the data to be fetched.
[like] | string | A selector which is used to filter all rows where the values of the first element of fields fulfil a certain regular expression specified by this property. For MongoDB, avoid inclding regex symbols in the string.
[between] | array | An array of two elements ([minval, maxval]) which is used to select all rows where the values of the first element of fields are between minval and maxval.
[in] | array | An array of pre-defined values which is used to select all rows where the values of the first element of fields are among those pre-defined values.
[limit] | number | The maximum number of records to fetch.
[offset] | string | The number or rows to skip during fetching the results.
[innerJoin] * | string | A table name to make inner join with the table given by table property.
[leftJoin] | string | A table name to make left join with the table given by table property.
[fullOuterJoin] | string | A table name to make full outer join with the table given by table property.
[crossJoin] * | string | A table name to make cross join with the table given by table property.
[naturalJoin] * | string | A table name to make natural join with the table given by table property.
[on] | string | A criteria which specifies on what condition that two tables are joined.
[orderBy] | string | A field name to be used to order the records fetched from the database.
[order] | "ASC", "DESC" | A propery which describes how the data is ordered while using the orderBy property. Use ASC for an ascending order and DESC for a descending order.
[groupBy] | string | A property which specifies based on which field that the results of aggregate operations will be categorized.
[union] * | string | A table name to fetch a data from in combination with the records from the table given bytable property. Note that all the data in the table given by this property will be used for the union.
[except] * | string | A table name with records which must be excluded while fetching records from the table given by table property. Note that all the data in the table given by this property will be used for the exclusion.
[intersect] * | string | A table name with records in which only records contained in this table will be fetched from the table given by table property. Note that all the data in the table given by this property will be used for the intersection.
[having] | string | A criteria which filters results grouped by the groupBy property.
``* - feature not supported in MongoDB``
fields to update the table with. Their order should correspond to the order of the field names given in the field property described above.
[from] | string | The name of a source table from which the data which is used to update table is obtained.
[sourceFields] | array | The field names of the source table which correspond with fields for the update. If this property is not specified, the columns given by fields will be used as a substitute. In MongoDB, this field has no effect and the fields in the table and sourceTable should match.
where | string | A condition which specifies which rows should be updated.
true to truncate the table instead of deleting it.
where condition and used to delete selected rows in table.
[where] | string | A selector which is used to delete all rows which pass a comparison stated by this property.
[like] | string | A selector which is used to delete all rows in which the values of the first element of fields fulfil a certain regular expression specified by this property.
[between] | array | An array of two elements ([minval, maxval]) which is used to delete all rows where the values of the first element of fields are between minval and maxval.
[in] | array | An array of pre-defined values which is used to delete all rows where the values of the first element of fields are among those pre-defined values.
``* - feature not supported in MongoDB``
The advantage of the above functions is that they are database independent. You will use the same javascript code inspite of
the database (PostgreSQL, MongoDB, ...) you use. However, they do not cover all possible database querries. However, there is still a back door offered by this module to execute any database query via this function. But, these querries are database specific.
And the developer should take this into consideration while using this function. The document argument is the database specific SQL statement (string) for regular databases or a query document (object) for noSQL databases.
In MongoDB, the document object should be constructed according to database commands stated in
this official documentation. Apart from these specifications,
each document may also include additional $db key with a corresponding value of the name of the
database on which the database operations will be performed. If this property is not included, the database name specified
in the config parameter passed to the connect function will be used. See an example below.
This function closes the database connection.
Example (MySQL or PostgreSQL) ``` db.query("select * from customers", (err, res) => { if (err) { console.log("Error occured."); return; } console.log(res); db.close(); }); ``` #### 🔷 commands:This property contains the list of all commands (or status) of the executed operations. Each element in the array contains an object with two properties - one for SQL and another for its NoSQL equivalent or vice versa.
Example ``` db.commands.forEach((command) => { console.log(command); // logs each command }); ``` A samplecommand 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](https://github.com/Asaye/db-terminal/issues).
---
_Source: https://npm.io/package/db-terminal · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._