> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # Next.js and Prisma with MySQL > Build a Next.js App Router application on Prisma 7 and MySQL end to end: schema, migrations, the driver adapter Prisma 7 now requires, and a working read/write page. Every command in this guide runs unchanged against VillageSQL. All examples in this guide work on VillageSQL. Install Now → Prisma is one of the most widely used TypeScript ORMs, and Next.js's App Router is a common way new projects reach a database. This guide builds a small Next.js app that lists and creates rows in MySQL through Prisma 7, covering the parts that changed since older Prisma tutorials: connection URLs moved out of the schema file, and a driver adapter is no longer optional. Because VillageSQL is a drop-in replacement for MySQL, every command here works against it unchanged. ## Create the database Prisma needs two databases: the one your app uses, and a second "shadow" database it uses internally to compute migrations. Create both, and a dedicated user with rights to each: ```sql theme={null} CREATE DATABASE prisma_guide CHARACTER SET utf8mb4; CREATE DATABASE prisma_guide_shadow CHARACTER SET utf8mb4; CREATE USER 'prisma_app'@'127.0.0.1' IDENTIFIED BY 'change-me'; GRANT ALL PRIVILEGES ON prisma_guide.* TO 'prisma_app'@'127.0.0.1'; GRANT ALL PRIVILEGES ON prisma_guide_shadow.* TO 'prisma_app'@'127.0.0.1'; ``` The shadow database only needs to exist. Prisma creates and drops tables in it automatically. Creating it up front and granting the app user rights on that one database, instead of granting broader `CREATE`/`DROP` privileges on `*.*` so Prisma can create a shadow database itself, keeps the blast radius of a leaked password to two databases instead of the whole server. ## Scaffold the app ```bash theme={null} npx create-next-app@latest my-app --typescript --app --src-dir cd my-app npm install prisma@7 @prisma/client@7 ``` Pin the major version. At the time of writing, npm's `latest` tag for `prisma` points at an 8.0 release candidate: fine to try, but the wrong default for a guide meant to keep working. ## Initialize Prisma ```bash theme={null} npx prisma init --datasource-provider mysql --no-skills ``` `prisma init` runs a postinstall step by default that scaffolds Claude Code, Cursor, and Windsurf agent skills into your repo (`.claude/`, `.windsurf/`, `.agents/`, plus a `skills-lock.json`). `--no-skills` skips it. It's a genuinely useful feature if you're using an AI coding agent alongside Prisma, just not part of this guide's scope. This creates `prisma/schema.prisma`, a `.env` file, and `prisma7.config.ts` (the config filename is version-suffixed in Prisma 7). Set the connection strings: ```bash .env theme={null} DATABASE_URL="mysql://prisma_app:change-me@127.0.0.1:3306/prisma_guide" SHADOW_DATABASE_URL="mysql://prisma_app:change-me@127.0.0.1:3306/prisma_guide_shadow" ``` Older Prisma tutorials put the datasource URL directly in `schema.prisma`. As of Prisma 7, connection URLs, including the shadow database URL, live in `prisma7.config.ts`, not the schema file: ```ts prisma7.config.ts theme={null} import "dotenv/config"; import { defineConfig } from "prisma/config"; export default defineConfig({ schema: "prisma/schema.prisma", migrations: { path: "prisma/migrations" }, datasource: { url: process.env["DATABASE_URL"], shadowDatabaseUrl: process.env["SHADOW_DATABASE_URL"], }, }); ``` `schema.prisma` keeps the `generator` block `prisma init` created and now only declares the provider on the datasource, with no connection URL: ```prisma prisma/schema.prisma theme={null} generator client { provider = "prisma-client" output = "../src/generated/prisma" } datasource db { provider = "mysql" } model Post { id Int @id @default(autoincrement()) title String @db.VarChar(255) body String @db.Text createdAt DateTime @default(now()) } ``` ## Run the migration ```bash theme={null} npx prisma migrate dev --name init ``` This applies the schema, generates `prisma/migrations/_init/migration.sql`, and calls `prisma generate` for you. Confirm the table exists: ```sql theme={null} SHOW TABLES FROM prisma_guide; -- _prisma_migrations, Post ``` ## Connect with a driver adapter Prisma 7's generated client no longer connects on its own: `new PrismaClient()` with no arguments now fails a type check. You pass it a driver adapter, one package per database: ```bash theme={null} npm install @prisma/adapter-mariadb mariadb ``` `@prisma/adapter-mariadb` is the adapter for both MariaDB and MySQL: the wire protocol is shared, so it works against VillageSQL the same way it works against stock MySQL. Set up a singleton so Next.js's dev-mode hot reload doesn't open a new connection pool on every file save: ```ts src/lib/prisma.ts theme={null} import { PrismaClient } from "@/generated/prisma/client"; import { PrismaMariaDb } from "@prisma/adapter-mariadb"; const globalForPrisma = globalThis as unknown as { prisma: PrismaClient | undefined; }; const adapter = new PrismaMariaDb(process.env.DATABASE_URL!); export const prisma = globalForPrisma.prisma ?? new PrismaClient({ adapter }); if (process.env.NODE_ENV !== "production") { globalForPrisma.prisma = prisma; } ``` ### The First Connection to a New Account MySQL 8 and VillageSQL create accounts with the `caching_sha2_password` plugin. The first time a given account authenticates, the client has to complete an RSA key exchange; after that the server caches the result and later connections take a faster path that skips it. `@prisma/adapter-mariadb` refuses that exchange on an unencrypted connection, so on a server that has never authenticated this account, the build fails: ``` Database error. Code: `45028`. Message: `pool timeout: failed to retrieve a connection from pool after 10000ms (pool connections: active=0 idle=0 limit=10)` ``` Nothing in that message mentions authentication, and `active=0 idle=0` makes it read like the server is unreachable. The underlying driver error is more specific: ``` RSA public key is not available client side. Either set option `cachingRsaPublicKey` to indicate public key path, or allow public key retrieval with option `allowPublicKeyRetrieval` ``` For local development, add the option to the connection URL: ```bash .env theme={null} DATABASE_URL="mysql://prisma_app:change-me@127.0.0.1:3306/prisma_guide?allowPublicKeyRetrieval=true" ``` Retrieving the key over an unencrypted connection exposes the password to anyone able to intercept that first handshake, so use TLS rather than this option against anything but a local server. This is the same trap the [FastAPI guide](/docs/guides/fastapi-sqlalchemy-mysql) documents for `asyncmy`, and it is intermittent for the same reason: once any client has completed a full authentication for the account, including the `mysql` CLI, the cached result hides the problem until the cache is cleared by `FLUSH PRIVILEGES` or a server restart. The generated client's import path has two quirks. It has no `index.ts`, so you import `@/generated/prisma/client` (the file), not `@/generated/prisma` (the directory). And every export in that file is typed `@ts-nocheck`, since it's generated code you're meant to import, not read. ## Read and write from a Server Component ```tsx src/app/page.tsx theme={null} import { prisma } from "@/lib/prisma"; import { revalidatePath } from "next/cache"; async function createPost(formData: FormData) { "use server"; const title = formData.get("title") as string; const body = formData.get("body") as string; await prisma.post.create({ data: { title, body } }); revalidatePath("/"); } export default async function Home() { const posts = await prisma.post.findMany({ orderBy: { createdAt: "desc" } }); return (