Next.js 14 CRUD and Search with Prisma Mysql | TailwindCSS DaisyUI

Next.js 14 CRUD and Search with Prisma Mysql | TailwindCSS DaisyUI

Prisma is an open-source next-generation ORM. It consists of the following parts: Prisma Client: Auto-generated and type-safe query builder
https://www.prisma.io

Install prisma/client
npm install @prisma/client
https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/generating-prisma-client

Generate Prisma Client with the following command:
npx prisma generate

Install prisma
npm install prisma --save-dev
https://www.prisma.io/docs/getting-started/quickstart

set up Prisma with the init command of the Prisma CLI:
npx prisma init --datasource-provider sqlite
to npx prisma init --datasource-provider postgres

Model data in the Prisma schema
prisma/schema.prisma
model Employee {
id String @id @default(cuid())
name String
email String
phone String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}

Run a migration to create your database tables with Prisma Migrate
npx prisma migrate dev --name init

.env
DATABASE_URL="mysql://root:root@localhost:8889/nextjsdb"

https://www.prisma.io/docs/orm/prisma-client/queries/crud

https://nextjs.org/docs/app/building-your-application/routing/route-handlers

app\employee\page.tsx

//app\employee\page.tsx
import Link from "next/link";
import TableData from "@/components/tabledata";
import { Suspense } from "react";
import { Spinner } from "@/components/spinner";
import Search from "@/components/search";
 
const Home = async ({
        searchParams,
    }: {
        searchParams?: {
            query?: string;
        };
    }) => {
    const query = searchParams?.query || "";
    return (
    <div className="w-screen py-20 flex justify-center flex-col items-center">
      <div className="flex items-center justify-between gap-1 mb-5">
        <h1 className="text-4xl font-bold">Next.js 14 CRUD and Search with Prisma Mysql | TailwindCSS DaisyUI</h1>
      </div>    
        <div className="overflow-x-auto">
          <div className="mb-2 w-full text-right">
            <Link
              href="/employee/create"
              className="btn btn-primary">
              Create
            </Link>
          </div>
          <Search />
          <Suspense key={query} fallback={<Spinner />}>
            <TableData query={query}/>
          </Suspense>
      </div>  
    </div>
  ); 
};
  
export default Home;

app\employee\create\page.tsx

//app\employee\create\page.tsx
"use client";
  
import { useFormState } from "react-dom";
import { saveEmployee } from "@/lib/action";
  
const CreateEmployeePage = () => {
    const [state, formAction] = useFormState(saveEmployee, null);
    return (
    <div className="max-w-md mx-auto mt-5">
        <h1 className="text-2xl text-center mb-2">Add New Employee</h1>
        <div>
        <form action={formAction}>
        <div className="mb-5">
          <label htmlFor="name" className="block text-sm font-medium text-gray-900">
            Full Name
          </label>
          <input
            type="text"
            name="name"
            id="name"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Full Name..."
          />
          <div id="name-error" aria-live="polite" aria-atomic="true">
            <p className="mt-2 text-sm text-red-500">{state?.Error?.name}</p>
          </div>
        </div>
        <div className="mb-5">
          <label htmlFor="email" className="block text-sm font-medium text-gray-900">
            Email
          </label>
          <input
            type="email"
            name="email"
            id="email"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Email..."
          />
          <div id="email-error" aria-live="polite" aria-atomic="true">
            <p className="mt-2 text-sm text-red-500">{state?.Error?.email}</p>
          </div>
        </div>
        <div className="mb-5">
          <label
            htmlFor="phone" className="block text-sm font-medium text-gray-900">
            Phone Number
          </label>
          <input
            type="text"
            name="phone"
            id="phone"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Phone Number..."
          />
          <div id="phone-error" aria-live="polite" aria-atomic="true">
            <p className="mt-2 text-sm text-red-500">{state?.Error?.phone}</p>
          </div>
        </div>
          
        <button className="btn btn-primary">Save</button>
      </form>
    </div>
    </div>
  );
};
  
export default CreateEmployeePage;

app\employee\edit\[id]\page.tsx

//app\employee\edit\[id]\page.tsx
import UpdateForm from "@/components/editform";
import { getEmployeeById } from "@/lib/action";
import { notFound } from "next/navigation";
  
const UpdateEmployeePage = async ({ params }: { params: { id: string } }) => {
    const id = params.id;
    const employee = await getEmployeeById(id);
    //console.log(employee);
  
    if (!employee) {
        notFound();
    }
  
    return (
    <div className="max-w-md mx-auto mt-5">
      <h1 className="text-2xl text-center mb-2">Update Employee</h1>
        <UpdateForm employee={employee} />
    </div>
  );
};
  
