dbq

dbq = (mysql + (callbacks || promises)) / (brevity × medium naiveté)[+ CRUD].

npm i dbq

Table of Contents

• Example
• Callbacks or Promises
• Series or Parallel
• Return Shortcuts
• Schemize
• Setup & Options
  • Automatic integration with manowar
• Common Methods / CRUD
• Caveats

Example

Four queries, executed in parallel, four results:

db(  "select * from ricks order by rickness desc limit 1"
	,"select * from mortys where dim=? order by mortyness desc limit 1",["c-137"]
	,"select * from gazorpazorpians where father=?",["Morty"]
	,"select * from donors where recipient=? and organ=?",["Shrimply Pibbles","heart"]
,(rickest,mortyest,mortyJr,heartDonors)=>/*fiddle*/)

mysql's ?-substitution syntax to prevent SQL injection is allowed as needed in adjacent arrays (illustrated here):

                                  /*   ┌──⩤───┐                      */
db(  "select * from grandpa where name=?",["rick"]
	,"select * from zones where ?? in (?)",['allowed',['flarping','unflarping']]	
	,"select * from council" /* ┌────⩤─────────────┬──── = ─┐        */
	,"select * from morty where ? and pets=?",[{alignment:"evil"},0]
    ,"select * from cronenberg"       /*   └─⩤────────────────────┘  */	
                             //↑ note no substitution needed here; no [] supplied
).then(fiddle)

Callbacks or Promises

Pass a function as the last input, and that will receive query results as inputs in the order supplied:

db("select * from user where name=?",['morty'] //morty query (1)
,"select name,volume from dims where dim=?",['c-137'] //dimension query (2)
// ↓(1)  ↓(2)
,(morty,dim)=>{/*fiddle*/})

If the last input isn't a function, a promise is returned, so is thenable (and thus awaitable):

db("select * from jerrys where dim=?",["c-137"]
,"select * from ricks where dim=?",["J19ζ7"]
).then(([jerry,doofusRick])=>{
/* a promise resolves to 1 value but es6 destructuring separates them */
})
//if it's thenable, you can catch, too
.catch(errorHandler)
//but it's already going to message when errors happen anyway

Series or Parallel

It can execute queries in series or parallel (assuming you have connection pooling on).

//Parallel looks like this:
db(    //could also have been db.parallel or db.qp or db.q
	 "select * from user"
	,"select * from book"
	,"select * from dinosaur"
).then(([users,books,dinosaurs])=>{/*fiddle*/})

//series would be:
db.series( //or db.qs
	 "update cat set living=false"
	,"update treaty set active=true where title='Spider Peace'"
	,"insert into cat2 select * from cat where living=false"
)

Note series queries share the same connection, allowing connection-dependent features, like temp tables, variables, and transactions.

Below is a run of benchmark.js on 1, 4, and 16 core boxes in series and parallel. Depending on hardware and the types of queries you run, query speed can be increased appreciably. Note no meaningful difference for one core.

Return Shortcuts

Queries are often performed to retrieve single value results, not arrays of objects.

If you end a query with limit 1, it will take that one result out of its result [], returning just the row {}.

If you also supply only one select clause column, the result will be just that value, not a {key:value}.

Schemize

If your credentials have information_schema access, db.schemize() will query it and put a representation of the database's tables and their columns at db.table for easy referencing elsewhere in code.

Setup & Options

var mysql=require("mysql").createPool({
			   host:'x',user:'x',password:'x',database:'x'
			  ,useConnectionPooling:true//allow parallel querying!
			  ,connectionLimit:16
			  ,connectTimeout:15*60*1000
		  })
	,db=require("dbq")(mysql,{//pass in node-mysql pool initialized above, then an options {}
		//option:[default]
		,verbose:true// console.log queries as they happen?
		,log:(query,rows,queryLine,took,db)=>{//maybe you want to customize how queries are logged
			console.log(`query in ${took}s:`,query.sql)
		}
		//if you want to retain cls-hooked namespaces, supply a binder here
		,callbackNamespaceBinder:(cb)=>c//default is nothing meaningful
	})

db.setOnce({}) is a chainable function that allows you to set properties that will be reversed after the next query set:

//say query logging is normally off
db.verbose=false
//but you want to check a specific call:
db.setOnce({verbose:true})("select * from meeseeks").then(lookitMee=>{/* etc */})
//next call will be back to normal
db("select * from friends where name=?",['Bird Person']).then(birdPerson=>/**/)

Automatic integration with manowar

If you are using manowar, and global.cc exists before dbq is set up, dbq can detect and preserve logging contexts for you.

If you are managing cls-hooked namespaces on your own, you can supply your own callbackNamespaceBinder setup option, which is a function that accepts a callback function, binds it, then returns it.

If you do not want to use either of those, or if the above made no sense, you can safely ignore it.

Common Methods / CRUD

(Create, Read, Update, Delete)

If you want, you can pass an object and its single-column primary keyed table name into db.attachCommonMethods(model,name,done) to attach an opinionated:

insert(rows[,done])//rows=[{},{},...] / {col1name:val,col2name...}
upsert(rows[,done])//attempt insert, on conflict update non-primary key cols
update(rows[,done])//find by primary key in rows, update all other cols supplied
delete(rows[,done])//find by primary key in rows, delete
select(key[,done]) /*
	key: If not an {}, the 1-col primary key: user.get(1); user.get('schwify')
	Else, key creates the WHERE clause: {
			col1:val
			[,col2:val]...etc. If val is ever [an,array], uses IN syntax
			[,limit:# if supplied] so...don't be weird & name your column a MySQL keyword
		}
*/
get(key[,done])//alias for select
getBy${FieldName}(key[,done])// per column in the table, assuming schemize() has run to know this.

All of which support:

• proper ?-substitution
• promise/callback responses
• {single}/[many] things supplied at once
• query compaction wherever possible: many things can be edited or inserted into a table in one query

Further usage examples can be found in test.js.

How do I sort, offset, group by, _____?

Anything more complex, consider just writing clear SQL, placing reused queries in descriptively named functions. There's a reason SQL is its own language.

Caveats

• variables and temp tables in parallel - since parallel execution requires a connection pool, this means parallel queries will occur across different connections, which means in-sql defined variables, transactions, and temporary tables have no guarantee of existing between queries, since they're connection-local. So...define your variables in code, not queries, and consider refactoring or phrasing in series before reaching for connection-dependent features. Or just query them in db.series!
• multiple cores - if your db is operating with only one core, you won't benefit meaningfully from running queries in parallel with a connection pool. 2+ cores and you will. See the benchmark.js for benchmark numbers, where the db was on the same server as the app, so the local core count was relevant.
• but isn't node single-threaded? Yes! But db requests go out to a separate system, node makes the request and receives the data. And mysql / mariadb can handle multiple queries at once, so why not supply them when you can?