PostgreSQL

PostgreSQL

This guide covers how to connect Tabula Lens to PostgreSQL databases, including self-hosted PostgreSQL, Supabase, and Neon.

Installation

Install the Tabula Lens Node package and the PostgreSQL driver:

npm

npm i @tabula-lens/node pg

pnpm

pnpm add @tabula-lens/node pg

yarn

yarn add @tabula-lens/node pg

Self-Hosted PostgreSQL

Connection String Format

Set your database connection string as an environment variable:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

You can also use the shorter postgres:// scheme:

DATABASE_URL="postgres://user:password@localhost:5432/mydb"

Connection String Components

A PostgreSQL connection string has the following format:

postgresql://[user[:password]@][host][:port][/database][?parameters]

Components:

• user - Database username
• password - Database password (optional)
• host - Database host (default: localhost)
• port - Database port (default: 5432)
• database - Database name
• parameters - Additional connection parameters

Connection Parameters

Common connection parameters:

# SSL mode
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?sslmode=require"

# Connection pool settings
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?connectionLimit=10"

# Connection timeout
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?connectionTimeoutMillis=10000"

# Statement timeout
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?statement_timeout=30000"

# Application name (useful for monitoring)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?application_name=tabula-lens"

SSL Configuration

PostgreSQL supports multiple SSL modes:

# Disable SSL (not recommended for production)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?sslmode=disable"

# Allow SSL (non-encrypted connection allowed)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?sslmode=allow"

# Prefer SSL (try SSL, fall back to non-SSL)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?sslmode=prefer"

# Require SSL (SSL required, but certificate not verified)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?sslmode=require"

# Verify CA (SSL required with CA verification)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?sslmode=verify-ca"

# Verify Full (SSL required with CA and hostname verification)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?sslmode=verify-full"

For production deployments, use sslmode=verify-full to ensure secure connections.

Connection Patterns

import { TabulaLens } from '@tabula-lens/node';

// Local development
const tabulaLens = new TabulaLens({
  url: process.env.DATABASE_URL,
  type: 'pg',
});

// Production
const tabulaLens = new TabulaLens({
  url: process.env.DATABASE_URL,
  type: 'pg',
  logLevel: 'error',
  logFormat: 'json',
  sensitiveDataMasking: true,
  enableRequestLogging: true,
});

// With SSL
const tabulaLens = new TabulaLens({
  url: 'postgresql://user:password@localhost:5432/mydb?sslmode=verify-full',
  type: 'pg',
  logLevel: 'error',
});

Security Best Practices

Database User Permissions

Create a dedicated user with minimal required permissions:

-- Create a dedicated user for Tabula Lens
CREATE USER tabula_lens WITH PASSWORD 'secure_password';

-- Grant connect permission
GRANT CONNECT ON DATABASE mydb TO tabula_lens;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO tabula_lens;

-- Grant select on specific tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tabula_lens;

-- Grant select on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tabula_lens;

Connection Security

• Always use SSL in production: Set sslmode=require or sslmode=verify-full
• Use strong passwords: Use environment variables or secret management
• Limit network access: Use firewall rules, VPCs, or private networks
• Rotate credentials regularly: Change database passwords periodically
• Use connection pooling: Configure appropriate pool sizes
• Monitor connections: Track connection usage and patterns

Supabase (Hosted PostgreSQL)

Supabase is a hosted PostgreSQL platform. Because Tabula Lens uses a standard PostgreSQL connection, it integrates directly with Supabase’s underlying database.

Scope

This guide covers connecting Tabula Lens to the Supabase PostgreSQL database. To protect your Tabula Lens endpoint using Supabase user sessions and JWTs, see the Supabase Auth Integration guide.

Connection Strings

Retrieve your connection strings from the Supabase Dashboard under Project Settings → Database → Connection string.

# Direct connection (IPv6 only, or IPv4 with the add-on)
DATABASE_URL="postgresql://postgres.project-ref:[email protected]:5432/postgres"

# Session pooler — recommended for most Node.js servers (IPv4)
DATABASE_URL="postgresql://postgres.project-ref:[email protected]:5432/postgres"

# Transaction pooler — for serverless functions (IPv4)
DATABASE_URL="postgresql://postgres.project-ref:[email protected]:6543/postgres"

Choosing a Connection Mode

	Direct	Session Pooler	Transaction Pooler
Best for	Single long-lived server	Multiple servers, IPv4	Serverless/edge
Port	5432	5432	6543
IPv4 support	❌ (add-on required)	✅	✅
Suitable for migrations	✅	✅	❌

Use the session pooler for most server deployments. It supports IPv4 and handles multiple concurrent connections efficiently.

Use the transaction pooler (port 6543) for serverless platforms like Vercel Functions or AWS Lambda.

Use the direct connection only when you have a single long-lived server and either have native IPv6 connectivity or the Supabase IPv4 add-on enabled.

