Custom baseline sql script

[KostyaZgara]

Hi there! I'm looking for something like a custom baseline SQL script that I can define in my atlas.hcl config. Here is what I mean

docker "postgres" "dev" {
  image  = "postgis/postgis:15-3.4-alpine"
  schema = "public"
  baseline = <<SQL
    drop extension postgis cascade;
  SQL
}

env "ci" {
  url = "postgres://${var.username}:${var.password}@${var.host}:${var.port}/${var.database}?search_path=public&sslmode=disable"
  dev = docker.postgres.dev.url

  migration {
    dir    = "file://db/migrations"
    format = atlas
    baseline = <<SQL
      drop extension postgis cascade;
    SQL
  }
}

But seems the current baseline parameter under migration block expects the baseline version instead of the script like in the docker driver. The problem is that I have different databases in CI and in the cloud, where the cloud database has an extension on the server, but is not initialized, so I have to create a new migration version to run the statement create extension postgis;, but in the CI I use postgis docker container which initializes postgis extension by default https://github.com/postgis/docker-postgis/blob/master/initdb-postgis.sh and that seems impossible to disable. So, to prevent failing my statement create extension in the CI, I have to drop that extension first and let my migrations initialize the extension again. I managed to get this work in dev database by providing a custom SQL script for baseline, but I failed to do it on my primary database. The current workaround I drop the extension in the CI before applying atlas migrations, but it seems relevant to let users define custom baseline script per environment without modifying the CI itself. If you have other options on how I can get this thing to work fully on Atlas, I will be happy to hear! Thanks!

[giautm]

Hello @KostyaZgara, the PG extension was supported in the canary version of the atlas, which you can install using our install script. Check more information about it here: https://atlasgo.io/atlas-schema/hcl#extension

Please let me know it you still have issue with PG extension, cheers!

[KostyaZgara]

Hi, thanks for your response!

I tried to follow the documentation you sent me, but atlas doesn't see any changes between current migration directory and the desired state. I added extension resource to schema file

extension "postgis" {
  schema  = schema.public
  comment = "PostGIS geometry and geography spatial types and functions"
}

schema "public" {
  comment = "standard public schema"
}

Run command

$ atlas migrate diff --env moji install_postgis_extension

which gave me the following message

The migration directory is synced with the desired state, no changes to be made

But I expected that Atlas would generate a new migration entry with something like

create extension postgis;

Here is my Atlas CLI version

atlas version v0.19.3-eaf1e59-canary

[masseelch]

In general your local database container should have the same state of your production database so versioned migrations are valid.

I think what you can do is instead of using postgis locally, use bare postgres (if that is what you have in production). Then Atlas will generate the CREATE EXTENSION statement.

Another idea I can think of is maybe to use a baseline schema in the postgis docker container locally where you disable the extension. For the diuff computation Atlas will then generate a CREATE EXTENSION statement (I think).

[KostyaZgara]

I think what you can do is instead of using postgis locally, use bare postgres (if that is what you have in production). Then Atlas will generate the CREATE EXTENSION statement.

I don't fully understand how extension resources work. For instance, this postgres image https://hub.docker.com/r/bitnami/postgresql has postgis installed on server, but it's not enabled by default, and executing create extension postgis is required. In another case, when postgis extension doesn't exist on the server at all and executing create extension postgis statement fails, for instance for this image https://hub.docker.com/_/postgres/.

Theoretically, I can narrow down my image types to one when postgis is installed, but not enabled, cuz for example, for production I'm using GCP CloudSQL which seems to have postgis installed, but not enabled (I can't verify it for sure, cuz I'm blocked by issue where I can't push migrations to Atlas Cloud), but my research gave me that vast majority of cloud database has postgis onboard out-of-box. So let's say I would use for CI, local development, and dev database a similar image where postgis is installed, but not enabled, for instance, bitname image. But how atlas resource extension would work here? As I mentioned before, it doesn't generate a create extension postgis statement to enable postgis extension.

While writing this message, I realized I never tried to use bitnami image everywhere except production. Let me try to do it and I'll come back with the results

[masseelch]

Please see my comment here.

[KostyaZgara]

Thanks! The solution of using Atlas on the database level instead of schema worked for me. Here is my feedback comment #2596