Create your first CRUD 🚧
Make sure you followed the Getting Started before starting this tutorial.
Let’s dive into creating a full new entity with database, backend and ui.
We will create a “Project” entity with the full CRUD (Create Read Update Delete) screens.
Step 1: Create the Project database schema
Section titled “Step 1: Create the Project database schema”-
Update the Prisma Database Schema
We will use Prisma to add the project entity to our database schema.
Because we are creating a new entity, we need to create a new file to store all details related to the new
Project
model.Update the
prisma/schema.prisma
file and add a new model calledProject
with the following fields.model Project {id String @id @default(cuid())createdAt DateTime @default(now())updatedAt DateTime @updatedAtname String @uniquedescription String?}Cut the running
pnpm dev{:bash}
if needed, and then run thepnpm db:push{:bash}
command to update your database.You can run
pnpm dev{:bash}
again. -
Create you first project
We can see what is inside of our database with the
pnpm db:ui{:bash}
command.
You should see yourProject
model and be able to create a new project like the following.
-
Create database seeds
For easy development and better Developer eXperience (DX), we will create a new seed for our new
Project
model.
This will allow every new developer to start with some projects instead of an empty database.Create an new file
project.ts
in theprisma/seed
folder with acreateProjects
function.export async function createProjects() {// ...}Add a console.log for better DX.
export async function createProjects() {console.log(`⏳ Seeding projects`);// ...}Get existing projects. It will help us to make the seed idempotent.
import { prisma } from "prisma/seed/utils";export async function createProjects() {console.log(`⏳ Seeding projects`);const existingProjects = await db.project.findMany();}Create the projects with prisma.
import { db } from "@/server/db";export async function createProjects() {console.log(`⏳ Seeding projects`);const existingProjects = await db.project.findMany();const projects = [{ name: "Project 1" },{ name: "Project 2" },{ name: "Project 3" },] as const;const result = await db.project.createMany({data: projects.filter((project) =>!existingProjects.map((existingProject) => existingProject.name).includes(project.name)).map(({ name }) => ({ name })),});console.log(`✅ ${existingProjects.length} existing projects 👉 ${result.count} projects created`);}Now, import the function into the
prisma/seed/index.ts
file.import { db } from "@/server/db";import { createBooks } from "./book";import { createProjects } from "./project";import { createUsers } from "./user";async function main() {await createBooks();await createProjects();await createUsers();}main().catch((e) => {console.error(e);process.exit(1);}).finally(() => {db.$disconnect();});Finally, run the seed command.
Terminal window pnpm db:seedYou can check that the project is created by running the
pnpm db:ui{:bash}
command again.
Step 2: Create the backend router
Section titled “Step 2: Create the backend router”-
Setup the access controls
We will use BetterAuth to handle the access control and permissions for our application.
Update the filepermissions.ts
in theapp/features/auth
folder. Add the following code to define the permissions for theProject
entity.import { UserRole } from "@prisma/client";import {createAccessControl,Role as BetterAuthRole,} from "better-auth/plugins/access";import { adminAc, defaultStatements } from "better-auth/plugins/admin/access";import { z } from "zod";import { authClient } from "@/features/auth/client";const statement = {...defaultStatements,account: ["read", "update"],apps: ["app", "manager"],book: ["read", "create", "update", "delete"],genre: ["read"],project: ["read", "create", "update", "delete"],} as const;const ac = createAccessControl(statement);const user = ac.newRole({account: ["update"],apps: ["app"],book: ["read"],genre: ["read"],project: ["read"],});const admin = ac.newRole({...adminAc.statements,account: ["update"],apps: ["app", "manager"],book: ["read", "create", "update", "delete"],genre: ["read"],project: ["read", "create", "update", "delete"],});export const rolesNames = ["admin", "user"] as const;export const zRole: () => z.ZodType<Role> = () => z.enum(rolesNames);export type Role = keyof typeof roles;const roles = {admin,user,} satisfies Record<UserRole, BetterAuthRole>;export const permissions = {ac,roles,};export type Permission = NonNullable<Parameters<typeof authClient.admin.hasPermission>["0"]["permission"]>; -
Create the oRPC router
Create a
project.ts
file in theapp/server/routers
folder and create an empty object default export with the following code. This object will contain our router’s procedures.export default {// ...}; -
Add the first query to list the projects
We will create a query to get all the projects from the database.
In the projects router file (app/server/routers/project.ts
), create agetAll
key for our query.export default {getAll: // ...};We need this query to be protected and accessible only for users with
"read"
access to theproject
resource. So we will use theprotectedProcedure
.import { protectedProcedure } from "@/server/orpc";export default {getAll: protectedProcedure({permission: {project: ["read"],},}),};Then we need to create the
input
and theoutput
of our query. For now, the input will be void and the output will only return an array of projects withid
,name
anddescription
properties.import { z } from "zod";import { protectedProcedure } from "@/server/orpc";export default {getAll: protectedProcedure({permission: {project: ["read"],},}).input(z.void()).output(z.array(z.object({id: z.string().cuid(),name: z.string(),description: z.string().optional(),}))),};We will add some
meta
to auto generate the REST api based on the tRPC api.import { z } from "zod";import { protectedProcedure } from "@/server/orpc";const tags = ["projects"];export default {getAll: protectedProcedure({permission: {project: ["read"],},}).route({method: "GET",path: "/projects",tags,}).input(z.void()).output(z.array(z.object({id: z.string().cuid(),name: z.string(),description: z.string().optional(),}))),};And now, let’s create the handler with the projects.
import { z } from "zod";import { protectedProcedure } from "@/server/orpc";const tags = ["projects"];export default {getAll: protectedProcedure({permission: {project: ["read"],},}).route({method: "GET",path: "/projects",tags,}).input(z.void()).output(z.array(z.object({id: z.string().cuid(),name: z.string(),description: z.string().nullish(),}))).handler(async ({ context }) => {context.logger.info("Getting projects from database");return await context.db.project.findMany();}),}; -
Add load more capability
We will allow the query to be paginated with a load more strategy.
First, let’s update our input to accept a
limit
and acursor
params.import { z } from "zod";import { protectedProcedure } from "@/server/orpc";const tags = ["projects"];export default {getAll: protectedProcedure({permission: {project: ["read"],},}).route({method: "GET",path: "/projects",tags,}).input(z.object({cursor: z.string().cuid().optional(),limit: z.number().min(1).max(100).default(20),}).default({})).output(z.array(z.object({id: z.string().cuid(),name: z.string(),description: z.string().nullish(),}))).handler(async ({ context }) => {context.logger.info("Getting projects from database");return await context.db.project.findMany();}),};Then we will need to update our prisma query.
import { z } from "zod";import { protectedProcedure } from "@/server/orpc";const tags = ["projects"];export default {getAll: protectedProcedure({permission: {project: ["read"],},}).route({method: "GET",path: "/projects",tags,}).input(z.object({cursor: z.string().cuid().optional(),limit: z.number().min(1).max(100).default(20),}).default({})).output(z.array(z.object({id: z.string().cuid(),name: z.string(),description: z.string().nullish(),}))).handler(async ({ context, input }) => {context.logger.info("Getting projects from database");return await context.db.project.findMany({// Get an extra item at the end which we'll use as next cursortake: input.limit + 1,cursor: input.cursor ? { id: input.cursor } : undefined,});}),};Now, we need to update our output to send not only the projects but also the
nextCursor
.import { z } from "zod";import { protectedProcedure } from "@/server/orpc";const tags = ["projects"];export default {getAll: protectedProcedure({permission: {project: ["read"],},}).route({method: "GET",path: "/projects",tags,}).input(z.object({cursor: z.string().cuid().optional(),limit: z.number().min(1).max(100).default(20),}).default({})).output(z.object({items: z.array(z.object({id: z.string().cuid(),name: z.string(),description: z.string().nullish(),})),nextCursor: z.string().cuid().nullish(),})).handler(async ({ context, input }) => {context.logger.info("Getting projects from database");const projects = await context.db.project.findMany({// Get an extra item at the end which we'll use as next cursortake: input.limit + 1,cursor: input.cursor ? { id: input.cursor } : undefined,});let nextCursor: typeof input.cursor | undefined = undefined;if (projects.length > input.limit) {const nextItem = projects.pop();nextCursor = nextItem?.id;}return {items: projects,nextCursor,};}),};We will now add the total of projects in the output data to let the UI know how many projects are available even if now the UI will not request all projects at once.
import { z } from "zod";import { protectedProcedure } from "@/server/orpc";const tags = ["projects"];export default {getAll: protectedProcedure({permission: {project: ["read"],},}).route({method: "GET",path: "/projects",tags,}).input(z.object({cursor: z.string().cuid().optional(),limit: z.number().min(1).max(100).default(20),}).default({})).output(z.object({items: z.array(z.object({id: z.string().cuid(),name: z.string(),description: z.string().nullish(),})),nextCursor: z.string().cuid().nullish(),total: z.number(),})).handler(async ({ context, input }) => {context.logger.info("Getting projects from database");const [total, items] = await context.db.$transaction([context.db.project.count(),context.db.project.findMany({// Get an extra item at the end which we'll use as next cursortake: input.limit + 1,cursor: input.cursor ? { id: input.cursor } : undefined,}),]);let nextCursor: typeof input.cursor | undefined = undefined;if (items.length > input.limit) {const nextItem = items.pop();nextCursor = nextItem?.id;}return {items,nextCursor,total,};}),}; -
Add search capability
Let’s add the possibility to search a project by name. We are adding a
searchTerm
in the input and add awhere
clause. We need to put thiswhere
on both prisma requests, so we can create a constant with the help of thePrisma.ProjectWhereInput
generated types.import { Prisma } from "@prisma/client";import { z } from "zod";import { protectedProcedure } from "@/server/orpc";const tags = ["projects"];export default {getAll: protectedProcedure({permission: {project: ["read"],},}).route({method: "GET",path: "/projects",tags,}).input(z.object({cursor: z.string().cuid().optional(),limit: z.number().min(1).max(100).default(20),searchTerm: z.string().optional(),}).default({})).output(z.object({items: z.array(z.object({id: z.string().cuid(),name: z.string(),description: z.string().nullish(),})),nextCursor: z.string().cuid().nullish(),total: z.number(),})).handler(async ({ context, input }) => {context.logger.info("Getting projects from database");const where = {name: {contains: input.searchTerm,mode: "insensitive",},} satisfies Prisma.ProjectWhereInput;const [total, items] = await context.db.$transaction([context.db.project.count({ where }),context.db.project.findMany({// Get an extra item at the end which we'll use as next cursortake: input.limit + 1,cursor: input.cursor ? { id: input.cursor } : undefined,}),]);let nextCursor: typeof input.cursor | undefined = undefined;if (items.length > input.limit) {const nextItem = items.pop();nextCursor = nextItem?.id;}return {items,nextCursor,total,};}),}; -
Add the router to the Router.ts file
Finally, import this router in the
app/server/router.ts
file.import { InferRouterInputs, InferRouterOutputs } from "@orpc/server";import accountRouter from "./routers/account";import bookRouter from "./routers/book";import genreRouter from "./routers/genre";import projectRouter from "./routers/project";import userRouter from "./routers/user";export type Router = typeof router;export type Inputs = InferRouterInputs<typeof router>;export type Outputs = InferRouterOutputs<typeof router>;export const router = {account: accountRouter,book: bookRouter,genre: genreRouter,project: projectRouter,user: userRouter,};
Step 3: Create the feature folder
Section titled “Step 3: Create the feature folder”-
Create the feature folder
To put the UI and shared code, let’s create a
project
folder in theapp/features
folder. It’s in this folder that we will put all the UI of the projects feature and also the shared code between server and UI. -
Extract project zod schema
First, we will extract the zod schema for the project from the tRPC router and put it into a
schemas.ts
file in thesrc/features/projects
folder.Let’s create the
app/features/project/schema.ts
file with the zod schema for one project.import { z } from "zod";import { zu } from "@/lib/zod/zod-utils";export const zProject = () =>z.object({id: z.string().cuid(),name: zu.string.nonEmpty(z.string()),description: z.string().nullish(),});Let’s create the type from this schema.
import { z } from "zod";import { zu } from "@/lib/zod/zod-utils";export type Project = z.infer<ReturnType<typeof zProject>>;export const zProject = () =>z.object({id: z.string().cuid(),name: zu.string.nonEmpty(z.string()),description: z.string().nullish(),});Use this schema in the oRPC router in the
app/server/routers/project.ts
file.import { Prisma } from "@prisma/client";import { z } from "zod";import { zProject } from "@/features/projects/schema";import { protectedProcedure } from "@/server/orpc";const tags = ["projects"];export default {getAll: protectedProcedure({permission: {project: ["read"],},}).route(/* ... */).input(/* ... */).output(z.object({items: z.array(zProject()),nextCursor: z.string().cuid().nullish(),total: z.number(),})).handler(/* ... */),};