Databases always have a schema (2018)(utcc.utoronto.ca)
utcc.utoronto.ca
Databases always have a schema (2018)
https://utcc.utoronto.ca/~cks/space/blog/programming/DatabasesAlwaysSchemas
74 comments
Thanks, I almost posted the same. One of the things that makes our field so great is the ability to learn independently, be productive and accomplish cool things without needing formal study. But it's also one of it problems where people can be great engineers but as aren't necessarily familiar with long discusses concepts or ideas. Even CS education frequently won't cover them because they aren't practical nor the main focus of theory.
90 percent of us on here have an intuitive understanding of these concepts but not having formal language, definitions and knowledge of really causes less efficient communication and the frequent re-invent the wheel syndrome.
Maybe the answer is software engineering becoming a more popular major separate fom CS, or maybe its introducing more classes on it. Or maybe it's a separate major or learning track on data modeling, programming languages, applying them problem solving, validation, data communication.
As a random example, half the debates on static vs dynamic typing - is muddied by explicit vs more implicit (ala Haskell) typing.
Heck most of us probably couldn't really give an authoritative definition of a schema, but we know it when we see it.
Examples for something to be a schema does it have to be in a separate document or in code. Does it have to be explicit or can it be implicit? Does it have to be statically checkable or do run time semantic checks count? Is there a complexity bound on validating against a schema (syntactic vs semantic checking may take very different run times)? Can a schema be defined as "whatever data structure and content is need to make this program X over here run"? Why? Why not?
Not having rigorous definitions here makes conversations in this spaces usually end up as people reusing the same word with different interpretations / meanings and disagreeing without realizing why.
I don't know how we do better. And absolutely acknowledge I suffer from doing it just as much as anyone else.
90 percent of us on here have an intuitive understanding of these concepts but not having formal language, definitions and knowledge of really causes less efficient communication and the frequent re-invent the wheel syndrome.
Maybe the answer is software engineering becoming a more popular major separate fom CS, or maybe its introducing more classes on it. Or maybe it's a separate major or learning track on data modeling, programming languages, applying them problem solving, validation, data communication.
As a random example, half the debates on static vs dynamic typing - is muddied by explicit vs more implicit (ala Haskell) typing.
Heck most of us probably couldn't really give an authoritative definition of a schema, but we know it when we see it.
Examples for something to be a schema does it have to be in a separate document or in code. Does it have to be explicit or can it be implicit? Does it have to be statically checkable or do run time semantic checks count? Is there a complexity bound on validating against a schema (syntactic vs semantic checking may take very different run times)? Can a schema be defined as "whatever data structure and content is need to make this program X over here run"? Why? Why not?
Not having rigorous definitions here makes conversations in this spaces usually end up as people reusing the same word with different interpretations / meanings and disagreeing without realizing why.
I don't know how we do better. And absolutely acknowledge I suffer from doing it just as much as anyone else.
I would definitely prefer an approach where schema is explicit and defined in a minimum number of places. RDBMS/SQL schema do let you define schemas, however an important aspect of how SQL handles schemas which links back to their inflexibility is how they're defined at the level of a table, meaning a (possibly ordered) set of attributes.
A more flexible approach as taken by Datomic is to define schemas at the level of attributes, meaning an entity (table) does not have a schema, only attributes do. This requires attribute names to be within namespaces which can certainly be seen as equivalent to table names, but they don't have to, namespaces could also refer to an entire domain/subdomain/org/whatever.
Defining schema at the level of attributes provides a greater level of flexibility because client only declare/specify/depend-on a partial schema that only lists the attributes it needs. It also helps avoiding the "place oriented programming" approach of result sets, whereby attributes must be in a certain order, and if one does not have a value, you have to introduce NULL values.
A more flexible approach as taken by Datomic is to define schemas at the level of attributes, meaning an entity (table) does not have a schema, only attributes do. This requires attribute names to be within namespaces which can certainly be seen as equivalent to table names, but they don't have to, namespaces could also refer to an entire domain/subdomain/org/whatever.
Defining schema at the level of attributes provides a greater level of flexibility because client only declare/specify/depend-on a partial schema that only lists the attributes it needs. It also helps avoiding the "place oriented programming" approach of result sets, whereby attributes must be in a certain order, and if one does not have a value, you have to introduce NULL values.
Some domains or projects require or work better with "soft" schemas, at least initially. I always wanted "dynamic relational" where columns are optional and "create on write" (except for "ID", which is needed for row uniqueness guarantee).
If you query for a column that doesn't exist, instead of an error, you get a blank for that column (or null, depending on design decisions or settings).
But, one can gradually add rules as the requirements solidify. You can later add a rule that all rows of the Employee table must have a non-blank "last_name" column. You can have a rule/constraint that requires it for new rows, and perhaps have a "cleaning mode" to check existing rows. With enough rules/constraints, it acts pretty much like the current crop of RDBMS.
Dynamic relational could still use SQL, with some minor modifications to clarify intended compare types. (SQL has warts, but nothing better has arrived yet that's sufficiently better to justify tossing an established standard.)
It's not SQL versus No-SQL, it should be strong-typed SQL versus "soft" SQL. That way one doesn't have to toss all their SQL/RDBMS knowledge to get a dynamic RDBMS. The current dichotomy of choice is unnecessarily stark: it's either an RDBMS with SQL, or something very foreign. The No-SQL movement was driven by hardware & scaling needs, not query language needs.
If anyone is interested in starting up a dynamic relational company, I'd be glad to supply you with a draft spec for free.
But, one can gradually add rules as the requirements solidify. You can later add a rule that all rows of the Employee table must have a non-blank "last_name" column. You can have a rule/constraint that requires it for new rows, and perhaps have a "cleaning mode" to check existing rows. With enough rules/constraints, it acts pretty much like the current crop of RDBMS.
Dynamic relational could still use SQL, with some minor modifications to clarify intended compare types. (SQL has warts, but nothing better has arrived yet that's sufficiently better to justify tossing an established standard.)
It's not SQL versus No-SQL, it should be strong-typed SQL versus "soft" SQL. That way one doesn't have to toss all their SQL/RDBMS knowledge to get a dynamic RDBMS. The current dichotomy of choice is unnecessarily stark: it's either an RDBMS with SQL, or something very foreign. The No-SQL movement was driven by hardware & scaling needs, not query language needs.
If anyone is interested in starting up a dynamic relational company, I'd be glad to supply you with a draft spec for free.
You may have heard of it already, but if you haven't, I would recommend taking a look at RethinkDb: https://www.rethinkdb.com/docs/architecture/
It is a NoSQL Db, but has a lot of the features you're talking about. It doesn't have any form of "schema", but does allow for SQL style relational data. It definitely supports your "dynamic relational" data idea as stated above. The only issue is that any sort of data constraints need to be addressed at the outside of the Db itself.
It is a NoSQL Db, but has a lot of the features you're talking about. It doesn't have any form of "schema", but does allow for SQL style relational data. It definitely supports your "dynamic relational" data idea as stated above. The only issue is that any sort of data constraints need to be addressed at the outside of the Db itself.
Interesting, but the "outside" constraint processor issue could be a show-stopper. The "gradual tightening" of the DB is a key selling point of dynamic relational. Parts of it could be borrowed to make DR. And it's not clear how one queries it with SQL (or something very close). A translator layer would probably be needed.
Hmm I'm not familiar with Datomic but in SQL you can always break tables apart as it makes sense. NULL values are fine too if you've already decided that is a valid state in your schema but you can also use mapping tables instead of nullable columns.
>client only declare/specify/depend-on a partial schema that only lists the attributes it needs.
Can you describe what you mean here? It just sounds like specifying your selected columns to someone who is not familiar with the tool.
>client only declare/specify/depend-on a partial schema that only lists the attributes it needs.
Can you describe what you mean here? It just sounds like specifying your selected columns to someone who is not familiar with the tool.
Datomic is stritcly more(than SQL) between both worlds, you have half of your schema in the database(attributes) and the other half in clojure code (integrity checks, constraints, some validations, etc...).
I don't necessarily buy the two schema argument brought up in the article.
If you generate your model/domain based on the underlying database schema then you effectively have a single view of the world, one that will ideally blow up at compile time (if your language is statically typed) when the database schema changes.
It's not perfect, if someone changes the schema in production then you're SOL, but at least it brings some sanity to the table vs. NoSQL where you simply have no idea what the state of the world is until you run your program.
F# Type Providers are probably the gold standard wrt to binding schema to application code, but any code generation library will provide similar benefit.
If you generate your model/domain based on the underlying database schema then you effectively have a single view of the world, one that will ideally blow up at compile time (if your language is statically typed) when the database schema changes.
It's not perfect, if someone changes the schema in production then you're SOL, but at least it brings some sanity to the table vs. NoSQL where you simply have no idea what the state of the world is until you run your program.
F# Type Providers are probably the gold standard wrt to binding schema to application code, but any code generation library will provide similar benefit.
What I find is that, in practice, there are always multiple levels of schema.
You have a physical layer of schema that enforces data types, keys, relationships, things like that. This is where you typically enforce things like making sure that a price is always a number, not a string or a date or a BLOB, and also where you enforce that every product should have a price.
Then there's the logical schema. At minimum, your semantics end up existing here, but oftentimes there are also other written or unwritten rules that the DBMS itself isn't enforcing. It's possible to set up constraints to ensure that a one-to-many relationship is actually a one-to-at-least-one relationship, for example, but I don't see it happen often in practice - those rules are only enforced by the application. Or you'll often see structures where one field's value is interpreted differently depending on the value of another field - essentially, creating a union type in the database. This is typically done in the logical schema, not the physical schema, because most people find it annoying to write the constraints you need to ensure that the DBMS itself is enforcing those rules.
When you have multiple applications accessing the DB directly, it's common for all of them to implement a slightly different version of the logical schema. At which point you might have a plethora of different schemas co-existing within the same database. For better or for worse.
You have a physical layer of schema that enforces data types, keys, relationships, things like that. This is where you typically enforce things like making sure that a price is always a number, not a string or a date or a BLOB, and also where you enforce that every product should have a price.
Then there's the logical schema. At minimum, your semantics end up existing here, but oftentimes there are also other written or unwritten rules that the DBMS itself isn't enforcing. It's possible to set up constraints to ensure that a one-to-many relationship is actually a one-to-at-least-one relationship, for example, but I don't see it happen often in practice - those rules are only enforced by the application. Or you'll often see structures where one field's value is interpreted differently depending on the value of another field - essentially, creating a union type in the database. This is typically done in the logical schema, not the physical schema, because most people find it annoying to write the constraints you need to ensure that the DBMS itself is enforcing those rules.
When you have multiple applications accessing the DB directly, it's common for all of them to implement a slightly different version of the logical schema. At which point you might have a plethora of different schemas co-existing within the same database. For better or for worse.
Correction: you have a single authoritative view of the world, plus a secondary cached view of the world in a different language which probably doesn't perfectly map to the original one. Many people choose this approach, but it isn't without its own issues.
> you have a single authoritative view of the world, plus a secondary cached view of the world in a different language
Yes. OTOH, with NoSQL you have no authoritative schema; your code may allow for multiple different schemas, and your records may have multiple different schemas, and they could agree or disagree to any extent.
Yes. OTOH, with NoSQL you have no authoritative schema; your code may allow for multiple different schemas, and your records may have multiple different schemas, and they could agree or disagree to any extent.
You can still have an authoritative schema, it just doesn't live in the DB itself. It would have to be defined in the code, generally.
I would argue that it's not authoritative. Nothing guarantees that the records in the db match that schema; the closest you get to a guarantee is your diligence to 1) run a job mutating all records every time you change your in-code schema and 2) ensure that nothing but your latest application code can write to the db.
OTOH, `\d users` in PostgreSQL is authoritative; there cannot be a row that does not conform to the fields, types, and constraints listed there.
OTOH, `\d users` in PostgreSQL is authoritative; there cannot be a row that does not conform to the fields, types, and constraints listed there.
> I would argue that it's not authoritative.
I think the word you're looking for is "enforced". Where there are multiple schemas, one can certainly be the "authority", even if it's not enforced across all data.
I think the word you're looking for is "enforced". Where there are multiple schemas, one can certainly be the "authority", even if it's not enforced across all data.
Whatever terminology you use, every place you loop through records doing stuff with them, you'll either have to decide what to do with oddly-shaped records or implicitly decide to let exceptions occur at that point. Whereas if PostgreSQL tells you that `user` has a `email character varying(255) NOT NULL`, you can be sure that every `user` does. The only place you need a conditional related to that is when trying to insert an invalid record.
I think you might be assuming that there's a single dev team using the model/domain to do things with the data. Often, at least in big enterprise dev, there will be other teams doing things: Batch processing teams with their own apps, Data Service teams doing their own things. And they will all doing this likely outside the standard dev-centric data access. And then, what sometimes happens is that even a second team gets asked to innovate on the same data store, which can potentially bring in yet another code-to-db layer. (Granted this is ideally not a great place to be at, but due to time, cost. etc. it does happen).
I generally agree with you, though. In my ideal world, people working on the batch, offline processing, etc. would be integrating through a centralized API...
I generally agree with you, though. In my ideal world, people working on the batch, offline processing, etc. would be integrating through a centralized API...
I have only used Mongo with C# and the Mongo LINQ provider. When using C# you are using strongly typed IMongoQueryable<T> with strongly typed LINQ statements. The compiler enforces your schema.
One problem I had at jobs that used Mongo was that any other tools/systems that connected to the database had to be synchronized as well. It's not enough that this one system enforces a schema.
Classic example being that our code used visibility_score but a tool was adding a visibilityScore property to every document as part of a cron job calculation.
Classic example being that our code used visibility_score but a tool was adding a visibilityScore property to every document as part of a cron job calculation.
That comes down to organizational standards. When I worked with Mongo, all of the code was done in C# and the POCOs were in a shared internal Nuget package. There was never any harm in using older POCOs. You can add a catchall property to the POCOs that round trip any fields that it doesn’t know about.
The distinction between "database schema" and "data type" is the same impedance mismatch problem we've had for decades. You end up making an arbitrary transformation somewhere between your operational code and the persistence layer, at which time you have two different formats to represent your data. Any time you represent something twice, at least one of them is wrong, sooner or later.
The "schema" is the persistence-layer discussion of the contracts that programmers use to keep separate parts of the program consistent (even when they're divided up among developers, or with yourself over time). The persistence layer may use that information to provide additional services that it can't provide on a blob, like indexing. It's "natural" to do that for performance reasons: you want to lay stuff out on the disk, and limit network traffic. But in terms of code maintenance, that's optimization, and the root of all evil.
Few programming environments really take seriously the idea of eliminating that distinction. What I want is a language that pushes up notions like transactions and persistence right into the language itself, and let the compiler/VM take care of whether a piece of data is persistent or not. Ideally, I'd love a programming environment that could just be shut off at any moment and restore itself transparently, including unwinding that had become out of date. I gather that Inferno took that approach, but I haven't had a chance to use it.
The relevant bit of that is that it would eliminate the arbitrary distinction between "database schema" and the rest of your program. You'd use the same data specification for both, without a translator (or rather, with the translator completely hidden in the operating environment).
The "schema" is the persistence-layer discussion of the contracts that programmers use to keep separate parts of the program consistent (even when they're divided up among developers, or with yourself over time). The persistence layer may use that information to provide additional services that it can't provide on a blob, like indexing. It's "natural" to do that for performance reasons: you want to lay stuff out on the disk, and limit network traffic. But in terms of code maintenance, that's optimization, and the root of all evil.
Few programming environments really take seriously the idea of eliminating that distinction. What I want is a language that pushes up notions like transactions and persistence right into the language itself, and let the compiler/VM take care of whether a piece of data is persistent or not. Ideally, I'd love a programming environment that could just be shut off at any moment and restore itself transparently, including unwinding that had become out of date. I gather that Inferno took that approach, but I haven't had a chance to use it.
The relevant bit of that is that it would eliminate the arbitrary distinction between "database schema" and the rest of your program. You'd use the same data specification for both, without a translator (or rather, with the translator completely hidden in the operating environment).
Hard to understand what you're proposing... A language with a type system that brings its own serialization format (so you don't have to deal with the fact that SQL and friends have no good way to represent polymorphic data types such as enums or interfaces)? Or a language with a runtime that uses disk as main memory (this seems to be the implication from 'program can be terminated and resumed transparently')?
There are some languages, like Smalltalk and MUMPS, that try/tried to do this. In the 1990s and early 2000s you also had the emergence of object databases.
Databases like POET, Versant, Objectivity/DB, Matisse, Gemstone, ObjectStore, etc. surfaced together with the advent of object-oriented development in the late 1980s, and for a few years in the 1990s they seemed to be the future of databases. They were based on the idea that in a sufficiently powerful OO language, you could treat all your objects as transparently persistent, and use direct references to other objects to build your "object graph" instead of primary keys and joins. So you'd do something roughly like:
This glue had to rely extensively on lazy-loading proxy objects. For example, the employer would not be fetched until you actually called a method on the return value of person.getEmployer(). But it meant you could do something like person.getEmployer().getBoss().getAssistant() etc. and the entire graph would load as needed.
What was neat about object databases was precisely that you just wrote programs that interacted with state, and the state was automatically read and written in the background. All the best object databases had ACID transactions, requiring that you actually use transaction boundaries in the code, although I believe they typically allowed pure reads without an explicit transaction object. The database was also free to cache objects heavily, relying on a stream of invalidation messages from the server to keep the cache up to date.
These databases fell out of fashion for several reasons. SQL had already won back then, and these databases initially didn't have an SQL-like query language for ad-hoc queries — you had to write code to even look at your data. Whereas SQL lets you aggregate collections of data with joins and get flat tables out, the same code with an object database would typically rely on loops and recursive traversal of the object graph. OQL eventually materialized as an SQL competitor, but it was too little too late. There were other annoyances, such as the reliance on code munging or code generation, which was ultimately really specific to your programming language and platform.
Doing schema migrations without the DDL statements you get with SQL was another pain point. As I remember it, the usual way to do migrations in these databases was to define new fields and write the code to write the new fields from the old ones. Some of the databases, like Matisse, had GUI tools to let you work with schemas.
I still see object databases as something that could be done right today. For many of the applications we use relational databases and SQL for today, the data doesn't benefit greatly from being relational. Most web apps are in fact based on object-type graphs of entities, and rely on ORMs in order to bridge the famous "impedance mismatch" between relational data and objects. Document-oriented databases like MongoDB and Firebase are object databases without the transparent persistence.
I think Microsoft's LINQ had a lot of good ideas in terms of trying to marry the computer language and the data. In my current work, I use GraphQL heavily as way to exchange data between frontend and backend, which is a bit like LINQ if you squint hard enough, but the language integration isn't nearly as cohesive. For a current project, I'm experimenting with using JSON Schema to express the shape of the data (including validation of the persistent data), then generate the GraphQL schema from that. JSON Schema's data types map to GraphQL's, so there's no lossiness, although GraphQL is more restrictive on things like union types. As long as one sticks to a subset of JSON Schema, it goes really well.
It's a reasonably crutch until we get the next generation of tools, which I hope will be more similar to how object databases work.
Databases like POET, Versant, Objectivity/DB, Matisse, Gemstone, ObjectStore, etc. surfaced together with the advent of object-oriented development in the late 1980s, and for a few years in the 1990s they seemed to be the future of databases. They were based on the idea that in a sufficiently powerful OO language, you could treat all your objects as transparently persistent, and use direct references to other objects to build your "object graph" instead of primary keys and joins. So you'd do something roughly like:
tx = db.newTransaction()
person = Person.find(tx, "123")
person.setName("Bob")
person.getEmployer().setBoss(person)
tx.commit()
This glue had to rely extensively on lazy-loading proxy objects. For example, the employer would not be fetched until you actually called a method on the return value of person.getEmployer(). But it meant you could do something like person.getEmployer().getBoss().getAssistant() etc. and the entire graph would load as needed.
What was neat about object databases was precisely that you just wrote programs that interacted with state, and the state was automatically read and written in the background. All the best object databases had ACID transactions, requiring that you actually use transaction boundaries in the code, although I believe they typically allowed pure reads without an explicit transaction object. The database was also free to cache objects heavily, relying on a stream of invalidation messages from the server to keep the cache up to date.
These databases fell out of fashion for several reasons. SQL had already won back then, and these databases initially didn't have an SQL-like query language for ad-hoc queries — you had to write code to even look at your data. Whereas SQL lets you aggregate collections of data with joins and get flat tables out, the same code with an object database would typically rely on loops and recursive traversal of the object graph. OQL eventually materialized as an SQL competitor, but it was too little too late. There were other annoyances, such as the reliance on code munging or code generation, which was ultimately really specific to your programming language and platform.
Doing schema migrations without the DDL statements you get with SQL was another pain point. As I remember it, the usual way to do migrations in these databases was to define new fields and write the code to write the new fields from the old ones. Some of the databases, like Matisse, had GUI tools to let you work with schemas.
I still see object databases as something that could be done right today. For many of the applications we use relational databases and SQL for today, the data doesn't benefit greatly from being relational. Most web apps are in fact based on object-type graphs of entities, and rely on ORMs in order to bridge the famous "impedance mismatch" between relational data and objects. Document-oriented databases like MongoDB and Firebase are object databases without the transparent persistence.
I think Microsoft's LINQ had a lot of good ideas in terms of trying to marry the computer language and the data. In my current work, I use GraphQL heavily as way to exchange data between frontend and backend, which is a bit like LINQ if you squint hard enough, but the language integration isn't nearly as cohesive. For a current project, I'm experimenting with using JSON Schema to express the shape of the data (including validation of the persistent data), then generate the GraphQL schema from that. JSON Schema's data types map to GraphQL's, so there's no lossiness, although GraphQL is more restrictive on things like union types. As long as one sticks to a subset of JSON Schema, it goes really well.
It's a reasonably crutch until we get the next generation of tools, which I hope will be more similar to how object databases work.
I have been trying to find out for a long time now why more people don't use object databases, so thanks for this post.
One major reason that I didn't touch on in my comment was that there was no "MySQL of object databases" at that pivotal time when the web took off. And so they missed their moment.
The industry tends to spiral back constantly, with "worse is better" winning in the short run, but with old technologies slowly creeping back in new forms. So Postgres is finally able to supplant MySQL, despite being better all along. gRPC is basically CORBA/COM retrofitted for the web. Go is the new Turbo Pascal. MongoDB + JSON is the new (but not better) object database. And so on.
The industry tends to spiral back constantly, with "worse is better" winning in the short run, but with old technologies slowly creeping back in new forms. So Postgres is finally able to supplant MySQL, despite being better all along. gRPC is basically CORBA/COM retrofitted for the web. Go is the new Turbo Pascal. MongoDB + JSON is the new (but not better) object database. And so on.
> let the compiler/VM take care of whether a piece of data is persistent or not
I'm not sure if I understand what you mean. If a user starts a session and writes a value to a variable, how does the computer know whether the value should be temporary or permanent after the user ends the session?
I'm not sure if I understand what you mean. If a user starts a session and writes a value to a variable, how does the computer know whether the value should be temporary or permanent after the user ends the session?
Same as with variable scope in the language. Every variable is persistent until deleted or released. Such a language would need to manifest transactions (or some other behavior for consistency), but much of it would be handled with existing language concepts.
Now, this is just my opinion but I hate ORMs for several reasons. Highest on the list is that I fundamentally disagree with their reason for existence. They're designed to help manage the code schema vs db schema "problem" stated in the article.
The database schema is for the integrity and performance of data storage. The code schema is for the integrity of the business logic and api. Forcing both schema to solve both problems is a huge and IMO unnecessary pain. ORMs attempt to be all things to all use cases and you often end up with a lot of hoop jumping.
They're not totally disjoint, of course. Its the same data so it will naturally be in a similar format and how code and db interact will often inform how the schemas are designed. The take away is, use the best schema for the current use case and solve the mapping along the intersections.
My sense is that this is where a lot of the passion for NoSQL came from although its a bit misplaced.
The database schema is for the integrity and performance of data storage. The code schema is for the integrity of the business logic and api. Forcing both schema to solve both problems is a huge and IMO unnecessary pain. ORMs attempt to be all things to all use cases and you often end up with a lot of hoop jumping.
They're not totally disjoint, of course. Its the same data so it will naturally be in a similar format and how code and db interact will often inform how the schemas are designed. The take away is, use the best schema for the current use case and solve the mapping along the intersections.
My sense is that this is where a lot of the passion for NoSQL came from although its a bit misplaced.
I agree although I think the biggest problem with ORMs is they’re super hard to debug and reason about. You get all kinds of awful SQL out of them you might not expect
I've been using ORMs for nearly 20 years going back to WebObjects days.
Have never seen, not once, any ORM that doesn't make it trivial to print out the SQL to help with debugging the generated code. And I've never found it particular hard to reason about especially given many ORMs tend to stick to pretty vanilla SQL.
Have never seen, not once, any ORM that doesn't make it trivial to print out the SQL to help with debugging the generated code. And I've never found it particular hard to reason about especially given many ORMs tend to stick to pretty vanilla SQL.
Sure you can print it out, but if your data isn’t particularly normalised (which there are good reasons not to do that), it can get gross real quick. The alternative is just manually serialising/deserialising, which is verbose of course but a lot easier to debug.
This is a tired point and in a decade of using ORMs this hasn't been the case, ever.
As someone else mentioned, the fact that this is a tired point actually bolsters my argument; I’m not exactly the first person to run into this. Table rows and objects just aren’t the same thing and you get really leaky abstractions treating them as such.
Why do you think the comment is so common if it was never the case?
Missing knowledge / lack of documentation. Django for example has a couple ways to see the SQL, but the most useful one (generating it from a specific QuerySet) I can't find in the documentation - I only know about it from an answer on StackOverflow.
In my current case I have highly unstructured data stored as a jsonb in postgres with some basic fields for indexing. So the database doesn't have a schema and making many tables for each variation of the data would take an enormous amount of time.
> So the database doesn't have a schema
What you described IS the schema of the database.
Schema doesn't imply rigidness.
A schema can precisely define a wild lack of structure.
The reason it's important to have it is because it keeps you in the control seat.
What you described IS the schema of the database.
Schema doesn't imply rigidness.
A schema can precisely define a wild lack of structure.
The reason it's important to have it is because it keeps you in the control seat.
Another way of looking at it is that you have a schema whether you like it or not.
If I write some code to sum up account balances, it's going to expect a series of account objects, and they need to have some `.balance` field.
And those fields need to be decimals, because addition will choke if they're not.
Your code implicitly defines a schema, and it's utterly rigid because it will crash if the data structure is incorrect.
Or worse.
Imagine if some of the "balances" are actually some irrelevant field that happens to be a number, or some are missing because some of the JSON misspelled the field name and you skipped those records. Now the result you're processing is silently wrong, and who knows how far the corrupt data gets before someone catches it?
All schemas are enforced rigidly because that's how computers work. The difference is whether you want the DBMS to nag you while you're coding, or whether you'd rather be woken in the middle of the night by an angry customer or boss.
If I write some code to sum up account balances, it's going to expect a series of account objects, and they need to have some `.balance` field.
And those fields need to be decimals, because addition will choke if they're not.
Your code implicitly defines a schema, and it's utterly rigid because it will crash if the data structure is incorrect.
Or worse.
Imagine if some of the "balances" are actually some irrelevant field that happens to be a number, or some are missing because some of the JSON misspelled the field name and you skipped those records. Now the result you're processing is silently wrong, and who knows how far the corrupt data gets before someone catches it?
All schemas are enforced rigidly because that's how computers work. The difference is whether you want the DBMS to nag you while you're coding, or whether you'd rather be woken in the middle of the night by an angry customer or boss.
For many use cases we don't know the schema up front.
So it's irrelevant whether it exists or not because we don't know it.
So it's irrelevant whether it exists or not because we don't know it.
That's a great point, but I think the real issue there is that typical SQL DBMS's make it very hard to adjust the schema iteratively as you code. After all, if you're writing in a language like Java, you don't know what your classes look like to start with, but coders manage that by adjusting and refactoring them as they work.
Often the data in a database can outlive the application it was initially designed for.
Say you have a legacy application you want to rewrite.[1]
If the data is stored in an unstructured way, then the schema is in the code base of the legacy application, and will have to be reimplemented in the new application.[2]
However, if the schema is implemented by the database, then the application can be upgraded relatively easily.
[1] Or an example more suited to modern ears, say you just wrote an MVP and want to do a rewrite now that you have 50 customers on it.
[2] Often in a a bug for bug compatible manner.
Say you have a legacy application you want to rewrite.[1]
If the data is stored in an unstructured way, then the schema is in the code base of the legacy application, and will have to be reimplemented in the new application.[2]
However, if the schema is implemented by the database, then the application can be upgraded relatively easily.
[1] Or an example more suited to modern ears, say you just wrote an MVP and want to do a rewrite now that you have 50 customers on it.
[2] Often in a a bug for bug compatible manner.
I have an application written almost 20 years ago using unstructured storage (imagine a JSON blob but not JSON) and it still has code to handle the various schema changes that have happened over the years.
The data itself is what would be considered unstructured; basically many dozens of different document types. This is why I chose to store it this way. However, in retrospect, it would have been much better for maintenance, performance, quality, and code-size to have created every document type as a separate strongly-typed table.
The data itself is what would be considered unstructured; basically many dozens of different document types. This is why I chose to store it this way. However, in retrospect, it would have been much better for maintenance, performance, quality, and code-size to have created every document type as a separate strongly-typed table.
It sounds like you have an implicit schema, which the article addresses. Your "each variation of the data" is the gestalt of those implicit schemas. In the article's terms, the schema as built in the database itself is very minimal, but the schema represented by any code is much heavier. I'm not sure all of this is really useful distinctions to make, but it's what the article seems to be saying.
Pretty interesting that the author was upset someone said SQL = Schema and NoSQL = No Schema. It's usually pretty clear that the no schema part is usually in reference to no certainty in the schema of the data. Or I guess one could model it as a spectrum of certainty.
Actually now mulling over it some more, I see their point. It's just that their consideration of schema is more holistic than typical. I find people tend to use "schema" to refer to what's in the database, while in code people refer to it as a "model", and that a schema is just an implementation detail of a model.
Actually now mulling over it some more, I see their point. It's just that their consideration of schema is more holistic than typical. I find people tend to use "schema" to refer to what's in the database, while in code people refer to it as a "model", and that a schema is just an implementation detail of a model.
Hilarious because most NoSQL databases have a predefined schema.
This is a good take. I've been offended by this aspect of NoSQL for some time now, always using something like Mongoose to add schemas on top of it, but I'm starting to think that what I was really offended by was the lack of types inherent to plain JavaScript. It now occurs to me that a type system could be used to define the canonical form of a given DB schema.
I spent a year and a half at an enterprise Java shop, and there was a ton of work there put into maintaining and syncing the Java types and the SQL schema, even though that Java application was the only thing that would ever use that database.
I wonder what it would look like to have a schema definition system that was equally understood by the DB and the code, so you didn't have to pick one or the other or both.
I spent a year and a half at an enterprise Java shop, and there was a ton of work there put into maintaining and syncing the Java types and the SQL schema, even though that Java application was the only thing that would ever use that database.
I wonder what it would look like to have a schema definition system that was equally understood by the DB and the code, so you didn't have to pick one or the other or both.
I've been working on a kind of "universal type language" to address this problem. My goal is that it can have semantically identical data structures that function in multiple host languages, and then similarly on the DBMS. https://tenet-lang.org/
> that Java application was the only thing that would ever use that database
It's a mistake to make this assumption unless you've taken extreme measures to lock down and audit all access. It's pretty common for the entire dev team to have read access, and someone has inevitably built some ad hoc thing taking advantage of that.
It's a mistake to make this assumption unless you've taken extreme measures to lock down and audit all access. It's pretty common for the entire dev team to have read access, and someone has inevitably built some ad hoc thing taking advantage of that.
Perhaps I should've said "the only piece of infrastructure that would ever use that database". DB browsing software and even ad-hoc scripts don't need a perfect schema; you can usually just grok it by visual inspection
That's a problem when we break something ad hoc the founder wrote to reconcile the books once per month. Data attracts surprising and important uses.
You could use something like JOOQ[0], which (optionally) generates Java code based on your SQL schema.
[0] https://www.jooq.org/
[0] https://www.jooq.org/
We used hibernate, which does the opposite (Java classes/annotations -> SQL). You'd still have the same disadvantages: having an imperfect "clone" of your schema.
Can you elaborate what you mean by "imperfect"?
A Java string isn't precisely the same thing as a varchar(255), for example. Nor is an ArrayList (ordered) the same thing as a set of rows (unordered). Mapping between these slightly different sets of concepts usually works well enough, but it's an additional piece of disjointedness.
But it's unavoidable.
At some point you have to do that translation. And I would rather leave that up to the ORM where it fundamental to what they do then reimplement it.
At some point you have to do that translation. And I would rather leave that up to the ORM where it fundamental to what they do then reimplement it.
Very interesting. FWIW Martin Fowler, Pramod Sadalge et al have spoken about the implicit schema for NOSQL dbs from a design pattern perspective for quite a while now. Here is an early 2013 slide from him which is very informational - https://martinfowler.com/articles/schemaless/
He's correct that there's always a version of the schema in one's code — he's also correct that it's also nice to have a version of the schema in SQL, where certain properties can be checked & verified.
Wouldn't it be nice to have a schema DSL which enabled one to declare the schema in code, and have it checked at compile time? Sounds to me like that'd be the best of both worlds.
And client programs could just import that package as a library, so every would be on the same page …
Wouldn't it be nice to have a schema DSL which enabled one to declare the schema in code, and have it checked at compile time? Sounds to me like that'd be the best of both worlds.
And client programs could just import that package as a library, so every would be on the same page …
OCaml can do this with the PG'OCaml[0] library
0: http://pgocaml.forge.ocamlcore.org/
0: http://pgocaml.forge.ocamlcore.org/
Awesome link, thank you.
That's kind-of what some ORMs try to do. Either the learning curve is too high, the ORM is too buggy, or someone screws something up and ends up writing software that's worse that just doing it the old-fashioned way.
Honestly, I'd rather that the DSL just import adapters for stored procedures; or that the language itself is modified to be more SQL-like.
Honestly, I'd rather that the DSL just import adapters for stored procedures; or that the language itself is modified to be more SQL-like.
Isn't this what, for example, Django models are? Or even js simpleschema?
Django models only work insofar as all clients are interacting with the database through django. If even one other path to the database exists, the django models become unreliable as a source of truth. With the profliferation of microservices written in different languages, it would be nice to have a cross-language, shared-service solution as simple as django models. Something like protobufs or JSON might work well, but ultimately you still need to write a messaging and persistence layer for them, so it's not as simple as just writing a few model files.
Basically every ORM does it. Django models, hibernate (java) as well for just 2 examples.
As far as I know, those only check at runtime (or when generating code), not at compile time.
What you mean by compile time, when we're talking Django and nodejs?
Yeah, that's part of my point. JS and Python can't even do what we're talking about here.
A lot of ORMs do this. In .net Entity Framework, you can define your schema entirely with classes, properties, and attributes and if your database doesn't match on startup then it will fail.
As well you can do migrations to match your database schema to your current code model when it changes.
As well you can do migrations to match your database schema to your current code model when it changes.
In the context of this conversation, I assume we're talking about a single data store for all of an apps data needs. However, I'd argue in more modern application (newer) that one would design with a couple different data stores. using NoSQL for true data that can't be definied by a schema, and then the traditional RDMS when both ACID and schemas are needed. And then, there's several other types of data stores in between.
> your stored data always has a schema, unless your code neither reads nor writes it as anything except an opaque blob.
This is well-put. I'd go further: if there's no schema, you don't have data at all. A row with labelled email and name fields is data; a string of free-form input in which the user describe him/herself is not data. You might possibly be able to extract data from it, but if you do, you'll be building a set of labelled fields.
> You can tell that your code's implicit schema exists even with an SQL database, even if your code auto-reads the SQL schemas, by asking what happens if the DBAs decide to rename a bunch of tables and a bunch of fields in those tables, maybe dropping some and adding others
Maybe a quibble, but I'd say your code has partial knowledge of the relational db's schema, not that it duplicates the schema, just as it may have knowledge of the file system structure, the domain names of various servers, etc, and depend on that knowledge being accurate, without duplicating them entirely.
> In some ways NoSQL is more honest than SQL, because it tells you straight up that it's entirely up to your code to have and maintain a schema.
With a relational db it's not entirely up to your code to have and maintain a schema. Eg, your code can assume that every "user" record has an "email" field and that it's NOT NULL and always a string and always unique, assuming the db is set up to guarantee that. The part of your code that reads records can be sure of that, even if the part of your code that writes records is lax about checking (and therefore blows up a lot). With a NoSQL database, it's possible that some records have blank emails, or numeric emails, or don't have that field at all, or have that field named something different. Those things will happen unless you're very careful to ensure in your code that they don't, and also unless you're careful to never to let anything write to the database except your code.
I think a differentiating question is "what is it that guarantees all your records of type X have the same schema?" If you use a SQL database, the answer can be "the db". With NoSQL it might be "a combination of app logic, background jobs and manual intervention for weird cases" or "nothing".
In cases where you want to store JSON blobs, an RDBMS like PostgreSQL lets you have a JSON column. This is useful (eg) for cases where you need to capture some input now, and might get around to parsing it out into real data later - eg "we got these records from the legacy system and don't yet know if / whether we can import them".
Finally, although I wouldn't advocate moving complex application logic to the database, there are some validations that can only be reliably done by the database itself or by leaning on the database. Specifically, any validation that relies on the current contents of the database, such as "don't allow duplicate user names" (unique constraint) or "don't allow creating a comment for a post that was deleted" (foreign keys) or "don't allow overlapping reservations" (PostgreSQL exclusion constraint). Application code can do a read, check, and insert, but two threads may have a race condition and insert conflicting data. Application code can ask the db to lock while it does this, but that's leaning on the db. Or the database schema itself can guarantee this in a safe and performant way. But only if the database has a real schema.
This is well-put. I'd go further: if there's no schema, you don't have data at all. A row with labelled email and name fields is data; a string of free-form input in which the user describe him/herself is not data. You might possibly be able to extract data from it, but if you do, you'll be building a set of labelled fields.
> You can tell that your code's implicit schema exists even with an SQL database, even if your code auto-reads the SQL schemas, by asking what happens if the DBAs decide to rename a bunch of tables and a bunch of fields in those tables, maybe dropping some and adding others
Maybe a quibble, but I'd say your code has partial knowledge of the relational db's schema, not that it duplicates the schema, just as it may have knowledge of the file system structure, the domain names of various servers, etc, and depend on that knowledge being accurate, without duplicating them entirely.
> In some ways NoSQL is more honest than SQL, because it tells you straight up that it's entirely up to your code to have and maintain a schema.
With a relational db it's not entirely up to your code to have and maintain a schema. Eg, your code can assume that every "user" record has an "email" field and that it's NOT NULL and always a string and always unique, assuming the db is set up to guarantee that. The part of your code that reads records can be sure of that, even if the part of your code that writes records is lax about checking (and therefore blows up a lot). With a NoSQL database, it's possible that some records have blank emails, or numeric emails, or don't have that field at all, or have that field named something different. Those things will happen unless you're very careful to ensure in your code that they don't, and also unless you're careful to never to let anything write to the database except your code.
I think a differentiating question is "what is it that guarantees all your records of type X have the same schema?" If you use a SQL database, the answer can be "the db". With NoSQL it might be "a combination of app logic, background jobs and manual intervention for weird cases" or "nothing".
In cases where you want to store JSON blobs, an RDBMS like PostgreSQL lets you have a JSON column. This is useful (eg) for cases where you need to capture some input now, and might get around to parsing it out into real data later - eg "we got these records from the legacy system and don't yet know if / whether we can import them".
Finally, although I wouldn't advocate moving complex application logic to the database, there are some validations that can only be reliably done by the database itself or by leaning on the database. Specifically, any validation that relies on the current contents of the database, such as "don't allow duplicate user names" (unique constraint) or "don't allow creating a comment for a post that was deleted" (foreign keys) or "don't allow overlapping reservations" (PostgreSQL exclusion constraint). Application code can do a read, check, and insert, but two threads may have a race condition and insert conflicting data. Application code can ask the db to lock while it does this, but that's leaning on the db. Or the database schema itself can guarantee this in a safe and performant way. But only if the database has a real schema.
This blog sort of makes the definition of schema so broad that it ceases to have any real meaning. And it's a stupid argument to have anyway--whether or not something qualifies as being called schema. The point is how it functions, not what you want to call it.
I like to think of model code and sql schema as two branches of government in your data democracy. Where your code is roughly congress and your SQL schema is the supreme court. In a well-designed system, your code can do a lot of different things without running afoul of the courts. Code has quite a lot of freedom to operate on existing data and store what it needs for the future. But when there's a conflict, your SQL schema pops up and says, "No. You can't do that. That's in direct conflict of the stated goals of what this data is supposed to mean. Either don't do that, or go back and explicitly change what you mean."
Regardless of whether you want to call it schema or not, NoSQL is an extremely weak court of final appeal when it comes to deciding whether the code is obeying the intention of the data design. That is sometimes a problem and sometimes not.
In general, if you are dealing with data, you need type checking somewhere. Regardless of your opinion about programming languages, if you care about data integrity at all, it has to be checked for type correctness. Strong SQL schemas are far more important when the access layer is via dynamically typed or weakly typed languages. You can feel a lot better about a weaker schema if your language is statically typed. Sure, it's weaker and violates a certain separation of powers and flips the power dynamic to the code being final arbiter, but it's not always completely awful.
On the other hand, if your application code is written in javascript and your database has no enforced schematic constraints, your data is already corrupt. You just don't know it yet.
While theoretically we can account for weaker schemata in NoSQL data stores, the reality is that every useful dataset will eventually be accessed and written to by a client you didn't originally plan for. And that client isn't going to be aware of or play by the rules you intended to enforce in your code. So once again, your data is, in reality, already corrupt if you don't/can't enforce schema in your data store.
This isn't always a killer problem. There are a lot of cases where your data isn't all that important and this is an okay trade off. But pretending that application code is schema because you want to call it schema and it sort of does some of the same things so therefore schema is an energy field created by all living code that surrounds us, penetrates us, and binds our data together is just kind of bonkers.
I like to think of model code and sql schema as two branches of government in your data democracy. Where your code is roughly congress and your SQL schema is the supreme court. In a well-designed system, your code can do a lot of different things without running afoul of the courts. Code has quite a lot of freedom to operate on existing data and store what it needs for the future. But when there's a conflict, your SQL schema pops up and says, "No. You can't do that. That's in direct conflict of the stated goals of what this data is supposed to mean. Either don't do that, or go back and explicitly change what you mean."
Regardless of whether you want to call it schema or not, NoSQL is an extremely weak court of final appeal when it comes to deciding whether the code is obeying the intention of the data design. That is sometimes a problem and sometimes not.
In general, if you are dealing with data, you need type checking somewhere. Regardless of your opinion about programming languages, if you care about data integrity at all, it has to be checked for type correctness. Strong SQL schemas are far more important when the access layer is via dynamically typed or weakly typed languages. You can feel a lot better about a weaker schema if your language is statically typed. Sure, it's weaker and violates a certain separation of powers and flips the power dynamic to the code being final arbiter, but it's not always completely awful.
On the other hand, if your application code is written in javascript and your database has no enforced schematic constraints, your data is already corrupt. You just don't know it yet.
While theoretically we can account for weaker schemata in NoSQL data stores, the reality is that every useful dataset will eventually be accessed and written to by a client you didn't originally plan for. And that client isn't going to be aware of or play by the rules you intended to enforce in your code. So once again, your data is, in reality, already corrupt if you don't/can't enforce schema in your data store.
This isn't always a killer problem. There are a lot of cases where your data isn't all that important and this is an okay trade off. But pretending that application code is schema because you want to call it schema and it sort of does some of the same things so therefore schema is an energy field created by all living code that surrounds us, penetrates us, and binds our data together is just kind of bonkers.
1) Schema on write
The schema is enforced by the storage layer when data is persistet, optimized for defined use cases and fast. ACID. Typical RDBMS systems.
2) Schema on read
This is was big data query processing and data lakes are about. Data is not interpreted or given meaning until it is read from the store and put into context.
Just thought that might add value to the discussion.