Make jsonschema the source from which the database schema is created

[gedw99]

Post edit: just realised that this repo has discussions :) should have used that …

What would you like to be added:

use json schema ( which btw is very close to openapi 3.1 ) to drive creation of the db migration ddl script.

Why is this needed:

might make creation of sql dialect per database type easier , since we have more control about how the sql is created.

wil allow us to do validation of all data at runtime because json schema is very good at that .

At the moment db does validation but not for all use cases. Many situations Ned extensive business logic validation and at the moment we use js for doing that .

because the db is created off the json schema , the validation schema will always match the database tables !

Reduces a ton of js that we currently do validation in.

opens up the possibility of reusing the json schema higher up in the client architecture.

this gets us the 3 levels of validation from one reusable mechanism. Client side Server side Db side

Client side example:

https://github.com/rjsf-team/react-jsonschema-form

Gen time example:

https://github.com/gophergala2016/dbservice

Produces the db scripts

finally this opens the door to add a Developer and user admin gui that works along side the existing graphql explorer. So you can see the json schema and the data base table scripts and edit them

but you can also test the validation at all levels for which you need validation of your mutations.

In a graphql client , you I’ll be able to check all mutations before you send them to the server and in the ide you can also check them. All off this one mechanism.

if you think this idea has some merit or see some aspects that you think could be better pleae add your thoughts.

I am wanting to extend the existing web Ide gui of graphjin and jsonschema is the first step . Mostky because json schema provides the required reflection capabilities .

The web ide gui would be a PR later , but first I need to PR the json schema code described above.

[dosco]

Interesting idea, this could be built as a separate json schema based migrations library

[gedw99]

@dosco

Thank you for considering and replying to the idea ...

In that case, i will do it as a plugin for Benthos, and then it will call GraphJib.

Benthos is designed as a streaming transformation engine. https://github.com/Jeffail/benthos

For validation: https://www.benthos.dev/docs/components/processors/json_schema

For placing into Graphjin's path: https://www.benthos.dev/docs/components/outputs/file

I was toying with the idea of adding benthos into the pipeline anyway so that Users / Developers can do customisations on the Mutations going into GraphJin and the queries coming out of GraphJin.

Benthos can be run as a library, and so compiled in with GraphJin or run as its own process. Benthos also has a Web GUI. I heard that a new one is coming out too. The GUi currently is Golang compiled to wasm.

@dosco please let me know what you think in general.

[gedw99]

Interesting idea, this could be built as a separate json schema based migrations library

Hey @dosco could you add some more depth to your comment ? I rushed ahead with the benthos approach but can now see it’s overkill :)

[dosco]

You originally mentioned migrations. GraphJin itself does not generate SQL for database migrations (adding, modifying, deleting tables and columns) GraphJin supports migrations where you write the SQL for the migration and run it via the migrate command.

[gedw99]

Ok now I get it 👍

does this sound about right as a logically sequence then ?

1 Gen the SQL from the jsonschema.

2 Use a data time or sequence for each generated SQL fille name sequence.

3 Transact each SQL into the DB

[gedw99]

https://blog.ariga.io/meet-atlas-cli/

I think jsonschema to HCL is straitforward.

https://github.com/ariga/atlas

Atlas encompasses all the tricky details.