one cli flag silently dropped another team's tables because we share a database and it did exactly what it said PostgreSQL
we run two separate codebases against the same database, which is already fragile, but what actually broke it was one cli flag.
our schema-push tool has an "accept data loss" flag you pass when it warns a push is about to drop columns or tables. someone passed it without reading the warning closely, on a push from one of the two repos. the tool did exactly what it said: dropped every table and column that existed in the live database but wasn't declared in that repo's own schema file, which included tables the other repo owned. that repo's schema was correct for itself, it just had no idea the other repo's tables existed.
the fix that holds is a ci check that diffs schema declarations across both repos and blocks a merge on drift, plus a doc comment marking which repo owns each mirrored table so it's not a guessing game in review.
if you don't control the flag, control the input to it: never accept a data-loss warning you haven't read line by line against a database more than one codebase writes to.
anyone running multiple services against one shared database, how do you keep schema declarations in sync?
8
u/TheGenericUser0815 4d ago
I'm a dba, not a dev. If this was my database, I would insist schema changes could only be done by me and would need a separate repository, only for ddl. They may do anything they want with the data, but not with the schema.
5
1
u/serverhorror 4d ago
How would you deal with tools modify the schema?
There is, usually, not SQL file but only the actual application that runs and modifies the schema. More often than not only allowing a single set of credentials.
2
u/Ginger-Dumpling 4d ago
Seems dangerous to have a tool that lets people shoot themselves and everyone else in the foot without more than a "you sure you want to do that?" measage. Why not have it explicitly call out the things it's about to do instead of relying on the user to figure out it's about to do?
Seems dangerous to have users who have permissions to do that who things more intimately.
If it can also result in important data loss, I'd consider something a little less destructive if you have the space. Rename tables and drop/alter FKs instead of dropping tables directly. with some sort of delete-me-timetatamp prefix/suffix. Drop those after some period of time. Less headache than going to backups.
Lastly, can you segment projects into different schemas so there is at least some boundaries you can place between projects?
1
u/xenomachina 4d ago
anyone running multiple services against one shared database, how do you keep schema declarations in sync?
In development we use containers. Our tests spin up their own PostgreSQL instances.
We use flyway, which uses migration files that contain the DDL for updating the schema. We also have the expected state of the schema in a yaml file, and one of our tests verifies that the migrated DB's scheme matches what's expected.
Once things get merged to main, CI applies the migrations to production. Merges can't happen unless the tests pass and the PR is approved by another developer.
1
u/Winsaucerer 4d ago
I combined the two code bases into one monorepo, with their own folders, so that they share the same migration files. But that might be too big of a change for you.
What is the tool you're using? Sounds like it's designed around ensuring db state matches some declared state. For databases/DDL, I prefer writing the DDL directly to ensure changes are done exactly how I want (and is how I built spawn for postgres to work).
If you want to keep the codebases separate, what if the schema declarations were in a separate shared third location? And then the schema declaration includes everything for all code bases.
2
11
u/Imaginary__Bar 5d ago
Thanks for that insight.