0

I am not sure if what I'm asking is even possible (or desirable, for that matter), but we were wondering what would be the best way to handle SQL changes to a Database schema, when this schema is shared across multiple apps/teams.

We work for a large corporation where database schemas are shared between different software applications (and therefore independent, unrelated, incommunicated, development teams). This means that certain changes to the schema (new views, procedures, constraints, tables, etc.) can come from a variety of apps. Having version control of the DB objects is ideal, but depending on the strategy

  • either a DB snapshot has to be composed from the changes produced by different apps (who knows which?), by traversing all possible apps that touch that schema,
  • or to see the latest changes in a specific application's version, two (or more) repositories have to be checked (in addition, there is no hard-link between commits, releases, etc.).

i.e: Attempting to version control the schema objects (Oracle) puts us in the dilemma of how to do so:

  • Do we store the changes to the different schema objects inside the application's repository (thus distributing the schema snapshot between an undefined nº of repos)?
  • Or do we have separate repositories for the DB schemas, and make two (or more) commits to different repositories when a new version is uploaded (thus difficulting the compilation of a new release changeset)?

I was wondering if it was possible to specify in Git:

Whenever you make a push on the Application repo, send the files in the /SchemaA SQL/ folder to SchemaA repository, the files in /Schema B/ folder to the Schema B repository, and finally the rest to the Application repository. Thus, distributing the contents of the commit between repos in a single operation. Maybe .gitattributes? If using Github/Gitlab, maybe through a webhook on the remote?

1

No, you can't really split a commit into multiple commits across different repositories. The next best thing is that you can extract and push the history for a directory with git subtree, or that you can mount a repository as a git submodule.

But neither of those approaches is an appropriate solution in your scenario.

Your database is a centralized resource. Trying to manage it in a distributed manner is inevitable chaos. No automated solution will prevent one team from accidentally breaking the other teams' access.

The long-term solution would be to reconsider whether a common database is really necessary, or whether different applications or services should rather use separate databases. Disentangling the dependencies between different components is a core idea of approaches like domain driven design, microservices, and service-oriented architecture, but admittedly they don't always make sense. It tends to be a bit more scalable/maintainable if every team offers its resources/data through an API that other teams can consume, rather than sharing data via a common database. Compare the Bezos API mandate, e.g. as discussed here. This isn't a technology or architecture-level thing, this is more about preventing organizational bottlenecks so that teams within an organization are able to deliver value on their own.

The next best thing is to maintain the schema in a shared but centralized manner. You should have one repository that contains all schema migrations for the database. You should be able to test the migration in a test or staging database before rolling the change out to production systems. Other teams should be able to test and review a schema change before it is applied.

Alternatively, there might be a single team or architect who is responsible for the DB schema evolution. Other teams would have to go to that team or person to request changes. This kind of role often emerges naturally, but becomes problematic if this role acts as a gatekeeper and impediment to change.

Once the DB schema has its own repository, other teams can occasionally merge updates into their repositories using git-worktree or git-submodule. If the schema contains incompatible changes, deployment of the various dependent services will require careful coordination – teams necessarily will have to talk with each other.

There's really no substitute for people talking with each other, at most your technological decisions can reduce how many people need to talk with each other. Having a centralized place for management of a shared resource at least scales a bit better than everyone having to seek out every other user of one part of the schema in order to negotiate a change.

2
  • Thank you very much for your answer, very clear and concise. I imagined I'd get something along those lines, but unfortunately as external contractors there are decisions which are out of our control, and I was just attempting to help clear out this mess a bit.
    – Nelladel
    Jan 15 at 15:59
  • @Nelladel Well, being an internal contractor means you likely don't have the clout to change anything, but at least you won't have to deal with long-term consequences of such dysfunctional processes :) In the meanwhile, something like your webhook idea doesn't sound so bad, though it should rather be implemented as a CI job.
    – amon
    Jan 15 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.