Use Bookshelf with postgis and knex-postgis without the hassle

Bookshelf-postgis.md

How to use Bookshelf with postgis and knex-postgis without the hassle

Using Bookshelf.js with postgis and knex-postgis is a huge pain in the ass.

TLDR - Override Bookshelf methods to parse postgis formats in JS, not in SQL to avoid having to do awkward Bookshelf modifications anywhere you make a bookshelf insert/update/delete on Geo data (middleware like approach)

class Event extends Bookshelf.Model {
  get tableName() { return 'event'; }

  toJSON() { // overrides the Bookshelf.Model toJSON method
    var attrs = Bookshelf.Model.prototype.toJSON.apply(this, arguments);
    const b = new Buffer(attrs.coordinates, 'hex');
    const c = wkx.Geometry.parse(b);
    attrs.coordinates = {lat: c.x, lng: c.y};
    return attrs;
  }
}

Lets say I want to store a Point (lat, long) point as a field in a table in my Postgres DB. In order to update or insert the point I need to do a

Update or insert

Event
  .forge({
    ...data,
    coordinates: BookShelf.knex.raw(`ST_SetSRID(ST_Point(${data.coordinates.lat},${data.coordinates.lng}) , 4326)`)
  })
  .save()

## Fetch

Event
  .where({id: id})
  .query((qb) => {
    qb.select('*', kp.asGeoJSON('coordinates'));
  })
  .fetch({require: true})

But now

• a) I need to change all queries to Event to support this datatype
• b) I need to do post parsing, as the coordinates field is a string {"type":"Point","coordinates":[52.3050168,4.75151979999998]} and I want a {lat: 52.3050168, lng: 4.75151979999998} object.

What a giant pain! If only I could do the parsing of the data stored in the database in Javascript instead of SQL. Then I could override the Bookshelf model fetch / update functions, or the toJSON function to parse the coordinates. After some research I discovered that postgis stores the data in a HEXEWKB format. https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary Well it turns out we can do just this using a library called buffer, wkx https://github.com/cschwarz/wkx to parse the data. Now whenever the toJSON method on the model gets called (for instance when you are parsing a server response), the coordinates are transformed to data readable format. You can use a similar approach for insertion (I haven't tried that yet)

// SQL creation code
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

CREATE TABLE event (
  coordinates GEOGRAPHY(Point),
);

// Event Class

import Bookshelf from '../database';
import wkx from 'wkx';
var Buffer = require('buffer').Buffer;

class Event extends Bookshelf.Model {
  get tableName() { return 'event'; }

  toJSON() { // overrides the Bookshelf.Model toJSON method
    var attrs = Bookshelf.Model.prototype.toJSON.apply(this, arguments);
    const b = new Buffer(attrs.coordinates, 'hex');
    const c = wkx.Geometry.parse(b);
    attrs.coordinates = {lat: c.x, lng: c.y};
    return attrs;
  }
}

export default Bookshelf.model('Event', Event);

// database setup
import config from '../../knexfile';
import bookshelf from 'bookshelf';
import knexPostgis from 'knex-postgis';

const knex = require('knex');
export const kp = knexPostgis(knex(config[process.env.NODE_ENV || 'development']));

const Bookshelf = bookshelf(knex(config[process.env.NODE_ENV || 'development']));

Bookshelf.plugin('registry'); // Resolve circular dependencies with relations
Bookshelf.plugin('visibility');

export default Bookshelf;

I hope this helps you and saves you time!