3 ms·

There's a wide variety of reasons to want the schema in the database, and schemas are so valuable that even our good friends at Google decoded to add database l

by hibikir 10y ago

There's a wide variety of reasons to want the schema in the database, and schemas are so valuable that even our good friends at Google decoded to add database level schemas to their massively distributed data stores. The strongest reason is that the database will complain loudly if you want to make a change that breaks existing constraints. An application, no matter how simple, will probably change, and you don't want to leave data out there that is unreadable and that will break your application's expectations. The schema minimizes the chances of garbage historical data that have been, in my experience the plague of NoSQL-backed apps. Often you find people that end up writing ad hoc constraint checkers, creating a poor imitation of what a database that understands schemas does well. Another big reason is that there's rarely just one application, and the applications could (and very often should) be written in different languages. A schema at that point becomes a service which just happens to not use Json or Thrift, but a far more complicated, sturdier one. You could still argue that databases are too complicated to expose very widely, but I'd much rather have all my data sitting in the very schema-heavy redshift to do reporting than to rely on application logic, or use most business insight tools out there. Also, let's not forget that schemas let databases do a lot of complex operations rather cheaply, because they can have very optimized code of very specific shapes next to the data. In the cases where 'first, I will take 50K rows out of the db and into my application' is just not acceptable, you have to let the DB do the work. In those cases, the interface to use a windowing function in a SQL database is IMO far more convenient than having to go into the classic remote map reduce function in erlang. I think there were plenty of gains from the NoSQL movement, especially as far as to start writing database where high availability and horizontally scalable loads are concerned. But outside of those realms, where few companies really are, I would not recommend a schemaless database today.

ianamartin 10y ago
Good points all around, and I fully agree. Just to elaborate a little on your third paragraph, this is incredibly important as an app matures. You might start out thinking you're only ever going to have one interface to a datastore, but the reality is that you always have at least two. Someone has the ability to go directly to the store itself and make changes. That's life. Someone at some point in the lifetime of your app or company is going to go in and have to fix things by hand. If you have no schema and no constraints in the datastore itself, the odds of an error make things worse are far, far greater. We all know that's bad practice, but we all also know that sometimes you just have to do it for some reason. We also know that there will be times when someone is testing something on what they think is the local testing db but are actually connected to prod, and they do something experimental, and it screws things up. That's also a terrible thing to let happen, but we all know that it does. The second thing I'd like to point out that, much like sending email, any sufficiently useful application will, at some point, provide an API. And you're going to have to re-implement the backend validation again for that. So you know from the outset that if you are working on something you expect to be successful, you're going to have 3 access points. You're going to have to do the same work 3 times in 3 different places, unless you just decide to take the risk and use a db that doesn't have schema or constraints--which is a big risk. Doesn't make sense to me. Finally, I'd like to add to this good post that it's pretty easy to work in either direction with many ORMs. If you're comfortable with DBs, you can write out your migration scripts and use the ORM to generate models from it. If you prefer to work with an ORM, you can write the model and manage the migrations in the ORM. Either way, there's really not that much overhead compared to what you gain in safety and security and DRY. I'm not a DRY nazi, and when I'm prototyping, I'll repeat code anywhere I suspect a later iteration to diverge from the other instances of that code, and then refactor when I get closer to production. But when you're talking about database structure, I don't see how that can diverge, so it makes sense to me that you should never repeat yourself there. That's just asking for trouble. Do it once in the database, and do it right. Then all you have to do is trap database errors in your backend code and validate on the front end. That will be different for every interface, but it's a lot simpler and cleaner and safer than enforcing schemata and constraints in every codebase that accesses the data store.

by hibikir 10y ago

ianamartin 10y ago

Good points all around, and I fully agree. Just to elaborate a little on your third paragraph, this is incredibly important as an app matures. You might start out thinking you're only ever going to have one interface to a datastore, but the reality is that you always have at least two. Someone has the ability to go directly to the store itself and make changes. That's life. Someone at some point in the lifetime of your app or company is going to go in and have to fix things by hand. If you have no schema and no constraints in the datastore itself, the odds of an error make things worse are far, far greater. We all know that's bad practice, but we all also know that sometimes you just have to do it for some reason. We also know that there will be times when someone is testing something on what they think is the local testing db but are actually connected to prod, and they do something experimental, and it screws things up. That's also a terrible thing to let happen, but we all know that it does. The second thing I'd like to point out that, much like sending email, any sufficiently useful application will, at some point, provide an API. And you're going to have to re-implement the backend validation again for that. So you know from the outset that if you are working on something you expect to be successful, you're going to have 3 access points. You're going to have to do the same work 3 times in 3 different places, unless you just decide to take the risk and use a db that doesn't have schema or constraints--which is a big risk. Doesn't make sense to me. Finally, I'd like to add to this good post that it's pretty easy to work in either direction with many ORMs. If you're comfortable with DBs, you can write out your migration scripts and use the ORM to generate models from it. If you prefer to work with an ORM, you can write the model and manage the migrations in the ORM. Either way, there's really not that much overhead compared to what you gain in safety and security and DRY. I'm not a DRY nazi, and when I'm prototyping, I'll repeat code anywhere I suspect a later iteration to diverge from the other instances of that code, and then refactor when I get closer to production. But when you're talking about database structure, I don't see how that can diverge, so it makes sense to me that you should never repeat yourself there. That's just asking for trouble. Do it once in the database, and do it right. Then all you have to do is trap database errors in your backend code and validate on the front end. That will be different for every interface, but it's a lot simpler and cleaner and safer than enforcing schemata and constraints in every codebase that accesses the data store.