How to connect MySQL in your backend project (NestJS + Prisma) / Хабр

Hi! While working on a backend project, I ran into a simple but annoying problem — I couldn’t find a clear and complete guide on connecting MySQL to a basic backend project.

Most tutorials either:

skip important steps
assume too much prior knowledge
or don’t explain why things work the way they do

So in this guide, I’ll show you:

how to connect MySQL using NestJS + Prisma
how it works under the hood
and how to use it in a monorepo setup

📌 Why Prisma?

Before we start — a quick note.

Prisma is not just another ORM. It provides:

type-safe database queries
auto-generated client based on schema
better developer experience than classic ORMs like TypeORM

In simple terms: less SQL, fewer runtime errors, more productivity.

Create a NestJS project

npm i -g @nestjs/clinest new my-backend-projectcd my-backend-projectnpm install

Run it:

npm run start

Install Prisma

npm install prisma --save-devnpm install @prisma/client

Initialize Prisma:

npx prisma init

💡 Best practice: always use npx prisma instead of global CLI

📁 After initialization, you’ll get:

schema.prisma — database schema & connection
.env — environment variables
prisma.config.ts — project config

⚙️Configure MySQL

By default Prisma uses PostgreSQL or SQLite. We switch to MySQL. Now open .env:

DATABASE_URL="mysql://root:password@localhost:3306/my_db"
DATABASE_USER="root"
DATABASE_PASSWORD="password"
DATABASE_NAME="my_db"
DATABASE_HOST="localhost"
DATABASE_PORT=3306

And now open schema.prisma:

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
  moduleFormat  = "cjs"
}

datasource db {
  provider = "mysql"
}

This is the standard connection format for MySQL in Prisma

🧠 Generate Prisma Client

npx prisma generate

👉 Prisma generates a type-safe client based on your schema.
This means your queries are validated at compile time.

🧩Create PrismaService

import "dotenv/config";
import { PrismaMariaDb } from "@prisma/adapter-mariadb";
import { PrismaClient } from "../generated/prisma/client";
import { Injectable } from "@nestjs/common";

@Injectable()
export class PrismaService extends PrismaClient{
    constructor(){
        const adapter = new PrismaMariaDb({
        host: process.env.DATABASE_HOST,
        user: process.env.DATABASE_USER,
        password: process.env.DATABASE_PASSWORD,
        database: process.env.DATABASE_NAME,
        connectionLimit: 5,
        });
        super({adapter})
    }
}

💡 Why this is needed:

integrates Prisma into Nest lifecycle
ensures proper connection handling
avoids memory leaks in production

🧩Create PrismaModule

import { Module } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

🔌Use Prisma in your service

We’ll write our service in the constructor. I’ll show you how to use it with an example:

import { Injectable } from '@nestjs/common';
import { PrismaService } from 'prisma/prisma.service';
import { TodoDTO } from './dto/todo.dto';

@Injectable()
export class TodoService {
    constructor(private readonly todoService: PrismaService){} //this is mandatory

    public getOne(id:number){
        return this.todoService.todo.findFirst(
            {
                where: {id}
            }
        )
    }

    public getAll(){
        return this.todoService.todo.findMany()
    }

    public create(data: TodoDTO){
        return this.todoService.todo.create({data: data})
    }

    public upgrade(id: number, data: TodoDTO){
        return this.todoService.todo.update({
            where: {id},
            data: data
        })
    }

    public delete(id:number){
        return  this.todoService.todo.delete(
            {
                where: {id}
            }
        )
    }

}

📌 This is how Prisma is typically used in NestJS services

🔥Run migrations

npx prisma migrate dev --name initnpx prisma generate

🧩 Monorepo setup (short explanation)

In a monorepo:

keep Prisma in a separate package/lib
export only services (not raw client)
reuse across apps

This helps:

isolate DB logic
simplify scaling
switch databases easier

📚 Bonus — Official docs

Also, especially for you, I’ve included a reference card so you can remember and save it for future use. In this card, I’ve gathered all the material to make things easier for you.

If this guide helped you — feel free to support the post.
Thanks for your attention!

Author: Starikov Maxim