SupabaseS
Supabase•3y ago
Waldemar

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:
{
  tables: [
    {
      name: orders,
      columns: [
        {
          name: 'id',
          type: 'TEXT'
        }
      ],
      constraints: [
        ...
      ]
    }
  ]
}


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.
Was this page helpful?