4 ms·

Everyone here is arguing for their favorite side. As often is the case, the best solution is often somewhere in the middle, using the best tools for the job. I

Everyone here is arguing for their favorite side. As often is the case, the best solution is often somewhere in the middle, using the best tools for the job. I authored a complete rewrite of an ancient and rotting PHP+MySQL web ticket reservation app in Python+SQLAlchemy+PostgreSQL. I use an ORM - except where it doesn't make sense because a SQL query expresses what I need to do more concisely and effectively. I don't use stored procedures - except where I do because I need one specific atomic DB operation to be performant and not bottlenecked on the app. I use relational storage - except almost every table in the database has a big JSON column for everything I don't need to ever join, filter on, or index in production codepaths (though I can still do that with PG's native json support, which is great for the rare case I have to move something to a real column). And I use triggers, stored procedures, and notifies to implement live change notifications for a table, that eventually get fed via WebSockets server to users. This hybrid approach has served me extremely well, resulting in very readable and maintainable code, minimal DB schema migration pain (most upgrades only touch JSON fields and thus require no migration), and much better performance than the old app, especially in that hot path using the SP, while keeping table column bloat down, and avoiding the join spam that results from keeping everything religiously normalized even in cases where that doesn't buy you anything. Of course, that does mean you need to know all the relevant technologies involved, SQL, ORMs, etc. YMMV, but consider that if you think a single solution is the right solution in all cases, you're most likely wrong.

jbob2000 6y ago
The thing I struggle with is knowing whether or not the solution I designed was biased towards SQL (because that's what I learned years ago) and therefore would be better suited for SQL. OR maybe if I learned more about ORMs I could craft a solution that isn't SQL biased. Or maybe SQL "just works" for most of what we need to do with applications right now, and that's where the SQL bias comes in?
msluyter 6y ago
YMMV, but consider that if you think a single solution is the right solution in all cases, you're most likely wrong. This is sort of the meta-principle that I apply to all of software development: no principle or methodology (DRY, YAGNI, SOLID, TDD, etc...) applies 100% of the time (except for this principle. ;))
mritchie712 6y ago
JSONB is awesome, a lot of our tables end up being something like this: CREATE TABLE some_table ( id SERIAL PRIMARY KEY, uuid DEFAULT uuid_generate_v1(), user_id INTEGER, details JSONB, updated_on timestamp DEFAULT current_timestamp, created_on timestamp DEFAULT current_timestamp )
- nicwolff 6y ago
  I suggest adding `details_schema JSONB` and validating `details` against it. Now you can still add fields to `details` without deploying code changes, but every dev in the company won't throw random garbage in there – or expose that field as raw JSON in an admin panel somewhere.
  - mritchie712 6y ago
    We're still a pretty small so unlikely to happen, but great tip for a larger team.
  - 10000truths 6y ago
    The whole point of having a JSON type is to do away with needing a fixed schema, no? If you need to constrain the format of the JSON object, why not just represent it with more DB-friendly types in the first place?
    - nicwolff 6y ago
      In an enterprise production environment, it's much easier to update a JSON schema, which is stored as data, than to alter the DB schema. The point of having a JSON field isn't to let everyone with write access to the API stick whatever they want in there; it's to let the administrators of the service change what it can store without engineers deploying code changes and DB migrations.

jbob2000 6y ago

The thing I struggle with is knowing whether or not the solution I designed was biased towards SQL (because that's what I learned years ago) and therefore would be better suited for SQL. OR maybe if I learned more about ORMs I could craft a solution that isn't SQL biased. Or maybe SQL "just works" for most of what we need to do with applications right now, and that's where the SQL bias comes in?

msluyter 6y ago

YMMV, but consider that if you think a single solution is the right solution in all cases, you're most likely wrong. This is sort of the meta-principle that I apply to all of software development: no principle or methodology (DRY, YAGNI, SOLID, TDD, etc...) applies 100% of the time (except for this principle. ;))

mritchie712 6y ago

JSONB is awesome, a lot of our tables end up being something like this: CREATE TABLE some_table ( id SERIAL PRIMARY KEY, uuid DEFAULT uuid_generate_v1(), user_id INTEGER, details JSONB, updated_on timestamp DEFAULT current_timestamp, created_on timestamp DEFAULT current_timestamp )

nicwolff 6y ago
I suggest adding `details_schema JSONB` and validating `details` against it. Now you can still add fields to `details` without deploying code changes, but every dev in the company won't throw random garbage in there – or expose that field as raw JSON in an admin panel somewhere.
- mritchie712 6y ago
  We're still a pretty small so unlikely to happen, but great tip for a larger team.
- 10000truths 6y ago
  The whole point of having a JSON type is to do away with needing a fixed schema, no? If you need to constrain the format of the JSON object, why not just represent it with more DB-friendly types in the first place?
  - nicwolff 6y ago
    In an enterprise production environment, it's much easier to update a JSON schema, which is stored as data, than to alter the DB schema. The point of having a JSON field isn't to let everyone with write access to the API stick whatever they want in there; it's to let the administrators of the service change what it can store without engineers deploying code changes and DB migrations.