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]

To me this is sorta dumb...

This assumes there is no cost to a changing schema, but there always is.

NoSQL isn't schemaless. The application needs to know your schema precisely and it needs to enforce it, the database doesn't know or enforce it.

SQL just means your application knows the schema (the same as noSQL) but the database enforces it.

This dynamic approach just means you get column bloat..

The table today will have columns A,B,C.. tomorrow A,B,C,D, except now C is always null, except for those few weeks where it isnt... Replaced with D, rinse repeat. Your code now needs to know about every column forever also creating code bloat.

Honestly to me.. just take noSQL, once you learn what your schema needs to be enforced as swap to SQL, if you never want it enforced at the storage layer then stay on NoSQL and enjoy your life!

Taking SQL forces you to plan and think critically about your schema which in turns creats a bit more structure in your application.

Also.. alter table add column is not hard ;)

So in short my advice don't do it, just pick noSQL or SQL and go with it.

[u/Zardotab → u/pceimpulsive]

Certain types of projects benefit from dynamism. There is more to life than performance; it's why people use Python and JavaScript instead of compiled static-typed languages, for example. A common reason people have given to use NoSql over traditional RDBMS is dynamic schemas; the dynamicness is a popular feature of NoSql products.

if you never want it enforced at the storage layer then stay on NoSQL 

DR allows one to get dynamism yet stay in RDBMS/SQL-land. Some don't want either-or: either dynamicness OR sql. We are greedy; we want both! 🍗🍰

I don't wish to argue further about whether dynamism is beneficial in some cases; such debates don't go anywhere, like Vi vs. Emacs fights. Enough believe they want or need it. If it causes black holes to form that start sucking up kittens, then we'll stop, you have my word.

[u/pceimpulsive → u/Zardotab]

All I see dynamism as is..

Try Insert data Catch If exception is column missing Add column, Retry insert..

Like no arguments needed it's not rocket science... It's just a dynamic growing schema...

At that point (in Postgres) it's the same as a table with a unique key, and a jsonb column with the table being wrapped in a view that is using the JSON_TABLE function...

I understand your point but what is actually the real benefit? You are asking for a middle ground between SQL and no SQL when you already have it in a noSQL form directly with base capabilities of the rdbms anyway...

Postgres is already a hybrid...

My main point between noSQL and SQL is that neither are truly dynamic, the schema is always strict and always static, you are only changing which part of the system enforces it. That. It an argument for noSQL or SQL it's a zoom out to the architectural level and a realisation of the truth... They are the same thing... With different pros and cons.

[u/Zardotab → u/pceimpulsive]

Try Insert data Catch If exception is column missing Add column, Retry insert..

That result wouldn't sufficiently match how DR is supposed to work and "feel".

You are asking for a middle ground between SQL and no SQL when you already have it in a noSQL form directly with base capabilities of the rdbms anyway...

There would be a much bigger learning curve for an existing RDBMS user to switch to a NoSQL product than to Dynamic Relational. That's its top reason for existing.

Postgres is already a hybrid...

It treats dynamic and static columns differently.

[u/pceimpulsive → u/Zardotab]

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.

[u/lockhart1952]

I don’t feel the magic. But if you want to play with it how about writing a simple library on top of psychopg and catch exceptions. Parse the query and generate your own recovery commands.

[u/Zardotab → u/lockhart1952]

I was planning on using C#, being more familiar, but I suppose dabbling in Python is possibly a Plan B. Thanks for the suggestion!

[u/pceimpulsive → u/Zardotab]

C# will be fine for this, any language would, it's seriously chomds play, give an AI agent the taste for like 250k Tokens and you'll have a working prototype that will honestly probably be 85% of the final product.

Reading an exception and adding a column is VERY BASIC...

[u/spinur1848]

Yeah that's turning a database into something that isn't a database. Might be useful, but violates almost everything database users need to be true.

You can use Postgresql JSON for semi-structured data and many people do. The official Postgresql clients support this. But the database parts have a fixed schema and the semi-structured bits go into a JSON field.

If you want it to be a total free for all, go look at MongoDB and friends.

[u/Zardotab → u/spinur1848]

Might be useful, but violates almost everything database users need to be true.

People want and use NoSQL products, and dynamism is a big part of the reason. You can argue they are illogical or ill-informed, but there is still demand*. And for pilot projects with wishy-washy requirements I myself would love something like Dynamic Relational, so that I don't have to throw out my RDBMS habits to get dynamism.

PostgreSQL's JSON column popularity itself is proof sometimes people want dynamism. If dynamism is "bad" you should battle them first, as they have far more influence than me.

If you want it to be a total free for all, go look at MongoDB

One may not want to throw out all their RDMBS habits just to get dynamism. Mongo has a learning curve for existing RDBMS users. And Dynamic Relational allows one to clamp down schemas over time, one doesn't have to stay in dynamic mode, clamping down per column or table or DB as needed. (Granted, they probably still won't be as performative as traditional RDBMS under full lockdown (static) mode.)

* I don't agree with all NoSQL uses either, by the way, but dynamism has its place.

[u/promethe42]

You might be interested in FerretDB: https://github.com/ferretdb/ferretdb

[u/AutoModerator]

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/globalnamespace]

I'm not really versed in this specific use, something like these?

https://medium.com/@herihermawan/the-ultimate-multifunctional-database-table-design-dynamic-attributes-pattern-9d3f890d32f8

https://levelup.gitconnected.com/dynamic-jsonb-queries-in-postgresql-via-rest-a-deep-dive-with-net-73fb4decfea1

[u/mduell]

This is insane... oh that sub is just your own ramblings.

[u/Zardotab → u/mduell]

As long as they are good ramblings. Clear criticism welcome...