8 ms·
I’m confused. Isn’t including the canonical state of the database schema in version control along with all the migrations that brought it to that point a comple
by semiquaver 1y ago
I’m confused. Isn’t including the canonical state of the database schema in version control along with all the migrations that brought it to that point a completely standard part of every web framework?
- wredcoll 1y agoThat "works" for about as long as you have <10 employees and <3 customers or so. After that the railsapp doesn't get to be the sole owner of the db.
- sbarre 1y agoDo you have multiple separate apps that can change a shared DB schema? How do you keep that all in sync across your apps?
- int_19h 1y agoIn situations like these, the database admins are the ones responsible for the schema; the apps are mere users.
- scott_w 1y agoSo usually one is the “main” but, personally speaking, I’d just say “don’t do it,” for the obvious issues I suspect you’re aware of ;-) By “don’t do it,” I mean having multiple apps talk to one DB schema.
- throwaway7783 1y ago"don't do it" is the right answer. Others have pointed it out as well, many large SaaS companies I worked with, have had apps owning their databases. Anyone else needs anything - use APIs (and ETL if you need everything)
- ecb_penguin 1y agoIt's definitely not the right answer. It's actually the completely wrong answer. Services are slow, restrictive, and don't enjoy the benefits of an actual DBMS, like transactions. You also add additional dependencies and failure points.
- scott_w 1y agoThis isn’t a discussion of whether you should use services or not, only what to do in the case where you do have separate services interacting with a schema. And, in this case, trying to maintain consistency with multiple apps accessing the DB directly can quickly turn into a nightmare. Better to maintain separate services interacting over some contract to insulate multiple codebases from the internals of each other.
- ecb_penguin 1y ago> Better to maintain separate services interacting over some contract to insulate multiple codebases from the internals of each other. All you've done is shifted the dependency and made extra work when schemas change. Now we have schema updates, _and_ service updates. As I said, you give up transactions, performance, and flexibility. You also increase the workload, and increase failures. There is almost no good reason for what you're proposing.
- throwaway7783 1y agoI am not sure I understand. The context was multiple applications managing/accessing the same database. No matter how you slice it, a schema update is accompanied by application updates. If anything, in a service oriented approach, you can potentially isolate the impact by versioning APIs and writing compatibility logic in a single place.
- ecb_penguin 1y ago> a schema update is accompanied by application updates 1. DB -> App = Two updates, the schema and the app 2. DB -> Service -> App = Three updates, the schema, the service, and the app In both cases, obviously the DB changes. In both cases, obviously the App changes. In the second case, you also get to update your services.
- YorickPeterse 1y agoIt worked fine for GitLab when it had 2000+ employees and god knows how many customers. The same applies to many other large Rails shops.
- bigfatkitten 1y agoProviding access to other services is what APIs are for. Jeff Bezos famously said[1] that anyone who does otherwise should be fired, and I agree. [1] https://news.ycombinator.com/item?id=18916406 https://news.ycombinator.com/item?id=18916406
- int_19h 1y agoSQL is also an API.
- bsaul 1y agothe reason he did that was for business reasons. He wanted to be able to expose any part of the stack as a public service to external customers, and vice versa, to let his internal service compete against the publicly available ones. But this is only valid when you're trying to build AWS. Not everyone does that. Relational databases have extensive permission systems for a reason.
- cowsandmilk 1y agoThe distributed computing manifesto for Amazon is from 1998. Jeff was not thinking about those things then. And wouldn’t for 5 more years.
- dragonwriter 1y ago> Providing access to other services is what APIs are for. Yeah, and databases expose extensive APIs with extremely battle-tested security and permissions models for multiple consumers.
- bigfatkitten 1y agoThat’s why your bank lets you connect directly to their database to see your transaction history.
- cerved 1y agoI'm more confused why the version control of the thing using the database is including the entire schema of the database in it's repository
- koolba 1y agoIt makes it trivial to have pretty diffs of the net result of migrations.
- schrodinger 1y agoThe database schema of an app is tightly coupled enough to essentially be code, and migrations are also probably checked in. This lets you see how the db schema changes over time -- likely along with the queries using the schema.
- int_19h 1y agoThis is only true when your app is the only thing that is using the database. In enterprise environments, databases are frequently shared between many different apps.
- mnahkies 1y agoSure, and in those cases you'd typically have a dedicated repository storing said schema and migrations. It's important to manage your schema in code for various reasons from change control to standing up development databases, etc.
- xyzzy123 1y agoIt often is, but the schema might be written against a language-specific ORM. That code might in turn have plugins or feature flags that mean you don't know the concrete sql schema until runtime. Same for seed data and migrations. So it depends on the use-case how useful this format is for tooling and discovery vs an actual connection to the database.
- tough 1y agoSane ORMs will still use sql for migration files
- OJFord 1y agoI agree it's much better to, but the biggest ones are probably Django & Rails (?), and they don't.
- tough 1y agoyeah tbh i was mostly thinking about the newer crop of ts/js ones (kysely/drizzle) vs the earlier mess that TypeORM or others where on its place, so at least its not so bad.
- cortesoft 1y agoThere are a lot of advantages to using a DSL for migration files rather than pure sql. For one, many times you will get both forward and reverse migration ability automatically, to allow rollbacks (with the ability to mark certain migrations as unable to rollback). You also can have the DSL utilize different features depending on which database you are using without having to change your migration. You can also use special syntax to designate relations between tables easily without having to write the boilerplate SQL every time. If you are using an ORM, using the same language and mental model to describe your database structure as you do to interact with the database makes a lot of sense.
- jen20 1y agoThe _canonical_ state is what is in the production database(s). What's in version control is hopefully able to recreate it, with the obvious caveat of being highly unlikely to be able to repopulate the data.
- dotancohen 1y agoFor development, the actual data itself is not so important, but the features of the data are extremely important. Such as which fields have higher or lower cardinality, which fields are accessed often and which are barely touched. Often times, the indexes will reflect this. Often times, not.