MySQL/MariaDB

The MySQL data source connector connects Prisma ORM to a MySQL or MariaDB database server.

By default, the MySQL connector contains a database driver responsible for connecting to your database. You can use a driver adapter (Preview) to connect to your database using a JavaScript database driver from Prisma Client.

To connect to a MySQL database server, you need to configure a datasource block in your Prisma schema:

datasource db {

  provider = "mysql"

}

The datasource block specifies the mysql data source connector, which is used for both MySQL and MariaDB.

In Prisma ORM 7, the database connection URL is configured in prisma.config.ts:

import { defineConfig, env } from "prisma/config";

import "dotenv/config";

export default defineConfig({

  schema: "prisma/schema.prisma",

  datasource: {

    url: env("DATABASE_URL"),

  },

});

This configuration uses an environment variable to provide the database connection URL.

As of v5.4.0, you can use Prisma ORM with database drivers from the JavaScript ecosystem (instead of using Prisma ORM's built-in drivers). You can do this by using a driver adapter.

For MySQL and MariaDB, mariadb is one of the most popular drivers in the JavaScript ecosystem.

This section explains how you can use it with Prisma ORM and the @prisma/adapter-mariadb driver adapter.

First, install Prisma ORM's driver adapter for mariadb:

bun add @prisma/adapter-mariadb@6.19.3

pnpm add @prisma/adapter-mariadb@6.19.3

yarn add @prisma/adapter-mariadb@6.19.3

npm install @prisma/adapter-mariadb@6.19.3

Now, when you instantiate Prisma Client, you need to pass an instance of Prisma ORM's driver adapter to the PrismaClient constructor:

import "dotenv/config";

import { PrismaMariaDb } from "@prisma/adapter-mariadb";

import { PrismaClient } from "../generated/prisma/client";

const adapter = new PrismaMariaDb({

  host: "localhost",

  port: 3306,

  connectionLimit: 5,

});

const prisma = new PrismaClient({ adapter });

A MySQL connection URL has these components:

Here is an example of the structure of the base URL and the path using placeholder values in uppercase letters:

mysql://USER:PASSWORD@HOST:PORT/DATABASE

The following components make up the base URL of your database, they are always required:

A connection URL can also take arguments. Here is the same example from above with placeholder values in uppercase letters for three arguments:

mysql://USER:PASSWORD@HOST:PORT/DATABASE?KEY1=VALUE&KEY2=VALUE&KEY3=VALUE

The following arguments can be used:

As an example, if you want to set the connection pool size to 5 and configure a timeout for queries of 3 seconds, you can use the following arguments:

mysql://USER:PASSWORD@HOST:PORT/DATABASE?connection_limit=5&socket_timeout=3

You can add various parameters to the connection URL if your database server uses SSL. The possible parameters are:

• sslcert=<PATH>: Path to the server certificate. This is the root certificate used by the database server to sign the client certificate. You need to provide this if the certificate doesn't exist in the trusted certificate store of your system. For Google Cloud this likely is server-ca.pem. Certificate paths are resolved relative to the ./prisma folder

• sslidentity=<PATH>: Path to the PKCS12 certificate database created from client cert and key. This is the SSL identity file in PKCS12 format which you will generate using the client key and client certificate. It combines these two files in a single file and secures them via a password (see next parameter). You can create this file using your client key and client certificate by using the following command (using openssl):

  openssl pkcs12 -export -out client-identity.p12 -inkey client-key.pem -in client-cert.pem

• sslpassword=<PASSWORD>: Password that was used to secure the PKCS12 file. The openssl command listed in the previous step will ask for a password while creating the PKCS12 file, you will need to provide that same exact password here.

• sslaccept=(strict|accept_invalid_certs):

  • strict: Any missing value in the certificate will lead to an error. For Google Cloud, especially if the database doesn't have a domain name, the certificate might miss the domain/IP address, causing an error when connecting.
  • accept_invalid_certs (default): Bypass this check. Be aware of the security consequences of this setting.

Your database connection URL will look similar to this:

mysql://USER:PASSWORD@HOST:PORT/DATABASE?sslidentity=client-identity.p12&sslpassword=mypassword&sslcert=rootca.cert

To connect to your MySQL/MariaDB database via a socket, you must add a socket field as a query parameter to the connection URL (instead of setting it as the host part of the URI). The value of this parameter then must point to the directory that contains the socket, e.g. on a default installation of MySQL/MariaDB on Ubuntu or Debian use: mysql://USER:PASSWORD@HOST/DATABASE?socket=/run/mysqld/mysqld.sock

Note that localhost is required, the value itself is ignored and can be anything.

Note: You can find additional context in this GitHub issue.

The MySQL connector maps the scalar types from the Prisma ORM data model as follows to native column types:

Alternatively, see Prisma schema reference for type mappings organized by Prisma ORM type.

When introspecting a MySQL database, the database types are mapped to Prisma ORM according to the following table:

Introspection adds native database types that are not yet supported as Unsupported fields:

model Device {

  id   Int                   @id @default(autoincrement())

  name String

  data Unsupported("circle")

}

If you are using a version of MySQL where MyISAM is the default engine, you must specify ENGINE = InnoDB; when you create a table. If you introspect a database that uses a different engine, relations in the Prisma Schema are not created (or lost, if the relation already existed).

A fresh new installation of MySQL/MariaDB has by default only a root database user. Do not use root user in your Prisma configuration, but instead create a database and database user for each application. On most Linux hosts (e.g. Ubuntu) you can run this as the Linux root user (which automatically has database root access as well):

mysql -e "CREATE DATABASE IF NOT EXISTS $DB_PRISMA;"

mysql -e "GRANT ALL PRIVILEGES ON $DB_PRISMA.* TO $DB_USER@'%' IDENTIFIED BY '$DB_PASSWORD';"

The above is enough to run the prisma db pull and prisma db push commands. In order to also run prisma migrate dev, which creates temporary shadow databases named prisma_migrate_shadow_db_<id>, grant schema privileges on that name pattern instead of on all databases:

mysql -e "GRANT CREATE, ALTER, DROP, INDEX, REFERENCES ON \`prisma_migrate_shadow_db%\`.* TO $DB_USER@'%';"

INDEX is required because generated migrations create and drop indexes with standalone CREATE INDEX and DROP INDEX statements, which the CREATE and ALTER privileges alone do not permit.

If you use a dedicated shadow database via shadowDatabaseUrl, grant the same privileges on that database (for example `myapp_shadow`.*) instead of on the prisma_migrate_shadow_db% pattern.

Wildcard database names in GRANT are deprecated in MySQL as of 8.0.35, and when the partial_revokes system variable is enabled MySQL treats % as a literal character, so the grant above no longer matches the generated shadow database names. On such servers, configure a dedicated shadow database with shadowDatabaseUrl and grant the privileges on that database by name. See Database privileges in the MySQL GRANT reference. MariaDB supports wildcard database names without these caveats.