Using Knex Postgis with Lucid

The guides cover the process of using the Knex Postgis extension with the Lucid ORM.

The knex-postgis is an npm package that hooks itself into an instance of Knex and provides the JavaScript API for using the Postgis functions.

Since Lucid uses Knex under the hood, you can use this package within your AdonisJS applications.

Extending the Database module

If you look at the knex-postgis usage documentation, you will find it needs an instance of the knex connection and returns an object with the available methods.

const knex = require('knex')
const knexPostgis = require('knex-postgis')

// Knex connection
const db = knex({ client: 'postgres' })

// Return value "st" has the API for Postgis extension
const st = knexPostgis(db)

Since the management of connections is abstracted with Lucid, we need a graceful API to grab the st object for any connection on-demand.

We can do this by extending the Database class and adding an st method to it. For the sake of simplicity, I will write the following code inside a preload file.

import knexPostgis from 'knex-postgis'
import Database from '@ioc:Adonis/Lucid/Database'

Database.Database.macro('st', function (connectionName?: string) {
  connectionName = connectionName || this.primaryConnectionName
  this.manager.connect(connectionName)

  const connection = this.getRawConnection(connectionName)!.connection!

  /**
   * Ensure we are dealing with a PostgreSQL connection
   */
  if (connection.dialectName !== 'postgres') {
    throw new Error('The "st" function can only be used with PostgreSQL')
  }

  /**
   * Configure extension if not already configured
   */
  if (!connection.client!['postgis']) {
    knexPostgis(connection.client!)
    if (connection.hasReadWriteReplicas) {
      knexPostgis(connection.readClient!)
    }
  }

  return connection.client!['postgis']
})

Let's walk through the above code snippet.

1. We start by adding a new method, st, to the Database module.
2. The method accepts the connection name for which we want to configure the Postgis extension and returns an instance of the knex-postgis package for that specific connection.
3. If the connection has read-write replicas, we configure the connection for both the read and the write connections.

Notifying TypeScript about the new method

Let's create a new file inside the contracts directory. Here we will use declaration merging to add the st method.

declare module '@ioc:Adonis/Lucid/Database' {
  import { KnexPostgis } from 'knex-postgis'

  interface DatabaseContract {
    st(): KnexPostgis
  }
}

Access st instance for a connection

You can access the st instance for a given connection as follows.

Database.st() // default connection
Database.st('primary') // named connection

Select columns as text

You can convert columns to their text representation using the asText method.

import Database from '@ioc:Adonis/Lucid/Database'

await Database
  .from('points')
  .select(
    'id',
    Database.st().asText('geom')
  )

Insert values

You can insert values using the available spatial methods .

import Database from '@ioc:Adonis/Lucid/Database'

await Database.table('points').insert({
  geom: Database.st().geomFromText('Point(0 0)', 4326)
})

Insert using Models

When using models, either you can use the prepare method to convert the string value to a raw query or mark the column as any to assign raw queries directly.

Using the prepare hook

When using models, make sure to mark the column as any to assign raw values to it.

class Point {
  @column({
    prepare: (value?: string) => {
      return value ? Database.st().geomFromText(value, 4326) : value
    }
  })
  public geom: string
}

const point = new Point()
point.geom = 'Point(0 0)'
await point.save()

Assigning raw queries directly

Make sure to mark the column type as any when assigning raw queries directly.

class Point {
  @column()
  public geom: any // 👈 Make sure the type is any
}

const point = new Point()
point.geom = Database.st().geomFromText('Point(0 0)', 4326)
await point.save()