@apigrate/mysqlutils

mysqlutils

A library that simplifies working with MySQL databases (it does carry a dependency on the mysql) package. It provides promise-based functions making it easy to get objects out of database table rows with intuitive language.

What it does.

Work directly on any table in your mysql database using any of the following functions, summarized as follows:

Single Row Queries

• get - selects a single row by id
• exists - similar to get, but returns a 1 if found or 0 if not found.

Multiple Row Queries

• all - selects all rows in a table (offset and limit are supported for paging)
• find - selects rows that meet criteria
• count - similar to find, 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 find, but an explicit where clause is used as input.
• select - supports a fully parameterized SQL select statement

Insert and Update

• create - inserts a row in a table (returns an autogenerated id if applicable)
• update - updates a row in a table by primary key (supports sparse updates)
• save - "upserts" a row in a table (i.e. performs an update if an primary keys match an existing row, else performs an insert)

Delete

• 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

Generic

• sqlCommand - issues any kind of parameterized SQL command.

How to use it.

Instantiate

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.

//var pool = (assumed to be provided by your app)
const {Dao} = require('@apigrate/mysqlutils');

//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...

Read/Query

Get by id.

Get a single table row by id and return it as an object.

//Get a customer by id=27

Customer.get(27)
.then(function(cust){

  //cust = {id: 27, name: 'John Smith', city: 'Chicago', active: true ... }

})
.catch(function(err){
  console.error(err.message);
});

Find

//Search for customers where status='active' and city='Chicago'

Customer.find({status: 'active', city: 'Chicago'})
.then(function(customers){

  //customers: an array of customer objects like,
  // [ {id: 27, name: 'John Smith', city: 'Chicago' active: true ... }, {id: 28, name: 'Sally Woo', city: 'Chicago', active: true ... }, ...]

})
.catch(function(err){
  console.error(err.message);
});

todo: more examples!

Create

todo: more examples!

Update

todo: more examples!

Delete

todo: more examples!

More

Support for Logging

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
2. at DEBUG='gr8:db', the following is logged:
   • method call announcement
   • SQL used for query/execution
   • a count of the results (if any).
3. at DEBUG='gr8:db:verbose', the following is logged:
   • raw SQL command output from the underlying mysql library create, update, and delete statements.