PostgreSQL

Connection URL

Prisma is based on the official PostgreSQL format for connection URLs, but does not support all arguments and includes additional arguments such as schema. Here's an overview of the components needed for a PostgreSQL connection URL:

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

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

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=myschema&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:

• sslmode=(disable|prefer|require):
  • prefer (default): Prefer TLS if possible, accept plain text connections.
  • disable: Do not use TLS.
  • require: Require TLS or fail if not possible.
• 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:

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

Connecting via sockets

To connect to your PostgreSQL database via sockets, you must add a host 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.: postgresql://USER:PASSWORD@localhost/database?host=/var/run/postgresql/

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

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

Mapping between Prisma scalar types and PostgreSQL database column types

The PostgreSQL connector maps the scalar types from the Prisma data model as follows to database column types:

Mapping between PostgreSQL database column types to Prisma scalar and native types

• When introspecting a PostgreSQL database, the database types are mapped to Prisma types according to the following table.
• When creating a migration or prototyping your schema the table is also used - in the other direction.

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

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