How to deal with a single database with many schemas?

[robahtou]

I'm working with a postgres database that has many schemas. We especially have a single schema that has all of our extensions and some shared functions, and the rest of the schemas references this schema when trying to use any of the extensions and/or functions.

Is there a best practice in Atlas on how to handle the intial migration? We want atlas to manage the whole database instance (realm connection). We run into a couple of extensions not being found when referenced in different schemas when trying atlas migrate diff. Instead of creating a single schema file with all the schemas of the database, should I do one schema at a time? But then I get the same issue that some schemas not being able to reference other schemas.

NOTE: We use the atlas schema inspect -s <schema> ... to generate the inital schema per schema in the database (we do not use search_path in any of our connection strings. The generated schema is then used as the --to in the migration diff

atlas migrate diff init_bootstrap \
--dir file://$MIGRATIONS_DIR \
--to "file://<schema_01>.sql" \
--dev-url $DEV_CONNECTION_STRING

postgres v16
atlas v0.22.1-1f6fa87-canary

[robahtou]

Another way we have tried is to create a baseline.sql in our migrations directory, run the atlas hash command, and then apply the migration: (we are using neondb)

atlas migrate apply \
-u $NEON_DB_DEV \
--dir "file://migrations" \
--revisions-schema public

This worked for the initial bootstrap. Our dev db on neon is correctly set up. We then want to add a new table. This is where we get lost.

Using the migrate diff command:

atlas migrate diff create_new_table \
--dir file://$MIGRATIONS_DIR \
--to $LOCAL_DB_WITH_NEW_TABLE \
--dev-url $DEV_CONNECTION_STRING

The $LOCAL_DB_WITH_NEW_TABLE is the desired state. It is a local database that mirrors the neon db but with the new added table to it. The --dev-url is plain container with postgres (very similar to the docker container set up in the atlas docs). Running the diff command we start getting the error Error: pq: schema <some_schema> does not exist.

In the docs the --dev-url is a temporary databases. Since we do not use the atlas docker driver, I'm assuming we have to create our --dev-url database with a baseline script. If we run the baseline script in our --dev-url database, the only difference between LOCAL_DB_WITH_NEW_TABLE and DEV_CONNECTION_STRING is that LOCAL_DB_WITH_NEW_TABLEhas the new table that we want to migrate to. Now we get the error connected database is not clean.

Is this a correct approach? Should we just add sql files with our changes to the migration folder and run the atlas migrate apply command? Ideal, we'd like to have a running local database that mirrors production. And when we add new tables, functions, etc we can do that locally, calculate the diff, and then migrate our cloud dbs. Not sure if we are operating with the right mental model in regards to atlas.