Questions from a prospective user

[ozum]

Hi,

I just discovered this repo via prisma/prisma#5128 (comment). As a prospective user, I skimmed the docs but need some details. I'll be happy if I can get the info for the questions below. (Sorry for the number of questions)

1. I first create DB and then generate the Prisma schema from the database. I have several valid reasons for DB-first workflow. I see that this is not possible with ZenStack. How about creating a ZenStack specific details in the schema.zmodel and having a schema.base (original raw prisma schema created by hand or generated from DB) and ZenStack merge/inject specific it's own parts to create a merged schema.prisma?
2. Are RLS features entirely based on PostgreSQL policies? (Is RLS back-end framework agnostic?)
3. Are there edge cases when I try to use ZenStack with Nuxt? (All examples are based on React and Next)
4. Currently, I'm using Supabase with Nuxt and Prisma.
   a. Can I use RLS features with Supabase?
   b. Is it possible to integrate Supabase's PostgreSQL session variables and PostgreSQL functions defined in the auth schema (for example auth.uid())? Maybe info below helps:

Let me explain how I use Prisma with Nuxt and Supabase:

I developed an open-source Nuxt module for Prisma which has built-in Supabase support. It creates a Prisma client using a Prisma extension. It sets Supabase (or any other custom framework's PG session variables before returning the client) Please see https://github.com/ozum/nuxt-prisma/blob/main/src/runtime/server/middleware/prisma.ts file.

For the Supabase values see Line 12 of this file: https://github.com/ozum/nuxt-prisma/blob/main/src/module.ts

Maybe a similar method can be used to integrate ZenStack with Supabase.

Thanks,

[ymc9]

Glad you found us here 😄 @ozum , and thanks for the questions! Many of them should actually be included in a (now missing) FAQ section.

Let me try to address them, and we can keep discussing this in more detail.

1. So I guess the current workflow is to create the db manually and then use prisma introspect to bootstrap, right? If it's a one-time (per project) task, does it work if you run zenstack init after the bootstrapping process? The command installs dependencies and copies schema.prisma over to schema.zmodel. The separate & merge approach you suggested has its benefit, but I'm also a bit worried that keeping them in sync can be challenging (for developers), and also, it's harder to see the full picture of the schema.

2. Actually ZenStack's access policy implementation doesn't depend on Postgres RLS. It works by injecting filters into queries, together with some post-query/post-mutation validation/pruning in certain scenarios. The benefit is that the behavior is runtime, so changing access policies doesn't involve creating a migration. It's also a lot easier to read/write/maintain than SQL RLS IMO. You're right; it's framework (and database) agnostic and should work wherever Prisma works. We currently have adapters for Next.js and fastify, and express/sveltekit are work in progress.

3. Nuxt is on the radar, but I don't have much experience with it. The way how ZenStack integrates with a framework is by providing a framework-agnostic OpenAPI layer and then adding a thin layer of adapter for each framework. I don't anticipate much difficulty in adapting to Nuxt, but I haven't looked deeply into it yet. Let me know if you're interested in collaboration and contribution!

4. Since ZenStack doesn't depend on Postgres RLS (thus Supabase RLS), I don't see much value in using them together. ZenStack is more like an alternative to Postgres RLS in terms of modeling access control. But I may be missing some important scenarios that you already see. Let me know.

I checked your nuxt-prisma project, and the idea looks pretty cool. One thing I'm not sure about is if using Prisma client extensions is sufficient for isolating Postgres config/variables for concurrent requests. I tried to follow issue #5128 but still feel confused about where the safety boundary is 😂 .

[ozum]

1

If it's a one-time (per project) task, does it work if you run zenstack init after the bootstrapping process?

Unfortunately, it's a continuous task. We model our database in a database modeling tool and generate the database (including migration/change scripts) from there.

I'm also a bit worried that keeping them in sync can be challenging (for developers)

I agree. This is the reason I'm asking for 3 schemas approach. schema.base + schema.zmodel = schema.prisma. In this case, you can freely overwrite schema.prisma as before just by merging two files' related sections. Some more work on your side, but could have potential use cases for other yet unknown purposes too.

2

Actually ZenStack's access policy implementation doesn't depend on Postgres RLS.

This is bad news for me, but I cannot object to your choice, because it is a business/architectural decision.

It works by injecting filters into queries, together with some post-query/post-mutation validation/pruning in certain scenarios. .... It's also a lot easier to read/write/maintain than SQL RLS IMO.

I completely agree with you. The reason I prefer RLS is that it ensures access security policies in every scenario. (i.e. database management apps, reporting tools, bi tools, etc.)

3

Nuxt is on the radar .... Let me know if you're interested in collaboration and contribution.

Currently, all my open-source time is filled with other MIT projects, and I'm struggling to spend enough time. Otherwise, I'll be glad to contribute.

4

Since ZenStack doesn't depend on Postgres RLS (thus Supabase RLS), I don't see much value in using them together.

It seems so. However, please reconsider migration to RLS. In that case, ZenStack can be integrated and work within an ecosystem containing Supabase, Hasura, PostGraphile, and other business tools I mentioned in topic 2. I don't like to write RLS, but its (nearly complete) transparency provides many advantages to app developers and also business people.

Actually, I came to the ZenStack website, hoping it eases my burden to develop RLS policies by hand. Developing RLS is boring, and DB modeling tools do not help with this.

5

I checked your nuxt-prisma project, and the idea looks pretty cool. One thing I'm not sure about is if using Prisma client extensions is sufficient for isolating Postgres config/variables for concurrent requests.

Thanks for the nice words. Actually single purpose to use a Prisma Extension is to provide that security. This extension creates an extended Prisma client which does the following:

• Takes the original query generated by Prisma.
• Creates a transaction that wraps the original query.
• Sets config variables with set_config(..., ..., true). true parameter makes the variable local to the transaction.
• Executes SET LOCAL ROLE ..., which is local to the transaction.
• Executes the original query.
• Returns only results of the original query.

Roughly something like below, but only returns the products (not other two query):

BEGIN;

SELECT set_config('request.jwt.claims', '{ "key1": "value1", "keyN": "valueN" }', TRUE);
SET LOCAL ROLE my_role;
SELECT id, name, price FROM products WHERE price < 100;

COMMIT;

Since all queries are executed in a transaction using local config and role it is safe to use Session pooling and Transaction pooling. It is unsafe in Statement pooling, which is already unsafe to use in any transactional queries.

[ymc9]

Thanks for the detailed replies and moving the discussion forward!

This is the reason I'm asking for 3 schemas approach. schema.base + schema.zmodel = schema.prisma. In this case, you can freely overwrite schema.prisma as before just by merging two files' related sections.

Since the models and fields are in schema.base and additional annotations are in schema.zmodel, there'll still be some care to make sure they're in sync. I agree with you that it enables extra flexibility. We're thinking about how to support multi-file schema now and will consider this as well.

I can understand your preference for RLS. Yes, with that, the policies are applied regardless of where the requests are made. At least for the time being ZenStack is not a good fit for you. Switching to RLS will be a very big change. My current plan is to wait and see if Prisma will add some native support to RLS (in terms of modeling and migration), but the thought can change as we get more feedback. If we start to incorporate, we can use your code as an excellent reference implementation. Thanks for that!

Is the main project that you're working on a public one? Interested in checking it out in more detail.

[ozum]

Since the models and fields are in schema.base and additional annotations are in schema.zmodel, there'll still be some care to make sure they're in sync.

On second thought, I totally agree with you. Especially when there are lots of tables in a very long file, it is very hard to maintain two files in sync.

We're thinking about how to support multi-file schema now and will consider this as well.

You may consider separate files for each table, view, etc. A company I was working for, had a nice idea and I developed the tool for them. The idea was based on using separate files for each database table and even multiple files for different parts of the table.

They use two alternatives. The first one was my favorite.

|-- product
|---- definition.sql
|---- policy.sql
|---- trigger.sql
|---- relations.sql
|-- invoice
...

or

|-- product.definition.sql
|-- product.policy.sql
|-- product.trigger.sql
|-- product.relations.sql
|-- invoice.definition.sql
...

They merge those files to generate the final DDL. It was very easy to navigate in files and folders to find a specific tables definition and other objects.

Is the main project that you're working on a public one?

I usually divide my time between open-source projects and the ones paying my bills :). Balance is moving from time to time. On the open source part, there isn't a main project. I developed approx. 70 smallish open-source libraries/tools from scratch and try to maintain most of them when a case is opened. I create new ones occasionally when I have time.

At least for the time being ZenStack is not a good fit for you.

It seems so, but the tool seems brilliant and I enjoyed when reading the features. I have a general interest in tools for Developer Experience. Most of my open-source projects are for DX too. (i.e. https://www.pg-structure.com/, https://pg-generator.com/ and others on my GitHub profile).

I wish ZenStack good luck and success. Just IMHO, please consider how to integrate more tools (even competing ones) in the ecosystem in addition to Prisma and tRPC. Tools that are well-integrated with popular products catch the hype more easily.

[ymc9]

Yes, with table-level splitting, you can even collocate the schema with the corresponding business logic code. We'll keep thinking about it.

Thanks for the recognition of ZenStack! Yes, I wish it was not restricted to Prisma, but right now our bandwidth is quite limited to explore too much altogether. Making it work with more full-stack and backend frameworks is definitely in the plan. Wish there's another occasion ZenStack can help you.

Let's keep working on cool OSS stuff and make the world a better place for developers 😄