# Prisma
Source: https://docs.nestjs.com/data/prisma
[Prisma](https://www.prisma.io) is an [open-source](https://github.com/prisma/prisma) ORM for Node.js and TypeScript. You can use it as an **alternative** to writing plain SQL or to other database access tools, such as SQL query builders (like [knex.js](https://knexjs.org/)) or other ORMs (like [TypeORM](https://typeorm.io/) and [Sequelize](https://sequelize.org/)). Prisma supports PostgreSQL, MySQL, SQL Server, SQLite, CockroachDB, and MongoDB (see [supported databases](https://www.prisma.io/docs/orm/reference/supported-databases)). This chapter uses Prisma ORM 7, which works with the SQL databases; MongoDB projects stay on Prisma ORM 6 for now.
While you can use Prisma with plain JavaScript, it embraces TypeScript and provides a level of type safety that goes beyond the guarantees of other ORMs in the TypeScript ecosystem. See the [comparison of the type safety guarantees of Prisma and TypeORM](https://www.prisma.io/docs/orm/more/comparisons/prisma-and-typeorm#type-safety) for details.
Prisma has no Nest-specific package. Instead, you generate a type-safe client (Prisma Client) from your Prisma schema, wrap it in a provider, and inject that provider wherever you need database access. This chapter sets up that integration with a [SQLite](https://sqlite.org/) database, which needs no database server, and notes the differences for PostgreSQL, MySQL, and SQL Server along the way.
> info **Note** For an overview of how Prisma works, follow the [Quickstart](https://www.prisma.io/docs/getting-started/prisma-orm/quickstart/prisma-postgres) or read the [Introduction](https://www.prisma.io/docs/orm/overview/introduction/what-is-prisma) in the [Prisma documentation](https://www.prisma.io/docs). To add Prisma to a project that already has a database, follow the guide for [adding Prisma to an existing project](https://www.prisma.io/docs/getting-started/setup-prisma/add-to-existing-project-typescript-postgres). If you are migrating from TypeORM, read [Migrating from TypeORM to Prisma](https://www.prisma.io/docs/guides/migrate-from-typeorm).
#### Installation
Install the [Prisma CLI](https://www.prisma.io/docs/orm/tools/prisma-cli) and `dotenv` as development dependencies, then install Prisma Client and the driver adapter for your database. For SQLite, the `@prisma/adapter-better-sqlite3` adapter also installs the `better-sqlite3` driver:
```bash
$ npm install prisma@7 dotenv --save-dev
$ npm install @prisma/client@7 @prisma/adapter-better-sqlite3
```
> warning **Warning** Install Prisma ORM 7 explicitly, as shown above. At the time of writing, the `latest` tag of the `prisma` package on npm points to a Prisma 8 pre-release, which is a different CLI that doesn't provide the `prisma migrate` and `prisma generate` commands used in this chapter. Keep `prisma` and `@prisma/client` on the same major version.
Expand if you're using PostgreSQL, MySQL, SQL Server, or Azure SQL
Install the adapter for your database instead of `@prisma/adapter-better-sqlite3`. Each adapter package includes its database driver.
- For PostgreSQL:
```bash
$ npm install @prisma/adapter-pg
```
- For MySQL (and MariaDB):
```bash
$ npm install @prisma/adapter-mariadb
```
- For SQL Server and Azure SQL:
```bash
$ npm install @prisma/adapter-mssql
```
Expand if you're using PostgreSQL, MySQL, SQL Server, or Azure SQL
With PostgreSQL, MySQL, and SQL Server, the connection URL points to the _database server_. Pass the matching provider to `prisma init` (e.g., `--datasource-provider postgresql`), or change the `provider` field of the `datasource` block, and set `DATABASE_URL` in `.env`. See the [connection URL reference](https://www.prisma.io/docs/orm/reference/connection-urls) for the required format.
**PostgreSQL**
```groovy
datasource db {
provider = "postgresql"
}
```
```bash
DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA"
```
Replace the uppercase placeholders with your database credentials. If you're unsure what to provide for the `SCHEMA` placeholder, it's most likely the default value `public`. If you don't have a PostgreSQL server yet, run `npx prisma dev` to start a local Prisma Postgres database in your terminal.
**MySQL**
```groovy
datasource db {
provider = "mysql"
}
```
```bash
DATABASE_URL="mysql://USER:PASSWORD@HOST:PORT/DATABASE"
```
Replace the uppercase placeholders with your database credentials.
**Microsoft SQL Server / Azure SQL Server**
```groovy
datasource db {
provider = "sqlserver"
}
```
```bash
DATABASE_URL="sqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;encrypt=true"
```
Replace the uppercase placeholders with your database credentials. If you're unsure what to provide for the `encrypt` option, it's most likely the default value `true`.
Expand to view the generated SQL statements
The following tables were created in your SQLite database:
```sql
-- CreateTable
CREATE TABLE "User" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"email" TEXT NOT NULL,
"name" TEXT
);
-- CreateTable
CREATE TABLE "Post" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"title" TEXT NOT NULL,
"content" TEXT,
"published" BOOLEAN DEFAULT false,
"authorId" INTEGER,
CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);
-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
```
Expand if you're using PostgreSQL, MySQL, SQL Server, or Azure SQL
Replace `PrismaBetterSqlite3` with the adapter class for your database (`PrismaPg`, `PrismaMariaDb`, or `PrismaMssql`), and pass it the connection settings its driver expects. Each of these adapters also accepts a connection string. For example, with PostgreSQL:
```typescript
import { PrismaPg } from '@prisma/adapter-pg';
// inside the PrismaService constructor
const adapter = new PrismaPg({
connectionString: configService.getOrThrow('DATABASE_URL'),
});
super({ adapter });
```