7 ms·

In projects I've been involved with, storing JSON in the database has often turned out to be a mistake. Django models create a well-defined self-documenting st

In projects I've been involved with, storing JSON in the database has often turned out to be a mistake. Django models create a well-defined self-documenting structure for your schema, are easy to evolve using migrations, and there's a wealth of tooling built on top. IMHO, these far outweigh the perceived convenience of simply storing some stuff in a JSON field. If you find yourself implementing your own validation and schema code for JSON fields, I'd say it's a sign that you should probably stop and migrate the data to Django models instead. There are some cases when storing JSON is fine, of course, but in my experience they are few and far between.

StavrosK 6y ago
100% agreed, JSON fields are good when you want to dump a bunch of data you don't care about the shape of, but if you need a schema, pull the data into actual fields.
- formerly_proven 6y ago
  For a database like postgres, or even for something like SQLite, this mostly becomes a distinction without a whole lot of difference, since the database can index and access JSON structures just like regular columns.
  - OJFord 6y ago
    Access, but not constrain or type-check. And it's just harder to use, I'm with it being generally a 'mistake' (in the 'you will regret this later' sense) - it's better than text, but I'm in the camp of as much structure as possible ('you will thank yourself later').
    - formerly_proven 6y ago
      > Access, but not constrain or type-check. You can create CHECK constraints involving JSON operations in postgres, which is the same way you'd enforce any constraint.
      - theptip 6y ago
        Manually, right? Django autogenerates all of the SQL schema for you on non-JSON fields. You could replicate that work, but that sounds tedious.
  - fabian2k 6y ago
    JSONB columns are incredibly useful if you have data that doesn't fit well into a strict relational schema. And they are very powerful in terms of query ability in Postgres, but still quite far from a conventional relational schema. Queries that act on the insides of a JSONB column are much harder to write than the equivalent conventional queries. They are also much, much slower in certain cases as the DB has to read the entire JSONB blob in many cases, and because they don't have proper statistics. The performance can range from slightly slower to completely pathological query plans that take ages. I'm a big fan of JSONB in Postgres, but it is no replacement for a relational schema, there are far too many problems you inflict on yourself if you try to use it like that.
  - mercer 6y ago
    As others have mentioned, it definitely does have downsides when it comes to writing queries and especially when using frameworks/libraries to interface with the db. But for the right job, JSONB columns are awesome!
  - wil421 6y ago
    What’s a real world example where you would choose to index the JSON as you said rather than putting them into the fields/schema.
    - SahAssar 6y ago
      Any time you'd otherwise use EAV.
      - collyw 6y ago
        EAV is considered an antipattern. While wouldn't rule out using it, it probably means it should be avoided if there is another solution.
        SahAssar 6y ago
        I've been working on a project using schema.org objects (100's of types of objects) in postgres in a graph/tree like structure. While structured the objects are not consistent enough for me to want separate schemas for them. Using JSONb in postgres allows me to index separate fields within the JSON while still having all the objects in a single graph. If I couldn't use JSONb with indexes (and triggers for custom foreign key checks) it'd be much harder to do this in in a relational database (and the data is relational, it just isn't consistent across all types). Perhaps this would be better in a Triplestore but there are a lot of features of postgres that are not available in them.
        formerly_proven 6y ago
        I used to believe in these database antipatterns (e.g. avoid EAV, avoid attribute tables, avoid denormalization, avoid overnormalization, avoid inheritance, avoid wide tables, only use synthetic keys, only use natural keys, never use autoincrement, always use autoincrement), but these days I'm almost certain all of them are wrong, because they are independent of business domain needs. Business domains have antipatterns for their data, data in general decidedly does not. For example, storing physical addresses is subject to a number of antipatterns, including stuff like "natural keys are not a good idea for this". GP cited EAV as an antipattern, yet it is one of the most reasonable approaches to user-defined fields (especially when suitably augmented), which are frequently a business requirement.
grenoire 6y ago
I think there was a bit of a confusion on the implied use case of our project. As another commenter pointed out, when the data is user supplied and is not always uniform in nature, creating migrations for each adjustment to the custom fields they can modify becomes virtually impossible. I ended up settling for JSON fields because I don't have to migrate the data or the database schema; I can just do a best-guess conversion for the data when it hits the UI, and supply some validation warnings if it really doesn't make sense (e.g. a non-date string somehow makes it into a "date" field).
Alex3917 6y ago
> If you find yourself implementing your own validation and schema code for JSON fields, I'd say it's a sign that you should probably stop and migrate the data to Django models instead. Don't agree. Where JSON fields with validators are useful is when you would otherwise have inherited models for different variants of the same type of thing. It's a lot easier to understand and query one model with 50 Cerberus (or whatever) schema validators than it is to understand and query 50 Django models. With the JSON approach it leaves one part of the codebase that's complicated to understand, but you mostly shouldn't need to understand it. Whereas when you have tons of different models and each one needs to be used in different places and is queried in different ways and has its own serializers, the mess tends to spread throughout the entire codebase.
- metafunctor 6y ago
  Like I said, there are some cases where using JSON fields makes sense. Good examples would be cases where you wish to retain the structure of the data, but don't have control over it. Sounds like you're describing a case like that.
  - Alex3917 6y ago
    I’ve used it more for generating forms, e.g. for when there are different types of questions or modules can ask or show the user —- multiple choice, free response, upload an image, watch a video, etc. So I’ve used JSON here both for storing the questions and responses, and then have used validators for each question type and response type. The downside is that it’s hard to do migrations. But in most cases you can just save the rendered HTML each user is shown and that covers you for having an audit trail, rather than maintaining backwards compatibility indefinitely. And if you do need to maintain compatibility forever, the schema validators enable that. Having done or seen almost the exact same use for JSON at multiple startups, for whatever downsides there are I think it’s 10x better than needing to try to understand a bunch of models, each with their own queries and logic.
- theptip 6y ago
  I am skeptical (with an open mind) of the JSONField pattern. We’ve used it in a few places where you have something like inheritance, so you want to handle all Event objects the same at the top level, and then dispatch differently based on Type. This works fine. My concern is that this is a pattern which experts advocate, but less precise/experienced engineers are likely to break when it comes time for a data migration. With normal SQL fields, if you change the schema you need to create a migration, and that gives an easy point to check for datamigrations too. If any commit can silently change the write-schema, it’s a lot harder to police. So I suspect if your team skews towards very experienced engineers the JSONField pattern probably gains in value. If that’s right it’s probably best to caveat the recommendation. Interested to know your experiences with schema changes though. I’m also interested to note that you are pointing out a general issue with Django I’ve experienced - the Models spread through the whole system. You can solve that in other ways, say by having a repository layer that used normal (non-JSONField) models and maps them through to a POPO. I’m not sure you _need_ the JSONField pattern to get the separation you’re looking for.
heliodor 6y ago
Can you please elaborate on the "wealth of tooling" part? Makes me wonder what I don't know...
- brylie 6y ago
  Some useful ORM extensions that I know of include: - Core Forms and Class-based views - https://django-extensions.readthedocs.io/en/latest/model_extensions.html https://django-extensions.readthedocs.io/en/latest/model_ext... - https://django-model-utils.readthedocs.io/en/latest/ https://django-model-utils.readthedocs.io/en/latest/ - https://django-queryable-properties.readthedocs.io/en/stable/ https://django-queryable-properties.readthedocs.io/en/stable... - https://django-mptt.readthedocs.io/en/latest/ https://django-mptt.readthedocs.io/en/latest/ - https://rsinger86.github.io/django-lifecycle/ https://rsinger86.github.io/django-lifecycle/
  - theptip 6y ago
    Also the ModelAdmin won’t be friendly for JSON data.
  - WD-42 6y ago
    This is the best comment I've read on HN in a year, thank you. Some great stuff in here I didn't know existed, will save me a ton of time and frustration in the future.
enraged_camel 6y ago
>>In projects I've been involved with, storing JSON in the database has often turned out to be a mistake. I mostly agree. The only JSON I store in the database is UI settings, because it's easy enough to parse it with JavaScript (and fall back in default if the JSON doesn't contain that prop). It also decouples your database schema from the UI, the latter of which can evolve much more rapidly. For everything else, having a schema is better.

StavrosK 6y ago

100% agreed, JSON fields are good when you want to dump a bunch of data you don't care about the shape of, but if you need a schema, pull the data into actual fields.

formerly_proven 6y ago
For a database like postgres, or even for something like SQLite, this mostly becomes a distinction without a whole lot of difference, since the database can index and access JSON structures just like regular columns.
- OJFord 6y ago
  Access, but not constrain or type-check. And it's just harder to use, I'm with it being generally a 'mistake' (in the 'you will regret this later' sense) - it's better than text, but I'm in the camp of as much structure as possible ('you will thank yourself later').
  - formerly_proven 6y ago
    > Access, but not constrain or type-check. You can create CHECK constraints involving JSON operations in postgres, which is the same way you'd enforce any constraint.
    - theptip 6y ago
      Manually, right? Django autogenerates all of the SQL schema for you on non-JSON fields. You could replicate that work, but that sounds tedious.
- fabian2k 6y ago
  JSONB columns are incredibly useful if you have data that doesn't fit well into a strict relational schema. And they are very powerful in terms of query ability in Postgres, but still quite far from a conventional relational schema. Queries that act on the insides of a JSONB column are much harder to write than the equivalent conventional queries. They are also much, much slower in certain cases as the DB has to read the entire JSONB blob in many cases, and because they don't have proper statistics. The performance can range from slightly slower to completely pathological query plans that take ages. I'm a big fan of JSONB in Postgres, but it is no replacement for a relational schema, there are far too many problems you inflict on yourself if you try to use it like that.
- mercer 6y ago
  As others have mentioned, it definitely does have downsides when it comes to writing queries and especially when using frameworks/libraries to interface with the db. But for the right job, JSONB columns are awesome!
- wil421 6y ago
  What’s a real world example where you would choose to index the JSON as you said rather than putting them into the fields/schema.
  - SahAssar 6y ago
    Any time you'd otherwise use EAV.
    - collyw 6y ago
      EAV is considered an antipattern. While wouldn't rule out using it, it probably means it should be avoided if there is another solution.
      - SahAssar 6y ago
        I've been working on a project using schema.org objects (100's of types of objects) in postgres in a graph/tree like structure. While structured the objects are not consistent enough for me to want separate schemas for them. Using JSONb in postgres allows me to index separate fields within the JSON while still having all the objects in a single graph. If I couldn't use JSONb with indexes (and triggers for custom foreign key checks) it'd be much harder to do this in in a relational database (and the data is relational, it just isn't consistent across all types). Perhaps this would be better in a Triplestore but there are a lot of features of postgres that are not available in them.
        formerly_proven 6y ago
        I used to believe in these database antipatterns (e.g. avoid EAV, avoid attribute tables, avoid denormalization, avoid overnormalization, avoid inheritance, avoid wide tables, only use synthetic keys, only use natural keys, never use autoincrement, always use autoincrement), but these days I'm almost certain all of them are wrong, because they are independent of business domain needs. Business domains have antipatterns for their data, data in general decidedly does not. For example, storing physical addresses is subject to a number of antipatterns, including stuff like "natural keys are not a good idea for this". GP cited EAV as an antipattern, yet it is one of the most reasonable approaches to user-defined fields (especially when suitably augmented), which are frequently a business requirement.

grenoire 6y ago

I think there was a bit of a confusion on the implied use case of our project. As another commenter pointed out, when the data is user supplied and is not always uniform in nature, creating migrations for each adjustment to the custom fields they can modify becomes virtually impossible. I ended up settling for JSON fields because I don't have to migrate the data or the database schema; I can just do a best-guess conversion for the data when it hits the UI, and supply some validation warnings if it really doesn't make sense (e.g. a non-date string somehow makes it into a "date" field).

Alex3917 6y ago

> If you find yourself implementing your own validation and schema code for JSON fields, I'd say it's a sign that you should probably stop and migrate the data to Django models instead. Don't agree. Where JSON fields with validators are useful is when you would otherwise have inherited models for different variants of the same type of thing. It's a lot easier to understand and query one model with 50 Cerberus (or whatever) schema validators than it is to understand and query 50 Django models. With the JSON approach it leaves one part of the codebase that's complicated to understand, but you mostly shouldn't need to understand it. Whereas when you have tons of different models and each one needs to be used in different places and is queried in different ways and has its own serializers, the mess tends to spread throughout the entire codebase.

metafunctor 6y ago
Like I said, there are some cases where using JSON fields makes sense. Good examples would be cases where you wish to retain the structure of the data, but don't have control over it. Sounds like you're describing a case like that.
- Alex3917 6y ago
  I’ve used it more for generating forms, e.g. for when there are different types of questions or modules can ask or show the user —- multiple choice, free response, upload an image, watch a video, etc. So I’ve used JSON here both for storing the questions and responses, and then have used validators for each question type and response type. The downside is that it’s hard to do migrations. But in most cases you can just save the rendered HTML each user is shown and that covers you for having an audit trail, rather than maintaining backwards compatibility indefinitely. And if you do need to maintain compatibility forever, the schema validators enable that. Having done or seen almost the exact same use for JSON at multiple startups, for whatever downsides there are I think it’s 10x better than needing to try to understand a bunch of models, each with their own queries and logic.
theptip 6y ago
I am skeptical (with an open mind) of the JSONField pattern. We’ve used it in a few places where you have something like inheritance, so you want to handle all Event objects the same at the top level, and then dispatch differently based on Type. This works fine. My concern is that this is a pattern which experts advocate, but less precise/experienced engineers are likely to break when it comes time for a data migration. With normal SQL fields, if you change the schema you need to create a migration, and that gives an easy point to check for datamigrations too. If any commit can silently change the write-schema, it’s a lot harder to police. So I suspect if your team skews towards very experienced engineers the JSONField pattern probably gains in value. If that’s right it’s probably best to caveat the recommendation. Interested to know your experiences with schema changes though. I’m also interested to note that you are pointing out a general issue with Django I’ve experienced - the Models spread through the whole system. You can solve that in other ways, say by having a repository layer that used normal (non-JSONField) models and maps them through to a POPO. I’m not sure you _need_ the JSONField pattern to get the separation you’re looking for.

heliodor 6y ago

Can you please elaborate on the "wealth of tooling" part? Makes me wonder what I don't know...

brylie 6y ago
Some useful ORM extensions that I know of include: - Core Forms and Class-based views - https://django-extensions.readthedocs.io/en/latest/model_extensions.html https://django-extensions.readthedocs.io/en/latest/model_ext... - https://django-model-utils.readthedocs.io/en/latest/ https://django-model-utils.readthedocs.io/en/latest/ - https://django-queryable-properties.readthedocs.io/en/stable/ https://django-queryable-properties.readthedocs.io/en/stable... - https://django-mptt.readthedocs.io/en/latest/ https://django-mptt.readthedocs.io/en/latest/ - https://rsinger86.github.io/django-lifecycle/ https://rsinger86.github.io/django-lifecycle/
- theptip 6y ago
  Also the ModelAdmin won’t be friendly for JSON data.
- WD-42 6y ago
  This is the best comment I've read on HN in a year, thank you. Some great stuff in here I didn't know existed, will save me a ton of time and frustration in the future.

enraged_camel 6y ago

>>In projects I've been involved with, storing JSON in the database has often turned out to be a mistake. I mostly agree. The only JSON I store in the database is UI settings, because it's easy enough to parse it with JavaScript (and fall back in default if the JSON doesn't contain that prop). It also decouples your database schema from the UI, the latter of which can evolve much more rapidly. For everything else, having a schema is better.