> ## Knowledge Base Index
> Fetch the complete knowledge base index at: https://help.squarecloud.app/sitemap.xml
> Use this file to discover available pages before exploring further.
> Pure-Markdown content can be obtained by appending a '.md' suffix to the content URLs listed in the sitemap (without the trailing slash).

# How to create a PostgreSQL database and connect

## 1. Prerequisites

To follow this tutorial, you will need:

* **Standard Plan or higher:** Database hosting on Square Cloud is a feature available starting from the Standard plan.
* **Dashboard Access:** Your access credentials for the Square Cloud platform.

---

## 2. Creating the Database Instance

1. Access the [Square Cloud Dashboard](https://squarecloud.app/en/dashboard).
2. Go to the [Databases](https://squarecloud.app/en/dashboard/databases) page and select the **Create database** option.
3. Name your instance.
4. Choose **PostgreSQL** from the list of available databases.
5. Select the desired RAM and confirm the creation.
6. After initialization, you will have access to the essential credentials: `Host`, `Port`, `User`, and `Password`.

> **Tip:** To make things easier, Square Cloud automatically initializes a database named "squarecloud" inside the instance, but you can create your own within the instance.

---

## 3. Understanding SSL Certificates

Square Cloud requires encrypted connections to ensure data security. Unlike local connections, here you will need specific certificates provided by the platform.

In your database settings tab, you will find the following download options:

* **`certificate.pem`**: This is a combined file containing the private key (`key`) and the certificate (`cert`). For most connection drivers, this is the only file required.
* **`client-key.key`** and **`client-cert.crt`**: Provided separately for systems or drivers that require individual files for the key and the certificate.

> **Warning:** Some ORMs and drivers may require a `.p12` certificate, such as PrismaORM v6 and the CLI. If that is the case, download the `certificate.pem` and generate the `.p12` file.

---

## 4. Technical Connection Configuration

For PostgreSQL, a secure connection is established by pointing the SSL parameters to the certificate file. The technical secret here is that when using the `.pem` file, you must reference it in three distinct fields of the driver:

* **sslkey:** Path to `certificate.pem`
* **sslcert:** Path to `certificate.pem`
* **sslrootcert:** Path to `certificate.pem`

### Connection String Example (URI)

If you are connecting via a direct connection string, the format will be:

```text
postgresql://squarecloud:{password}@square-cloud-db-{id}.squareweb.app:{port}/{dbname}?sslmode=verify-ca&sslkey=./certificate.pem&sslcert=./certificate.pem&sslrootcert=./certificate.pem
```

### Separate Data Examples

If your connection uses separate data files, just follow the examples below:

* Python Example: 

```py
import psycopg2

conn = psycopg2.connect(
    host="square-cloud-db-{id}.squareweb.app",
    port=7072,
    user="squarecloud",
    password="password",
    database="squarecloud",
    sslmode="verify-ca",
    sslrootcert="ca-certificate.crt",
    sslkey="private-key.key",
    sslcert="certificate.pem"
)
```

* JavaScript Example:

```js
import { Client } from "pg";
import fs from "fs";

const client = new Client({
  host: "square-cloud-db-{id}.squareweb.app",
  port=7072,
  user="squarecloud",
  password="password",
  database="squarecloud",
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync("ca-certificate.crt").toString(),
    key: fs.readFileSync("private-key.key").toString(),
    cert: fs.readFileSync("certificate.pem").toString(),
  },
});

async connect(){
  try {
    await client.connect();
    console.log("Connected to the database!");
  } catch (err) {
    console.error("Error connecting:", err);
  }
};
connect();
```
---

## 5. Best Practices and Security

* **.gitignore File:** Never upload your `.pem`, `.key`, or `.crt` files to public repositories (GitHub/GitLab). Add them to your `.gitignore` immediately.

---

## Extra

For a better visual guide, watch our video tutorial: https://www.youtube.com/watch?v=YipIaaqYtBI