Overview

The PostgreSQL data source connector connects Prisma to a PostgreSQL database server.

Example

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

datasource postgresql {
provider = "postgresql"
url = env("DATABASE_URL")
}

The fields passed to the datasource block are:

Connection details

Connection URL

Prisma follows the official PostgreSQLl format for connection URLs. Here's an overview of the components needed for a PostgreSQL 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:

postgresql://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
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:

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

The following arguments can be used

Argument nameRequiredDefaultDescription
schemaYespublicName of the schema you want to use, e.g. myschema
connection_limitNonum_cpus * 2 + 1Maximum size of the connection pool)
connect_timeoutNo5Maximum number of seconds to wait for a new connection
socket_timeoutNo5Maximum number of seconds to wait until a single query terminates
sslmodeNopreferConfigures whether to use TLS, possible values: prefer, disable, require
sslcertNoPath the the server certificate
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
hostNoPoints to a directory that contains a socket to be used for the connection

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

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 string 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 the 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.
  • 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.

To recap, in order to create a SSL connection to your database, you need:

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 string (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:POST@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.

Type mapping between PostgreSQL to Prisma schema

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

Prisma Migrate

PrismaPostgreSQL
Stringtext
Booleanboolean
Intinteger
Floatreal
Datetimetimestamp
Jsonjsonb

Introspection

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

PostgreSQLPrismaSupported
smallint | int2Int✔️
integer | int, int4Int✔️
bigint | int8Int✔️
numeric(p,s) | decimal(p,s)Float✔️
real | float, float4Float✔️
double precision | float8Float✔️
smallserial | serial2Int✔️
serial | serial4Int✔️
bigserial | serial8Int✔️
moneyFloat✔️
character(n) | char(n)String✔️
character varying(n) | varchar(n)String✔️
textString✔️
timestamp with time zone | timestamptzDateTime✔️
dateDateTime✔️
time without time zone | timeDateTime✔️
time with time zone | timetzDateTime✔️
boolean | boolBool✔️
enumEnum✔️
inetString✔️
bit(n)String✔️
bit varying(n)String✔️
uuidString✔️
jsonJson✔️
jsonbJson✔️
Array types[]✔️
intervalStringNot yet
cidrStringNot yet
macaddrStringNot yet
tsvectorStringNot yet
tsqueryStringNot yet
oidIntNot yet
int4rangeStringNot yet
int8rangeStringNot yet
numrangeStringNot yet
tsrangeStringNot yet
tstzrangeStringNot yet
daterangeStringNot yet
xmln/aNot yet
bytean/aNot yet
pointn/aNot yet
linen/aNot yet
lsegn/aNot yet
boxn/aNot yet
pathn/aNot yet
polygonn/aNot yet
circlen/aNot yet
Composite typesn/aNot yet
Domain typesn/aNot yet

During introspection, fields with types that already have match in the Prisma schema but are not yet supported will be added to the Prisma schema as comments, e.g. macaddr would be added to a model as follows:

model Device {
id Int @id @default(autoincrement())
name String
// This type is currently not supported.
// mac String
}

Fields with types that do not yet have match in the Prisma schema (and are not yet supported) will be also be added as comments to the Prisma schema as comments using the PostgreSQL type, e.g. macaddr would be added to a model as follows:

model Device {
id Int @id @default(autoincrement())
name String
// This type is currently not supported.
// data xml
}
Edit this page on Github