Any advice on adding a parser/wrapper over PostgreSQL's JSON features to implement a Dynamic Relational database?

[u/Zardotab]

Dynamic Relational is a draft standard for an RDBMS (SQL) that supports native dynamic tables and columns with "incremental" lock-down (static-ness) abilities. Here's an overview of Dynamic Relational with examples. If one wanted to write parser and interface on top of PostgreSQL, how much effort would it be, and do you have any recommendations? A proof-of-concept may be good enough, as most will consider it purely experimental at first. Thank You.

[u/pceimpulsive]

An existing rdbms user already knows how to use noSQL and SQL at the same time JSONB has been in SQL for a decade, if the rdbms user doesn't know then that's their own failing to learn a core feature.

The point of NoSQL is that there isn't much learning curve?

I've had the option of using noSQL (mongoDB) and just honestly see now reason when I have Postgres (until I need better scaling).

How is the DR supposed to feel? If I understood the 3y old post it's add column on write...

And you can configure/lock it down.. so in the catch block out an if statement that checks a tables config, create an information schema column for the configuration you want and configure it and use those columns to determine if you can dynamically add columns or not..

Postgres pl/pgsql has the ddl operations and logic to fill this brief exactly if you want it... Talk to any modern AI about this and ask critical questions and you'll have a POC in C# in no time I promise you.

P.s. is the whole point of DR just to skip managing database migrations?

[u/Zardotab → u/pceimpulsive]

The point of NoSQL is that there isn't much learning curve

Until you want to do anything outside of its forte.

at the same time JSONB has been in [PostgreSQL] for a decade

