Happy Employees == Happy ClientsCAREERS AT DEPT®
DEPT® Engineering BlogPlatforms

Setting up PostGIS with Rails and ActiveRecord on Fly.io

How to get Ruby on Rails working with PostGIS and PostgreSQL on Fly.io

If you're interested in deploying a Rails application to Fly.io and need support for geographic objects and location data in your database, they now support PostGIS - an extension to the powerful PostgreSQL database backend out of the box.  It took a little extra tweaking to make it work with Rails the way I needed, though.

There is a very handy database adapter for ActiveRecord - the aptly named ActiveRecord PostGIS Adapter that enables you to include spatial data types in your ActiveRecord models, and then lean on the database for spatial queries.  

Basic Setup

I set up a simple Rails application.  Added the PostGIS adapter in the Gemfile:

# Use postgresql as the database for Active Record
gem "pg", "~> 1.1"
gem "activerecord-postgis-adapter", "~> 8.0"

I  configured the database.yml configuration as specified in the documentation, with the addition of the url as an environment variable provided by Fly.io:

  adapter: postgis
  encoding: unicode
  url: <%= ENV['DATABASE_URL'] %>  
  schema_search_path: public, postgis

I ran fly launch to get the app set up.  All good.

A Bump in the Road

I built a very simple model and migration with a single piece of spatial data to test things out:

class CreatePlaces < ActiveRecord::Migration[7.0]
  def change
    create_table :places do |t|
      t.string :name, null: false
      t.st_point :latlon, geographic: true

I ran fly deploy - which promptly blew up trying to run the migration:

 undefined method `st_point' for #<ActiveRecord::ConnectionAdapters::PostgreSQL::TableDefinition...

(many, many more lines of error followed)

Looks like Rails was still trying to do everything via the PostgreSQL adapter, as opposed to the PostGIS one.  The issue is that ActiveRecord takes its cue for the adapter from the protocol prefix on the URL.  Fly.io configures our database URL to look like this:


And we need it to look like this:


The Solution

There are a couple of roads to take to solve this problem.  You can reset the DATABASE_URL environment variable - stored in a secret in Fly.io:

flyctl secrets set DATABASE_URL=postgis://appname:password@host:5432/appname

There are some potential problems with doing that to the environment variable, as other extensions may read it, and they will probably not interpret a "postgis" protocol correctly.

I have chosen to adjust my database.yml to swap the protocols:

  encoding: unicode
  adapter: postgis
  url: <%= ENV['DATABASE_URL'].gsub(/^postgres/,'postgis') %>  
  schema_search_path: public, postgis

Both solutions work - but there could be some follow-on consequences with changing the DATABASE_URL environment variable across the board.  Either way, run fly deploy to push changes out, and it should deploy cleanly:

         Preparing to run: `bin/rails fly:release` as root
         2022/10/05 18:25:45 listening on [fdaa:...]:22 (DNS: [fdaa::3]:53)
         Starting clean up.
==> Monitoring deployment

 1 desired, 1 placed, 1 healthy, 0 unhealthy [health checks: 1 total, 1 passing]
--> v7 deployed successfully

In console, everything worked as expected - I was able to add a Place with a latitude/longitude point:

irb(main):007:0> Place.create(name: "Statue of Liberty", latlon: 'POINT(40.689347001801465 -74.04447894312094)')
 id: 4,
 name: "Statue of Liberty",
 latlon: #<RGeo::Geographic::SphericalPointImpl:0x2f4e0 "POINT (40.689347001801465 -74.04447894312094)">,
 created_at: Wed, 05 Oct 2022 16:49:31.455995000 UTC +00:00,
 updated_at: Wed, 05 Oct 2022 16:49:31.455995000 UTC +00:00>

Wrapping Up

This problem had me stumped for a few hours.  Hopefully this writeup helps you get through it faster than I did!