export default UpdateEmployeePage;

components\tabledata.tsx

//components\tabledata.tsx
import Link from "next/link";
//import { getEmployeelist } from "@/lib/action";
import { getData } from "@/lib/action";
import { formatDate } from "@/lib/utils";
import { DeleteButton } from "@/components/delete";
 
const Employee = async ({
    query
    }: {
    query: string;
    }) => {
    const employees = await getData(query);
    //const employees = await getEmployeelist(query);
    return (
        <table className="table table-zebra">
        <thead className="text-sm text-gray-700 uppercase bg-gray-50">
            <tr>
            <th className="py-3 px-6">#</th>
            <th className="py-3 px-6">Name</th>
            <th className="py-3 px-6">Email</th>
            <th className="py-3 px-6">Phone Number</th>
            <th className="py-3 px-6">Created At</th>
            <th className="py-3 px-6 text-center">Actions</th>
            </tr>
        </thead>
        <tbody>
            {employees.map((rs, index) => (
            <tr key={rs.id} className="bg-white border-b">
                <td className="py-3 px-6">{index + 1}</td>
                <td className="py-3 px-6">{rs.name}</td>
                <td className="py-3 px-6">{rs.email}</td>
                <td className="py-3 px-6">{rs.phone}</td>
                <td className="py-3 px-6">
                {formatDate(rs.createdAt.toString())}
                </td>
                <td className="flex justify-center gap-1 py-3">
                    <Link
                        href={`/employee/edit/${rs.id}`} 
                        className="btn btn-info"
                        >
                        Edit
                    </Link>
                    <DeleteButton id={rs.id} />
                </td>
            </tr>
            ))}
        </tbody>
        </table>
  );
};
  
export default Employee;

components\editform.tsx

//components\editform.tsx
"use client";
  
import { updateEmployee } from "@/lib/action";
import { useFormState } from "react-dom";
import type { Employee } from "@prisma/client";
  
const UpdateForm = ({ employee }: { employee: Employee }) => {
    const UpdateEmployeeWithId = updateEmployee.bind(null, employee.id);
    const [state, formAction] = useFormState(UpdateEmployeeWithId, null);
  
    return (
    <div>
      <form action={formAction}>
        <div className="mb-5">
          <label htmlFor="name" className="block text-sm font-medium text-gray-900">
            Full Name
          </label>
          <input
            type="text"
            name="name"
            id="name"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Full Name..."
            defaultValue={employee.name}
          />
          <div id="name-error" aria-live="polite" aria-atomic="true">
            <p className="mt-2 text-sm text-red-500">{state?.Error?.name}</p>
          </div>
        </div>
        <div className="mb-5">
          <label htmlFor="email" className="block text-sm font-medium text-gray-900">
            Email
          </label>
          <input
            type="text"
            name="email"
            id="email"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Email..."
            defaultValue={employee.email}
          />
          <div id="email-error" aria-live="polite" aria-atomic="true">
            <p className="mt-2 text-sm text-red-500">{state?.Error?.email}</p>
          </div>
        </div>
        <div className="mb-5">
          <label htmlFor="phone" className="block text-sm font-medium text-gray-900">
            Phone Number
          </label>
          <input
            type="text"
            name="phone"
            id="phone"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Phone Number..."
            defaultValue={employee.phone}
          />
          <div id="phone-error" aria-live="polite" aria-atomic="true">
            <p className="mt-2 text-sm text-red-500">{state?.Error?.phone}</p>
          </div>
        </div>
        <div id="message-error" aria-live="polite" aria-atomic="true">
          <p className="mt-2 text-sm text-red-500">{state?.message}</p>
        </div>
        <button className="btn btn-primary">Update</button>
      </form>
    </div>
  );
};
  
export default UpdateForm;

components\search.tsx

//components\search.tsx
"use client";
  
import { useSearchParams, usePathname, useRouter } from "next/navigation";
import { useDebouncedCallback } from "use-debounce"; //npm i use-debounce https://www.npmjs.com/package/use-debounce
  
const Search = () => {
  const searchParams = useSearchParams();
  const pathname = usePathname();
  const { replace } = useRouter();
  
  const handleSearch = useDebouncedCallback((term: string) => {
    console.log(term);
    const params = new URLSearchParams(searchParams);
    if (term) {
        params.set("query", term);
    } else {
      params.delete("query");
    }
    replace(`${pathname}?${params.toString()}`);
  }, 300);
  
  return (
    <div className="relative flex flex-1 mb-5 ml-5 mr-5">
      <input
        type="text"
        className="input input-bordered input-accent w-full"
        placeholder="Search..."
        onChange={(e) => handleSearch(e.target.value)}
        defaultValue={searchParams.get("query")?.toString()}
      />
    </div>
  );
};
  
