Connect PostgreSQL to PostPenguin

Store PostPenguin blog posts directly in your PostgreSQL database. This guide works with any backend that can connect to PostgreSQL.

🗄️ Database Schema

First, create the posts table in your PostgreSQL database:

-- Create posts table for PostPenguin content
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    postpenguin_id VARCHAR(255) UNIQUE NOT NULL,
    title VARCHAR(500) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    html TEXT NOT NULL,
    meta_title VARCHAR(500),
    meta_description TEXT,
    featured_image_url VARCHAR(1000),
    tags JSONB DEFAULT '[]',
    status VARCHAR(50) DEFAULT 'publish',
    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published_at ON posts(published_at DESC);
CREATE INDEX idx_posts_postpenguin_id ON posts(postpenguin_id);

-- Create updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

🚀 Node.js Webhook Handler

Create a webhook endpoint that saves posts to PostgreSQL:

// webhook-handler.js
const express = require('express')
const { Pool } = require('pg')
const crypto = require('crypto')

const app = express()
app.use(express.json())

// PostgreSQL connection
const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    // Or use individual settings:
    // host: process.env.DB_HOST,
    // port: process.env.DB_PORT,
    // database: process.env.DB_NAME,
    // user: process.env.DB_USER,
    // password: process.env.DB_PASSWORD,
})

// Verify webhook signature
function verifySignature(payload, signature, secret) {
    const expectedSignature = crypto
        .createHmac('sha256', secret)
        .update(payload)
        .digest('hex')
    const receivedSignature = signature.replace('sha256=', '')
    return crypto.timingSafeEqual(
        Buffer.from(expectedSignature),
        Buffer.from(receivedSignature)
    )
}

// PostPenguin webhook endpoint
app.post('/api/webhooks/postpenguin', async (req, res) => {
    const client = await pool.connect()
    
    try {
        // Verify signature
        const signature = req.headers['x-postpenguin-signature']
        const secret = process.env.POSTPENGUIN_WEBHOOK_SECRET
        
        if (secret && signature) {
            const payload = JSON.stringify(req.body)
            if (!verifySignature(payload, signature, secret)) {
                return res.status(401).json({ error: 'Invalid signature' })
            }
        }
        
        const { 
            postPenguinId, 
            title, 
            slug, 
            html, 
            meta_title, 
            meta_description, 
            featured_image,
            tags 
        } = req.body
        
        // Validate required fields
        if (!title || !slug || !html) {
            return res.status(400).json({ error: 'Missing required fields' })
        }
        
        // Upsert post (insert or update)
        const result = await client.query(`
            INSERT INTO posts (
                postpenguin_id, title, slug, html, 
                meta_title, meta_description, featured_image_url, 
                tags, published_at
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW())
            ON CONFLICT (postpenguin_id) 
            DO UPDATE SET
                title = EXCLUDED.title,
                slug = EXCLUDED.slug,
                html = EXCLUDED.html,
                meta_title = EXCLUDED.meta_title,
                meta_description = EXCLUDED.meta_description,
                featured_image_url = EXCLUDED.featured_image_url,
                tags = EXCLUDED.tags,
                updated_at = NOW()
            RETURNING id, postpenguin_id
        `, [
            postPenguinId || `pp_${Date.now()}`,
            title,
            slug,
            html,
            meta_title || title,
            meta_description || '',
            featured_image || null,
            JSON.stringify(tags || [])
        ])
        
        const post = result.rows[0]
        
        console.log(`✅ Post saved: ${title}`)
        
        res.status(200).json({
            success: true,
            postId: post.id,
            postPenguinId: post.postpenguin_id
        })
        
    } catch (error) {
        console.error('Webhook error:', error)
        res.status(500).json({ error: 'Internal server error' })
    } finally {
        client.release()
    }
})

// Fetch posts API
app.get('/api/posts', async (req, res) => {
    try {
        const { slug, limit = 10, offset = 0, status = 'publish' } = req.query
        
        if (slug) {
            const result = await pool.query(
                'SELECT * FROM posts WHERE slug = $1 AND status = $2',
                [slug, status]
            )
            if (result.rows.length === 0) {
                return res.status(404).json({ error: 'Post not found' })
            }
            return res.json({ post: result.rows[0] })
        }
        
        const result = await pool.query(`
            SELECT * FROM posts 
            WHERE status = $1 
            ORDER BY published_at DESC 
            LIMIT $2 OFFSET $3
        `, [status, limit, offset])
        
        const countResult = await pool.query(
            'SELECT COUNT(*) FROM posts WHERE status = $1',
            [status]
        )
        
        res.json({
            posts: result.rows,
            pagination: {
                total: parseInt(countResult.rows[0].count),
                limit: parseInt(limit),
                offset: parseInt(offset)
            }
        })
        
    } catch (error) {
        console.error('Error fetching posts:', error)
        res.status(500).json({ error: 'Internal server error' })
    }
})

