PostgreSQL
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:
schema.prisma
1datasource db {2 provider = "postgresql"3 url = env("DATABASE_URL")4}
The fields passed to the datasource
block are:
provider
: Specifies thepostgresql
data source connector.url
: Specifies the connection URL for the PostgreSQL database server. In this case, an environment variable is used to provide the connection URL.
Connection details
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:
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:
Name | Placeholder | Description |
---|---|---|
Host | HOST | IP address/domain of your database server, e.g. localhost |
Port | PORT | Port on which your database server is running, e.g. 5432 |
User | USER | Name of your database user, e.g. janedoe |
Password | PASSWORD | Password for your database user |
Database | DATABASE | Name of the database you want to use, e.g. mydb |
You must percentage-encode special characters.
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 name | Required | Default | Description |
---|---|---|---|
schema | Yes | public | Name of the schema you want to use, e.g. myschema |
connection_limit | No | num_cpus * 2 + 1 | Maximum size of the connection pool |
connect_timeout | No | 5 | Maximum number of seconds to wait for a new connection to be opened, 0 means no timeout |
pool_timeout | No | 10 | Maximum number of seconds to wait for a new connection from the pool, 0 means no timeout |
sslmode | No | prefer | Configures whether to use TLS. Possible values: prefer , disable , require |
sslcert | No | Path of the server certificate. Certificate paths are resolved relative to the ./prisma folder | |
sslidentity | No | Path to the PKCS12 certificate | |
sslpassword | No | Password that was used to secure the PKCS12 file | |
sslaccept | No | accept_invalid_certs | Configures whether to check for missing values in the certificate. Possible values: accept_invalid_certs , strict |
host | No | Points to a directory that contains a socket to be used for the connection | |
socket_timeout | No | Maximum number of seconds to wait until a single query terminates | |
pgbouncer | No | false | Configure the Engine to enable PgBouncer compatibility mode |
application_name | No | Since 3.3.0: Specifies a value for the application_name configuration parameter | |
channel_binding | No | prefer | Since 4.8.0: Specifies a value for the channel_binding configuration parameter |
options | No | Since 3.8.0: Specifies command line options to send to the server at connection start |
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 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 isserver-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 (usingopenssl
):openssl pkcs12 -export -out client-identity.p12 -inkey client-key.pem -in client-cert.pemsslpassword=<PASSWORD>
: Password that was used to secure the PKCS12 file. Theopenssl
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.
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:
Alternatively, see Prisma schema reference for type mappings organized by Prisma type.
Native type mapping from Prisma to PostgreSQL
Prisma | PostgreSQL |
---|---|
String | text |
Boolean | boolean |
Int | integer |
BigInt | bigint |
Float | double precision |
Decimal | decimal(65,30) |
DateTime | timestamp(3) |
Json | jsonb |
Bytes | bytea |
Native type mappings
When introspecting a PostgreSQL database, the database types are mapped to Prisma according to the following table:
PostgreSQL (Type | Aliases) | Prisma | Supported | Native database type attribute | Notes |
---|---|---|---|---|
bigint | int8 | BigInt | ✔️ | @db.BigInt * | *Default mapping for BigInt - no type attribute added to schema. |
boolean | bool | Bool | ✔️ | @db.Boolean * | *Default mapping for Bool - no type attribute added to schema. |
timestamp with time zone | timestamptz | DateTime | ✔️ | @db.Timestamptz(x) | |
time without time zone | time | DateTime | ✔️ | @db.Time(x) | |
time with time zone | timetz | DateTime | ✔️ | @db.Timetz(x) | |
numeric(p,s) | decimal(p,s) | Decimal | ✔️ | @db.Decimal(x, y) | |
real | float , float4 | Float | ✔️ | @db.Real | |
double precision | float8 | Float | ✔️ | @db.DoublePrecision * | *Default mapping for Float - no type attribute added to schema. |
smallint | int2 | Int | ✔️ | @db.SmallInt | |
integer | int , int4 | Int | ✔️ | @db.Int * | *Default mapping for Int - no type attribute added to schema. |
smallserial | serial2 | Int | ✔️ | @db.SmallInt @default(autoincrement()) | |
serial | serial4 | Int | ✔️ | @db.Int @default(autoincrement()) | |
bigserial | serial8 | Int | ✔️ | @db.BigInt @default(autoincrement() | |
character(n) | char(n) | String | ✔️ | @db.Char(x) | |
character varying(n) | varchar(n) | String | ✔️ | @db.VarChar(x) | |
money | Decimal | ✔️ | @db.Money | |
text | String | ✔️ | @db.Text * | *Default mapping for String - no type attribute added to schema. |
timestamp | DateTime | ✔️ | @db.TimeStamp * | *Default mapping for DateTime - no type attribute added to schema. |
date | DateTime | ✔️ | @db.Date | |
enum | Enum | ✔️ | N/A | |
inet | String | ✔️ | @db.Inet | |
bit(n) | String | ✔️ | @Bit(x) | |
bit varying(n) | String | ✔️ | @VarBit | |
oid | Int | ✔️ | @db.Oid | |
uuid | String | ✔️ | @db.Uuid | |
json | Json | ✔️ | @db.Json | |
jsonb | Json | ✔️ | @db.JsonB * | *Default mapping for Json - no type attribute added to schema. |
bytea | Bytes | ✔️ | @db.ByteA * | *Default mapping for Bytes - no type attribute added to schema. |
xml | String | ✔️ | @db.Xml | |
Array types | [] | ✔️ | ||
citext | String | ✔️* | @db.Citext | * Only available if Citext extension is enabled. |
interval | Unsupported | Not yet | ||
cidr | Unsupported | Not yet | ||
macaddr | Unsupported | Not yet | ||
tsvector | Unsupported | Not yet | ||
tsquery | Unsupported | Not yet | ||
int4range | Unsupported | Not yet | ||
int8range | Unsupported | Not yet | ||
numrange | Unsupported | Not yet | ||
tsrange | Unsupported | Not yet | ||
tstzrange | Unsupported | Not yet | ||
daterange | Unsupported | Not yet | ||
point | Unsupported | Not yet | ||
line | Unsupported | Not yet | ||
lseg | Unsupported | Not yet | ||
box | Unsupported | Not yet | ||
path | Unsupported | Not yet | ||
polygon | Unsupported | Not yet | ||
circle | Unsupported | Not yet | ||
Composite types | n/a | Not yet | ||
Domain types | n/a | Not yet |
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 String4 data Unsupported("circle")5}