Connect to Plexivo Postgres

Connect to Plexivo Postgres from any PostgreSQL driver, ORM or tool. Every connection is encrypted with TLS, and the database’s own certificate lets your client check it is talking to your database.

Connection details#

Open the database and choose the Connect tab.

• Connection string: choose Reveal to see it with the password, and Copy to copy it. Switch between Public · external backends and Private · apps in this project.
• Use it in your code: ready-to-paste setup for .env, Node.js, Drizzle, Prisma, Python, Django, Go, Rails and Laravel.
• Download CA certificate: only for the strictest check (see TLS). It saves as <name>-<id>-ca.pem, for example orders-db-3f9a1c-ca.pem.
• Database name, Connection pooling and Port.

A revealed connection string looks like this:

postgresql://app:<password>@orders-db-3f9a1c.db.plexivo.app:31542/app?sslmode=require

It works as pasted: sslmode=require keeps the connection encrypted, and there is no file to download.

• The user and the database are both app.
• The public host ends in .db.plexivo.app, and each database has its own public port. Always use the port shown on the Connect tab.
• The private address, for apps in the same project, uses port 5432.

Connect from your app#

Give the connection string to your app as an environment variable, ideally from Plexivo Secrets, and keep it out of browsers and mobile apps. Reuse a connection pool rather than opening a connection for every request.

import pg from 'pg';

// A connected Plexivo app gets DATABASE_URL and DATABASE_CA_CERT.
// node-postgres lets sslmode in the URL override ssl.ca, so drop it and pass the CA.
const url = new URL(process.env.DATABASE_URL);
url.searchParams.delete('sslmode');

const pool = new pg.Pool({
  connectionString: url.toString(),
  ssl: { ca: process.env.DATABASE_CA_CERT },
  max: 10
});

Using the string you copied from Connect instead (sslmode=require)? With node-postgres add &uselibpqcompat=true to it; Drizzle (postgres.js), Prisma, Python, Go, Django, Rails and Laravel take it as it is.

When you connect a Plexivo App Runner app or a Plexivo Containers for Kubernetes workload to the database in Plexivo, it receives these environment variables and is updated when they change:

Connect from your laptop#

The Plexivo CLI opens an encrypted tunnel to the database, whether it is public, limited to trusted networks or private:

plexivo auth login
plexivo db connect orders-db

It listens on 127.0.0.1:5432, or a free port if that one is taken, and prints the connection string to use after Connect with:. Add --show-password to include the password. Keep it running and connect psql, TablePlus or DBeaver to it in a second terminal. See Install the Plexivo CLI.

psql "postgresql://app@127.0.0.1:5432/app?sslmode=require"

TLS#

Plexivo Postgres refuses any connection without TLS. The console’s connection string uses sslmode=require: always encrypted, nothing to download. For the strictest check, use sslmode=verify-full&sslrootcert=/full/path/to/orders-db-3f9a1c-ca.pem: each database has its own certificate authority and its certificate carries its public host name, so this checks both. Give the file’s full path: a bare file name is looked up in the folder your app runs from.

On PostgreSQL 18 the database accepts the X25519MLKEM768, X25519 and P-256 key exchanges, so current clients, libpq included, finish the TLS handshake without an extra round trip. PostgreSQL 17 accepts only P-256, which costs those clients one more round trip on each new connection.

Connection limits and the pooler#

Each size has its own number of connections for your app, shown on the create form and on the Settings tab. By default they are: Plexivo’s own monitoring and backups use separate, reserved connections, never yours.

On Business and Enterprise, by default, clients connect through a built-in PgBouncer pooler in transaction mode, at the same host and port. Many client connections then share a smaller number of database connections, and connections are held through a failover instead of dropped. The Connection pooling line on the Connect tab shows whether it is on and its limits.

Through the connection pooler#

Server-side ORMs such as GORM, Prisma, SQLAlchemy and Ecto work through the pooler as they are. In transaction mode, each transaction gets a database connection of its own for as long as it runs, so:

• Prepared statements work. The pooler keeps track of each client’s protocol-level prepared statements, so drivers that prepare (pgx, the default for GORM, JDBC, asyncpg) need no change.
• Session state does not carry over between transactions. Use SET LOCAL inside a transaction instead of SET, transaction-level advisory locks (pg_advisory_xact_lock) instead of session ones, and keep a temporary table within the transaction that uses it.
• No LISTEN. For live changes in an app, use Realtime.
• Only if your driver reports prepared statement … already exists, switch it to the simple protocol: in GORM, PreferSimpleProtocol: true.

GORM through the pooler, with a transaction that sets its own timeout and lock:

package main

import (
	"crypto/x509"
	"log"
	"os"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

type Author struct {
	ID    uint
	Name  string `gorm:"uniqueIndex"`
	Posts []Post
}

type Post struct {
	ID        uint
	AuthorID  uint
	Author    Author
	Title     string
	Likes     int
	CreatedAt time.Time
}

func main() {
	// A connected Plexivo app gets DATABASE_URL (verify-full) and DATABASE_CA_CERT.
	cfg, err := pgx.ParseConfig(os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	roots := x509.NewCertPool()
	roots.AppendCertsFromPEM([]byte(os.Getenv("DATABASE_CA_CERT")))
	cfg.TLSConfig.RootCAs = roots

	db, err := gorm.Open(postgres.New(postgres.Config{Conn: stdlib.OpenDB(*cfg)}), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	if err := db.AutoMigrate(&Author{}, &Post{}); err != nil {
		log.Fatal(err)
	}

	asha := Author{Name: "Asha"}
	db.Clauses(clause.OnConflict{DoNothing: true}).Create(&asha)
	db.Where("name = ?", "Asha").First(&asha)
	posts := []Post{
		{AuthorID: asha.ID, Title: "Hello", Likes: 5},
		{AuthorID: asha.ID, Title: "World", Likes: 10},
	}
	db.Create(&posts)

	var withAuthors []Post
	if err := db.Joins("Author").Order(`"Author".name, posts.id`).Find(&withAuthors).Error; err != nil {
		log.Fatal(err)
	}

	err = db.Transaction(func(tx *gorm.DB) error {
		if err := tx.Exec("SET LOCAL statement_timeout = '5s'").Error; err != nil {
			return err
		}
		if err := tx.Exec("SELECT pg_advisory_xact_lock(?)", 42).Error; err != nil {
			return err
		}
		var p Post
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).First(&p, "title = ?", "Hello").Error; err != nil {
			return err
		}
		return tx.Model(&p).Update("likes", gorm.Expr("likes + ?", 1)).Error
	})
	if err != nil {
		log.Fatal(err)
	}

	var total int64
	db.Raw("SELECT sum(likes) FROM posts WHERE author_id = ?", asha.ID).Scan(&total)
	log.Printf("%d posts, %d likes", len(withAuthors), total)
}

Without the pooler, every client connection is a database connection, so keep your app’s pool below the limit. With several app instances, the limit is shared between them.

Rotate the password#

If the connection string was shared or leaked, choose Rotate password on the Connect tab. The old password stops working, open connections keep running, and apps you connected in Plexivo receive the new one. See Rotate the database password.

Next steps#