Designing a API Gateway with PostgreSQL

What is an API Gateway?

An API Gateway is a server-side component that acts as a single entry point for client requests to a collection of backend services. It handles request routing, composition, authentication, rate limiting, logging, and other cross-cutting concerns. Instead of clients calling microservices directly, they communicate with the gateway, which forwards requests to the appropriate service.

Using a database like PostgreSQL as the backing store for an API Gateway is a practical approach for small to medium-scale deployments where you need persistence for routing rules, API keys, rate limit counters, and audit logs. PostgreSQL's reliability, rich data types, and transactional guarantees make it an excellent choice for this role.

Why Use PostgreSQL as the Backend for an API Gateway?

🚀 Deploy your AI agent in 10 minutes

Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.

Try it free →

While many API gateways use in-memory stores (like Redis) for high performance, PostgreSQL offers several advantages:

Persistence: Routing rules, API keys, and configurations survive restarts.
ACID compliance: Safe concurrent updates to rate limit counters or API key revocation.
Rich querying: You can implement dynamic routing based on request parameters stored in the database.
Built-in replication and backups: PostgreSQL provides robust data safety without additional infrastructure.
Unified storage: One database for gateway configuration, logs, and analytics simplifies operations.

Core Components of an API Gateway with PostgreSQL

Before diving into code, understand the main tables you'll need:

api_routes – maps incoming paths/methods to backend services.
api_keys – stores client credentials and permissions.
rate_limits – defines per-key or per-route limits.
access_logs – records every request for auditing and analytics.
rate_limit_counters – tracks current usage windows (can be stored with timestamps).

Step-by-Step Implementation

We'll build a simple API Gateway in Node.js using Express and the pg library. The gateway will read routes from PostgreSQL and forward requests accordingly.

1. Setting Up PostgreSQL Schema

Create the following tables. You can run this SQL script against your PostgreSQL instance.

-- Routes table
CREATE TABLE api_routes (
    id SERIAL PRIMARY KEY,
    method VARCHAR(10) NOT NULL,          -- GET, POST, etc.
    path_pattern VARCHAR(255) NOT NULL,   -- e.g. /users/:id
    target_url TEXT NOT NULL,             -- e.g. http://user-service:3001
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);

