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.
1
2
3
| exports.up = function(knex, Promise) {
return knex.raw(`YOUR RAW SQL`);
};
|
Add a PostGIS Point type to a table in a migration:
1
2
3
| return knex.schema.table('events', function(table) {
table.specificType('point', 'geometry(point, 4326)');
})
|
Add a foreign key to another table.
1
2
3
| return knex.schema.table('events', function(table) {
table.integer('device_id').references('id').inTable('devices');
});
|
Add a multi-column unique index
1
2
3
| return knex.schema.table('events', function(table) {
table.unique(['start_time', 'end_time', 'start_location', 'end_location', 'distance_miles']);
});
|
Find a collection
1
2
3
4
| knex.select('*')
.from('participants')
.where({ name: 'Jason' })
.andWhere('age', '>=', 20)
|
Custom operations in SELECT clause
1
2
3
| knex('trips')
.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:
1
2
| 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.