Am I missing something?
> In comparison, writing a validation on the application side is easy
Writing validation on the application side is easy up front, but over the years I have been moving more and more smarts to the database:
- it's often less typing in SQL than your procedural language of choice
- some constraints are easy in a database but 1,000 times harder in a procedural middle layer (uniqueness, ACID transactions, etc.)
- it's more efficient, because it saves the transit of data that your application-layer validation often needs from the database to do its own thing anyway
The DB is a gigantic bag of data: if its got anything useful in it, then everyone will eventually want to put their grubby little hands all over it
Much better to just tell your DBMS the rules you want enforced, such that when that ne'er-do-well (which might be, say, future you) attempts to insert crazy data, it just doesn't work.
But if you have multiple apps sharing the same data, or the app is a bunch of different things from many years glued together, etc., then there is no longer "the" application side. You'd have to write validation on multiple application sides, and make sure they all agree. The data is at this point often more important than a single app.
Even with single-app data stores, if you ever have to use the SQL prompt directly (say, to do a one-off change that it doesn't make sense to write an app feature for, or time doesn't permit doing so) it's nice to have all the constraints specified in the database.
I have had to push for leveraging postgres for data validation (including typing columns with enums where applicable), and sometimes it's hard to convince people, but all you have to do is (a) poke around your existing data and find cases where you have junk because of some out-of-band process that created data without using your main app's validation, and (b) do a couple out-of-band things like an ETL or two and see how it saves you from creating junk.
I don't know where I picked this adage up, but it's also something I constantly think about:
Your data will outlive your application code.
IME, if you value the data's integrity, it's not really a question whether you should be pushing validations down to the db layer.
Again, not saying you're doing something wrong, just sharing a thought.
If its becoming a new table because you now want to associate metadata with it, then you'll have to rewrite queries in either case: previously it was just for data validation (so only relevant on insert, and never really referenced); now information is required so queries will have to join to it where they weren't before.
And if the enum existed in multiple places, and now you have to go around hunting them down to construct the FKs.. then it probably should have just been a table in the first place (otherwise any change to the enum requires finding and syncing each instance of it)
If it'll eventually want to become its own table, then at least while its still small we can claim the benefit of not having to name the thing. And that alone seems sufficiently beneficial to accept having to convert to a table later (unless, ofc, you expect to have to convert by like, tomorrow)
Yes, you are using your company's practices to discount the general benefit of typed data stores. You are also trading safety and usefulness at use/read time for ease at write time which is almost never correct. If your schema mutates frequently, you need to cope. Fearing schema alterations, because of your migration setup is bad IMO (there are plenty of other reasons to fear them though of course). This is how schemas become stale and data analysts (or just other devs) have no clue what they're doing with a schema because developers short-sightedly refuse to improve/refactor schema because of business processes.
In one approach, enum columns were integer columns in db & we relied on ActiveRecord's (RoR) enum definition to map string values their integer representation. This created issues for our (separate & independent) internal application which was feeding off the read-replica.
In second approach, enums were defined as db-types. This solved most of issues seen with the first approach, but again sql-migration for maintaining the enum-values was another pain-point. Though it was not as painful as i thought, as the enums didn't change very frequently.
In third approach, used a separate table to maintain enum definition. If the column has a wider presence then, additional join might prove relatively costly compared to second approach.
Currently, we follow a mix of last two approaches.
It's literally 3 lines of work, if you use a tool that does most of the work for you. [Here](https://github.com/fake-name/ReadableWebProxy/blob/510cc41ae...) is a migration I have for one of my hobby projects.
Tthe process is
`alembic revision --autogenerate` Add two lines to the migration skeleton (4 if you care about downgrades). `alembic migrate +1`.
I may have the alembic migration commands slightly wrong. It's been a while since I've done them without having bash scrollback handy.
And I use dbmate which puts all migrations in transactions.