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.
• Download CA certificate: save it as plexivo-postgres-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=verify-full&sslrootcert=plexivo-postgres-ca.pem

• 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';

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

When you connect a Plexivo App Runner app or a Plexivo 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. Each database has its own certificate authority, and its certificate carries its public host name, so sslmode=verify-full with the downloaded CA checks both the certificate and the host. The console’s connection string uses it once you choose Reveal.

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 (
	"log"
	"os"
	"time"

	"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() {
	db, err := gorm.Open(postgres.Open(os.Getenv("DATABASE_URL")), &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#