Connect to PostgreSQL

Connect to your Elestio PostgreSQL database from Node.js, Python, PHP, Go, Java, psql, and pgAdmin.

Connecting with Node.js

This guide explains how to establish a connection between a Node.js application and a PostgreSQL database using the pg package. It walks through the necessary setup, configuration, and execution of a simple SQL query.

Variables

Certain parameters must be provided to establish a successful connection to a PostgreSQL database. Below is a breakdown of each required variable, its purpose, and where to find it. Here’s what each variable represents:

Variable	Description	Purpose
`USER`	PostgreSQL username, from the Elestio service overview page	Identifies the database user who has permission to access the PostgreSQL database.
`PASSWORD`	PostgreSQL password, from the Elestio service overview page	The authentication key required for the specified `USER` to access the database
`HOST`	Hostname for PostgreSQL connection, from the Elestio service overview page	The address of the server hosting the PostgreSQL database.
`PORT`	Port for PostgreSQL connection, from the Elestio service overview page	The network port is used to connect to PostgreSQL. The default port is `5432`.
`DATABASE`	Database Name for PostgreSQL connection, from the Elestio service overview page	The name of the database being accessed. A PostgreSQL instance can contain multiple databases.

These values can usually be found in the Elestio service overview details as shown in the image below, make sure to take a copy of these details and add it to the code moving ahead.

Prerequisites

Install Node.js and NPM
- Check if Node.js is installed by running: node -v
- If not installed, download it from nodejs.org and install.
- Verify npm installation: npm -v
Install the pg Package
The pg package enables Node.js applications to interact with PostgreSQL. Install it using:

npm install pg --save

Code

Once all prerequisites are set up, create a new file named pg.js and add the following code:

const pg = require("pg"); 

// Database connection configuration
const config = {
  user: "USER", 
  password: "PASSWORD",
  host: "HOST",
  port: "PORT",
  database: "DATABASE",
};

// Create a new PostgreSQL client
const client = new pg.Client(config);

// Connect to the database
client.connect((err) => {
  if (err) {
    console.error("Connection failed:", err);
    return;
  }
  console.log("Connected to PostgreSQL");

  // Run a test query to check the PostgreSQL version
  client.query("SELECT VERSION()", [], (err, result) => {
    if (err) {
      console.error("Query execution failed:", err);
      client.end();
      return;
    }

    console.log("PostgreSQL Version:", result.rows[0]);

    // Close the database connection
    client.end((err) => {
      if (err) console.error("Error closing connection:", err);
    });
  });
});

To execute the script, open the terminal or command prompt and navigate to the directory where pg.js. Once in the correct directory, run the script with the command

node pg.js

If the connection is successful, the terminal will display output similar to:

Connected to PostgreSQL
PostgreSQL Version: {
  version: 'PostgreSQL 16.8 (Debian 16.8-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit'
}

Connecting with Python

This guide explains how to establish a connection between a Python application and a PostgreSQL database using the psycopg2-binary package. It walks through the necessary setup, configuration, and execution of a simple SQL query.

Variables

To connect to a PostgreSQL database, you only need one environment variable — the connection URI. This URI contains all the necessary information like username, password, host, port, and database name.

Variable	Description	Purpose
POSTGRESQL_URI	Full PostgreSQL connection string (from the Elestio service overview page)	Provides all necessary credentials and endpoint details in a single URI format.

The URI will look like this:

postgresql://<USER>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>

You can find the details needed in the URI from the Elestio service overview details. Copy and replace the variables carefully in the URI example provided above.

Prerequisites

Install Python

Check if Python is installed by running:

python --version

If not installed, download it from python.org and install it.

Install `psycopg2-binary` Package

The psycopg2-binary package enables Python applications to interact with PostgreSQL. Install it using:

pip install psycopg2-binary

Code

Once all prerequisites are set up, create a new file named pg.py and add the following code and replace the POSTGRESQL_URI with actual link or in environment setup as you wish:

import psycopg2

