8 ms·

> my experience dabbling in json fields for CRUD apps has been mostly trouble stemming from the lack of typechecks Well, you move the type checks from the dat

> my experience dabbling in json fields for CRUD apps has been mostly trouble stemming from the lack of typechecks Well, you move the type checks from the database to the app, effectively, which is not a new idea by any means (and a bad idea in many cases), but with JSON, it can actually work out nicely-ish, as long as there are no significant relationships between tables. Practical example: I recently wrote my own SMTP server (bad idea!), mostly to be able to control spam (even worse idea! don't listen to me!). Initially, I thought I would be really interested in remote IPs, reverse DNS domains, and whatever was claimed in the (E)HLO. So, I designed my initial database around those concepts. Turns out, after like half a million session records: I'm much more interested in things like the Azure tenant ID, the Google 'groups' ID, the HTML body tag fingerprint, and other data points. Fortunately, my session database is just 'JSON(B) in a single table', so I was able to add those additional fields without the need for any migrations. And SQLite's `json_extract` makes adding indexes after-the-fact super-easy. Of course, these additional fields need to be explicitly nullable, and I need to skip processing based on them if they're absent, but fortunately modern C# makes that easy as well. And, no, no need for an ORM, except `JsonSerializer.Deserialize<T>`... (And yeah, all of this is just a horrible hack, but one that seems surprisingly resilient so far, but YMMV)

throwup238 2y ago
> And, no, no need for an ORM, except `JsonSerializer.Deserialize<T>`... (And yeah, all of this is just a horrible hack, but one that seems surprisingly resilient so far, but YMMV) I do the same thing with serde_json in Rust for a desktop app sqlitedb and it works great so +1 on that technique. In Rust you can also tell serde to ignore unknown fields and use individual view structs to deserialize part of the JSON instead of the whole thing and use string references to make it zero copy.
jvans 2y ago
why is a migration such a burden in that scenario
- immibis 2y ago
  because they put everything in JSON. Migration means running a script to parse and edit each JSON item instead of letting the database do database things automatically.
Vampiero 2y ago
> Well, you move the type checks from the database to the app, effectively, which is not a new idea by any means (and a bad idea in many cases), but with JSON, it can actually work out nicely-ish, as long as there are no significant relationships between tables. That way you're throwing away 50% of the reason you use a relational database in the first place. Has it occurred to you that MongoDB exists? Also I don't understand why you're afraid of migrations, especially since you're the only developer on your own SMTP server.
- throwup238 2y ago
  > That way you're throwing away 50% of the reason you use a relational database in the first place. Has it occurred to you that MongoDB exists? Did you miss that he’s using sqlite? The dev experience with a sqlitedb is way better than running yet another service, especially for personal projects. Sqlite is used just as much as an application file format as it is a relational database.
- PreInternet01 2y ago
  > Has it occurred to you that MongoDB exists? My original comment started with "but it feels "prior to the MongoDB-is-webscale memes"" So, care to take another guess? And, while we're here, does MongoDB run fully in-process these days? And/or allow easy pagination by ROWID?
  - CRConrad 2y ago
    > My original comment started with "but it feels "prior to the MongoDB-is-webscale memes"" Which feels off by six generations or so of memes. It feels prior to "memes" existing in the first place (at least this modern iteration, pics with captions in them; not Dawkins' original verion). I'd guess it is, ironically, chronologically closer to, well, your username here.
- throwaway173738 2y ago
  How does MongoDB handle someone pulling the power cord out of the server? Because that’s another reason to use something like SQLite, and it often gets used in embedded systems.
Izkata 2y ago
> Fortunately, my session database is just 'JSON(B) in a single table', so I was able to add those additional fields without the need for any migrations. And SQLite's `json_extract` makes adding indexes after-the-fact super-easy. Our solution for a similar situation involving semi-structured data (in postgres) was to double it up: put all the json we send/receive with a vendor into a json field, then anything we actually need to work on gets extracted into regular table/columns. We get all the safety/performance guarantees the database would normally give us, plus historical data for debugging or to extract into a new column if we now need it. The one thing we had to monitor in code reviews was to never use the json field directly for functionality.
- jsonis 2y ago
  This is exactly what I've tried (and failed at) doing! Can I ask how you handle normalization from vendor data when it contains relationships and multilevel nesting? How do you know when to create a new child table, and which ones to create, and their relationships etc. I haven't found a good balance yet.
  - immibis 2y ago
    Odd-shaped miscellaneous data that you only need to retrieve is a good candidate for a JSON field. Once you're heavily using some piece of data, or if you need to index it (which means you are heavily using it), you should insert the data in the database "properly". If some vendor is giving you a list of categories you don't care about, there's no need to make a vendor categories table and a many-to-many link table until you actually need them. The point is that putting data properly in the database lets you use database features on it and get database performance.
  - Izkata 2y ago
    Basically what the other reply said - handle it the same as you would any complex data. You just don't need to handle all of the json immediately, only the parts you plan on using for the moment.
deleted 2y ago
[deleted]
collinmanderson 2y ago
> I recently wrote my own SMTP server (bad idea!) Recently I’ve been tempted to make an SMTP server that translates emails into a web-hook. Please tell me more horror stories so that I might be convinced not do it.
hyperadvanced 2y ago
“Without the need for any migrations” seems like a weird one to me. Of all of the things I don’t like having to do, migrations are pretty low on my list - far below any scripting-in-prod or applying hacks or even just rudimentary business logic changes. Granted, I used to fear them a lot back when I was expected to apply them by hand rather than use CICD and testing to validate/apply them
immibis 2y ago
> Fortunately, my session database is just 'JSON(B) in a single table', so I was able to add those additional fields without the need for any migrations. > And SQLite's `json_extract` makes adding indexes after-the-fact super-easy. That's a migration. > Of course, these additional fields need to be explicitly nullable, and I need to skip processing based on them if they're absent That's an effect of not migrating - having to process null and absent fields instead of just null fields. After doing more of these, you'll run into the same thing that made people stop using NoSQL databases: with no schema, your code has to parse all previous versions of the data format and they probably aren't even well-documented. While an RDBMS can just set the new column to null in existing rows.

throwup238 2y ago

> And, no, no need for an ORM, except `JsonSerializer.Deserialize<T>`... (And yeah, all of this is just a horrible hack, but one that seems surprisingly resilient so far, but YMMV) I do the same thing with serde_json in Rust for a desktop app sqlitedb and it works great so +1 on that technique. In Rust you can also tell serde to ignore unknown fields and use individual view structs to deserialize part of the JSON instead of the whole thing and use string references to make it zero copy.

jvans 2y ago

why is a migration such a burden in that scenario

immibis 2y ago
because they put everything in JSON. Migration means running a script to parse and edit each JSON item instead of letting the database do database things automatically.

Vampiero 2y ago

> Well, you move the type checks from the database to the app, effectively, which is not a new idea by any means (and a bad idea in many cases), but with JSON, it can actually work out nicely-ish, as long as there are no significant relationships between tables. That way you're throwing away 50% of the reason you use a relational database in the first place. Has it occurred to you that MongoDB exists? Also I don't understand why you're afraid of migrations, especially since you're the only developer on your own SMTP server.

throwup238 2y ago
> That way you're throwing away 50% of the reason you use a relational database in the first place. Has it occurred to you that MongoDB exists? Did you miss that he’s using sqlite? The dev experience with a sqlitedb is way better than running yet another service, especially for personal projects. Sqlite is used just as much as an application file format as it is a relational database.
PreInternet01 2y ago
> Has it occurred to you that MongoDB exists? My original comment started with "but it feels "prior to the MongoDB-is-webscale memes"" So, care to take another guess? And, while we're here, does MongoDB run fully in-process these days? And/or allow easy pagination by ROWID?
- CRConrad 2y ago
  > My original comment started with "but it feels "prior to the MongoDB-is-webscale memes"" Which feels off by six generations or so of memes. It feels prior to "memes" existing in the first place (at least this modern iteration, pics with captions in them; not Dawkins' original verion). I'd guess it is, ironically, chronologically closer to, well, your username here.
throwaway173738 2y ago
How does MongoDB handle someone pulling the power cord out of the server? Because that’s another reason to use something like SQLite, and it often gets used in embedded systems.

Izkata 2y ago

> Fortunately, my session database is just 'JSON(B) in a single table', so I was able to add those additional fields without the need for any migrations. And SQLite's `json_extract` makes adding indexes after-the-fact super-easy. Our solution for a similar situation involving semi-structured data (in postgres) was to double it up: put all the json we send/receive with a vendor into a json field, then anything we actually need to work on gets extracted into regular table/columns. We get all the safety/performance guarantees the database would normally give us, plus historical data for debugging or to extract into a new column if we now need it. The one thing we had to monitor in code reviews was to never use the json field directly for functionality.

jsonis 2y ago
This is exactly what I've tried (and failed at) doing! Can I ask how you handle normalization from vendor data when it contains relationships and multilevel nesting? How do you know when to create a new child table, and which ones to create, and their relationships etc. I haven't found a good balance yet.
- immibis 2y ago
  Odd-shaped miscellaneous data that you only need to retrieve is a good candidate for a JSON field. Once you're heavily using some piece of data, or if you need to index it (which means you are heavily using it), you should insert the data in the database "properly". If some vendor is giving you a list of categories you don't care about, there's no need to make a vendor categories table and a many-to-many link table until you actually need them. The point is that putting data properly in the database lets you use database features on it and get database performance.
- Izkata 2y ago
  Basically what the other reply said - handle it the same as you would any complex data. You just don't need to handle all of the json immediately, only the parts you plan on using for the moment.

deleted 2y ago

[deleted]

collinmanderson 2y ago

> I recently wrote my own SMTP server (bad idea!) Recently I’ve been tempted to make an SMTP server that translates emails into a web-hook. Please tell me more horror stories so that I might be convinced not do it.

hyperadvanced 2y ago

“Without the need for any migrations” seems like a weird one to me. Of all of the things I don’t like having to do, migrations are pretty low on my list - far below any scripting-in-prod or applying hacks or even just rudimentary business logic changes. Granted, I used to fear them a lot back when I was expected to apply them by hand rather than use CICD and testing to validate/apply them

immibis 2y ago

> Fortunately, my session database is just 'JSON(B) in a single table', so I was able to add those additional fields without the need for any migrations. > And SQLite's `json_extract` makes adding indexes after-the-fact super-easy. That's a migration. > Of course, these additional fields need to be explicitly nullable, and I need to skip processing based on them if they're absent That's an effect of not migrating - having to process null and absent fields instead of just null fields. After doing more of these, you'll run into the same thing that made people stop using NoSQL databases: with no schema, your code has to parse all previous versions of the data format and they probably aren't even well-documented. While an RDBMS can just set the new column to null in existing rows.