A Promise-based DAO implementation, designed for mysql databases (formerly @apigrate/mysqlutils).
npm install @apigrate/dao- Removed lodash, @fast-csv/format, moment dependencies.
- Removed ExpressJS db-api.js helper library.
- Removed unnecessary type casting for dates.
- Introduce query method for better clarity.
- Deprecated filter method. Use query instead.
- Remove unused log_category Dao option.
* __get__ - selects a single row by id
* __exists__ - similar to __get__, but returns a 1 if found or 0 if not found.
* __all__ - selects all rows in a table (offset and limit are supported for paging)
* __query__ - selects rows that meet criteria
* __count__ - similar to __query__, but returns a count of the rows that match the criteria
__one__ - selects and returns only one* of a list of rows that meet criteria
* __selectWhere__ - same as __query__, but an explicit where clause is used as input.
* __select__ - supports a fully parameterized SQL select statement
* __delete__ - delete a single row by its id
* __deleteOne__ - same as delete, but supports multi-column primary keys
* __deleteMatching__ - deletes anything that matches the provided criteria
* __deleteWhere__ - deletes anything that matches the provided WHERE clause
__Important Prerequsite__: your app should configure a mysql connection pool that it can pass to this library. This library is not opinionated about connection management. It does not close or otherwise manage pool connections directly.
``javascript
//var pool = (assumed to be provided by your app)
const {Dao} = require('@apigrate/dao');
//An optional configuration object containing some options that you might want to use on a table.
var opts = {
created_timestamp_column: 'created',
updated_timestamp_column: 'updated',
version_number_column: 'version'
};
var Customer = new Dao('t_customer', 'customer', opts, pool);
//Note, in addition to tables, you use this on views as well...
`
when not found.
`javascript
//Get a customer by id = 27
let result = await Customer.get(27);
//result --> {id: 27, name: 'John Smith', city: 'Chicago', active: true ... }
`$3
#### Count
Simplest form of query. Retrieves a count rows from DB matching the query object.
`javascript
//Search for customers where status='active' and city='Chicago'
let result = await Customer.count({status: 'active', city: 'Chicago'})
//result --> 2
`#### Query
Simple matches-all query. Retrieves all rows from DB matching the query object as an array. Returns an empty array when not found.
`javascript
//Search for customers where status='active' and city='Chicago'
let result = await Customer.query({status: 'active', city: 'Chicago'})
//result --> [ {id: 27, name: 'John Smith', city: 'Chicago' active: true ... }, {id: 28, name: 'Sally Woo', city: 'Chicago', active: true ... }, ...]
`#### One
Identical to query, except only the first entity from results is returned as an object. Returns
null when not found.`javascript
//Search for customers where status='active' and city='Chicago'
let result = await Customer.one({status: 'active', city: 'Chicago'})
//result --> {id: 27, name: 'John Smith', city: 'Chicago' active: true ... }
`$3
Select multiple entities matching a where clause and parameters.
`javascript
//Retrieve active customers in Chicago, Indianpolis.
let result = await Customer.selectWhere("active=? AND (city=? or city=?)" [true, "Chicago", "Indianapolis"]);
//result --> [ {id: 27, name: 'John Smith', city: 'Chicago' active: true ... }, {id: 28, name: 'Sally Woo', city: 'Chicago', active: true ... }, {id: 28, name: 'Jake Plumber', city: 'Indianapolis', active: true ... }, ...]
`Create
Creates a new entity.
`javascript
//Create a new customer
let customerToSave = { name: 'Acme, Inc.', city: 'Chicago', active: true};
let result = await Customer.create(customerToSave);
//result --> {id: 27, name: 'Acme, Inc.', city: 'Chicago', active: true}; (assuming id is auto-generated)
`Update
Updates an entity by primary key (which must be included on the payload).
`javascript
//Update an existing customer by id.
let customerToSave = {id: 27, name: 'Acme, Inc.', city: 'Chicago', active: true};
customerToSave.active = false;
let result = await Customer.update(customerToSave);
//result --> {id: 27, name: 'Acme, Inc.', city: 'Chicago', active: false, _affectedRows: 1};
`Delete
$3
Deletes an entity by primary key.
`javascript
//Delete customer id = 27
let result = await Customer.delete(27);
//result --> {_affectedRows: 1, ...}
`$3
Deletes multiple entities matching the query object.
`javascript
//Delete inactive customers in Chicago
let result = await Customer.deleteMatching({active: false, city: "Chicago"});
//result --> {_affectedRows: 3, active: false, city: "Chicago"}
`$3
Deletes multiple entities matching a where clause and parameters.
`javascript
//Delete inactive customers in Chicago, Indianpolis.
let result = await Customer.deleteWhere("active=? AND (city=? or city=?)" [false, "Chicago", "Indianapolis"]);
//result --> {_affectedRows: 4}
`Generic SQL Support
Use the
sqlCommand method to issue any kind of parameterized SQL command (SELECT, INSERT, UPDATE, DELETE, etc.). The result
returned is simply the result returned from the underlying mysql library callback function.`javascript
//Custom query example
let result = await Customer.sqlCommand("SELECT id, name from my_customer_view where active=? AND (city=? or city=?)" [false, "Chicago", "Indianapolis"]);
//result --> [{id: 27, name: "Acme, Inc."}, {id: 33, name: "American Finance Corporation"}, {id: 35, name: "Integrity Engineering"}]
`
$3
The debug library is used. Use process.env.NODE_ENV='gr8:db' for general debugging. For verbose logging (outputs raw responses on create, update, delete operations) use gr8:db:verbose.Note: as of version 3.x logger injection is no longer supported and will be ignored.
#### What gets logged?
1. error messages (database exceptions) are logged to
console.error
4. at DEBUG='gr8:db', the following is logged:
* method call announcement
* SQL used for query/execution
* a count of the results (if any).
5. at DEBUG='gr8:db:verbose', the following is logged:
* raw SQL command output from the underlying mysql library create, update, and delete statements.
* parameters used in queries
#### Log Suppression
There are two ways to suppress log output:
1. Use the DB_SUPPRESS_LOGGING_TABLES environment to provide a comma-separated list of tables/views for which SQL/parameter logging output will be suppressed entirely.
1. You can suppress logging on individual statements by using the option {suppressLogging: true} on the sqlCommand opts` parameter