def get_db_version():
    try:
        db_connection = psycopg2.connect('POSTGRESQL_URI')
        db_cursor = db_connection.cursor()
        db_cursor.execute('SELECT VERSION()')
        db_version = db_cursor.fetchone()[0]
        return db_version
        
    except Exception as e:
        print(f"Database connection error: {e}")
        return None
        
    finally:
        if 'db_cursor' in locals():
            db_cursor.close()
        if 'db_connection' in locals():
            db_connection.close()

def display_version():
    version = get_db_version()
    if version:
        print(f"Connected to PostgreSQL: {version}")

if __name__ == "__main__":
    display_version()

To execute the script, open the terminal or command prompt and navigate to the directory where pg.py. Once in the correct directory, run the script with the command

python pg.py

If the connection is successful, the terminal will display output similar to:

Connected to PostgreSQL: PostgreSQL 16.8 (Debian 16.8-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Connecting with PHP

This guide explains how to establish a connection between a PHP application and a PostgreSQL database using the built-in PDO extension. It walks through the necessary setup, configuration, and execution of a simple SQL query.

Variables

Variable	Description	Purpose
POSTGRESQL_URI	Full PostgreSQL connection string (from the Elestio service overview page)	Provides all necessary credentials and endpoint details in a single URI format.

The URI will look like this:

postgresql://<USER>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>

You can find the details needed in the URI from the Elestio service overview details. Copy and replace the variables carefully in the URI example provided above.

Prerequisites

Install PHP

Check if PHP is installed by running:

php -v

If not installed, download and install it from https://www.php.net/downloads.php.

Code

Once all prerequisites are set up, create a new file named pg.php and add the following code and replace the POSTGRESQL_URI with actual link or in environment setup as you wish:

<?php

$db_url = "POSTGRESQL_URI";//Replace with actual URI
$db_parts = parse_url($db_url);

$dsn = "pgsql:host={$db_parts['host']};port={$db_parts['port']};dbname=Elestio";//Replace with your DB name
$pdo = new PDO($dsn, $db_parts['user'], $db_parts['pass']);

$version = $pdo->query("SELECT VERSION()")->fetchColumn();
echo $version;

To execute the script, open the terminal or command prompt and navigate to the directory where pg.php. Once in the correct directory, run the script with the command

php pg.php

If the connection is successful, the terminal will display output similar to:

PostgreSQL 16.8 (Debian 16.8-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Connecting with Go

This guide explains how to establish a connection between a Go (Golang) application and a PostgreSQL database using the github.com/lib/pq driver. It walks through the necessary setup, configuration, and execution of a simple SQL query.

Variables

Variable	Description	Purpose
POSTGRESQL_URI	Full PostgreSQL connection string (from the Elestio service overview page)	Provides all necessary credentials and endpoint details in a single URI format.

The URI will look like this:

postgresql://<USER>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>

You can find the details needed in the URI from the Elestio service overview details. Copy and replace the variables carefully in the URI example provided above.

Prerequisites

Install Go

Check if Go is installed by running:

go version

If not installed, download and install it from https://go.dev/dl/.

Install `pq` Package

Install the pq driver using:

go get github.com/lib/pq

Code

Once all prerequisites are set up, create a new file named main.go and add the following code, and replace the POSTGRESQL_URI with actual link or in environment setup as you wish:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"net/url"

	_ "github.com/lib/pq"
)

func getDBConnection(connectionString string) (*sql.DB, error) {
	parsedURL, err := url.Parse(connectionString)
	if err != nil {
		return nil, fmt.Errorf("Failed to parse connection string: %v", err)
	}

	db, err := sql.Open("postgres", parsedURL.String())
	if err != nil {
		return nil, fmt.Errorf("Failed to open database connection: %v", err)
	}

	return db, nil
}

func main() {
	connectionString := "POSTGRESQL_URI"

	db, err := getDBConnection(connectionString)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	query := "SELECT current_database(), current_user, version()"
	rows, err := db.Query(query)
	if err != nil {
		log.Fatal("Failed to execute query:", err)
	}
	defer rows.Close()

	for rows.Next() {
		var dbName, user, version string
		if err := rows.Scan(&dbName, &user, &version); err != nil {
			log.Fatal("Failed to scan row:", err)
		}
		fmt.Printf("Database: %s\nUser: %s\nVersion: %s\n", dbName, user, version)
	}
}

To execute the script, open the terminal or command prompt and navigate to the directory where main.go. Once in the correct directory, run the script with the command

go run main.go

If the connection is successful, the terminal will display output similar to:

Database: Elestio
User: postgres
Version: PostgreSQL 16.8 (Debian 16.8-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Connecting with Java

This guide explains how to establish a connection between a Java application and a PostgreSQL database using the JDBC driver. It walks through the necessary setup, configuration, and execution of a simple SQL query.

Variables

Variable	Description	Purpose
`USER`	PostgreSQL username, from the Elestio service overview page	Identifies the database user who has permission to access the PostgreSQL database.
`PASSWORD`	PostgreSQL password, from the Elestio service overview page	The authentication key required for the specified `USER` to access the database
`HOST`	Hostname for PostgreSQL connection, from the Elestio service overview page	The address of the server hosting the PostgreSQL database.
`PORT`	Port for PostgreSQL connection, from the Elestio service overview page	The network port is used to connect to PostgreSQL. The default port is `5432`.
`DATABASE`	Database Name for PostgreSQL connection, from the Elestio service overview page	The name of the database being accessed. A PostgreSQL instance can contain multiple databases.

These values can usually be found in the Elestio service overview details, as shown in the image below. Make sure to take a copy of these details and add them to the code moving ahead.

Screenshot 2025-03-20 at 12.34.30 PM.jpg

Prerequisites

Install Java & JDBC driver

Check if Java is installed by running:

java -version

If not installed, install it first and then download and install JDBC driver from https://jdbc.postgresql.org/download/ or if you have Maven installed, run the following command with updated version of the driver:

mvn org.apache.maven.plugins:maven-dependency-plugin:2.8:get -Dartifact=org.postgresql:postgresql:42.7.5:jar -Ddest=postgresql-42.7.5.jar

Code

Once all prerequisites are set up, create a new file named Pg.java and add the following code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

public class Pg {
    private static class ConnectionConfig {
        private final String host;
        private final String port;
        private final String database;
        private final String username;
        private final String password;

        public ConnectionConfig(String host, String port, String database, String username, String password) {
            this.host = host;
            this.port = port;
            this.database = database;
            this.username = username;
            this.password = password;
        }

        public String getConnectionUrl() {
            return String.format("jdbc:postgresql://%s:%s/%s?sslmode=require", host, port, database);
        }

        public boolean isValid() {
            return host != null && !host.isEmpty() && 
                   port != null && !port.isEmpty() && 
                   database != null && !database.isEmpty();
        }
    }

    private static Map<String, String> parseArguments(String[] args) {
        Map<String, String> config = new HashMap<>();
        for (int i = 0; i < args.length - 1; i++) {
            String key = args[i].toLowerCase();
            String value = args[++i];
            config.put(key, value);
        }
        return config;
    }

    private static ConnectionConfig createConfig(Map<String, String> args) {
        return new ConnectionConfig(
            args.get("-host"),
            args.get("-port"),
            args.get("-database"),
            args.get("-username"),
            args.get("-password")
        );
    }

    private static void validateConnection(Connection connection) throws SQLException {
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT version()")) {
            if (rs.next()) {
                System.out.println("Database Version: " + rs.getString("version"));
            }
        }
    }

    public static void main(String[] args) {
        try {
            // Load PostgreSQL driver
            Class.forName("org.postgresql.Driver");

            // Parse and validate configuration
            Map<String, String> parsedArgs = parseArguments(args);
            ConnectionConfig config = createConfig(parsedArgs);

            if (!config.isValid()) {
                System.err.println("Error: Missing required connection parameters (host, port, database)");
                return;
            }

            // Establish connection and validate
            try (Connection conn = DriverManager.getConnection(
                    config.getConnectionUrl(),
                    config.username,
                    config.password)) {
                
                System.out.println("Successfully connected to the database!");
                validateConnection(conn);
            }

        } catch (ClassNotFoundException e) {
            System.err.println("Error: PostgreSQL JDBC Driver not found");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("Database connection error:");
            e.printStackTrace();
        }
    }
}

To execute the script, open the terminal or command prompt and navigate to the directory where Pg.java. Once in the correct directory, run the script with the command (Update the variables with actual values acquired from previous steps.)

javac Pg.java && java -cp postgresql-42.7.5.jar:. Pg -host HOST -port PORT -database DATABASE -username avnadmin -password PASSWORD

If the connection is successful, the terminal will display output similar to:

Successfully connected to the database!
Database Version: PostgreSQL 16.8 (Debian 16.8-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Connecting with psql

This guide explains how to connect to a PostgreSQL database using the psql command-line tool. It walks through the necessary setup, connection process, and execution of a simple SQL query.

Variables

Variable	Description	Purpose
POSTGRESQL_URI	Full PostgreSQL connection string (from the Elestio service overview page)	Provides all necessary credentials and endpoint details in a single URI format.

The URI will look like this:

postgresql://<USER>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>

You can find the details needed in the URI from the Elestio service overview details. Copy and replace the variables carefully in the URI example provided above.

Prerequisites

While following this tutorial, you will need to have psql already installed; if not head over to https://www.postgresql.org/download/ and download it first.

Connecting to PostgreSQL

Open your terminal and run the following command to connect to your PostgreSQL database using the full connection URI:

psql POSTGRESQL_URI

If the connection is successful, you’ll see output similar to this. Here it will show you the database you tried to connect to, which in this case is Elestio:

psql (17.4, server 16.8 (Debian 16.8-1.pgdg120+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none)
Type "help" for help.

Elestio=#

To ensure you're connected correctly, run this command inside the psql prompt:

SELECT version();

You should receive output like the following:

                                                       version                                                       
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.8 (Debian 16.8-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

Connecting with pgAdmin

pgAdmin is a widely used graphical interface for PostgreSQL that allows you to manage, connect to, and run queries on your databases with ease.

Variables

To connect using pgAdmin, you'll need the following connection parameters. When you deploy a PostgreSQL service on Elestio, you also get a pgAdmin dashboard configured for you to use with these variables. These details are available in the Elestio service overview page:

Variable	Description	Purpose
USER	pgAdmin username	Identifies the pgAdmin user with access permission.
PASSWORD	pgAdmin password	Authentication key for the `USER`.

You can find these values in your Elestio project dashboard under Admin section.

Prerequisites

Make sure the PostgreSQL service is correctly deployed on Elestio and you are able to access the Admin section like the one in the image above.

Setting Up the Connection

Launch pgAdmin from the Admin UI URL and log in with the credentials acquired in the steps before.
Click on "Create" and select "Server…" from the dropdown, or find Add New Server from the quick links
In the General tab:
- Enter a name for your connection (e.g., Trial pgAdmin Connection).
Go to the Connection tab and enter the following details:
- Host name/address: HOSTNAME
- Port: PORT
- Maintenance database: DATABASE
- Username: USERNAME
- Password: PASSWORD

Connecting with Node.js​

Variables​

Prerequisites​

Code​

Connecting with Python​

Variables​

Prerequisites​

Install Python​

Install psycopg2-binary Package​

Code​

Connecting with PHP​

Variables​

Prerequisites​

Install PHP​

Code​

Connecting with Go​

Variables​

Prerequisites​

Install Go​

Install pq Package​

Code​

Connecting with Java​

Variables​

Prerequisites​

Install Java & JDBC driver​

Code​

Connecting with psql​

Variables​

Prerequisites​

Connecting to PostgreSQL​

Connecting with pgAdmin​

Variables​

Prerequisites​

Setting Up the Connection​

Connecting with Node.js

Variables

Prerequisites

Code

Connecting with Python

Variables

Prerequisites

Install Python

Install `psycopg2-binary` Package

Code

Connecting with PHP

Variables

Prerequisites

Install PHP

Code

Connecting with Go

Variables

Prerequisites

Install Go

Install `pq` Package

Code

Connecting with Java

Variables

Prerequisites

Install Java & JDBC driver

Code

Connecting with psql

Variables

Prerequisites

Connecting to PostgreSQL

Connecting with pgAdmin

Variables

Prerequisites

Setting Up the Connection