prestonp

• http://www.infoq.com/presentations/Simple-Made-Easy
• http://jankfree.org/

prestonp

Transactions API

To perform transactions use the dirac.tx api. Dirac defaults to using client pooling per request. So a single client must be used over the lifespan of a transaction. The tx will fetch one for you and release it upon .commit().

A dirac tx object has access to the same DALs in addition to transactional commands.

prestonp

Dirac.js - Postgres ORM Thing

Paul Dirac was a theoretical physicist who made fundamental contributions to the early development of both quantum mechanics and quantum electrodynamics. Dirac.js is a flexible and extendable database layer for Node Postgres.

Database as a Data Structure

Dirac.js is built on top of MoSQL, whose primary goal is to provide SQL query construction, but maintain value consistently throughout. This library extends that goal allowing you to reflect on the overall state of your database and retrieve your table structure in semantic JSON.

Dirac provides you with a decent foundation to start a postgres project with. It allows you to easily group all of your table logic and schema into one file and keep things generally dry and well-namespaced.

prestonp

-- Setup
alter table orders add column search_vector tsvector;

update orders as o 
set search_vector = to_tsvector( 'english', 
    o.id                 || ' ' ||
    coalesce(o.name, '') || ' ' ||
    coalesce(r.name, '') || ' ' ||
    coalesce(u.name, '') || ' ' ||
    coalesce(u.email, '')|| ' ' ||

prestonp

mosql.registerConditionalHelper( '$matches', function( column, set, values, collection ) {
  return column + ' @@ plainto_tsquery(' + set + ')';
});

mosql.registerConditionalHelper( '$partialMatches', function( column, set, values, collection ) {
  // This breaks down a query for prefix matching.
  // The limitation is it does not support spaces
  // but rather & (AND) and | (OR) and ! (NOT).
  // Ex to_tsquery('cat & hat & !ham') -- works

prestonp

// http://tech.pro/blog/2097/clever-way-to-demethodize-native-js-methods
var demethodize = Function.prototype.bind.bind(Function.prototype.call);

// demethodize lets you generalize functions over nodelists, jquery objects, and strings
var map = demethodize([].map);

map('Hello world', function(c) { return c.toUpperCase(); });

// What's more interesting is you can do this despite strings not having a .prototype.map
[].map.call('Hello world', function(c) { return c.toUppercase(); });

prestonp

-- returning updated values and old values by using a sub-expression

update orders
  set type = 'delivery'
  where id = 3767
  returning id, type, (
    select type from orders where id = 3767
  ) as old_type;

prestonp

m.update = function(collection, options){
  options = options || {};

  return function(req, res){
    collection = collection || req.collection;

    collection.update(req.queryObj, req.body, req.queryOptions, function(error, results){
      if (error) return console.log(error), res.status(400).send();

prestonp

Changing timezone in postgres

To change your server’s timezone per session you can use the following query:

set timezone TO 'GMT';

If you need to permanently update the timezone, find your postgresql.conf file in psql:

prestonp

Postgres exists(EXPRESSION) so useful

select *, exists(select 1 from order_amenities where order_id = 4275) as checked from amenities where restaurant_id = 187;