export default Search;

components\delete.tsx

//components\delete.tsx
import { deleteEmployee } from "@/lib/action";
  
export const DeleteButton = ({ id }: { id: string }) => {
  const DeleteEmployeetWithId = deleteEmployee.bind(null, id);
  return (
    <form action={DeleteEmployeetWithId}>
      <button className="btn btn-error">
        Delete
      </button>
    </form>
  );
};

components\spinner.tsx

//components\spinner.tsx
export const Spinner = () => {
  return (
    <span className="loading loading-spinner loading-lg"></span>
  );
};

lib\action.ts

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

//lib\action.ts
"use server";
 
import { z } from "zod"; //npm i zod https://www.npmjs.com/package/zod
import { prisma } from "@/lib/prisma";
import { revalidatePath } from "next/cache";
import { redirect } from "next/navigation";
 
const EmployeeSchema = z.object({
  name: z.string().min(6),
  email: z.string().min(6),
  phone: z.string().min(11),
});
 
export const saveEmployee = async (prevSate: any, formData: FormData) => {
  const validatedFields = EmployeeSchema.safeParse(
    Object.fromEntries(formData.entries())
  );
  
  if (!validatedFields.success) {
    return {
      Error: validatedFields.error.flatten().fieldErrors,
    };
  }
  
  try {
    await prisma.employee.create({
      data: {
        name: validatedFields.data.name,
        email: validatedFields.data.email,
        phone: validatedFields.data.phone,
      },
    });
      
  } catch (error) {
    return { message: "Failed to create new employee" };
  }
  
  revalidatePath("/employee");
  redirect("/employee");
};
 
export const getEmployeelist = async (query: string) => {
  try {
    const employees = await prisma.employee.findMany({
      select: {
        id: true,
        name: true,
        email: true,
        phone: true,
        createdAt: true,
      },
      orderBy: {
        createdAt: "desc",
      },
    });
    return employees;
  } catch (error) {
    throw new Error("Failed to fetch employees data");
  }
};
 
export const getData = async (query: string) => {
  try {
    const employees = await prisma.employee.findMany({
        where: {
            name: {
            contains: query,
            },
        },
        orderBy: {
            createdAt: "desc",
        },
    });
    return employees;
  } catch (error) {
    throw new Error("Failed to fetch employees data");
  }
};
 
export const getEmployeeById = async (id: string) => {
  try {
    const employee = await prisma.employee.findUnique({
      where: { id },
    });
    return employee;
  } catch (error) {
    throw new Error("Failed to fetch contact data");
  }
};
 
export const updateEmployee = async (
  id: string,
  prevSate: any,
  formData: FormData
) => {
  const validatedFields = EmployeeSchema.safeParse(
    Object.fromEntries(formData.entries())
  );
  
  if (!validatedFields.success) {
    return {
      Error: validatedFields.error.flatten().fieldErrors,
    };
  }
  
  try {
    await prisma.employee.update({
      data: {
        name: validatedFields.data.name,
        email: validatedFields.data.email,
        phone: validatedFields.data.phone,
      },
      where: { id },
    });
  } catch (error) {
    return { message: "Failed to update employee" };
  }
  
  revalidatePath("/employee");
  redirect("/employee");
};
 
export const deleteEmployee = async (id: string) => {
  try {
    await prisma.employee.delete({
      where: { id },
    });
  } catch (error) {
    return { message: "Failed to delete employee" };
  }
  
  revalidatePath("/employee");
};

lib\prisma.ts

//lib\prisma.ts
import { PrismaClient } from "@prisma/client";
  
declare global {
  var prisma: PrismaClient | undefined;
}
  
export const prisma = globalThis.prisma || new PrismaClient();
  
if (process.env.NODE_ENV !== "production") globalThis.prisma = prisma;

lib\utils.ts

//lib\utils.ts
export const formatDate = (dateStr: string) => {
  const date = new Date(dateStr);
  const formatter = new Intl.DateTimeFormat("id-ID", {
    dateStyle: "medium",
    timeStyle: "short",
  });
  return formatter.format(date);
};

prisma\schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
 
// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init
 
generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}
 
model Employee {
  id String @id @default(cuid())
  name String
  email String
  phone String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

.env
DATABASE_URL="mysql://root:root@localhost:8889/nextjsdb"

run C:\nextjs>npm run dev

Tutorial101

article

Wednesday, March 13, 2024

Next.js 14 CRUD and Search with Prisma Mysql | TailwindCSS DaisyUI

Related Post

Search This Blog

HOME

Category

Popular Posts