MySQL database connector (Reference) | Prisma Docs

The MySQL knowledge supply connector connects Prisma to a MySQL database server.

Instance

To hook up with a MySQL database server, that you must configure a datasource block in your Prisma schema file:

1datasource db {

2 supplier = "mysql"

3 url = env("DATABASE_URL")

4}

The fields handed to the datasource block are:

Connection particulars

Connection URL

Here is an outline of the elements wanted for a MySQL connection URL:

Structure of the MySQL connection URL

Base URL and path

Right here is an instance of the construction of the base URL and the path utilizing placeholder values in uppercase letters:

mysql://USER:[email protected]:PORT/DATABASE

The next elements make up the base URL of your database, they’re at all times required:

Title Placeholder Description
Host HOST IP tackle/area of your database server, e.g. localhost
Port PORT Port on which your database server is working, e.g. 5432
Consumer USER Title of your database person, e.g. janedoe
Password PASSWORD Password on your database person
Database DATABASE Title of the database you need to use, e.g. mydb

You should percentage-encode particular characters.

Arguments

A connection URL may take arguments. Right here is similar instance from above with placeholder values in uppercase letters for 3 arguments:

mysql://USER:[email protected]:PORT/DATABASE?KEY1=VALUE&KEY2=VALUE&KEY3=VALUE

The next arguments can be utilized:

Argument title Required Default Description
connection_limit No num_cpus * 2 + 1 Most measurement of the connection pool
connect_timeout No 5 Most variety of seconds to attend for a brand new connection to be opened, 0 means no timeout
pool_timeout No 10 Most variety of seconds to attend for a brand new connection from the pool, 0 means no timeout
sslcert No Path to the server certificates. Certificates paths are resolved relative to the ./prisma folder
sslidentity No Path to the PKCS12 certificates
sslpassword No Password that was used to safe the PKCS12 file
sslaccept No accept_invalid_certs Configures whether or not to test for lacking values within the certificates. Potential values: accept_invalid_certs, strict
socket No Factors to a listing that comprises a socket for use for the connection
socket_timeout No Variety of seconds to attend till a single question terminates

For instance, if you wish to set the connection pool measurement to 5 and configure a timeout for queries of 3 seconds, you need to use the next arguments:

mysql://USER:[email protected]:PORT/DATABASE?connection_limit=5&socket_timeout=3

Configuring an SSL connection

You’ll be able to add numerous parameters to the connection URL in case your database server makes use of SSL. Here is an outline of the attainable parameters:

  • sslcert=: Path to the server certificates. That is the basis certificates utilized by the database server to signal the purchaser certificates. It is advisable to present this if the certificates does not exist within the trusted certificates retailer of your system. For Google Cloud this doubtless is server-ca.pem. Certificates paths are resolved relative to the ./prisma folder

  • sslidentity=: Path to the PKCS12 certificates database created from purchaser cert and key. That is the SSL identification file in PKCS12 format which you’ll generate utilizing the purchaser key and purchaser certificates. It combines these two information in a single file and secures them by way of a password (see subsequent parameter). You’ll be able to create this file utilizing your purchaser key and purchaser certificates by utilizing the next command (utilizing openssl):

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

  • sslpassword=: Password that was used to safe the PKCS12 file. The openssl command listed within the earlier step will ask for a password whereas creating the PKCS12 file, you’ll need to supply that very same precise password right here.

  • sslaccept=(strict|accept_invalid_certs):

    • strict: Any lacking worth within the certificates will result in an error. For Google Cloud, particularly if the database does not have a website title, the certificates would possibly miss the area/IP tackle, inflicting an error when connecting.
    • accept_invalid_certs (default): Bypass this test. Pay attention to the safety penalties of this setting.

Your database connection URL will look just like this:

mysql://USER:[email protected]:PORT/DATABASE?sslidentity=client-identity.p12&sslpassword=mypassword&sslcert=rootca.cert

Connecting by way of sockets