Row Level Security

Row Level Security Bypass

Tabula Lens connects using your database credentials (typically the postgres user or service role). This connection bypasses all Row Level Security (RLS) policies — Tabula Lens can read all tables and rows regardless of any RLS configuration.

This is intentional: Tabula Lens is an admin database viewer designed for backend services with elevated privileges. To limit exposure, create a dedicated read-only role for the Tabula Lens connection:

-- Create a read-only role for Tabula Lens
CREATE ROLE tabula_lens_viewer WITH LOGIN PASSWORD 'your-password';
GRANT CONNECT ON DATABASE postgres TO tabula_lens_viewer;
GRANT USAGE ON SCHEMA public TO tabula_lens_viewer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tabula_lens_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tabula_lens_viewer;

Then update your DATABASE_URL to authenticate as tabula_lens_viewer instead of postgres.

Important Notes

• Protect the endpoint — Restrict access to the Tabula Lens HTTP endpoint with authentication middleware. See the authentication guides for patterns.
• Never expose the connection string to the client — DATABASE_URL should only be used in server-side code.
• IPv4 by default for pooler — If your server does not have native IPv6, use the session or transaction pooler. Both support IPv4 without the add-on.
• SSL is required — Supabase requires SSL for all database connections. The connection strings from the Supabase Dashboard already include the appropriate SSL configuration.
• Migrations — Run migrations against the direct or session pooler connection. The transaction pooler (port 6543) does not support prepared statements required by most migration tools.

Neon (Serverless PostgreSQL)

Neon is a serverless PostgreSQL database with branching, autoscaling, and scale-to-zero. Tabula Lens connects to Neon using a standard PostgreSQL connection string — no additional drivers or configuration are required.

Prerequisites

A Neon account with a project created. Your connection strings are available in the Neon Console under your project’s Connection Details.

Connection Strings

Neon provides two connection types. Both are standard PostgreSQL URLs that work directly with Tabula Lens:

# Direct connection — long-running servers, migrations
DATABASE_URL="postgresql://user:[email protected]/neondb?sslmode=require"

# Pooled connection — serverless functions, high-traffic
DATABASE_URL="postgresql://user:[email protected]/neondb?sslmode=require"

The pooled hostname appends -pooler before the region segment; everything else is identical.

Choosing a Connection Type

	Direct	Pooled
Best for	Long-running servers	Serverless, high-traffic
Concurrent connections	Limited by compute	Up to 10,000
Hostname pattern	ep-xxx.region.aws.neon.tech	ep-xxx-pooler.region.aws.neon.tech
Suitable for migrations	✅ Yes	❌ No

Use the direct connection when running on Express, Railway, Render, Fly.io, or any long-lived server process.

Use the pooled connection when running on Vercel Functions, AWS Lambda, or any serverless platform.

Driver Compatibility

The @neondatabase/serverless driver uses HTTP/WebSocket transport and is not compatible with Tabula Lens. Tabula Lens uses Knex.js with the standard pg TCP driver and only accepts a connection string. Use the standard connection string formats shown above.

Important Notes

• SSL is required — All Neon connections must include ?sslmode=require. Connections without it will be rejected by Neon’s servers.
• Cold-start delay — Neon scales to zero when idle. The first connection after an inactivity period may have a brief delay while the compute instance resumes. Subsequent connections are immediate.
• Vercel deployments — Use the pooled connection string. Vercel Functions are short-lived and benefit from Neon’s built-in connection pooler.
• Migrations — Always run migrations against the direct connection string. The Neon pooler does not support the prepared statements used by most migration tools.

Troubleshooting

Self-Hosted PostgreSQL

Connection refused

Ensure PostgreSQL is running and accessible. Check your firewall settings and verify the host and port in your connection string.

SSL errors

Verify your SSL configuration matches your PostgreSQL server’s SSL settings. For development, you can use sslmode=disable (not recommended for production).

Supabase

ECONNREFUSED or timeout when connecting to the direct host

Your network likely does not support IPv6. The direct connection host (db.project-id.supabase.co) resolves to an IPv6 address by default. Switch to the session pooler connection string, which uses IPv4.

prepared statement "..." already exists error

This occurs when using the transaction pooler (port 6543) with clients that use prepared statements, such as Prisma or migration tools. Switch to the direct or session pooler connection for these clients.

Neon

SSL connection is required error

Add ?sslmode=require to your connection string. Neon requires SSL for all connections and will reject plain TCP connections.

Timeout on the first request after a period of inactivity

Neon scales to zero when idle. The first request triggers a cold start, which takes a few seconds to resume the compute instance. This is expected behavior — subsequent requests connect immediately once the instance is warm.

Additional Resources

• PostgreSQL Documentation
• pg driver documentation
• Knex.js PostgreSQL dialect
• PostgreSQL Performance Tips