tenant-postgres Documentation

1. Introduction

What is `tenant-postgres`?
It is a powerful database adapter for the @node-tenant ecosystem designed exclusively for PostgreSQL. It uses the official pg package to manage the creation, deletion, migration, and connection pooling for tenant databases/schemas.

What problem it solves:
Managing physical tenant boundaries in PostgreSQL is complex. You have to create users, schemas or databases, run migrations per tenant, and safely route queries during HTTP requests without leaking data. tenant-postgres standardizes this entire lifecycle using robust SQL implementations.

• Schema Strategy ('schema'): Creates a PostgreSQL schema named after the tenant slug (CREATE SCHEMA "<slug>") in a shared database. It switches contexts by executing SET search_path TO "<slug>".
• Database Strategy ('database'): Creates an entirely separate physical database (CREATE DATABASE "tenant_<slug>" TEMPLATE template1). It switches contexts by pulling a dedicated connection from a custom pool factory.

2. Prerequisites

Before using tenant-postgres, your environment must meet the following requirements:

• Node.js: Version 16.x or higher.
• Package Manager: npm, pnpm, or yarn.
• PostgreSQL: A running PostgreSQL server. No special extensions are strictly required by the adapter itself.
• pg (node-postgres): Version >= 8.0.0 is required as a peer dependency.
• Database Permissions: If using the 'database' strategy, the database user connecting to the admin pool MUST have CREATEDB privileges. If using 'schema', the user must have CREATE privileges on the shared database.
• @node-tenant/tenant-core: Required for the Tenant and DatabaseAdapter interface definitions.

3. Project Structure

The internal structure of the tenant-postgres package is highly focused:

• src/index.ts: The single source of truth. It exports the factory createPostgresDatabaseAdapter which returns an object satisfying the DatabaseAdapter interface. It implements connection pool handling and dynamic raw SQL generation for both strategies. Developers should not modify this package file directly.

4. Creating a New Tenant

When your application (or control plane) registers a new tenant, you pass the `Tenant` object to the adapter. The adapter then provisions the database structure based on the tenant's configured strategy.

Step-by-step Provisioning:

1. Tenant Creation: You create a Tenant record in your primary control-plane database (e.g. using tenant-core). The record includes slug and strategy (either 'schema' or 'database').
2. Provisioning: You call await dbAdapter.createTenantDatabase(tenant).
   - If schema: executes CREATE SCHEMA IF NOT EXISTS "slug".
   - If database: connects outside a transaction and executes CREATE DATABASE "tenant_slug" TEMPLATE template1.
3. Migrations: You call await dbAdapter.migrateTenant(tenant, './migrations'). The adapter reads .sql files, switches to the correct tenant context, and executes the SQL to build tables.
4. Seeding: Optionally, you call await dbAdapter.seedTenant(tenant, async (client) => { ... }).

import { Pool } from 'pg';
import { createPostgresDatabaseAdapter } from '@node-tenant/tenant-postgres';

const adminPool = new Pool({ connectionString: process.env.PG_ADMIN_URL });

const dbAdapter = createPostgresDatabaseAdapter({
  adminPool,
  // Required if using 'database' strategy to connect to the new physical DB
  getTenantPool: (slug) => new Pool({ connectionString: `postgres://user:pass@host/tenant_${slug}` }),
});

// Example provisioning function in your app
async function onTenantSignup(tenantData) {
  const tenant = { id: 1, slug: 'acme', strategy: 'schema' }; // Mock tenant
  
  // 1. Creates the Schema
  await dbAdapter.createTenantDatabase(tenant);
  
  // 2. Runs table creation scripts from folder
  await dbAdapter.migrateTenant(tenant, './src/migrations');
  
  console.log("Tenant created!");
}

5. Database Setup

To utilize tenant-postgres, you must configure standard pg connection pools and provide them to the adapter.