To hook up with your MySQL database by way of sockets, you will need to add a socket area as a question parameter to the connection URL (as an alternative of setting it because the host a part of the URI).
The worth of this parameter then should level to the listing that comprises the socket, e.g.: mysql://USER:[email protected]/database?socket=/var/run/mysql/

Word that localhost is required, the worth itself is ignored and could be something.

Word: You will discover further context on this GitHub concern.

Kind mapping between MySQL to Prisma schema

The MySQL connector maps the scalar sorts from the Prisma knowledge mannequin as follows to native column sorts:

Alternatively, see Prisma schema reference for kind mappings organized by Prisma kind.

Prisma Migrate

Knowledge mannequin MySQL Notes
String VARCHAR(191)
Boolean BOOLEAN In MySQL BOOLEAN is a synonym for TINYINT(1)
Int INT
BigInt BIGINT
Float DOUBLE
Decimal DECIMAL(65,30)
Datetime DATETIME(3)
Json JSON Supported in MySQL 5.7+ solely
Bytes LONGBLOB

Native kind mappings

When introspecting a MySQL database, the database sorts are mapped to Prisma based on the next desk:

MySQL Prisma Supported Native database kind attribute Notes
serial BigInt ✔️ @db.UnsignedBigInt @default(autoincrement())
bigint BigInt ✔️ @db.BigInt
bigint unsigned BigInt ✔️ @db.UnsignedBigInt
bit Bytes ✔️ @db.Bit(x) bit(1) maps to Boolean – all different bit(x) map to Bytes
boolean | tinyint(1) Boolean ✔️ @db.TinyInt(1)
varbinary Bytes ✔️ @db.VarBinary
longblob Bytes ✔️ @db.LongBlob
tinyblob Bytes ✔️ @db.TinyBlob
mediumblob Bytes ✔️ @db.MediumBlob
blob Bytes ✔️ @db.Blob
binary Bytes ✔️ @db.Binary
date DateTime ✔️ @db.Date
datetime DateTime ✔️ @db.DateTime
timestamp DateTime ✔️ @db.TimeStamp
time DateTime ✔️ @db.Time
decimal(a,b) Decimal ✔️ @db.Decimal(x,y)
numeric(a,b) Decimal ✔️ @db.Decimal(x,y)
enum Enum ✔️ N/A
float Float ✔️ @db.Float
double Float ✔️ @db.Double
smallint Int ✔️ @db.SmallInt
smallint unsigned Int ✔️ @db.UnsignedSmallInt
mediumint Int ✔️ @db.MediumInt
mediumint unsigned Int ✔️ @db.UnsignedMediumInt
int Int ✔️ @db.Int
int unsigned Int ✔️ @db.UnsignedInt
tinyint Int ✔️ @db.TinyInt(x) tinyint(1) maps to Boolean all different tinyint(x) map to Int
tinyint unsigned Int ✔️ @db.UnsignedTinyInt(x) tinyint(1) unsigned doesn’t map to Boolean
yr Int ✔️ @db.Yr
json Json ✔️ @db.Json Supported in MySQL 5.7+ solely
char String ✔️ @db.Char(x)
varchar String ✔️ @db.VarChar(x)
tinytext String ✔️ @db.TinyText
textual content String ✔️ @db.Textual content
mediumtext String ✔️ @db.MediumText
longtext String ✔️ @db.LongText
set Unsupported Not but
geometry Unsupported Not but
level Unsupported Not but
linestring Unsupported Not but
polygon Unsupported Not but
multipoint Unsupported Not but
multilinestring Unsupported Not but
multipolygon Unsupported Not but
geometrycollection Unsupported Not but

Introspection provides native database sorts which can be not but supported as Unsupported fields:

1mannequin Gadget {

2 id Int @id @default(autoincrement())

3 title String

4 knowledge Unsupported("circle")

5}

Engine

If you’re utilizing a model of MySQL the place MyISAM is the default engine, you will need to specify ENGINE = InnoDB; once you create a desk. When you introspect a database that makes use of a unique engine, relations within the Prisma Schema aren’t created (or misplaced, if the relation already existed).

Edit this web page on GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *