PostgreSQL Adapter - BuilderBot

The PostgreSQL adapter provides the most advanced database solution for BuilderBot, featuring automatic contact management, stored procedures, and support for complex data relationships.

Installation

npm install @builderbot/database-postgres

The adapter uses the pg library (v8.11+), which is included as a dependency.

Basic Usage

import { createBot, createFlow, createProvider } from '@builderbot/bot'
import { PostgreSQLAdapter as Database } from '@builderbot/database-postgres'
import { BaileysProvider as Provider } from '@builderbot/provider-baileys'

const main = async () => {
  const adapterDB = new Database({
    host: process.env.POSTGRES_DB_HOST,
    user: process.env.POSTGRES_DB_USER,
    database: process.env.POSTGRES_DB_NAME,
    password: process.env.POSTGRES_DB_PASSWORD,
    port: 5432
  })
  
  await createBot({
    flow: createFlow([]),
    provider: createProvider(Provider),
    database: adapterDB
  })
}

main()

Configuration

host

string

required

PostgreSQL server hostname or IP address.Examples: localhost, 127.0.0.1, postgres.example.com

user

string

required

PostgreSQL username for authentication.

database

string

required

Name of the database to use.

password

string

required

Password for the PostgreSQL user.

port

number

PostgreSQL server port.Default: 5432

Connection Examples

import { PostgreSQLAdapter } from '@builderbot/database-postgres'

const adapterDB = new PostgreSQLAdapter({
  host: 'localhost',
  user: 'postgres',
  database: 'builderbot',
  password: 'password',
  port: 5432
})

Environment Variables

Create a .env file:

.env

POSTGRES_DB_HOST=localhost
POSTGRES_DB_USER=builderbot_user
POSTGRES_DB_NAME=builderbot
POSTGRES_DB_PASSWORD=your_secure_password
POSTGRES_DB_PORT=5432

Load it in your application:

import 'dotenv/config'
import { PostgreSQLAdapter } from '@builderbot/database-postgres'

const adapterDB = new PostgreSQLAdapter({
  host: process.env.POSTGRES_DB_HOST,
  user: process.env.POSTGRES_DB_USER,
  database: process.env.POSTGRES_DB_NAME,
  password: process.env.POSTGRES_DB_PASSWORD,
  port: Number(process.env.POSTGRES_DB_PORT) || 5432
})

Database Schema

The adapter automatically creates two tables and two stored procedures:

Tables

`contact` Table

Stores user contact information:

CREATE TABLE IF NOT EXISTS contact (
  id SERIAL PRIMARY KEY,
  phone VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT current_timestamp,
  updated_in TIMESTAMP,
  last_interaction TIMESTAMP,
  values JSONB
);

`history` Table

Stores conversation history with foreign key to contacts:

CREATE TABLE IF NOT EXISTS history (
  id SERIAL PRIMARY KEY,
  ref VARCHAR(255) NOT NULL,
  keyword VARCHAR(255),
  answer TEXT NOT NULL,
  refSerialize TEXT NOT NULL,
  phone VARCHAR(255) DEFAULT NULL,
  options JSONB,
  created_at TIMESTAMP DEFAULT current_timestamp,
  updated_in TIMESTAMP,
  contact_id INTEGER REFERENCES contact(id)
);

Stored Procedures

The adapter creates two stored procedures for efficient data management:

1. `save_or_update_contact`

Saves or updates contact information:

CREATE OR REPLACE FUNCTION save_or_update_contact(
  in_phone VARCHAR(255),
  in_values JSONB
)
RETURNS VOID AS $$
DECLARE
  contact_id INT;
BEGIN
  SELECT id INTO contact_id FROM contact WHERE phone = in_phone;
  
  IF contact_id IS NULL THEN
    INSERT INTO contact (phone, "values")
    VALUES (in_phone, in_values);
  ELSE
    UPDATE contact SET "values" = in_values, updated_in = current_timestamp
    WHERE id = contact_id;
  END IF;
END;
$$ LANGUAGE plpgsql;

2. `save_or_update_history_and_contact`

Saves history entry and updates contact automatically:

CREATE OR REPLACE FUNCTION save_or_update_history_and_contact(
  in_ref VARCHAR(255),
  in_keyword VARCHAR(255),
  in_answer TEXT,
  in_refserialize TEXT,
  in_phone VARCHAR(255),
  in_options JSONB
)
RETURNS VOID AS $$
DECLARE
  _contact_id INT;
