Skip to main content

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.

Example

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

schema.prisma
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}

The fields passed to the datasource block are:

  • provider: Specifies the mysql data source connector, which is used both for MySQL and MariaDB.
  • url: Specifies the connection URL for the MySQL database server. In this case, an environment variable is used to provide the connection URL.

Connection details

Connection URL

Here's an overview of the components needed for a MySQL connection URL:

Structure of the MySQL connection URL

Base URL and path

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:

NamePlaceholderDescription
HostHOSTIP address/domain of your database server, e.g. localhost
PortPORTPort on which your database server is running, e.g. 5432 (default is 3306, or no port when using Unix socket)
UserUSERName of your database user, e.g. janedoe
PasswordPASSWORDPassword for your database user
DatabaseDATABASEName of the database you want to use, e.g. mydb

Arguments

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:

Argument nameRequiredDefaultDescription
connection_limitNonum_cpus * 2 + 1Maximum size of the connection pool
connect_timeoutNo5Maximum number of seconds to wait for a new connection to be opened, 0 means no timeout
pool_timeoutNo10Maximum number of seconds to wait for a new connection from the pool, 0 means no timeout
sslcertNoPath to the server certificate. Certificate paths are resolved relative to the ./prisma folder
sslidentityNoPath to the PKCS12 certificate
sslpasswordNoPassword that was used to secure the PKCS12 file
sslacceptNoaccept_invalid_certsConfigures whether to check for missing values in the certificate. Possible values: accept_invalid_certs, strict
socketNoPoints to a directory that contains a socket to be used for the connection
socket_timeoutNoNumber of seconds to wait until a single query terminates

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

Configuring an SSL connection

You can add various parameters to the connection URL if your database server uses SSL. Here's an overview of the possible parameters:

  • 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

Connecting via sockets

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.

Type mapping between MySQL to Prisma schema

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.

Native type mapping from Prisma ORM to MySQL

Prisma ORMMySQLNotes
StringVARCHAR(191)
BooleanBOOLEANIn MySQL BOOLEAN is a synonym for TINYINT(1)
IntINT
BigIntBIGINT
FloatDOUBLE
DecimalDECIMAL(65,30)
DateTimeDATETIME(3)Currently, Prisma ORM does not support zero dates (0000-00-00, 00:00:00) in MySQL
JsonJSONSupported in MySQL 5.7+ only
BytesLONGBLOB

Native type mapping from Prisma ORM to MariaDB

Prisma ORMMariaDBNotes
StringVARCHAR(191)
BooleanBOOLEANIn MariaDB BOOLEAN is a synonym for TINYINT(1)
IntINT
BigIntBIGINT
FloatDOUBLE
DecimalDECIMAL(65,30)
DateTimeDATETIME(3)
JsonLONGTEXTSee https://mariadb.com/kb/en/json-data-type/
BytesLONGBLOB

Native type mappings

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

MySQLPrisma ORMSupportedNative database type attributeNotes
serialBigInt✔️@db.UnsignedBigInt @default(autoincrement())
bigintBigInt✔️@db.BigInt
bigint unsignedBigInt✔️@db.UnsignedBigInt
bitBytes✔️@db.Bit(x)bit(1) maps to Boolean - all other bit(x) map to Bytes
boolean | tinyint(1)Boolean✔️@db.TinyInt(1)
varbinaryBytes✔️@db.VarBinary
longblobBytes✔️@db.LongBlob
tinyblobBytes✔️@db.TinyBlob
mediumblobBytes✔️@db.MediumBlob
blobBytes✔️@db.Blob
binaryBytes✔️@db.Binary
dateDateTime✔️@db.Date
datetimeDateTime✔️@db.DateTime
timestampDateTime✔️@db.TimeStamp
timeDateTime✔️@db.Time
decimal(a,b)Decimal✔️@db.Decimal(x,y)
numeric(a,b)Decimal✔️@db.Decimal(x,y)
enumEnum✔️N/A
floatFloat✔️@db.Float
doubleFloat✔️@db.Double
smallintInt✔️@db.SmallInt
smallint unsignedInt✔️@db.UnsignedSmallInt
mediumintInt✔️@db.MediumInt
mediumint unsignedInt✔️@db.UnsignedMediumInt
intInt✔️@db.Int
int unsignedInt✔️@db.UnsignedInt
tinyintInt✔️@db.TinyInt(x)tinyint(1) maps to Boolean all other tinyint(x) map to Int
tinyint unsignedInt✔️@db.UnsignedTinyInt(x)tinyint(1) unsigned does not map to Boolean
yearInt✔️@db.Year
jsonJson✔️@db.JsonSupported in MySQL 5.7+ only
charString✔️@db.Char(x)
varcharString✔️@db.VarChar(x)
tinytextString✔️@db.TinyText
textString✔️@db.Text
mediumtextString✔️@db.MediumText
longtextString✔️@db.LongText
setUnsupportedNot yet
geometryUnsupportedNot yet
pointUnsupportedNot yet
linestringUnsupportedNot yet
polygonUnsupportedNot yet
multipointUnsupportedNot yet
multilinestringUnsupportedNot yet
multipolygonUnsupportedNot yet
geometrycollectionUnsupportedNot yet

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

schema.prisma
model Device {
id Int @id @default(autoincrement())
name String
data Unsupported("circle")
}

Engine

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).

Permissions

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 simply 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 commands these permissions need to be granted:

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