const PORT = process.env.PORT || 3001
app.listen(PORT, () => {
    console.log(`🚀 Server running on port ${PORT}`)
    console.log(`📡 Webhook: http://localhost:${PORT}/api/webhooks/postpenguin`)
})

🐍 Python Webhook Handler

If you prefer Python, here's a Flask example:

# webhook_handler.py
from flask import Flask, request, jsonify
import psycopg2
import hmac
import hashlib
import os
from datetime import datetime
import json

app = Flask(__name__)

# Database connection
def get_db():
    return psycopg2.connect(os.environ['DATABASE_URL'])

# Verify signature
def verify_signature(payload, signature, secret):
    expected = hmac.new(
        secret.encode(),
        payload.encode(),
        hashlib.sha256
    ).hexdigest()
    received = signature.replace('sha256=', '')
    return hmac.compare_digest(expected, received)

@app.route('/api/webhooks/postpenguin', methods=['POST'])
def webhook():
    try:
        # Verify signature
        signature = request.headers.get('X-PostPenguin-Signature', '')
        secret = os.environ.get('POSTPENGUIN_WEBHOOK_SECRET', '')
        
        if secret and signature:
            if not verify_signature(request.get_data(as_text=True), signature, secret):
                return jsonify({'error': 'Invalid signature'}), 401
        
        data = request.json
        
        # Validate required fields
        if not all(k in data for k in ['title', 'slug', 'html']):
            return jsonify({'error': 'Missing required fields'}), 400
        
        conn = get_db()
        cur = conn.cursor()
        
        # Upsert post
        cur.execute("""
            INSERT INTO posts (
                postpenguin_id, title, slug, html,
                meta_title, meta_description, featured_image_url,
                tags, published_at
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW())
            ON CONFLICT (postpenguin_id)
            DO UPDATE SET
                title = EXCLUDED.title,
                slug = EXCLUDED.slug,
                html = EXCLUDED.html,
                meta_title = EXCLUDED.meta_title,
                meta_description = EXCLUDED.meta_description,
                featured_image_url = EXCLUDED.featured_image_url,
                tags = EXCLUDED.tags,
                updated_at = NOW()
            RETURNING id
        """, (
            data.get('postPenguinId', f'pp_{int(datetime.now().timestamp())}'),
            data['title'],
            data['slug'],
            data['html'],
            data.get('meta_title', data['title']),
            data.get('meta_description', ''),
            data.get('featured_image'),
            json.dumps(data.get('tags', []))
        ))
        
        post_id = cur.fetchone()[0]
        conn.commit()
        cur.close()
        conn.close()
        
        return jsonify({'success': True, 'postId': post_id})
        
    except Exception as e:
        print(f'Webhook error: {e}')
        return jsonify({'error': 'Internal server error'}), 500

if __name__ == '__main__':
    app.run(port=3001)

⚙️ Environment Variables

# .env file
DATABASE_URL=postgresql://user:password@localhost:5432/your_database
POSTPENGUIN_WEBHOOK_SECRET=your-secret-key-here
PORT=3001

🧪 Testing

Test Database Connection

# Connect and verify table exists
psql $DATABASE_URL -c "SELECT COUNT(*) FROM posts;"

Test Webhook

curl -X POST http://localhost:3001/api/webhooks/postpenguin \
  -H "Content-Type: application/json" \
  -d '{
    "postPenguinId": "test_123",
    "title": "Test PostgreSQL Post",
    "slug": "test-postgresql-post",
    "html": "<p className="text-gray-700">This is a test post stored in PostgreSQL.</p>",
    "meta_title": "Test Post",
    "meta_description": "Testing PostgreSQL webhook",
    "featured_image": "https://via.placeholder.com/800x400",
    "tags": ["test", "postgresql"]
  }'

Verify Post Was Saved

# Check database
psql $DATABASE_URL -c "SELECT id, title, slug FROM posts ORDER BY created_at DESC LIMIT 5;"

📊 Useful Queries

-- Get recent posts
SELECT id, title, slug, published_at 
FROM posts 
WHERE status = 'publish' 
ORDER BY published_at DESC 
LIMIT 10;

-- Search posts by title
SELECT * FROM posts 
WHERE title ILIKE '%keyword%';

-- Get posts by tag
SELECT * FROM posts 
WHERE tags @> '["seo"]';

-- Count posts by status
SELECT status, COUNT(*) 
FROM posts 
GROUP BY status;

-- Get post with full content
SELECT * FROM posts WHERE slug = 'my-post-slug';

🚀 Production Deployment

Using Docker

# Dockerfile
FROM node:18-alpine
WORKDIR /app
COPY package*.json ./
RUN npm ci --only=production
COPY . .
EXPOSE 3001
CMD ["node", "webhook-handler.js"]

Using PM2

# Install PM2
npm install -g pm2

# Start with PM2
pm2 start webhook-handler.js --name postpenguin-webhook

# Save and setup startup
pm2 save
pm2 startup