[Yandex Cloud documentation](../../../index.md) > [Yandex Managed Service for PostgreSQL](../../index.md) > [Step-by-step guides](../index.md) > [Connection](index.md) > Code examples

# Code examples for connecting to a PostgreSQL cluster

**The examples were tested in the following environment**:

* Yandex Cloud virtual machine running Ubuntu 20.04 LTS:
  * Bash: `5.0.16`.
  * Python: `3.8.2`; pip3: `20.0.2`.
  * PHP: `7.4.3`.
  * OpenJDK: `11.0.8`; Maven: `3.6.3`.
  * Node.JS: `10.19.0`, npm: `6.14.4`.
  * Go: `1.13.8`.
  * Ruby: `2.7.0p0`.
  * unixODBC: `2.3.6`.
* Yandex Cloud virtual machine running Windows Server 2019 Datacenter:
  * PostgreSQL: `13`.
  * PowerShell: ` 5.1.17763.1490 Desktop`.  
  * .NET 5
  * Microsoft.EntityFrameworkCore 5.0.9
  * Npgsql.EntityFrameworkCore.PostgreSQL 5.0.7

Connections to public PostgreSQL hosts require an SSL certificate. [Prepare a certificate](index.md#get-ssl-cert) before connecting to such hosts.

The examples below assume that the `root.crt` SSL certificate is located in the following directory:

* `/home/<home_directory>/.postgresql/` for Ubuntu.
* `$HOME\AppData\Roaming\postgresql` for Windows.

Connections without an SSL certificate are only supported for hosts that are not publicly accessible. In this case, internal cloud network traffic will not be encrypted during database connections.

You can connect to a cluster using either regular host [FQDNs](../../concepts/network.md#hostname), including a comma-separated list of several FQDNs, or [special FQDNs](fqdn.md#special-fqdns). In our examples, we use the special FQDN pointing to the current master host.

To see code examples with the host FQDN filled in, open the cluster page in the [management console](https://console.yandex.cloud) and click **Connect**.

## 1C:Enterprise {#1c}

If your cluster uses a <q>1C:Enterprise</q>-optimized PostgreSQL version, specify the following settings:

* **Secure connection**: Disabled.
* **DBMS type**: `PostgreSQL`.
* **Database server**: `<special_FQDN> port=6432`.
* **Database name**: `<DB_name>`.
* **Database user**: `<username>`.
* **User password**: `<password>`.
* **Create a database if none exists**: Disabled.

## C++ (userver framework) {#cpp-userver}

The asynchronous [userver](https://userver.tech/) framework provides a rich set of abstractions for creating utilities, services, and microservices in C++. This framework also provides capabilities for PostgreSQL integration.

Before connecting, access the framework using one of the following methods:

* [Create a Yandex Compute Cloud virtual machine](../../../compute/operations/images-with-pre-installed-software/create.md) using the [userver image](https://yandex.cloud/en/marketplace/products/yc/userver). This image contains both the framework and all required dependencies.
* [Manually install the framework and all required dependencies](https://userver.tech/docs/v2.0/d3/da9/md_en_2userver_2tutorial_2build.html).

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

    1. Create a project from the [service template](https://github.com/userver-framework/pg_service_template).

    1. Modify the `configs/config_vars.yaml` configuration file. Specify the PostgreSQL cluster connection string in the `dbconnection` variable:

        ```url
        postgres://<username>:<user_password>@<special_FQDN>:6432/<DB_name>
        ```

    1. Build the project and start the service:

        ```bash
        make build-debug && \
        ./build_debug/pg_service_template -c configs/static_config.yaml --config_vars configs/config_vars.yaml
        ```

- Connecting with SSL {#with-ssl}

    1. Create a project from the [service template](https://github.com/userver-framework/pg_service_template).

    1. Modify the `configs/config_vars.yaml` configuration file. Specify the PostgreSQL cluster connection string in the `dbconnection` variable:

        ```url
        postgres://<username>:<user_password>@<special_FQDN>:6432/<DB_name>?ssl=true&sslmode=verify-full
        ```

    1. Build the project and start the service:

        ```bash
        make build-debug && \
        ./build_debug/pg_service_template -c configs/static_config.yaml --config_vars configs/config_vars.yaml
        ```

{% endlist %}

Once started, the service will wait for a POST request from the user. While waiting, the service will periodically check the PostgreSQL cluster's availability by running the `SELECT 1 as ping` request. You can find this information in the service logs.

{% cut "Log example of successful cluster connection" %}

```text
tskv ... level=INFO      module=MakeQuerySpan ( userver/postgresql/src/storages/postgres/detail/connection_impl.cpp:647 )
...
db_statement=SELECT 1 AS ping
db_type=postgres
db_instance=********
peer_address=rc1a-goh2a9tr********.mdb.yandexcloud.net:6432
...
```

{% endcut %}

## C# EF Core {#csharpefcore}

To connect to a cluster, you need the [Npgsql](https://www.nuget.org/packages/Npgsql/) package.

{% list tabs group=connection %}

- Connecting with SSL {#with-ssl}

  ```csharp
  using Npgsql;

  namespace ConsoleApp
  {
      class Program
      {
          static async Task Main(string[] args)
          {
              var host       = "<list_of_hosts>";
              var port       = "6432";
              var db         = "<DB_name>";
              var username   = "<username>";
              var password   = "<user_password>";
              var connString = $"Host={host};Port={port};Database={db};Username={username};Password={password};Ssl Mode=VerifyFull;";

              await using var conn = new NpgsqlConnection(connString);
              await conn.OpenAsync();

              await using (var cmd = new NpgsqlCommand("SELECT VERSION();", conn))
              await using (var reader = await cmd.ExecuteReaderAsync())
              {
                  while (await reader.ReadAsync())
                  {
                      Console.WriteLine(reader.GetInt32(0));
                  }
              }
          }
      }
  }
  ```

{% endlist %}

In the `host` parameter, specify comma-separated cluster hosts, in `<availability_zone>-<host_ID>.<DNS_zone>:6432` format (e.g., `rc1a-goh2a9tr********:6432.mdb.yandexcloud.net`).

## Go {#go}

Before connecting, install the required dependencies:

```bash
sudo apt update && sudo apt install --yes golang git && \
go mod init example && go get github.com/jackc/pgx/v4
```

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  1. Code example:

      `connect.go`

      ```go
      package main

      import (
          "context"
          "fmt"
          "os"

          "github.com/jackc/pgx/v4"
      )

      const (
        host     = "<list_of_hosts>"
        port     = 6432
        user     = "<username>"
        password = "<user_password>"
        dbname   = "<DB_name>"
      )

      func main() {

          connstring := fmt.Sprintf(
              "host=%s port=%d dbname=%s user=%s password=%s target_session_attrs=read-write",
              host, port, dbname, user, password)

          connConfig, err := pgx.ParseConfig(connstring)
          if err != nil {
              fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
              os.Exit(1)
          }

          conn, err := pgx.ConnectConfig(context.Background(), connConfig)
          if err != nil {
              fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
              os.Exit(1)
          }

          defer conn.Close(context.Background())

          var version string

          err = conn.QueryRow(context.Background(), "select version()").Scan(&version)
          if err != nil {
              fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
              os.Exit(1)
          }

          fmt.Println(version)
      }
      ```
    
    In the `host` parameter, specify comma-separated cluster hosts, in `<availability_zone>-<host_ID>.<DNS_zone>:6432` format (e.g., `rc1a-goh2a9tr********:6432.mdb.yandexcloud.net`).

  1. Connecting:

      ```bash
      go run connect.go
      ```

- Connecting with SSL {#with-ssl}

  1. Code example:

      `connect.go`

      ```go
      package main

      import (
          "context"
          "crypto/tls"
          "crypto/x509"
          "fmt"
          "io/ioutil"
          "os"

          "github.com/jackc/pgx/v4"
      )

      const (
        host     = "<list_of_hosts>"
        port     = 6432
        user     = "<username>"
        password = "<user_password>"
        dbname   = "<DB_name>"
        ca       = "/home/<home_directory>/.postgresql/root.crt"
      )

      func main() {

          rootCertPool := x509.NewCertPool()
          pem, err := ioutil.ReadFile(ca)
          if err != nil {
              panic(err)
          }

          if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
              panic("Failed to append PEM.")
          }

          connstring := fmt.Sprintf(
              "host=%s port=%d dbname=%s user=%s password=%s sslmode=verify-full target_session_attrs=read-write",
              host, port, dbname, user, password)

          connConfig, err := pgx.ParseConfig(connstring)
          if err != nil {
              fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
              os.Exit(1)
          }

          connConfig.TLSConfig = &tls.Config{
              RootCAs:            rootCertPool,
              ServerName: "<list_of_hosts>",
          }

          conn, err := pgx.ConnectConfig(context.Background(), connConfig)
          if err != nil {
              fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
              os.Exit(1)
          }

          defer conn.Close(context.Background())

          var version string

          err = conn.QueryRow(context.Background(), "select version()").Scan(&version)
          if err != nil {
              fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
              os.Exit(1)
          }

          fmt.Println(version)
      }
      ```

      In the `host` parameter, specify comma-separated cluster hosts, in `<availability_zone>-<host_ID>.<DNS_zone>:6432` format (e.g., `rc1a-goh2a9tr********:6432.mdb.yandexcloud.net`).

      For this connection method, you must specify the full path to the PostgreSQL `root.crt` certificate in the `ca` variable.

  1. Connecting:

      ```bash
      go run connect.go
      ```

{% endlist %}

## Java {#java}

Before connecting:

1. Install the dependencies:

    ```bash
    sudo apt update && sudo apt install --yes default-jdk maven
    ```

1. Create a directory for the Maven project:

    ```bash
    cd ~/ && mkdir -p project/src/java/com/example && cd project/
    ```

1. Create a configuration file for Maven:

    {% cut "pom.xml" %}

    ```xml
    <?xml version="1.0" encoding="utf-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

      <modelVersion>4.0.0</modelVersion>
      <groupId>com.example</groupId>
      <artifactId>app</artifactId>
      <packaging>jar</packaging>
      <version>0.1.0</version>
      <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
      </properties>
      <dependencies>
        <dependency>
          <groupId>org.postgresql</groupId>
          <artifactId>postgresql</artifactId>
          <version>42.2.16</version>
        </dependency>
      </dependencies>
      <build>
        <finalName>${project.artifactId}-${project.version}</finalName>
        <sourceDirectory>src</sourceDirectory>
        <resources>
          <resource>
            <directory>src</directory>
          </resource>
        </resources>
        <plugins>
          <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-assembly-plugin</artifactId>
            <executions>
              <execution>
                <goals>
                  <goal>attached</goal>
                </goals>
                <phase>package</phase>
                <configuration>
                  <descriptorRefs>
                    <descriptorRef>
                    jar-with-dependencies</descriptorRef>
                  </descriptorRefs>
                  <archive>
                    <manifest>
                      <mainClass>com.example.App</mainClass>
                    </manifest>
                  </archive>
                </configuration>
              </execution>
            </executions>
          </plugin>
          <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-jar-plugin</artifactId>
            <version>3.1.0</version>
            <configuration>
              <archive>
                <manifest>
                  <mainClass>com.example.App</mainClass>
                </manifest>
              </archive>
            </configuration>
          </plugin>
        </plugins>
      </build>
    </project>
    ```

    {% endcut %}

    Current dependency version for Maven: [postgresql](https://mvnrepository.com/artifact/org.postgresql/postgresql).

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  1. Code example:

      `src/java/com/example/App.java`

      ```java
      package com.example;

      import java.sql.*;

      public class App {
        public static void main(String[] args) {
          String DB_URL     = "jdbc:postgresql://<list_of_hosts>/<DB_name>?targetServerType=master&ssl=false&sslmode=disable";
          String DB_USER    = "<username>";
          String DB_PASS    = "<user_password>";

          try {
            Class.forName("org.postgresql.Driver");

            Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            ResultSet q = conn.createStatement().executeQuery("SELECT version()");
            if(q.next()) {System.out.println(q.getString(1));}

            conn.close();
          }
          catch(Exception ex) {ex.printStackTrace();}
        }
      }
      ```

     In the `host` parameter, specify comma-separated cluster hosts, in `<availability_zone>-<host_ID>.<DNS_zone>:6432` format (e.g., `rc1a-goh2a9tr********:6432.mdb.yandexcloud.net`).

  1. Building and connecting:

      ```bash
      mvn clean package && \
      java -jar target/app-0.1.0-jar-with-dependencies.jar
      ```

- Connecting with SSL {#with-ssl}

  1. Code example:

      `src/java/com/example/App.java`

      ```java
      package com.example;

      import java.sql.*;

      public class App {
        public static void main(String[] args) {
          String DB_URL     = "jdbc:postgresql://<list_of_hosts>:6432/<DB_name>?targetServerType=master&ssl=true&sslmode=verify-full";
          String DB_USER    = "<username>";
          String DB_PASS    = "<user_password>";

          try {
            Class.forName("org.postgresql.Driver");

            Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            ResultSet q = conn.createStatement().executeQuery("SELECT version()");
            if(q.next()) {System.out.println(q.getString(1));}

            conn.close();
          }
          catch(Exception ex) {ex.printStackTrace();}
        }
      }
      ```

     In the `host` parameter, specify comma-separated cluster hosts, in `<availability_zone>-<host_ID>.<DNS_zone>:6432` format (e.g., `rc1a-goh2a9tr********:6432.mdb.yandexcloud.net`).

  1. Building and connecting:

      ```bash
      mvn clean package && \
      java -jar target/app-0.1.0-jar-with-dependencies.jar
      ```

{% endlist %}

## Node.js {#nodejs}

Before connecting, install the required dependencies:

```bash
sudo apt update && sudo apt install --yes nodejs npm && \
npm install pg
```

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

    `app.js`

    ```javascript
    "use strict";
    const pg = require("pg");

    const config = {
        connectionString:
            "postgres://<username>:<user_password>@<special_FQDN>:6432/<DB_name>"
    };

    const conn = new pg.Client(config);

    conn.connect((err) => {
        if (err) throw err;
    });
    conn.query("SELECT version()", (err, q) => {
        if (err) throw err;
        console.log(q.rows[0]);
        conn.end();
    });
    ```

- Connecting with SSL {#with-ssl}

    `app.js`

    ```javascript
    "use strict";
    const fs = require("fs");
    const pg = require("pg");

    const config = {
        connectionString:
            "postgres://<username>:<user_password>@<special_FQDN>:6432/<DB_name>",
        ssl: {
            rejectUnauthorized: true,
            ca: fs
                .readFileSync("/home/<home_directory>/.postgresql/root.crt")
                .toString(),
        },
    };

    const conn = new pg.Client(config);

    conn.connect((err) => {
        if (err) throw err;
    });
    conn.query("SELECT version()", (err, q) => {
        if (err) throw err;
        console.log(q.rows[0]);
        conn.end();
    });
    ```

    For this connection method, you must specify the full path to the PostgreSQL `root.crt` certificate in the `ca` variable.

{% endlist %}

You can get the cluster ID from the [cluster list](../cluster-list.md#list-clusters).

Connecting:

```bash
node app.js
```

## ODBC {#odbc}

Before connecting, install the required dependencies:

```bash
sudo apt update && sudo apt install --yes unixodbc odbc-postgresql
```

The system will automatically register the PostgreSQL ODBC driver in `/etc/odbcinst.ini`.

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  1. Code example:

      `/etc/odbc.ini`

      ```ini
      [postgresql]
      Driver=PostgreSQL Unicode
      Servername=<special_FQDN>
      Username=<username>
      Password=<user_password>
      Database=<DB_name>
      Port=6432
      Pqopt=target_session_attrs=read-write
      ```

  1. Connecting:

      ```bash
      isql -v postgresql
      ```

      Once connected to the DBMS, run the `SELECT version();` command.

- Connecting with SSL {#with-ssl}

  1. Code example:

      `/etc/odbc.ini`

      ```ini
      [postgresql]
      Driver=PostgreSQL Unicode
      Servername=<special_FQDN>
      Username=<username>
      Password=<user_password>
      Database=<DB_name>
      Port=6432
      Pqopt=target_session_attrs=read-write
      Sslmode=verify-full
      ```

  1. Connecting:

      ```bash
      isql -v postgresql
      ```

      Once connected to the DBMS, run the `SELECT version();` command.

{% endlist %}

## PHP {#php}

Before connecting, install the required dependencies:

```bash
sudo apt update && sudo apt install --yes php php-pgsql
```

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  1. Code example:

      `connect.php`

      ```php
      <?php
        $conn = pg_connect("
            host=<special_FQDN>
            port=6432
            sslmode=disable
            dbname=<DB_name>
            user=<username>
            password=<user_password>
            target_session_attrs=read-write
        ");

      $q = pg_query($conn, "SELECT version()");
      $result = pg_fetch_row($q);
      echo $result[0];

      pg_close($conn);
      ?>
      ```

  1. Connecting:

      ```bash
      php connect.php
      ```

- Connecting with SSL {#with-ssl}

  1. Code example:

      `connect.php`

      ```php
      <?php
        $conn = pg_connect("
            host=<special_FQDN>
            port=6432
            sslmode=verify-full
            dbname=<DB_name>
            user=<username>
            password=<user_password>
            target_session_attrs=read-write
        ");

      $q = pg_query($conn, "SELECT version()");
      $result = pg_fetch_row($q);
      echo $result[0];

      pg_close($conn);
      ?>
      ```

  1. Connecting:

      ```bash
      php connect.php
      ```

{% endlist %}

## Python {#python}

Before connecting, install the required dependencies:

```bash
sudo apt update && sudo apt install -y python3 python3-pip && \
pip3 install psycopg2-binary
```

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  1. Code example:

      `connect.py`

      ```python
      import psycopg2

      conn = psycopg2.connect("""
          host=<list_of_hosts>
          port=6432
          sslmode=disable
          dbname=<DB_name>
          user=<username>
          password=<user_password>
          target_session_attrs=read-write
      """)

      q = conn.cursor()
      q.execute('SELECT version()')

      print(q.fetchone())

      conn.close()
      ```

     In the `host` parameter, specify the cluster hosts, comma-separated, in `<availability_zone>-<host_ID>.<DNS_zone>` format (e.g., `rc1a-goh2a9tr********.mdb.yandexcloud.net`).

  1. Connecting:

      ```bash
      python3 connect.py
      ```

- Connecting with SSL {#with-ssl}

  1. Code example:

      `connect.py`

      ```python
      import psycopg2

      conn = psycopg2.connect("""
          host=<list_of_hosts>
          port=6432
          sslmode=verify-full
          dbname=<DB_name>
          user=<username>
          password=<user_password>
          target_session_attrs=read-write
      """)

      q = conn.cursor()
      q.execute('SELECT version()')

      print(q.fetchone())

      conn.close()
      ```

     In the `host` parameter, specify the cluster hosts, comma-separated, in `<availability_zone>-<host_ID>.<DNS_zone>` format (e.g., `rc1a-goh2a9tr********.mdb.yandexcloud.net`).

  1. Connecting:

      ```bash
      python3 connect.py
      ```

{% endlist %}

## R {#r}

Before connecting:

1. Install the dependencies:

    ```bash
    sudo apt update && sudo apt install libpq-dev r-base --yes
    ```

1. Install the [RPostgres](https://rpostgres.r-dbi.org/) library:

    ```bash
    sudo R --interactive
    install.packages("RPostgres")
    quit()
    ```

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

    1. Code example:

        `connect.r`

        ```R
        library(DBI)

        conn <- dbConnect(RPostgres::Postgres(),
            dbname="<DB_name>",
            host="<special_FQDN>",
            port=6432,
            user="<username>",
            password="<user_password>"
        )

        res <- dbSendQuery(conn, "SELECT VERSION();")
        dbFetch(res)
        dbClearResult(res)

        dbDisconnect(conn)
        ```

    1. Connecting:

        ```bash
        R connect.r
        ```

- Connecting with SSL {#with-ssl}

    1. Code example:

        `connect.r`

        ```R
        library(DBI)

        conn <- dbConnect(RPostgres::Postgres(),
            dbname="<DB_name>",
            host="<special_FQDN>",
            port=6432,
            sslmode="verify-full",
            user="<username>",
            password="<user_password>"
        )

        res <- dbSendQuery(conn, "SELECT VERSION();")
        dbFetch(res)
        dbClearResult(res)

        dbDisconnect(conn)
        ```

    1. Connecting:

        ```bash
        R connect.r
        ```

{% endlist %}

## Ruby {#ruby}

Before connecting, install the required dependencies:

```bash
sudo apt update && sudo apt install --yes ruby ruby-pg
```

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  1. Code example:

      `connect.rb`

      ```ruby
      require "pg"

      conn = PG.connect("
              host=<special_FQDN>
              port=6432
              dbname=<DB_name>
              user=<username>
              password=<user_password>
              target_session_attrs=read-write
              sslmode=disable
      ")

      q = conn.exec("SELECT version()")
      puts q.getvalue 0, 0

      conn.close()
      ```

  1. Connecting:

      ```bash
      ruby connect.rb
      ```

- Connecting with SSL {#with-ssl}

  1. Code example:

      `connect.rb`

      ```ruby
      require "pg"

      conn = PG.connect("
              host=<special_FQDN>
              port=6432
              dbname=<DB_name>
              user=<username>
              password=<user_password>
              target_session_attrs=read-write
              sslmode=verify-full
      ")

      q = conn.exec("SELECT version()")
      puts q.getvalue 0, 0

      conn.close()
      ```

  1. Connecting:

      ```bash
      ruby connect.rb
      ```

{% endlist %}

If your cluster connection and test query are successful, you will see the PostgreSQL version. One exception is the [userver framework example](#cpp-userver), where the `SELECT 1 as ping` test query will be executed for periodic PostgreSQL cluster availability checks.