7 ms·

This story supports my growing theory that you should put as much of your app's rules in the database as you can. There were three problems with having the rul

This story supports my growing theory that you should put as much of your app's rules in the database as you can. There were three problems with having the rule in Rails: 1. The need for an index was easily overlooked. 2. The rule would be bypassed if a different app used the same database. 3. The rule wasn't even foolproof. Only a database constraint would guarantee uniqueness when two users are saving at the same instant. The problem is, SQL is hard. We should not forget how much a programmer must learn. For example: Ruby, Rails, Linux command line, HTML, CSS, JavaScript, vi, how to exit vi, etc. Each of these takes years to master. SQL is especially SQuirreLy. However, it can't possibly be worse than learning the myriad JavaScript frameworks and complicated server-build tools that are completely optional for 99% of us. My advice: Don't do a SPA. Spend your time on SQL instead :D

Retra 9y ago
Maybe I'm crazy, but I thought the whole point of putting validation and constraint capabilities into RDBMSs was because it was more efficient to do so, and that this has been pretty well understood for like... eons. If you didn't want to write SQL and use constraints, then why would you even use a database (over just plain files)? That's what they are designed for. RDBMS developers are probably all sitting around giggling (nihilistically) over experiences with users who think their database is slow on account of their failure to actually use it in the way it was optimized to be used.
- bobwaycott 9y ago
  I believe the primary driver here is the overwhelming reliance upon ORMs when building anything with your average web framework—be it Python, Ruby, JavaScript, or whatever. Sure, these ORMs may, and usually do, offer ways to write and execute queries directly in SQL, but a vast majority of current developers seems to always reach for the ORM. Now, for simple CRUD apps, sure, that’ll help get you going quickly without having to think outside your chosen language. But this then snowballs into never thinking outside your chosen language. When ORMs are the average developer’s introduction and sole interface to using databases, we wind up with all the StackOverflow questions about how to write query X in ORM Y, when it could have been written as an SQL statement, sproc, or function, and executed directly (and probably more efficiently). It’s both amusing and sad when I see or hear of someone being unable to interact with their db directly. I can only suspect they actually believe the ORM is the way to interact with their db. Oh, they may know SQL exists, but that’s about it. This problematic state of affairs is only compounded by the fact that ORMs don’t properly and easily handle taking constraint, validation, and other data-focused application logic and actually writing it into the schema (some ORMs are better/worse at this than others, of course). They all seem to just default to letting as much of it be application-level code as possible. This then leaves new developers with the impression this is where such code belongs, so they never experience anything telling them it should be anywhere else. It’d be great to see more education offered within ORM docs, but the really important stuff seems to often be there only if you already know what you’re looking for. I’ve seen so many models defined over the years without indexes on the most obvious columns that are queried a bajillion times. Or maybe you can set a unique constraint on certain fields without a bunch of effort—sqlalchemy is decent at this in the Python world—but then you have to have unique validators for forms, rather than relying on the db to complain about a violation of the the constraint, and the ORM catching it in a friendly way devs can bubble up when needed. Oh, and then some insist on adding client-side validation of that same constraint. So, you take a constraint that matters at the db level, and is already easily handled at the db level, and people are writing client and server validation to enforce a condition the db is already supposed to be enforcing. There’s madness here. Maybe it’s just me, though.
lucisferre 9y ago
> Don't do a SPA. Spend your time on SQL instead :D I wholeheartedly agree that developers learn SQL way too little and way too late (I blame ORM/AR overuse early on) but SPA/SQL are possibly the least mutually exclusive issues I can imagine.
EpicEng 9y ago
...well why wouldn't you put your constraints in the DB? Really, why wouldn't you? Off topic: learning to exit vi takes "years to master"?
- dboreham 9y ago
  No way: you can just kill -9 it from another terminal.
  - shoo 9y ago
    The generic editor agnostic solution: kill pid -1
- wernercd 9y ago
  If exiting VI takes years... I shudder to think how long it'll someone to learn the "Write with Sudo" trick.
BurningFrog 9y ago
Well, there are problems with that approach too. At the far end, any programming logic you put in the DB will be impossible to scale if needed. You can only have one DB, but any number of Rails servers. Database constraints are a matter of preference and skill set. They make some things much harder when you have to delete things is certain convoluted orders, and I can get the correctness through tests. Your decisions may differ. That's fine. You should always put indexes on things you query by. Be vigilant. This is the one case where "only optimize when you have a performance problem" rule doesn't apply.
- jaggederest 9y ago
  Indexes are useful for speeding up select statements but they run a significant cost on insert and update. Sometimes it's better not to index, if you have a table that is updated often and queried in varying, complex, rarely repeated ways.
  - BurningFrog 9y ago
    Yeah, I'm aware of that, and I'm sure these cases exist, but after 20 years around these things I have yet to see one in real life.
    - jaggederest 9y ago
      FWIW I've worked for multiple companies where tables couldn't reasonably be indexed. Special cases though, usually write-only analytic tables that are queried once in a blue moon and have incredibly high traffic (~millions of rows per hour).
- mongol 9y ago
  You can certainly have more than one db.
- deleted 9y ago
  [deleted]
- combatentropy 9y ago
terminalcommand 9y ago
vi, how to exit vi part made me laugh. Joke aside, the basics of SQL can be learned in a very short time. Before fancy ORMs all novice programmers managed to get by using SQL in big-ridden PHP projects ;). I for example, got my introduction from querying a national database leak and than writing a simple blog with tricks I've learned from sqlzoo.
- lostboys67 9y ago
  Yes on before my first oracle project I did a weeks course on Pl/SQL and a week on Forms and that worked out fine