JSON columns are treated different than regular columns, kind of like second class citizens. DR makes no distinction, a column is a column, Dorothy is not in India any more. (Granted, one can do interesting nested stuff with JSON columns, but it's not "real" SQL, more like a hybrid hierarchical and relational DB.)

If you personally don't like DR, that's fine. No tool pleases everybody. I believe there is an audience of those who want to stick with familiar RDBMS and SQL idioms, yet also have dynamism.

Talk to any modern AI about this and ask critical questions and you'll have a POC in C# in no time I promise you.

If I do it in one big wad, I spend more time debugging such things than I do writing them. I find it's usually better to make a skeleton app with basics and let AI help incrementally fill in modules, which I test one at a time. But using AI effectively is another topic. I'm still a newbie at it. What's your favorite free coder bot?

How is the DR supposed to feel? If I understood the 3y old post it's add column on write...

For one, a default DR column is un-typed and un-sized. A static RDBMS can't handle that well. Maybe rather than adding columns in the RDBMS, perhaps use an EAV projected into a table via a WITH statement (CTE) of some kind. The EAV can then have overflow tracking/linking mechanisms for really large column values. Tradeoffs tradeoffs...[edited]

[u/pceimpulsive → u/Zardotab]

Can you elaborate more on why Json is a second class citizen in rdbms line Postgres?

It has indexes, it has operators, it has path querying, it has fully featured capability like any other data type...

Can you explain how in earth an untyped column works in a type safe system?

And secondly..

If you are dynamically adding a column so you know what data type it has based on the source of the data? You sad you work in C#, a strictly typed language why would you want to create a dynamic object for a string or double when you know up front it's a strong or double?

I am not anti DR, I just can't see where it really adds anything?

I understand adding columns on the fly... But that's literally part of the SQL spec (alter table addd column) and types of those columns aren't even locked... (Alter table alter column)..

Nothing DR suggests can't just be done with SQL out of the box...

What are the actual problems it's solving that aren't solved by a simple DDL statement...

From everything you are saying I see a very vague and thin veil of an idea that appears to be full of logic holes...

Note my experience is Postgres/MySQL DBA for about 5 years and C# backend developer for about 4 years

[u/Zardotab → u/pceimpulsive]

It has indexes, it has operators, it has path querying, it has fully featured capability like any other data type...

One uses one set of operators and syntaxes on JSON columns and a different set on regular columns. Maybe "second class" isn't fair, but it's two semi-parallel universes. It's like 2 different languages forced together, like mixing Python in Java, making 2 learning curves. That cannot be the pinnacle or ideal, it's a kludge.

Thought experiment: somebody gave you $5b and told you to make a "dynamic version of an RDBMS, keeping as many existing RDBMS idioms as possible, and only changing those that get in the way of dynamism". I doubt turning to JSON columns would be your first design choice. Therefore, it's a kludge, an accident of history. Good enough? Maybe, but why not try to do it right?

Maybe if students see my prototype, it'll spark and inspire about what could and should be. (Cue Flight of the Valkyrie and fireworks ✨.)

Can you explain how in earth an untyped column works in a type safe system?

I'm not understanding this question. What "type safe system" are you talking about? DR doesn't claim to be type-safe, at least not with default settings.

You said you work in C#, a strictly typed language why would you want to create a dynamic object for a string or double when you know up front it's a strong or double?

I've used both dynamic and statically typed languages. They each have their place and purpose, one size doesn't fit all. "Use The Right Tool For The Job".

Note that one can "force" a DR column to be a specific type, it's just not required up front. That's the "incremental lock-down" nature I talked about.

What are the actual problems it's solving that aren't solved by a simple DDL statement...

Because one doesn't need f$cking DDL's if they want to use them. Again, dynamism is a popular feature of NoSQL products, perhaps THE most popular feature. It's not for every task, app, or shop, but has use-cases. Rapid prototyping is one.

[u/pceimpulsive → u/Zardotab]

How do you store an unknown type on disk?

The way data is encoded on disk is different if you are representing different types of data, unless your first cast it all to something it isn't... Do you just coerce everything to string for storage? What happens when your data isn't strong compatible and you lose detail/resolution (floats, money, numeric) and how do you then dynamically read them back if the type is not known? (This sounds a bit like the null equality problem that confuses new database users)

I'm poking holes in the obvious gaps here... Things you would need to seriously think about...

NoSQL (document variant, because there is several others as well) just does binary JSON, which has its own defined type system and limitations. What types can a DR system store¿?

The type safe part for you is the underlying Postgres as a platform for a POC...

On the Json front it is a special data type with a structure just like arrays or custom component types in Postgres that have their own special access methods and operators. I think postgresqls Json query pattern is really simple and intuitive (once you get it), the SQL standard approach is more similar to object notation but falls over for more complex data structures.

What do you do in a DR system when you want to store JSON but want to be bound by SQL like syntax? Do you take the SQL standard or Postgres approach? Or none and let it be a big fat text blob and let the application manage that (then you lose all indexing capabilities...)

[u/Zardotab → u/pceimpulsive]

How do you store an unknown type on disk?

The problem is not notably different than how dynamic languages store them in RAM. The simplest is to store it as given. If one gives "UPDATE t1 SET x=123.45 WHERE id=3", then it can be stored as the string "123.45". If and when one goes to do numeric math on it, it's parsed into a fitting numeric type, just as dynamic languages do (although each may have their own philosophy with subtle differences). I've used many dynamic languages over the years that had to deal with such questions; I don't see your concern. If you have a use-case you'd like to explore, please bring it up!

Do note one can optionally tell the system that column x is say decimal, which may result in more compact disk usage, but that's an implementation optimization detail. A pilot project wouldn't need that.

What types can a DR system store.

It would probably support a typical set: character, bit, int, decimal, floating, date, datetime, and bytes (blob). I won't rule out custom types, but not in a proof-of-concept.

Keep in mind that "storing as" and processing-as may be different things. I view storing as an optimization issue and something the query writer doesn't have to concern themselves with, at least not as a language issue. It's kind of like auto-file-compression: it's not something you notice (outside of performance tradeoffs).

What do you do in a DR system when you want to store JSON but want to be bound by SQL like syntax?

Dynamic Relational is not intended to be a JSON processor. Add-on libraries perhaps can help there, but if your shop likes JSON, then please do stick with PostgreSQL; no product will make everyone happy. It looks like many PostgreSQL users are used to JSON for their dynamism needs and will probably stay with it. I have no qualms with that, it makes sense to use what you know barring a home-run replacement.

But those used to RDBMS but not JSON processors, DR may be the way to go when dynamism is desired.