simple sql for nodejs
simply elegant sql for nodejs
this documentation targets the upcoming
currently in alpha and available on npm as
it's already used in production, is extremely useful, well tested
and quite stable !
mesa is a moving target. we are using it in production and it grows and changes with the challenges it helps us solve.
firstname.lastname@example.org will be released when it's done !
this documentation does not yet represent everything that is possible with mesa. feel free to look at the code. it's just around 600 lines.
npm install --save mesa
mesa needs node-postgres:
npm install --save pg
var mesa = require'mesa';var pg = require'pg';
let's tell mesa how to get a database connection for a query:
var database = mesasetConnectionpgconnect'postgres://localhost/your-database' cb;;
a call to
setConnection is the (only) thing
to the node-postgres library and to the specific database.
setConnection(callbackOrConnection) has returned a new object.
the original mesa-object is not modified:
assertdatabase !== mesa;
mesa embraces functional programming: no method call on a mesa-object modifies that object. mesa configuration methods are pure: they create a NEW mesa-object copy all OWN properties over to it, set some property and return it.
this has no effect:
it creates a new object that is not used anywhere and eventually gets garbage collected.
let's configure some tables:
var movieTable = databasetable'movie';var personTable = databasetable'person';
there are no special database-objects, table-objects or query-objects in mesa. only mesa-objects that all have the same methods. order of configuration method calls does not matter. you can change anything at any time:
var personTableInOtherDatabase = personTablesetConnectionpgconnect'postgres://localhost/your-other-database' cb;;
it naturally follows that method calls on mesa-objects are chainable !
var rRatedMoviesOfThe2000s = movieTable// `where` accepts raw sql and optional parameter bindingswhere'year BETWEEN ? AND ?' 2000 2009// repeated calls to where are 'anded' together// `where` accepts objects that describe conditionswhererating: 'R';
.having() methods take exactly the same
arguments as criterion...
we can always get the SQL and parameter bindings of a mesa-object:
rRatedMoviesOfThe2000ssql;// -> 'SELECT * FROM "movie" WHERE (year BETWEEN ? AND ?) AND (rating = ?)'rRatedMoviesOfThe2000sparams;// -> [2000, 2009, 'R']
mesa uses mohair to generate sql which it then sends to the database. in addition to it's own methods every mesa-object has the entire interface of a mohair-object. for this reason the mohair methods are not documented in this readme. consult the mohair documentation as well to get the full picture.
mesa supports all methods supported by mohair with some additions. look into mohairs documentation to get the full picture of what's possible with mesa.
**mohair powers mesa's
.where method is one such method that is implemented by mohair
mohair uses criterion
for this reason the criterion methods are not documented in this readme.
**criterion powers/documents mesa's
we can refine:
var top10GrossingRRatedMoviesOfThe2000s = rRatedMoviesOfThe2000sorder'box_office_gross_total DESC'limit10;
because every mesa-object gets a copy of all a method added to a mesa-object is available on all mesa-objects down the chain.
this makes it very easy to extend the chainable interface...
return thiswhere'year BETWEEN ? AND ?' from to;;perPage = perPage ? perPage : 10;return thislimitperPageoffsetpage * perPage;;var paginatedTopGrossingPG13RatedMoviesOfThe90s = movieTable// we can freely chain and mix build-in and custom methods !order'box_office_gross_total DESC'page2whererating: 'PG13'betweenYears1990 1999;
we see how pure functions and immutability lead to simplicity, reusability and composability !
we can run a select query on a mesa object and return all results:
top10GrossingRRatedMoviesOfThe2000s// run a select query and return all resultsfind// running a query always returns a promisethen;
running a query always returns a promise !
we can run a select query on a mesa object and return only the first result:
top10GrossingRRatedMoviesOfThe2000s// run a select query and return only the first result// `first` automatically calls `.limit(1)` to be as efficient as possiblefirst// running a query always returns a promisethen;
we can also simply check whether a query returns any records:
movieTablewherename: 'Moon'exists// running a query always returns a promisethen;
we can run an insert query on a mesa object:
movieTable// whitelist some properties to prevent mass assignmentallow'name'insertname: 'Moon'// running a query always returns a promise// if insert is called with a single object only the first inserted object is returnedthen
before running insert queries
if you have control over the properties of the inserted objects
and can ensure that no properties
can disable this by calling
you can reenable it by calling
you can insert multiple records by passing multiple arguments and/or arrays to insert:
movieTable// disable mass-assignment protectionunsafe// running a query always returns a promiseinsertname: ''name: ''name: ''name: ''then
you see that mesa returns the inserted records by default
This part is coming soon.
This part is coming soon.
an sql fragment is any object with the following properties:
an sql([escape]) method which returns an sql string takes an optional parameter escape which is a function to be used to escape column and table names in the resulting sql a params() method which returns an array
at the heart of mesa is the query method
if you pass mesa (which is an sql fragment) into the query function...
This part is coming soon.
setConnection either accepts
all of sql
down to the metal
mesaWithDebug = mesadebug
only on refined versions
debugging per table, per query, ...
directly before a query debug will just for that specific query
just display sql
mesa = mesadebugif topic === 'query' && event === 'before'console.log'QUERY' datasql dataparams;;
the topics are
that function will be called with five arguments
the first argument is
the fifth argument is the instance
the fourth argument contains ALL additional local state that is
here is a quick overview:
look into the source to see exactly which
often you want to for all tables, a specific table a specific do something to the records
configure mesa instances.
you can add functions to the queues with the following ways
hooks either run on a the array of all items or item
array queues are run before
functions in queues are run in the order they were added.
there are the following queues:
queueBeforeInsert run before insert on array of items
there is no
queueBeforeUpdate because update always operates on a single item. use
queueBeforeEachInsert run before insert on each item
queueBeforeEachUpdate run before update on each item
queueBeforeEach run before update or insert on each item
queueAfterSelect run after find or first on array of items
queueAfterInsert run after insert on array of items
queueAfterUpdate run after update on array of items
queueAfterDelete run after delete on array of items
queueAfter run after find, first, insert, update and delete on array of items
queueAfterEachSelect run after find or first on each item
queueAfterEachInsert run after insert on each item
queueAfterEachUpdate run after update on each item
queueAfterEachDelete run after delete on each item
queueAfterEach run after find, first, insert, update and delete on each item
var _ = require'lodash';userTablequeueAfterEachSelect_omit 'password'whereid: 3first;
var Promise = require'bluebird';var bcrypt = PromisepromisifyAllrequire'brypt';varif recordpasswordbcryptgenSaltAsync10thenreturn bcrypthashAsyncpassword salt;;elsereturn Promiseresolvenull;;userTable = userTablequeueBeforeEachhashPassword;
userTable = userTablequeueBeforeEachrecordupdated_at = ;return record;queueBeforeInsertrecordcreated_at = ;return record;;
mesa comes with a very powerful mechanism to manipulate records before they are sent to the database or after they were received from the database and before returning them.
if you are familiar with the active record pattern prefer a more object-oriented style here is how you would use mesa to implement it as the foundation as the building blocks
if you want to use camelcased property names in your program and underscored in your database you can automate the translation
add them to the mesa instance and have it work for all your tables
by setting the order you ensure that the other hooks see camelcased properties !!!
includes are a NEW feature and may not be as stable as the rest
in any rows in different tables are linked via foreign keys.
includes make it easy to fetch those linked rows and add them to our data:
lets assume, for a moment, the following tables and relationships:
name. has one
address, has many
user_id. belongs to
uservia foreign key
status. belongs to
userTable = databasetable'user';addressTable = databasetable'address';orderTable = databasetable'order';
we can now find some users and include the orders in each of them:
a lot is happening here. let's break it down:
include has no side-effects and does not fetch any data.
instead it queues a function to be executed
on all results (if any) of
queries further down the chain.
in this case that function will
will run a query on
orderTable to fetch all
order.user_id is in the list of all
id values in
it will then for every user add as property
orders the list of all
user.id === order.user_id.
by default include queues a fetch of a has-many relationship
the above code snippet is equivalent to this:
userTableincludeleft: 'id'right: 'user_id'forward: truefirst: falseas: 'orders'orderTablefind
the first argument to
in case that link-object is missing or any properties are missing (and only those fields)
mesa will autocomplete it from table names , primary keys set with
orderTableincludeforward: false first: true userTablefind
you can add as many additional link
you can modify, add conditions
you can nest
using an explicit link object:
you get the idea
includes are intentionally very flexible. they work with any two tables where the values in whose values match up.
if you are using primary keys other than
fetch a one-to-one association (in a single additional query)
the implementation uses the hooks its surprisingly simple
using the same connection as the
use one additional query to fetch all and then associate them with the records
order and conditions and limits on the other tables have their full effects
using mesa you'll often find yourself calling methods only when certain conditions are met:
var dontFindDeleted = true;var pagination = page: 4 perPage: 10;var tmp = userTable;if dontFindDeletedtmp = userTablewhereis_deleted: false;if paginationtmp = tmplimitpaginationperPageoffsetpaginationpage * paginationperPage;tmpfind;
all those temporary objects are not very nice.
fortunately there is another way:
userTablewhendontFindDeleted userTablewhere is_deleted: falsewhenpaginationreturn thislimitpaginationperPageoffsetpaginationpage * paginationperPage;find;
TL;DR: bugfixes, issues and discussion are always welcome. ask me before implementing new features.
i will happily merge pull requests that fix bugs with reasonable code.
i will only merge pull requests that modify/add functionality if the changes align with my goals for this package and only if the changes are well written, documented and tested.
communicate: write an issue to start a discussion before writing code that may or may not get merged.