-- API keys table
CREATE TABLE api_keys (
    id SERIAL PRIMARY KEY,
    key_value VARCHAR(64) UNIQUE NOT NULL,
    owner VARCHAR(255) NOT NULL,
    is_revoked BOOLEAN DEFAULT false,
    rate_limit_per_minute INT DEFAULT 60,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Access logs
CREATE TABLE access_logs (
    id SERIAL PRIMARY KEY,
    api_key VARCHAR(64),
    method VARCHAR(10),
    path TEXT,
    status_code INT,
    response_time_ms INT,
    timestamp TIMESTAMP DEFAULT NOW()
);

-- Rate limit counters (one per key per minute window)
CREATE TABLE rate_limit_counters (
    api_key VARCHAR(64) REFERENCES api_keys(key_value),
    window_start TIMESTAMP NOT NULL,
    count INT DEFAULT 0,
    PRIMARY KEY (api_key, window_start)
);

2. Implementing the Gateway Logic

Install dependencies: npm init -y && npm install express pg http-proxy-middleware. Then create gateway.js.

const express = require('express');
const { Pool } = require('pg');
const { createProxyMiddleware } = require('http-proxy-middleware');

const app = express();
const pool = new Pool({
  user: 'gateway_user',
  host: 'localhost',
  database: 'gateway_db',
  password: 'secret',
  port: 5432,
});

// Middleware to check API key and rate limit
async function authenticateAndThrottle(req, res, next) {
  const apiKey = req.headers['x-api-key'];
  if (!apiKey) {
    return res.status(401).json({ error: 'Missing API key' });
  }

  try {
    // Validate API key
    const keyResult = await pool.query(
      'SELECT id, is_revoked, rate_limit_per_minute FROM api_keys WHERE key_value = $1',
      [apiKey]
    );
    if (keyResult.rows.length === 0 || keyResult.rows[0].is_revoked) {
      return res.status(403).json({ error: 'Invalid or revoked API key' });
    }

    const { rate_limit_per_minute } = keyResult.rows[0];

    // Rate limiting using PostgreSQL – get current minute window
    const now = new Date();
    const windowStart = new Date(now.getFullYear(), now.getMonth(), now.getDate(),
                                 now.getHours(), now.getMinutes(), 0, 0);
    
    // Upsert counter
    await pool.query(
      `INSERT INTO rate_limit_counters (api_key, window_start, count)
       VALUES ($1, $2, 1)
       ON CONFLICT (api_key, window_start)
       DO UPDATE SET count = rate_limit_counters.count + 1
       WHERE rate_limit_counters.count < $3`,
      [apiKey, windowStart, rate_limit_per_minute]
    );

    // Check if limit exceeded
    const counterResult = await pool.query(
      'SELECT count FROM rate_limit_counters WHERE api_key = $1 AND window_start = $2',
      [apiKey, windowStart]
    );

    if (counterResult.rows.length > 0 && counterResult.rows[0].count > rate_limit_per_minute) {
      return res.status(429).json({ error: 'Rate limit exceeded' });
    }

    req.apiKey = apiKey;
    next();
  } catch (err) {
    console.error('Auth/Throttle error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
}

// Apply middleware to all routes
app.use(authenticateAndThrottle);

// Load routes from PostgreSQL and create proxy middleware
async function loadRoutes() {
  const result = await pool.query('SELECT * FROM api_routes WHERE is_active = true');
  result.rows.forEach(route => {
    const proxyOptions = {
      target: route.target_url,
      changeOrigin: true,
      onProxyRes: async (proxyRes, req, res) => {
        // Log request to access_logs
        const start = Date.now();
        res.on('finish', async () => {
          const duration = Date.now() - start;
          await pool.query(
            'INSERT INTO access_logs (api_key, method, path, status_code, response_time_ms) VALUES ($1,$2,$3,$4,$5)',
            [req.apiKey, req.method, req.originalUrl, res.statusCode, duration]
          );
        });
      }
    };

    app.use(route.path_pattern, createProxyMiddleware(proxyOptions));
    console.log(`Route registered: ${route.method} ${route.path_pattern} -> ${route.target_url}`);
  });
}

// Start the gateway
loadRoutes().then(() => {
  app.listen(3000, () => {
    console.log('API Gateway running on port 3000');
  });
}).catch(err => {
  console.error('Failed to load routes:', err);
});

3. Routing Requests

The gateway automatically creates proxy middleware for each active route in the api_routes table. To add a new route, simply insert a row:

INSERT INTO api_routes (method, path_pattern, target_url)
VALUES ('GET', '/api/users', 'http://user-service:4000/users');

After restarting the gateway (or implementing a reload endpoint), the new route becomes active.

4. Rate Limiting and Throttling

Our implementation uses PostgreSQL's ON CONFLICT ... DO UPDATE to atomically increment a counter per API key per minute. The WHERE rate_limit_counters.count < $3 condition prevents unnecessary writes after the limit is exceeded. The check after the upsert ensures we return 429 if the count exceeds the allowed limit.

For production, you may want to add a cleanup job to delete old windows.

5. Authentication and Authorization

We authenticate using the x-api-key header and check against the api_keys table. You can extend this to include role-based permissions by adding a permissions field to the table and checking it against the route.

-- Add permissions column
ALTER TABLE api_keys ADD COLUMN permissions TEXT[] DEFAULT '{}';

-- In middleware, check if route requires permission
const routePermissions = route.permissions; // fetch from api_routes
if (routePermissions.length > 0) {
  const keyPermissions = keyResult.rows[0].permissions;
  if (!routePermissions.some(p => keyPermissions.includes(p))) {
    return res.status(403).json({ error: 'Insufficient permissions' });
  }
}

6. Logging and Monitoring

We log every request to access_logs after the proxy response finishes. This provides a complete audit trail. You can later query this table for analytics:

-- Top 10 slowest endpoints
SELECT path, AVG(response_time_ms) as avg_time
FROM access_logs
GROUP BY path
ORDER BY avg_time DESC
LIMIT 10;

Best Practices

Use connection pooling: Always reuse database connections (the pg.Pool does this).
Index key columns: Add indexes on api_keys(key_value), rate_limit_counters(api_key, window_start), and access_logs(timestamp).
Cache frequently accessed data: For high‑throughput, cache route configurations in memory and reload on change.
Implement graceful shutdown: Close pool connections before exiting.
Use parameterized queries: Always use placeholders to prevent SQL injection (as shown).
Separate read/write concerns: Use a read replica for logs and counters if writes become a bottleneck.
Monitor database load: Rate limit counters can cause write contention – consider using Redis for counters and PostgreSQL for persistent data.
Version your routes: Include version prefixes in path_pattern (e.g., /v1/users).

Conclusion

Designing an API Gateway with PostgreSQL is a pragmatic solution for teams that need persistent, queryable configuration and audit trails without introducing additional infrastructure like Redis or a separate configuration service. By storing routing rules, API keys, rate limits, and logs in PostgreSQL, you gain ACID compliance, easy backups, and the ability to run complex analytical queries directly on gateway data. The implementation shown here is fully functional for moderate loads and can be extended with caching, health checks, and dynamic route reloading. While PostgreSQL may not match the raw throughput of an in‑memory store, its simplicity and reliability make it an excellent starting point for many projects.