YouKnowBetter 9y ago
All business / data rules go into and are enforced by, the database. This was learned the hard way: creative sales people manipulating sales data (via ODBC & Excel on NT3.51 no less). Since all logic was in the applications, they had a field day when they bypassed those. This was 1996. Not much has changed.

Retra 9y ago

Maybe I'm crazy, but I thought the whole point of putting validation and constraint capabilities into RDBMSs was because it was more efficient to do so, and that this has been pretty well understood for like... eons. If you didn't want to write SQL and use constraints, then why would you even use a database (over just plain files)? That's what they are designed for. RDBMS developers are probably all sitting around giggling (nihilistically) over experiences with users who think their database is slow on account of their failure to actually use it in the way it was optimized to be used.

bobwaycott 9y ago
I believe the primary driver here is the overwhelming reliance upon ORMs when building anything with your average web framework—be it Python, Ruby, JavaScript, or whatever. Sure, these ORMs may, and usually do, offer ways to write and execute queries directly in SQL, but a vast majority of current developers seems to always reach for the ORM. Now, for simple CRUD apps, sure, that’ll help get you going quickly without having to think outside your chosen language. But this then snowballs into never thinking outside your chosen language. When ORMs are the average developer’s introduction and sole interface to using databases, we wind up with all the StackOverflow questions about how to write query X in ORM Y, when it could have been written as an SQL statement, sproc, or function, and executed directly (and probably more efficiently). It’s both amusing and sad when I see or hear of someone being unable to interact with their db directly. I can only suspect they actually believe the ORM is the way to interact with their db. Oh, they may know SQL exists, but that’s about it. This problematic state of affairs is only compounded by the fact that ORMs don’t properly and easily handle taking constraint, validation, and other data-focused application logic and actually writing it into the schema (some ORMs are better/worse at this than others, of course). They all seem to just default to letting as much of it be application-level code as possible. This then leaves new developers with the impression this is where such code belongs, so they never experience anything telling them it should be anywhere else. It’d be great to see more education offered within ORM docs, but the really important stuff seems to often be there only if you already know what you’re looking for. I’ve seen so many models defined over the years without indexes on the most obvious columns that are queried a bajillion times. Or maybe you can set a unique constraint on certain fields without a bunch of effort—sqlalchemy is decent at this in the Python world—but then you have to have unique validators for forms, rather than relying on the db to complain about a violation of the the constraint, and the ORM catching it in a friendly way devs can bubble up when needed. Oh, and then some insist on adding client-side validation of that same constraint. So, you take a constraint that matters at the db level, and is already easily handled at the db level, and people are writing client and server validation to enforce a condition the db is already supposed to be enforcing. There’s madness here. Maybe it’s just me, though.

lucisferre 9y ago

> Don't do a SPA. Spend your time on SQL instead :D I wholeheartedly agree that developers learn SQL way too little and way too late (I blame ORM/AR overuse early on) but SPA/SQL are possibly the least mutually exclusive issues I can imagine.

EpicEng 9y ago

...well why wouldn't you put your constraints in the DB? Really, why wouldn't you? Off topic: learning to exit vi takes "years to master"?

dboreham 9y ago
No way: you can just kill -9 it from another terminal.
- shoo 9y ago
  The generic editor agnostic solution: kill pid -1
wernercd 9y ago
If exiting VI takes years... I shudder to think how long it'll someone to learn the "Write with Sudo" trick.

BurningFrog 9y ago

Well, there are problems with that approach too. At the far end, any programming logic you put in the DB will be impossible to scale if needed. You can only have one DB, but any number of Rails servers. Database constraints are a matter of preference and skill set. They make some things much harder when you have to delete things is certain convoluted orders, and I can get the correctness through tests. Your decisions may differ. That's fine. You should always put indexes on things you query by. Be vigilant. This is the one case where "only optimize when you have a performance problem" rule doesn't apply.

jaggederest 9y ago
Indexes are useful for speeding up select statements but they run a significant cost on insert and update. Sometimes it's better not to index, if you have a table that is updated often and queried in varying, complex, rarely repeated ways.
- BurningFrog 9y ago
  Yeah, I'm aware of that, and I'm sure these cases exist, but after 20 years around these things I have yet to see one in real life.
  - jaggederest 9y ago
    FWIW I've worked for multiple companies where tables couldn't reasonably be indexed. Special cases though, usually write-only analytic tables that are queried once in a blue moon and have incredibly high traffic (~millions of rows per hour).
mongol 9y ago
You can certainly have more than one db.
deleted 9y ago
[deleted]
combatentropy 9y ago

terminalcommand 9y ago

vi, how to exit vi part made me laugh. Joke aside, the basics of SQL can be learned in a very short time. Before fancy ORMs all novice programmers managed to get by using SQL in big-ridden PHP projects ;). I for example, got my introduction from querying a national database leak and than writing a simple blog with tricks I've learned from sqlzoo.

lostboys67 9y ago
Yes on before my first oracle project I did a weeks course on Pl/SQL and a week on Forms and that worked out fine

YouKnowBetter 9y ago

All business / data rules go into and are enforced by, the database. This was learned the hard way: creative sales people manipulating sales data (via ODBC & Excel on NT3.51 no less). Since all logic was in the applications, they had a field day when they bypassed those. This was 1996. Not much has changed.