sqlpage--sqlpage/examples/nginx/README.md

SQLPage with NGINX Example

This example demonstrates how to set up SQLPage behind an NGINX reverse proxy using Docker Compose. It showcases various features such as rate limiting, URL rewriting, caching, and more.

Overview

The setup consists of three main components:

1. SQLPage: The main application server
2. NGINX: The reverse proxy
3. MySQL: The database

Getting Started

1. Clone the repository and navigate to the examples/nginx directory.

2. Start the services using Docker Compose:

   docker compose up
   

3. Access the application at http://localhost.

Docker Compose Configuration

The docker-compose.yml file defines the services.

SQLPage Service

The SQLPage service uses the latest SQLPage development image, sets up necessary volume mounts for configuration (on /etc/sqlpage) and website (on /var/www) files, and establishes a connection to the MySQL database. It reads http requests from a Unix socket (instead of a TCP socket) for communication with NGINX. This removes the overhead of TCP/IP when nginx and sqlpage are running on the same machine.

NGINX Service

The NGINX service uses the official Alpine-based image. It exposes port 80 and mounts the SQLPage socket and the custom NGINX configuration file.

MySQL Service

This service sets up a MySQL database with predefined credentials and a persistent volume for data storage.

NGINX Configuration

The nginx.conf file contains the NGINX configuration:

Streaming and compression

SQLPage streams HTML as it is generated, so browsers can start rendering before the database finishes returning rows. NGINX enables proxy_buffering by default, which can delay those first bytes but stores responses for slow clients. Start with a modest buffer configuration and let the proxy handle compression:

    proxy_buffering on;
    proxy_buffer_size 16k;
    proxy_buffers 4 16k;

    gzip on;
    gzip_buffers 2 4k;
    gzip_types text/html text/plain text/css application/javascript application/json;

    chunked_transfer_encoding on;

Keep buffering when you expect slow clients or longer SQLPage queries, increasing the buffer sizes only if responses overflow. When most users are on fast connections reading lightweight pages, consider reducing the buffer counts or flipping to proxy_buffering off; to minimise latency, accepting the extra load on SQLPage. See the proxy buffering, gzip, and chunked transfer directives for more guidance.

When SQLPage runs behind a reverse proxy, set compress_responses to false in its configuration (documented here) so that NGINX can perform compression once at the edge.

Rate Limiting

    limit_req_zone $binary_remote_addr zone=one:10m rate=1r/s;

This line defines a rate limiting zone that allows 1 request per second per IP address.

Server Block

    server {
        listen 80;
        server_name localhost;

        location / {
            limit_req zone=one burst=5;

            proxy_pass http://unix:/tmp/sqlpage/sqlpage.sock;
        }
    }

The server block defines how NGINX handles incoming requests.

URL rewriting:

            rewrite ^/post/([0-9]+)$ /post.sql?id=$1 last;

This line rewrites URLs like /post/123 to /post.sql?id=123.

Proxy configuration:

proxy_pass http://unix:/tmp/sqlpage/sqlpage.sock;

These lines configure NGINX to proxy requests to the SQLPage Unix socket.

Caching:

            # Enable caching
            proxy_cache_valid 200 60m;
            proxy_cache_valid 404 10m;
            proxy_cache_use_stale error timeout http_500 http_502 http_503 http_504;

These lines enable caching of responses from SQLPage.

Buffering:

            # Enable buffering
            proxy_buffering on;
            proxy_buffer_size 128k;
            proxy_buffers 4 256k;
            proxy_busy_buffers_size 256k;

These lines configure response buffering for improved performance.

SQLPage Configuration

The SQLPage configuration is stored in sqlpage_config/sqlpage.json:

{
  "max_database_pool_connections": 10,
  "database_connection_idle_timeout_seconds": 1800,
  "max_uploaded_file_size": 10485760,
  "compress_responses": false,
  "environment": "production"
}

This configuration sets various SQLPage options, including the maximum number of database connections and the environment.

Application Structure

The application consists of several SQL files in the website directory:

1. index.sql: Displays a list of blog posts
2. post.sql: Shows details of a specific post and its comments
3. add_comment.sql: Handles adding new comments

The database schema and initial data are defined in sqlpage_config/migrations/000_init.sql.