how to create table in supabase via api

[liujichao2017]

I can only find the console in supabase to manually create a table, but I don't see any API that can create a table. Is there an API that supports creating a table?

[GaryAustin1]

You would use SQL thru the database port typically.

Some users have called rpc functions which then use SQL to create a table. Creating tables though dynamically is not a great idea though.

[GaryAustin1]

You would use an security definer function. You typically do not want to pass in SQL with an rpc. You would have a very fixed SQL EXECUTE statement in the function and just plug in table name or columns that are passed in.

I am not recommending you do this, but if you have to for some reason this is a way.

[liujichao2017]

If I use the call rpc function to pass in the table creation statement, an error will be reported, permission denied for schema public. How should this be solved?

[liujichao2017]

Can you give me a specific demo example? It's a bit hard to understand what you mean.

[GaryAustin1]

There are threads here in discussions and just googling the web for postgres create table with a function.
https://github.com/orgs/supabase/discussions/20711
https://github.com/orgs/supabase/discussions/3852
As I said though this is normally a bad idea and I personally will not suggest code as you have to know what you are doing or you will open yourself up to SQL injection and possibly create an unmaintainable schema in your database.

[liujichao2017]

Thank you very much for your guidance. Now I can create tables, but every time I change the database, it is not just creating tables, but also modifying tables, deleting tables, creating indexes, creating views and other operations. I should not need to create so many RPC functions to parse them. Is there a better solution for this problem?

[GaryAustin1]

I have no idea what you are doing or why you would do this thru rpc. You asked if there was a way to create thru an API there is not, except for what I mention. Sounds like you really should not be doing this thru an API.

There are migration files you can use with the CLI, 3rd party tools you can use, ORMs like Prisma, the dashboard UI. Basically you generate this stuff normally thru SQL and the database connection and not thru the REST API unless you have some odd need.