How to connect your prisma in PostgreSQL hosted in Square Cloud

​

Introduction

• This tutorial will guide you through the steps to connect your Prisma with your PostgreSQL database hosted on Square Cloud.
• First, you need to have an active paid plan on your account. You can view our plans and purchase one according to your needs here.
• Next, you’ll need a PostgreSQL database hosted on Square Cloud. If you haven’t created one yet, you can create it here.

​

Prerequisites

• Before you begin, ensure you have created database in Square Cloud and connect on some manager client like dbeaver or any other to create a database.

• You will also need to have Prisma set up in your project. If you haven’t done this yet, you can follow the official Prisma documentation here.

​

Connecting prisma with postgresql

​

Database URL

postgresql://username:password@host:port/dbname

postgresql://username:password@host:port/squarecloud

​

Certificates

• Prisma CLI, v6 or lower
• Prisma v7 or higher

1

Getting base certificates

In the database connection settings, get the certificate.pem. With this certificate, do a copy of it and removes the key part and in the other, remove the certificate part. Name them as key.pem and cert.pem to know which is which.

2

Creating client-identity.p12

With the previous 2 files .pem, the key and cert, you will create the .p12 certificate. To do so, you will need to have openssl tool installed or use it through Git Bash. To convert the .pem files to .p12, you can use the following OpenSSL command:

Copy

Ask AI

openssl pkcs12 -export -out path/to/create/client-identity.p12 -inkey path/to/key.pem -in path/to/cert.pem

Replace path/to/key.pem and path/to/cert.pem with the actual paths to your key and certificate files. You will be prompted to set an export password. Remember this password, as you will need it later. Save the client-identity.p12 file in a folder within your prisma folder, for example, in a folder named certs.

3

Setting up certificate

Update your .env file to include the connection string and SSL configuration. In your Prisma project, locate the .env file and update the DATABASE_URL variable with your PostgreSQL connection string. Make sure to include the SSL configuration in the connection string. It should look something like this:

Copy

Ask AI

DATABASE_URL="postgresql://username:password@host:port/database?sslidentity=./certs/client-identity.p12&sslpassword=your_export_password"

1

Getting the certificate

In the database connection settings, get the certificate.pem. This will be our ca, cert and key certificate to connect in our database.

2

Setting up certificate

Update your Prisma connection to include the .pem certificate. You can check how to do this in the example below:

prisma.config.ts

Copy

Ask AI

import { PrismaClient } from "../../../generated/prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";
import fs from "fs";
import path from "node:path";

const certificatePath = path.resolve("prisma/certs/certificate.pem");
const certificate = fs.readFileSync(certificatePath, "utf-8");
const adapter = new PrismaPg({
connectionString: process.env.DATABASE_URL,
ssl: {
    ca: certificate,
    cert: certificate,
    key: certificate,
    rejectUnauthorized: true
}
});
export const prisma = new PrismaClient({
adapter,
});