BEGIN
  SELECT id INTO _contact_id FROM contact WHERE phone = in_phone;
  
  IF _contact_id IS NULL THEN
    INSERT INTO contact (phone)
    VALUES (in_phone)
    RETURNING id INTO _contact_id;
  ELSE
    UPDATE contact SET last_interaction = current_timestamp WHERE id = _contact_id;
  END IF;
  
  INSERT INTO history (ref, keyword, answer, refserialize, phone, options, contact_id, created_at)
  VALUES (in_ref, in_keyword, in_answer, in_refserialize, in_phone, in_options, _contact_id, current_timestamp);
END;
$$ LANGUAGE plpgsql;

Auto-Migration

The adapter automatically creates all tables and stored procedures on first connection:

packages/database-postgres/src/postgresAdapter.ts

async checkTableExistsAndSP(): Promise<void> {
  // Create contact table
  await this.db.query(contactTableSQL)
  
  // Create history table
  await this.db.query(historyTableSQL)
  
  // Create stored procedures
  await this.createSP()
}

No manual migration required!

Contact Management

Unique to the PostgreSQL adapter, you can save and manage contact information:

import { PostgreSQLAdapter } from '@builderbot/database-postgres'

const adapterDB = new PostgreSQLAdapter({ /* credentials */ })

// Save contact with custom data
await adapterDB.saveContact({
  from: '1234567890',
  values: {
    name: 'John Doe',
    email: '[email protected]',
    preferences: { language: 'en', notifications: true }
  },
  action: 'a'  // 'a' = add/merge, 'u' = update/replace
})

// Get contact information
const contact = await adapterDB.getContact({ from: '1234567890' })
console.log(contact)
// {
//   id: 1,
//   phone: '1234567890',
//   created_at: '2024-03-15T10:30:00Z',
//   last_interaction: '2024-03-15T14:20:00Z',
//   values: { name: 'John Doe', email: '[email protected]', ... }
// }

Contact Actions

action

string

How to handle existing contact values:

'a' (default): Add/merge new values with existing ones
'u': Update/replace all values with new ones

// Merge new fields (keeps existing data)
await adapterDB.saveContact({
  from: '1234567890',
  values: { subscribed: true },
  action: 'a'  // Merges with existing values
})

// Replace all fields
await adapterDB.saveContact({
  from: '1234567890',
  values: { name: 'Jane Doe' },
  action: 'u'  // Replaces all values
})

PostgreSQL Setup

1. Install PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

2. Create Database and User

# Switch to postgres user
sudo -u postgres psql

-- Create database
CREATE DATABASE builderbot;

-- Create user
CREATE USER builderbot_user WITH PASSWORD 'your_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE builderbot TO builderbot_user;

-- Connect to database
\c builderbot

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO builderbot_user;

-- Exit
\q

3. Test Connection

psql -U builderbot_user -d builderbot -h localhost

Cloud PostgreSQL Providers

Railway

Create new PostgreSQL database in Railway
Copy connection details from “Connect” tab
Use provided credentials:

const adapterDB = new PostgreSQLAdapter({
  host: process.env.PGHOST,
  user: process.env.PGUSER,
  database: process.env.PGDATABASE,
  password: process.env.PGPASSWORD,
  port: Number(process.env.PGPORT)
})

Render

Create new PostgreSQL database in Render
Copy “Internal Connection String” for same-region access
Use provided credentials

Supabase

Create new project in Supabase
Go to Settings → Database
Use “Connection pooling” settings for production:

const adapterDB = new PostgreSQLAdapter({
  host: 'db.xxxxx.supabase.co',
  user: 'postgres',
  database: 'postgres',
  password: process.env.SUPABASE_PASSWORD,
  port: 6543  // Pooler port
})

Indexes for Performance

Add indexes to improve query performance:

-- Indexes on history table
CREATE INDEX idx_history_phone ON history(phone);
CREATE INDEX idx_history_created ON history(created_at DESC);
CREATE INDEX idx_history_contact ON history(contact_id);

-- Indexes on contact table
CREATE INDEX idx_contact_phone ON contact(phone);
CREATE INDEX idx_contact_last_interaction ON contact(last_interaction DESC);

-- Index on JSONB values (for searching contact data)
CREATE INDEX idx_contact_values ON contact USING GIN (values);

Advanced Queries

Query JSONB contact data:

-- Find contacts with specific value
SELECT * FROM contact WHERE values->>'name' = 'John Doe';

-- Find contacts with email
SELECT * FROM contact WHERE values ? 'email';

-- Find contacts with notifications enabled
SELECT * FROM contact WHERE values->'preferences'->>'notifications' = 'true';

TypeScript Support