• Admin Pool: Passed via options.adminPool. This connects to your primary database (often named postgres or your app's main DB). It is used to execute CREATE DATABASE and to serve all queries for schema-strategy tenants.
• Tenant Pool Factory: Passed via options.getTenantPool. If you use the database strategy, the adapter calls this function to get a dedicated Pool for tenant_<slug>.
• Template Database: When using the database strategy, Postgres creates the new database by cloning a template. By default, this is template1, but you can override it via options.template.

Native Migrations & Seeding

How migrations work: When you call migrateTenant(tenant, migrationPath), the adapter reads all .sql files in the provided directory, sorts them alphabetically, switches the PostgreSQL connection to the correct tenant (setting search_path if needed), and executes the raw SQL.

Note: The adapter executes the SQL blindly; it does not currently maintain a migrations_history table natively to track state. The consuming application must ensure migrations are idempotent (e.g., CREATE TABLE IF NOT EXISTS) or manage state tracking manually in the seedFn.

6. Environment Variables

tenant-postgres itself reads no environment variables directly. Your application uses them to initialize the pg Pools that you pass to the adapter.

7. Running Locally

Setting up local development:

1. Start PostgreSQL & Create Admin Database

Ensure Postgres is running and create an initial database.

createdb my_admin_db

2. Install Dependencies

Install the tenant adapter and the postgres driver.

npm install @node-tenant/tenant-postgres pg

3. Create Migration File

Create a test SQL migration file at ./migrations/001_init.sql.

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

4. Provision and Query a Tenant

Write a quick Node script to test the adapter.

import { Pool } from 'pg';
import { createPostgresDatabaseAdapter } from '@node-tenant/tenant-postgres';

const adminPool = new Pool({ connectionString: 'postgres://localhost:5432/my_admin_db' });
const adapter = createPostgresDatabaseAdapter({ adminPool });
const tenant = { id: 1, slug: 'test_tenant', strategy: 'schema' };

async function run() {
  await adapter.createTenantDatabase(tenant);
  await adapter.migrateTenant(tenant, './migrations');
  
  const client = await adapter.switchTenant(tenant);
  await client.query("INSERT INTO users (name) VALUES ('Alice')");
  
  const res = await client.query("SELECT * FROM users");
  console.log("Tenant Users:", res.rows);
  
  client.release();
}
run();

8. Deployment Guide

PostgreSQL multi-tenancy heavily relies on connection limits. Keep these in mind for production:

1. Connection Limits: A default Postgres server usually limits connections to 100. If you use the database strategy for 50 tenants, and each creates a pool of 10 connections, your app will crash. You must use an external connection pooler like PgBouncer if adopting the database strategy at scale.
2. Schema Strategy Performance: The schema strategy is highly efficient for Postgres connections because all tenants share the single adminPool. SET search_path happens on the checked-out connection and costs virtually zero overhead.
3. Releasing Clients: You must always call client.release() after using the client returned by switchTenant(tenant), or your application will leak connections.

9. DNS Configuration

For wildcard subdomains (*.example.com), configure a CNAME or A Record. When the request reaches your Node.js app, parse the subdomain (e.g. acme) and use it to look up the Tenant object. Finally, pass that object to adapter.switchTenant(tenant) to retrieve the correctly scoped PostgreSQL client.

10. Verifying the Deployment

Verify your production databases using `psql`.

1. Verify Database Strategy Creation

Check if databases were physically created.

SELECT datname FROM pg_database WHERE datname LIKE 'tenant_%';

2. Verify Schema Strategy Creation

Connect to the admin database and list schemas.

\dn

3. Verify search_path

When running your Node.js app, you can log the active path.

const client = await adapter.switchTenant(tenant);
const res = await client.query("SHOW search_path");
console.log(res.rows[0]); // Should print the tenant slug

11. Common Problems

12. Best Practices

13. Internal Workflow

The following diagram illustrates the internal PostgreSQL request lifecycle implemented by this adapter:

flowchart TD
    A[Client Request] --> B[App resolves Tenant Object]
    B --> C["dbAdapter.switchTenant(tenant)"]
    C --> D{tenant.strategy}
    D -- "database" --> E["getTenantPool(slug)"]
    D -- "schema" --> F["adminPool"]
    E --> G[pool.connect()]
    F --> G
    G --> H{tenant.strategy}
    H -- "schema" --> I["client.query('SET search_path TO slug')"]
    H -- "database" --> J[No operation required]
    I --> K[Return PoolClient]
    J --> K
    K --> L[App executes SQL Query]
    L --> M["client.release()"]

Explanation: The adapter handles the complexity of obtaining the correct connection. If the tenant uses the schema strategy, it pulls a connection from the main pool and modifies its search path dynamically. If it's a database strategy, it asks the app for the custom pool and uses it.

14. FAQs

15. Deployment Checklist

Copy this checklist into your issue tracker before going to production:

- [ ] Node.js version verified
- [ ] Dependencies installed (pg)
- [ ] Production environment variables configured
- [ ] PostgreSQL production server configured
- [ ] PostgreSQL roles/permissions verified (CREATEDB if using 'database' strategy)
- [ ] Connection pool configuration verified (PgBouncer evaluated for 'database' strategy)
- [ ] Migration files (.sql) are idempotent
- [ ] Tenant creation tested
- [ ] Tenant isolation (schema path or db) tested
- [ ] Application build completed
- [ ] PM2 configured
- [ ] Reverse proxy configured
- [ ] SSL configured
- [ ] DNS configured
- [ ] Wildcard DNS configured if required
- [ ] APIs verified
- [ ] PostgreSQL queries correctly releasing clients (client.release())
- [ ] Logs verified
- [ ] Backups configured
- [ ] Restore process tested