C# Type Generation

[tylershatch]

Does anyone know of any way to automatically generate C# types from a Supabase database, in the same way you can generate Typescript types by using the command npx supabase gen types typescript?

For context, I am hoping I can find a way to define schemas/types in one place (such as a .prisma file), perform a database migration, perform a codegen step that is equivalent to the non-existent command npx supabase gen types csharp, and end up with types that I can use with supabase-csharp's strongly-typed LINQ-like interface (i.e. those generated types are derived from BaseModel, their properties all have the correct attributes, etc.).

It appears there is no ready-made way to do this, so I am considering trying to implement a way myself (probably by generating the typescript types first and then converting them to C#), but I wanted to find out if any similar effort might already be underway, or already exists and I somehow missed it.

Thank in advance for any insights you can provide!

[wiverson]

Not that I know of off the top of my head. Historically this has been an area targeted by ORMs, which usually sit on the middle tier in a secure environment. You would just point your tooling at the ODBC connection string and hit the database directly.

There are a lot of C# (and FWIW Java) tools and frameworks that are expecting to sit in the middle as a REST->SQL service. I just did a quick search for traditional C# ORMs and found a ton of stuff (I'm more of a Java dev by background). Off the cuff there's NHibernate, Entity Framework, Dapper, and a search turned up a ton of others...

FWIW what I did with my Unity project was to create the backend using pl/SQL stored procedures in the public schema, and then have my data in an app schema. I wrote C# test cases to hit those auto-generated REST services from the public schema to validate both the functionality and the async/await stuff. I used a standard RDMBS modeling tool to generate and manage the schema, including backups. I actually found this much easier than trying to use a traditional ORM. I did have to write C# types for the data coming coming back from the RPCs but TBH it really wasn't that much work.

[tylershatch]

Hi wiverson, thank you for the response! I was just using some of your youtube tutorials yesterday to get started with supabase-csharp in my Unity project, thank you for those!

Doing everything inside of a REST->SQL service would have been my natural instinct, but I had recently used supabase in a typescript-only project and was intrigued by how RLS+postgrest seemed to eliminate the security concerns that necessitated that approach in the first place (although maybe I’m mistaken about that?).

I didn't really have to worry about security for that project, and I had never encountered RLS before, so it is definitely possible I misunderstood something and was doing things all wrong from a security perspective, but it seemed like I was taking the intended approach by never standing up a custom REST service and instead just using the supabase client directly in my client code (implictly relying on postgrest as the underlying REST service), with types generated by the codegen command I mentioned. I had hoped to find a way to do something similar in Unity, assuming it is not a terrible idea for some reason I’m unaware of.

[wiverson]

You can put data (tables) in the public schema and use RLS. Or you can do what I'm doing and just put public functions in. Both of these options are handled by https://postgrest.org/en/stable/

https://supabase.com/docs/guides/api

If you write stored procedures, you can just use the auth.uid() function in the stored procedure to get the JWT-based user id for the logged in user. I use that and basically just write SQL.

Here's a hello world function to see the currently logged in user.

create function hello_user() returns text
    language plpgsql
as
$$
BEGIN
    RETURN (SELECT auth.uid()::text);
EXCEPTION
    WHEN OTHERS THEN RAISE;
END;
$$;

You can call this by just doing supabase.rpc in the client. If the user is logged in (eg w/email pass) you'll get their id back.

Basically I just this stuff in all of my server-side functions. I'm using plpgsql for the language, but you can use the v8 if you prefer writing JS.