This content originally appeared on DEV Community and was authored by ymc9
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:
// fetching a single user
GET /user?id=1
// search with filters
GET /user?age=gte.18&paid=is.true
// fetch with relation
GET /user?select=last_name,post(title)
// create
POST /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):
-- users have full access to their own posts
CREATE POLICY post_owner_policy ON post
USING (owner = current_user);
-- public posts are readable to all
CREATE POLICY post_read_policy ON post FOR SELECT
USING (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:
model User {
id String @id
email String
password String
posts Post[]
}
model Post {
id String @id
title String
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId String
}
And the generated Typescript client is very pleasant to use:
// 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):
model User {
id String @id
email String
password String
posts Post[]
// policy: everybody can signup
@@allow('create', true)
// policy: allow full CRUD by self
@@allow('all', auth() == this)
}
model Post {
id String @id
title String
published 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:
CREATE POLICY "public_readable_to_all" ON post
FOR SELECT USING (true);
ZenStack:
model Post {
...
@@allow('read', true)
}
2. Allow users with "ADMIN" role to update any post
PostgREST:
CREATE POLICY post_admin_update_policy
ON post
FOR UPDATE
USING (
EXISTS (
SELECT 1 FROM user
WHERE user.id = post.authorId AND
user.role = 'ADMIN'
)
);
ZenStack:
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:
CREATE POLICY post_group_admin_update_policy
ON post
FOR UPDATE
USING (
EXISTS (
SELECT 1
FROM usergroup AS ug1
JOIN usergroup AS ug2 ON ug1.groupId = ug2.groupId
WHERE ug1.userId = current_user
AND ug2.userId = post.authorId
AND ug1.role = "ADMIN"
)
);
ZenStack:
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:
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:
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!
This content originally appeared on DEV Community and was authored by ymc9
![](https://www.radiofree.org/wp-content/plugins/print-app/icon.jpg)
ymc9 | Sciencx (2023-04-24T18:27:14+00:00) Prisma + ZenStack: An Alternative to PostgREST. Retrieved from https://www.scien.cx/2023/04/24/prisma-zenstack-an-alternative-to-postgrest/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.