Managing PostgreSQL Extensions in a Dedicated Migration Process

PostgreSQL extensions are additional modules that extend the functionality of the PostgreSQL database. Examples of popular extensions include PostGIS for geographic data handling, PGVector for vector similarity search, and pgcrypto for cryptographic functions.

Once installed, these extensions provide additional data types and objects that users' schemas can leverage. However, because extensions are managed at the database level (and can only be installed once per database rather than per schema), users may prefer handling these extensions separately from their primary application schema migrations.

For example, if you maintain multiple applications – each with its own schema(s) but several relying on the same PostgreSQL extension – determining which application should handle installation, upgrades, or removal can be challenging. To address this complexity, managing extensions through a dedicated migration process is beneficial. This separation ensures extension-related changes remain isolated.

This guide demonstrates how to use the --include flag (or env.include argument) provided by Atlas to limit migrations specifically to PostgreSQL extensions.

Limit migration scope to extensions only

Given a schema file named extensions.pg.hcl listing all extensions we expect to have in our database:

extensions.pg.hcl

extension "fuzzystrmatch" {
  version = "1.1"
  comment = "Determine similarities and distance between strings"
}
extension "citext" {
  version = "1.6"
  comment = "Data type for case-insensitive character strings"
}

To define a migration process for it, we need to create a new environment in our atlas.hcl file:

env "extensions" {
  src     = "file://extensions.pg.hcl"
  url     = getenv("DATABASE_URL")
  include = ["*[type=extension]"]
}

The include argument will limit Atlas' scope to inspect and manage only the database extensions in this environment.

Then, running atlas schema apply --env extensions results in:

Applying approved migration (2 statements in total):

  -- create extension "fuzzystrmatch"
    -> CREATE EXTENSION "fuzzystrmatch" VERSION "1.1";
  -- ok (8.79975ms)

  -- create extension "citext"
    -> CREATE EXTENSION "citext" VERSION "1.6";
  -- ok (8.856958ms)

  -------------------------
  -- 17.845333ms
  -- 1 migration
  -- 2 sql statements

SQL Schema Format

You can also define your schema using SQL files. In that case, the environment must include a dev attribute pointing to the dev-database.

Using extensions in application schemas

After moving the extensions to a separate migrations process, the application schemas that rely on them must have these extensions pre-installed in their development environments. The same assumption applies to staging and production environments.

Let's use a docker block with a baseline script to set up such a database:

docker "postgres" "dev" {
  image  = "postgres:15"
  schema = "public"
  baseline = <<SQL
   CREATE EXTENSION "citext";
   CREATE EXTENSION "fuzzystrmatch";
  SQL
}

env "app" {
  src = "file://app.sql"
  dev = docker.postgres.dev.url
}

In your application schema (app.sql), you can then utilize the pre-installed extensions:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username varchar(100) UNIQUE,
    email CITEXT UNIQUE -- The "citext" type is provided by the "citext" extension.
);

After setting up the app environment, all Atlas schema/migrate commands will first create a temporary database with the required extensions installed, and then load the schema onto it.