Code by Tom

In this brief post I’ll be doing a walk through of how to install, and enable the PostGIS extension in your Remix stack. This will assume you’re using the Blues Stack but I’m sure you can apply any learnings to other setups or stacks.

PostGIS is an open-source software extension for the PostgreSQL database that adds support for spatial and geographic data, as well as providing spatial operations and analysis on stored data.

For example, if you are storing restaurant data and want to find out, based on your current co-ordinates what restaurants are within a nearby radius to you.

Update your docker-compose.yml file

You need to update this file to use the PostGIS image. See my docker-compose.yml file below:

version: "3.7"
services:
  postgres:
    image: postgis/postgis:latest
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    ports:
      - "5432:5432"
    volumes:
      - ./postgres-data:/var/lib/postgresql/data

Update seeding script

Remix comes with a setup script which generates your Prisma client types and seeds your database. It is during this seeding processes that we’re going to make an addition to enable the PostGIS extension.

In my prisma/seed.ts file I’ve added a raw SQL query to enable the PostGIS extension after seeding is complete.

// seed() code precedes this point.
async function installExtensions() {
  await prisma.$queryRaw`CREATE EXTENSION postgis;`;
  console.log(`Extensions installed 🔌`);
  return;
}
seed()
  .then(installExtensions)
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Update package.json setup script

By activating the PostGIS extension our database will be seeded with some required data. This causes problems for Prisma if you want to rerun the setup or seeding script multiple times during this process since it will fail due to data already existing in the database. Because of this, every time we run setup we need to make sure we completely reset the database before running any further Prisma commands by first running prisma db push --force-reset

"setup": "prisma db push --force-reset && prisma generate && prisma migrate deploy && prisma db seed"

Conclusion

And that should be all you need to to. If you have already run the Remix getting started commands you may want to destroy your Docker container, and delete the postgres-data directory that’s stored in your project root before going through them again.

In the context of Remix these commands would be:

1. npm run docker (and once your database is ready to accept connections move on to step two)
2. npm run setup