7 ms·

> I miss that direct connection. The fast feedback. The lack of making grand plans. There's no date on this article, but it feels "prior to the MongoDB-is-webs

> I miss that direct connection. The fast feedback. The lack of making grand plans. There's no date on this article, but it feels "prior to the MongoDB-is-webscale memes" and thus slightly outdated? But, hey, I get where they're coming from. Personally, I used to be very much schema-first, make sure the data makes sense before even thinking about coding. Carefully deciding whether to use an INT data type where a BYTE would do. Then, it turned out that large swathes of my beautiful, perfect schemas remained unoccupied, while some clusters were heavily abused to store completely unrelated stuff. These days, my go-to solution is SQLite with two fields (well, three, if you count the implicit ROWID, which is invaluable for paging!): ID and Data, the latter being a JSONB blob. Then, some indexes specified by `json_extract` expressions, some clever NULL coalescing in the consuming code, resulting in a generally-better experience than before...

jrochkind1 2y ago
Oh good question on date essay was written -- put dates on your things on the internet people! Internet Archive has a crawl from today but no earlier; which doesn't mean it can't be earlier of course. My guess is it was written recently though.
- lexicality 2y ago
  created 14 hours ago https://github.com/jimmyhmiller/jimmyhmiller.github.io/commit/1e007eba3843eadaba97c1d713b7076580a616da https://github.com/jimmyhmiller/jimmyhmiller.github.io/commi...
  - __MatrixMan__ 2y ago
    But clearly in retrospect. It sounds like some of the things I was doing in 2009.
    - chatmasta 2y ago
      It includes a reference to Backbone and Knockout JS, which were released in 2010, so presumably it was around that era. The database, though, was probably much older...
      - __MatrixMan__ 2y ago
        Touche. It was "unnatural things to work around sql server's limitations" that rung a bell.
- stavros 2y ago
  At least we know it wasn't written after today!
  - shermantanktop 2y ago
    Falsehoods Programmers Believe…?
    - deleted 2y ago
      [deleted]
  - deleted 2y ago
    [deleted]
