The Sweet Spot
On software development, engineering leadership, machine learning and all things shiny.

Knex.js and PostGIS cheat sheet

As follows are some code snippets for using Knex.js for executing Postgres and PostGIS queries.

Execute raw SQL in migration

I often find this useful for fancy SQL, like creating views.

exports.up = function(knex, Promise) {
  return knex.raw(`YOUR RAW SQL`);

Add a PostGIS Point type to a table in a migration:

return knex.schema.table('events', function(table) {
  table.specificType('point', 'geometry(point, 4326)');

Add a foreign key to another table.

return knex.schema.table('events', function(table) {

Add a multi-column unique index

return knex.schema.table('events', function(table) {
  table.unique(['start_time', 'end_time', 'start_location', 'end_location', 'distance_miles']);

Find a collection'*')
.where({ name: 'Jason' })
.andWhere('age', '>=', 20)

Custom operations in SELECT clause

.select(knex.raw('miles * passengers as passenger_miles'))
.select(knex.raw("CONCAT('Hello, ', name) as greeting_message"))

Return PostGIS data from a spatial column:

We use knex-postgis to gain access to PostGIS functions in Postgres. Here, we return a ‘point’ column with ST_AsGeoJSON:

const knexPostgis = require('knex-postgis')(knex);
knex('events').select('*', knexPostgis.asGeoJSON('point'));

See knex-postgis documentation for a list of other PostGIS functions that are supported.

Liking what you read?

Accelerate your engineering leadership by receiving my monthly newsletter!

    At most 1 email a month. Unsubscribe at any time.