Databases always have a schema (2018)

Re:Databases always have a schema (2018) by _bxg1 on Monday February 25, 2019 @10:49AM (#19246436)

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.

[ Parent | on HN ]

Re:Databases always have a schema (2018) by ben509 on Monday February 25, 2019 @12:30PM (#19247419)

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/

[ Parent | on HN ]

Re:Databases always have a schema (2018) by erik_seaberg on Monday February 25, 2019 @11:30AM (#19246752)

> 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.

[ Parent | on HN ]

Re:Databases always have a schema (2018) by _bxg1 on Monday February 25, 2019 @11:57AM (#19247014)

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

[ Parent | on HN ]

Re:Databases always have a schema (2018) by erik_seaberg on Wednesday February 27, 2019 @12:17PM (#19264840)

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.

[ Parent | on HN ]

Re:Databases always have a schema (2018) by Arcsech on Monday February 25, 2019 @10:55AM (#19246489)

You could use something like JOOQ[0], which (optionally) generates Java code based on your SQL schema.

[0] https://www.jooq.org/

[ Parent | on HN ]

Re:Databases always have a schema (2018) by _bxg1 on Monday February 25, 2019 @10:58AM (#19246511)

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.

[ Parent | on HN ]

Re:Databases always have a schema (2018) by Jyaif on Monday February 25, 2019 @11:14AM (#19246619)

Can you elaborate what you mean by "imperfect"?

[ Parent | on HN ]

Re:Databases always have a schema (2018) by _bxg1 on Monday February 25, 2019 @11:40AM (#19246825)

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.

[ Parent | on HN ]

Re:Databases always have a schema (2018) by threeseed on Monday February 25, 2019 @05:55PM (#19250291)

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.

[ Parent | on HN ]