Most web apps only consist of two things: a frontend UI and a backend transaction system. And most of the time, the "backend" is just a glorified intermediary that reads from and writes to a database. So a naive question is raised: why do I need that intermediary? Why can't I just expose the database directly to the frontend?
Yes, you can! PostgREST is built exactly for that purpose. It turns a PostgreSQL database into a secure RESTful API, offering APIs like:
ts
// fetching a single userGET /user?id=1// search with filtersGET /user?age=gte.18&paid=is.true// fetch with relationGET /user?select=last_name,post(title)// createPOST /user{ "name": "J Doe", "age": 23 }
ts
// fetching a single userGET /user?id=1// search with filtersGET /user?age=gte.18&paid=is.true// fetch with relationGET /user?select=last_name,post(title)// createPOST /user{ "name": "J Doe", "age": 23 }
Of course, directly exposing database operations will be insecure and insane. PostgREST resolves that by delegating access control to Postgres's Row-Level-Security feature, which essentially allows defining rules with SQL like (more on this later):
sql
-- users have full access to their own postsCREATE POLICY post_owner_policy ON postUSING (owner = current_user);-- public posts are readable to allCREATE POLICY post_read_policy ON post FOR SELECTUSING (published = true);
sql
-- users have full access to their own postsCREATE POLICY post_owner_policy ON postUSING (owner = current_user);-- public posts are readable to allCREATE POLICY post_read_policy ON post FOR SELECTUSING (published = true);
PostgREST is a great tool in many ways but it may not fit everyone's preference for two reasons:
- It's a separate service that you need to host and manage in addition to your database and backend.
- It's very SQL-heavy, and you'll write a lot of SQL to define access policies in a complex system.
This article introduces an alternative approach that uses Prisma ORM and ZenStack to achieve the same goal without running one more service or writing a single line of SQL!
About Prisma
Prisma is a modern Typescript ORM that takes a schema-first approach and generates a fully type-safe client for your database operations.
A simple blogging app's schema looks like the following:
prisma
model User {id String @idemail Stringpassword Stringposts Post[]}model Post {id String @idtitle Stringpublished Boolean @default(false)author User @relation(fields: [authorId], references: [id])authorId String}
prisma
model User {id String @idemail Stringpassword Stringposts Post[]}model Post {id String @idtitle Stringpublished Boolean @default(false)author User @relation(fields: [authorId], references: [id])authorId String}
And the generated Typescript client is very pleasant to use:
ts
// the result is typed as: User & { posts: Post[] }const userWithPosts = await prisma.user.findUnique({where: { id: userId },include: { posts: true },});
ts
// the result is typed as: User & { posts: Post[] }const userWithPosts = await prisma.user.findUnique({where: { id: userId },include: { posts: true },});
About ZenStack
ZenStack supercharges Prisma and turns it into a powerful full-stack development toolkit. By extending its schema language to allow defining access policies, ZenStack can automatically generate a secure web service based on your data schema, solving the same problem that PostgREST does without the hassle of writing complex SQL.
Still using our blogging app as an example, the access policies can be added as the following (which is equivalent to the PostgREST example above):
prisma
model User {id String @idemail Stringpassword Stringposts Post[]// policy: everybody can signup@@allow('create', true)// policy: allow full CRUD by self@@allow('all', auth() == this)}model Post {id String @idtitle Stringpublished Boolean @default(false)author User @relation(fields: [authorId], references: [id])authorId String// policy: allow logged-in users to read published posts@@allow('read', auth() != null && published)// policy: allow full CRUD by author// auth() is a built-in function that returns current user@@allow('all', author == auth())}
prisma
model User {id String @idemail Stringpassword Stringposts Post[]// policy: everybody can signup@@allow('create', true)// policy: allow full CRUD by self@@allow('all', auth() == this)}model Post {id String @idtitle Stringpublished Boolean @default(false)author User @relation(fields: [authorId], references: [id])authorId String// policy: allow logged-in users to read published posts@@allow('read', auth() != null && published)// policy: allow full CRUD by author// auth() is a built-in function that returns current user@@allow('all', author == auth())}
More pleasant, isn't it? You can find a more comprehensive introduction to ZenStack's access policies here.
Read on for more side-by-side comparisons.
More Examples Of Access Policy
Here are a few more security requirement examples with progressively increasing complexity:
1. Make Post
readable to all
PostgREST:
sql
CREATE POLICY "public_readable_to_all" ON PostFOR SELECT USING (true);
sql
CREATE POLICY "public_readable_to_all" ON PostFOR SELECT USING (true);
ZenStack:
prisma
model Post {...@@allow('read', true)}
prisma
model Post {...@@allow('read', true)}
2. Allow users with "ADMIN" role to update any post
PostgREST:
sql
CREATE POLICY post_admin_update_policyON postFOR UPDATEUSING (EXISTS (SELECT 1 FROM user WHERE user.id = post.authorId AND user.role = 'ADMIN'));
sql
CREATE POLICY post_admin_update_policyON postFOR UPDATEUSING (EXISTS (SELECT 1 FROM user WHERE user.id = post.authorId AND user.role = 'ADMIN'));
ZenStack:
prisma
model Post {...@@allow('update', auth().role == 'ADMIN')}
prisma
model Post {...@@allow('update', auth().role == 'ADMIN')}
3. A post can be updated by a user if the user is in the same group as the author and has "ADMIN" role in that group
PostgREST:
sql
CREATE POLICY post_group_admin_update_policyON postFOR UPDATEUSING (EXISTS (SELECT 1FROM usergroup AS ug1JOIN usergroup AS ug2 ON ug1.groupId = ug2.groupIdWHERE ug1.userId = current_userAND ug2.userId = post.authorIdAND ug1.role = "ADMIN"));
sql
CREATE POLICY post_group_admin_update_policyON postFOR UPDATEUSING (EXISTS (SELECT 1FROM usergroup AS ug1JOIN usergroup AS ug2 ON ug1.groupId = ug2.groupIdWHERE ug1.userId = current_userAND ug2.userId = post.authorIdAND ug1.role = "ADMIN"));
ZenStack:
prisma
model Post {...@@allow('update', author.groups?[group.users?[userId == auth().id && role == 'ADMIN']])}
prisma
model Post {...@@allow('update', author.groups?[group.users?[userId == auth().id && role == 'ADMIN']])}
How Does It Work?
At runtime, ZenStack creates a transparent proxy around a regular Prisma client and injects proper filtering and checks to enforce access policies. For example, when you run the following code:
ts
const posts = await withPolicy(prisma, {user: session.user}).post.findMany();
ts
const posts = await withPolicy(prisma, {user: session.user}).post.findMany();
, only posts readable to the current user are returned.
Furthermore, it provides server adapters to install an automatic CRUD service to the Node.js server of your choice - Express, Fastify, Next.js, etc. Here's an example with Express.js:
ts
app.use('/api/data',ZenStackMiddleware({getPrisma: (request) => withPolicy(prisma, { user: getSessionUser(request) }),}));
ts
app.use('/api/data',ZenStackMiddleware({getPrisma: (request) => withPolicy(prisma, { user: getSessionUser(request) }),}));
A full list of currently supported adapters and their documentations can be found here.
The /api/data
endpoint will then provide a full set of Prisma operations for each model in your schema, like /api/data/post/findMany
. Since the Prisma client used is protected by the access policies, the generated web API is also secure.
Wrap Up
I hope you find the Prisma + ZenStack combination a useful alternative to PostgREST. Check out the Get Started and Guides pages for more details, and join our Discord for questions and updates!