klysm 2y ago
I’m still in the think hard about the schema camp. I like to rely on the database to enforce constraints.
- ibejoeb 2y ago
  Yeah, a good database is pretty damn handy. Have you had the pleasure of blowing young minds by revealing that production-grade databases come with fully fledged authnz systems that you can just...use right out of the box?
  - scythmic_waves 2y ago
    Can you say more? I’m interested.
    - stavros 2y ago
      I guess they mean something like Postgres' row-level security: https://www.postgresql.org/docs/current/ddl-rowsecurity.html https://www.postgresql.org/docs/current/ddl-rowsecurity.html
    - Merad 2y ago
      Databases have pretty robust access controls to limit (a sql user's) access to tables, schemas, etc. Basic controls like being able to read but not write, and more advanced situations like being able to access data through a view or stored procedure without having direct access to the underlying tables. Those features aren't used often in modern app development where one app owns the database and any external access is routed through an API. They were much more commonly used in old school apps enterprise apps where many different teams and apps would all directly access a single db.
      - klysm 2y ago
        I think supabase leans quite heavily into this, although I haven’t used it myself. Row level security has been wonderful for multi tenancy in my experience though. I would highly recommend it.
hobs 2y ago
This is perfectly fine when you are driving some app that has a per-user experience that allows you to wrap up most of their experience in some blobs. However I would still advise people to use a third normal form - they help you, constraints help you, and often other sets of tooling have poor support for constraints on JSON. Scanning and updating every value because you need to update some subset sucks. You first point is super valid though - understanding the domain is very useful and you can get easily 10x the performance by designing with proper types involved, but importantly don't just build out the model before devs and customers have a use for anything, this is a classic mistake in my eyes (and then skipping cleanup when that is basically unused.) If you want to figure out your data model in depth beforehand there's nothing wrong with that... but you will still make tons of mistakes mistakes, lack of planning will require last minute fixes, and the evolution of the product will have your original planning gather dust.
- jsonis 2y ago
  > Scanning and updating every value because you need to update some subset sucks. Mirrors my experience exactly. Querying json can get complex to get info from the db. SQLite is kind of forgiving because sequences of queries (I mean query, modify in appliation code that fully supports json ie js, then query again) are less painful meaning it's less moprtant to do everytning in the database for performance reasons. But if you're trying to do everything in 1 query, I think you pay for it at application-writing time over and over.
jsonis 2y ago
> These days, my go-to solution is SQLite with two fields (well, three, if you count the implicit ROWID, which is invaluable for paging!): ID and Data, the latter being a JSONB blob. Really!? Are you building applications by chance or something else? Are you doing raw sql mostly or an ORM/ORM-like library? This surprises me because my experience dabbling in json fields for CRUD apps has been mostly trouble stemming from the lack of typechecks. SQLite's fluid type system haa been a nice middle ground for me personally. For reference my application layer is kysely/typescript.
- PreInternet01 2y ago
  > 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.
teaearlgraycold 2y ago
Byte vs. Int is premature optimization. But indexing, primary keys, join tables, normalization vs. denormalization, etc. are all important.
- RaftPeople 2y ago
  > Byte vs. Int is premature optimization I think you can only judge that by knowing the context, like the domain and the experience of the designer/dev within that domain. I looked at a DB once and thought "why are you spending effort to create these datatypes that use less storage, I'm used to just using an int and moving on." Then I looked at the volumes of transactions they were dealing with and I understood why.
  - teaearlgraycold 2y ago
    Absolutely
  - xp84 2y ago
    Deep down, the optimizer in me wants this to be true, but I'm having trouble seeing how this difference manifests in these days of super powerful devices and high bandwidth. I guess I just answered my own question though. Supposing there's a system which is slow and connected with very slow connectivity and still sending lots of data around, I guess there's your answer. An embedded system on the Mars Rover or something.
chrisldgk 2y ago
I actually love your approach and haven’t thought of that before. My problem with relational databases often stems from the fact that remodeling data types and schemas (which you often do as you build an application, whether or not you thought of a great schema beforehand) often comes with a lot of migration effort. Pairing your approach with a „version“ field where you can check which version of a schema this rows data is saved with would actually allow you to be incredibly flexible with saving your data while also being able to be (somewhat) sure that your fields schema matches what you’re expecting.
- layer8 2y ago
  > remodeling data types and schemas (which you often do as you build an application, whether or not you thought of a great schema beforehand) This is not my experience, it only happens rarely. I’d like to see an analysis of what causes schema changes that require nontrivial migrations.
  - zo1 2y ago
    Same here. If your entities are modelled mostly correctly you really don't have to worry about migrations that much. It's a bit of a red herring and convenient "problem" pushed by the NoSQL camp. On a relatively neat and well modelled DB, large migrations are usually when relationships change. E.g. One to many becomes a many to many. Really the biggest hurdle is managing the change control to ensure it aligns with you application. But that's a big problem with NoSQL DB deployments too. At this point I don't even want to hear what kind of crazy magic and "weird default and fallback" behavior the schema less NoSQL crowd employs. My pessimistic take is they just expose the DB onto GraphQL and make it front ends problem.
    - v-erne 2y ago
      >> If your entities are modelled mostly correctly you really don't have to worry about migrations that much I'm gonna take a wild guess here that you have never worked in unfamiliar domains (like lets say deep cargo shiping or subpremium loans) where your so called subject matter experts provided by client werent the sharpest people you could hope for and actually did not understand what they where doing for most of the time? Because I on the other hand am very familiar with such projects and doing schema overhaul third time in a row for production system is bread and butter for me. Schemaless systems is the only reason I'm still developer and not lumberjack.
gonzo41 2y ago
This is essentially just a data warehousing style schema. I love me a narrow db table. But I do try and get a schema that fits the business if I can.
someuser2345 2y ago
So, you're basically running DynamoDB on top of a sql server?
arnorhs 2y ago
I think they are referring to the fact that software development as a field has matured a lot and there are established practices and experienced developers all over who have been in those situations, so generally, these days, you don't see such code bases anymore. That is how I read it. Another possible reason you don't see those code bases anymore is the fact that such teams/companies don't have a competitive comp, so there are mostly junior devs or people who can't get a job at a more competent team that get hired in those places
nextaccountic 2y ago
why a separate id and rowid? why not just rowid and data?
- sebastiennight 2y ago
  "The rowid is implicit and autoassigned, but we want developer-friendly IDs." maybe But of course, the obvious solution is to have one table with just ROWID and data, and another table with the friendly IDs! If you time the insertions really well, then the ROWIDs in both tables with match and voilà.
  - nextaccountic 2y ago
    It's the same thing actually https://stackoverflow.com/questions/41928749/pros-cons-of-relying-on-rowid-instead-of-id-primary-key-sqlite https://stackoverflow.com/questions/41928749/pros-cons-of-re...

jrochkind1 2y ago

Oh good question on date essay was written -- put dates on your things on the internet people! Internet Archive has a crawl from today but no earlier; which doesn't mean it can't be earlier of course. My guess is it was written recently though.

lexicality 2y ago
created 14 hours ago https://github.com/jimmyhmiller/jimmyhmiller.github.io/commit/1e007eba3843eadaba97c1d713b7076580a616da https://github.com/jimmyhmiller/jimmyhmiller.github.io/commi...
- __MatrixMan__ 2y ago
  But clearly in retrospect. It sounds like some of the things I was doing in 2009.
  - chatmasta 2y ago
    It includes a reference to Backbone and Knockout JS, which were released in 2010, so presumably it was around that era. The database, though, was probably much older...
    - __MatrixMan__ 2y ago
      Touche. It was "unnatural things to work around sql server's limitations" that rung a bell.
stavros 2y ago
At least we know it wasn't written after today!
- shermantanktop 2y ago
  Falsehoods Programmers Believe…?
  - deleted 2y ago
    [deleted]
- deleted 2y ago
  [deleted]

klysm 2y ago

I’m still in the think hard about the schema camp. I like to rely on the database to enforce constraints.

ibejoeb 2y ago
Yeah, a good database is pretty damn handy. Have you had the pleasure of blowing young minds by revealing that production-grade databases come with fully fledged authnz systems that you can just...use right out of the box?
- scythmic_waves 2y ago
  Can you say more? I’m interested.
  - stavros 2y ago
    I guess they mean something like Postgres' row-level security: https://www.postgresql.org/docs/current/ddl-rowsecurity.html https://www.postgresql.org/docs/current/ddl-rowsecurity.html
  - Merad 2y ago
    Databases have pretty robust access controls to limit (a sql user's) access to tables, schemas, etc. Basic controls like being able to read but not write, and more advanced situations like being able to access data through a view or stored procedure without having direct access to the underlying tables. Those features aren't used often in modern app development where one app owns the database and any external access is routed through an API. They were much more commonly used in old school apps enterprise apps where many different teams and apps would all directly access a single db.
    - klysm 2y ago
      I think supabase leans quite heavily into this, although I haven’t used it myself. Row level security has been wonderful for multi tenancy in my experience though. I would highly recommend it.

hobs 2y ago

This is perfectly fine when you are driving some app that has a per-user experience that allows you to wrap up most of their experience in some blobs. However I would still advise people to use a third normal form - they help you, constraints help you, and often other sets of tooling have poor support for constraints on JSON. Scanning and updating every value because you need to update some subset sucks. You first point is super valid though - understanding the domain is very useful and you can get easily 10x the performance by designing with proper types involved, but importantly don't just build out the model before devs and customers have a use for anything, this is a classic mistake in my eyes (and then skipping cleanup when that is basically unused.) If you want to figure out your data model in depth beforehand there's nothing wrong with that... but you will still make tons of mistakes mistakes, lack of planning will require last minute fixes, and the evolution of the product will have your original planning gather dust.

jsonis 2y ago
> Scanning and updating every value because you need to update some subset sucks. Mirrors my experience exactly. Querying json can get complex to get info from the db. SQLite is kind of forgiving because sequences of queries (I mean query, modify in appliation code that fully supports json ie js, then query again) are less painful meaning it's less moprtant to do everytning in the database for performance reasons. But if you're trying to do everything in 1 query, I think you pay for it at application-writing time over and over.

jsonis 2y ago

> These days, my go-to solution is SQLite with two fields (well, three, if you count the implicit ROWID, which is invaluable for paging!): ID and Data, the latter being a JSONB blob. Really!? Are you building applications by chance or something else? Are you doing raw sql mostly or an ORM/ORM-like library? This surprises me because my experience dabbling in json fields for CRUD apps has been mostly trouble stemming from the lack of typechecks. SQLite's fluid type system haa been a nice middle ground for me personally. For reference my application layer is kysely/typescript.

PreInternet01 2y ago
> 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.

teaearlgraycold 2y ago

Byte vs. Int is premature optimization. But indexing, primary keys, join tables, normalization vs. denormalization, etc. are all important.

RaftPeople 2y ago
> Byte vs. Int is premature optimization I think you can only judge that by knowing the context, like the domain and the experience of the designer/dev within that domain. I looked at a DB once and thought "why are you spending effort to create these datatypes that use less storage, I'm used to just using an int and moving on." Then I looked at the volumes of transactions they were dealing with and I understood why.
- teaearlgraycold 2y ago
  Absolutely
- xp84 2y ago
  Deep down, the optimizer in me wants this to be true, but I'm having trouble seeing how this difference manifests in these days of super powerful devices and high bandwidth. I guess I just answered my own question though. Supposing there's a system which is slow and connected with very slow connectivity and still sending lots of data around, I guess there's your answer. An embedded system on the Mars Rover or something.

chrisldgk 2y ago

I actually love your approach and haven’t thought of that before. My problem with relational databases often stems from the fact that remodeling data types and schemas (which you often do as you build an application, whether or not you thought of a great schema beforehand) often comes with a lot of migration effort. Pairing your approach with a „version“ field where you can check which version of a schema this rows data is saved with would actually allow you to be incredibly flexible with saving your data while also being able to be (somewhat) sure that your fields schema matches what you’re expecting.

layer8 2y ago
> remodeling data types and schemas (which you often do as you build an application, whether or not you thought of a great schema beforehand) This is not my experience, it only happens rarely. I’d like to see an analysis of what causes schema changes that require nontrivial migrations.
- zo1 2y ago
  Same here. If your entities are modelled mostly correctly you really don't have to worry about migrations that much. It's a bit of a red herring and convenient "problem" pushed by the NoSQL camp. On a relatively neat and well modelled DB, large migrations are usually when relationships change. E.g. One to many becomes a many to many. Really the biggest hurdle is managing the change control to ensure it aligns with you application. But that's a big problem with NoSQL DB deployments too. At this point I don't even want to hear what kind of crazy magic and "weird default and fallback" behavior the schema less NoSQL crowd employs. My pessimistic take is they just expose the DB onto GraphQL and make it front ends problem.
  - v-erne 2y ago
    >> If your entities are modelled mostly correctly you really don't have to worry about migrations that much I'm gonna take a wild guess here that you have never worked in unfamiliar domains (like lets say deep cargo shiping or subpremium loans) where your so called subject matter experts provided by client werent the sharpest people you could hope for and actually did not understand what they where doing for most of the time? Because I on the other hand am very familiar with such projects and doing schema overhaul third time in a row for production system is bread and butter for me. Schemaless systems is the only reason I'm still developer and not lumberjack.

gonzo41 2y ago

This is essentially just a data warehousing style schema. I love me a narrow db table. But I do try and get a schema that fits the business if I can.

someuser2345 2y ago

So, you're basically running DynamoDB on top of a sql server?

arnorhs 2y ago

I think they are referring to the fact that software development as a field has matured a lot and there are established practices and experienced developers all over who have been in those situations, so generally, these days, you don't see such code bases anymore. That is how I read it. Another possible reason you don't see those code bases anymore is the fact that such teams/companies don't have a competitive comp, so there are mostly junior devs or people who can't get a job at a more competent team that get hired in those places

nextaccountic 2y ago

why a separate id and rowid? why not just rowid and data?

sebastiennight 2y ago
"The rowid is implicit and autoassigned, but we want developer-friendly IDs." maybe But of course, the obvious solution is to have one table with just ROWID and data, and another table with the friendly IDs! If you time the insertions really well, then the ROWIDs in both tables with match and voilà.
- nextaccountic 2y ago
  It's the same thing actually https://stackoverflow.com/questions/41928749/pros-cons-of-relying-on-rowid-instead-of-id-primary-key-sqlite https://stackoverflow.com/questions/41928749/pros-cons-of-re...