import { addKeyword } from '@builderbot/bot'
import { PostgreSQLAdapter } from '@builderbot/database-postgres'
import { BaileysProvider } from '@builderbot/provider-baileys'

const welcomeFlow = addKeyword<BaileysProvider, PostgreSQLAdapter>(['hello'])
  .addAnswer('Hi! How can I help you?', async (ctx, { state }) => {
    const adapterDB = state.get('adapterDB') as PostgreSQLAdapter
    const contact = await adapterDB.getContact(ctx)
    console.log('Contact data:', contact?.values)
  })

Backup and Restore

Using pg_dump

# Backup entire database
pg_dump -U builderbot_user -d builderbot > backup.sql

# Backup specific tables
pg_dump -U builderbot_user -d builderbot -t history -t contact > backup.sql

# Backup with compression
pg_dump -U builderbot_user -d builderbot | gzip > backup.sql.gz

# Restore
psql -U builderbot_user -d builderbot < backup.sql

Automated Backups

# Add to crontab (crontab -e)
0 2 * * * pg_dump -U builderbot_user builderbot | gzip > /backups/builderbot_$(date +\%Y\%m\%d).sql.gz

Connection Pooling

The adapter uses pg.Pool for connection pooling:

packages/database-postgres/src/postgresAdapter.ts

import { Pool } from 'pg'

async init(): Promise<boolean> {
  const pool = new Pool(this.credentials)
  const db = await pool.connect()
  this.db = db
  return true
}

For custom pool configuration:

import { Pool } from 'pg'

const pool = new Pool({
  host: process.env.POSTGRES_DB_HOST,
  user: process.env.POSTGRES_DB_USER,
  database: process.env.POSTGRES_DB_NAME,
  password: process.env.POSTGRES_DB_PASSWORD,
  port: 5432,
  max: 20,                  // Maximum pool size
  idleTimeoutMillis: 30000, // Close idle clients after 30s
  connectionTimeoutMillis: 2000 // Return error after 2s if no connection available
})

Troubleshooting

Connection Refused

Error: connect ECONNREFUSED 127.0.0.1:5432

Solutions:

Check if PostgreSQL is running: sudo systemctl status postgresql
Verify port: sudo netstat -tlnp | grep 5432
Check postgresql.conf for listen_addresses

Authentication Failed

Error: password authentication failed for user "user"

Solutions:

Verify username and password
Check pg_hba.conf for authentication method
Ensure user has database access

Permission Denied

Error: permission denied for schema public

Solution:

GRANT ALL ON SCHEMA public TO builderbot_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO builderbot_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO builderbot_user;

Best Practices

Use environment variables for credentials

Create dedicated database user with limited privileges

Enable SSL/TLS for remote connections

Add indexes on frequently queried columns

Set up automated backups

Use connection pooling for high traffic

Monitor query performance with EXPLAIN ANALYZE

Leverage JSONB for flexible contact data

Next Steps

State Management

Learn about persisting conversation state

Testing

Test your bot with PostgreSQL

Get Started

Core Concepts

Providers

Databases

Guides

Integrations

​Installation

​Basic Usage

​Configuration

​Connection Examples

​Environment Variables

​Database Schema

​Tables

​contact Table

​history Table

​Stored Procedures

​1. save_or_update_contact

​2. save_or_update_history_and_contact

​Auto-Migration

​Contact Management

​Contact Actions

​PostgreSQL Setup

​1. Install PostgreSQL

​2. Create Database and User

​3. Test Connection

​Cloud PostgreSQL Providers

​Railway

​Render

​Supabase

​Indexes for Performance

​Advanced Queries

​TypeScript Support

​Backup and Restore

​Using pg_dump

​Automated Backups

​Connection Pooling

​Troubleshooting

​Connection Refused

​Authentication Failed

​Permission Denied

​Best Practices

​Next Steps

State Management

Testing

Build docs developers (and LLMs) love

Installation

Basic Usage

Configuration

Connection Examples

Environment Variables

Database Schema

Tables

`contact` Table

`history` Table

Stored Procedures

1. `save_or_update_contact`

2. `save_or_update_history_and_contact`

Auto-Migration

Contact Management

Contact Actions

PostgreSQL Setup

1. Install PostgreSQL

2. Create Database and User

3. Test Connection

Cloud PostgreSQL Providers

Railway

Render

Supabase

Indexes for Performance

Advanced Queries

TypeScript Support

Backup and Restore

Using pg_dump

Automated Backups

Connection Pooling

Troubleshooting

Connection Refused

Authentication Failed

Permission Denied

Best Practices

Next Steps