How to export SQL schema as JSON?
Currently I have a huge SQL script that I copy&paste and run in SB SQL Editor to (re)create our entire DB schema, including tables, FKs, indexes etc.
I want to replace this hardcoded SQL script (as much as possible) with something more dynamic, so I can:
1) (Re)create the schema programmatically (e.g. by generating SQL from JSON and executing via
pg)
2) Extend it with some access control rules (RBAC) for checking on the server-side code (e.g. which user role can insert/update which tables and columns)
We tried Postgresql RLS, but it doesn't work for us
I looked into generic tools that can generate JSON from Postgresql schema, such as:
https://github.com/tjwebb/pg-json-schema-export
https://www.pg-structure.com/
...but didn't manage to make them work and they seem too complicated for once-off use.
Also I know there is
https://supabase.com/docs/guides/api/rest/generating-types
...but it generates TypeScript, which I understand won't give me the 1) and 2) above.
Then there are tools like Prisma, but again, I don't think I can do 2) wit it and it is a full blown ORM and also an overkill for what I need anyway.
Can you recommend other ways / tools how I can do it?
I imagine something like:
Thanks!GitHub
GitHub - tjwebb/pg-json-schema-export: Export Postgres schemas as J...
Export Postgres schemas as JSON ( tables, constraints, sequences) - GitHub - tjwebb/pg-json-schema-export: Export Postgres schemas as JSON ( tables, constraints, sequences)
pg-structure
Reverse engineer PostgreSQL database as a detailed JS Object.
Generating Types | Supabase Docs
How to generate types for your API and Supabase libraries.
2 Replies