---
title: "Database, Redis, and infrastructure setup"
description: "Database, Redis, and file storage setup for the TinyMCE AI on-premises service"
canonical_url: "https://www.tiny.cloud/docs/tinymce/latest/tinymceai-on-premises-database/"
md_url: "https://www.tiny.cloud/docs/tinymce/latest/tinymceai-on-premises-database/index.md"
version: "latest"
last_updated: "2026-05-28T04:31:35Z"
tokens: 5478
---
# Database, Redis, and infrastructure setup

This page covers the **data layer**: the SQL database, Redis, and file storage. These components must be running and accessible before the AI service container can start — the service connects to them on boot and will not proceed without them.

- **SQL database**: stores persistent data such as configurations, conversations, files, and documents.
- **Redis**: caching and coordination (SSE delivery, rate limits, pub/sub). Enables the AI service to remain stateless.
- **File storage**: stores uploaded files and documents.

Configure the data layer first, then proceed to [LLM providers](../tinymceai-on-premises-providers/) and [JWT authentication](../tinymceai-on-premises-jwt/). For container runtimes, reverse proxies, Transport Layer Security (TLS), Kubernetes, and ECS deployment, see the [Production deployment guide](../tinymceai-on-premises-production/).

## Supported versions

| Component | Minimum | Recommended | Notes |
| --- | --- | --- | --- |
| MySQL | 8.0 | 8.0.x (latest patch) | Pin to `mysql:8.0`. See [MySQL](#mysql-version-pinning). |
| PostgreSQL | 13 | 16 |  |
| Redis | 3.2.6 | 7.x | Redis Cluster and TLS supported through `REDIS_CLUSTER_NODES` and `REDIS_TLS_ENABLE`. |

The AI service supports both MySQL and PostgreSQL equally. Pick whichever the operations team already runs.

## Choosing a setup path

Use Docker Compose for evaluation or managed cloud services (Amazon RDS, Cloud SQL, Azure Database) for production.

![Database setup decision tree: local Docker Compose vs managed cloud database for evaluation and production](../_images/tinymceai-on-premises/database-setup-fig-1.svg)

## PostgreSQL schema prerequisite

> **Note:** This section applies to PostgreSQL deployments only. MySQL deployments can skip to [Version pinning](#version-pinning).
The AI service expects a schema named `cs-on-premises` (with hyphens). If that schema does not exist, the container crashes on first boot with:

```
error: schema "cs-on-premises" does not exist
```
Apply one of the following fixes **before** starting the AI service for the first time.

### Option A: pre-create the schema

The double-quotes are mandatory because the schema name contains a hyphen.

```sql
CREATE SCHEMA "cs-on-premises";
```
Verify with `\dn` in psql. `cs-on-premises` should appear in the list.

### Option B: use the default `public` schema

Set the `DATABASE_SCHEMA` environment variable on the AI service container:

```
DATABASE_SCHEMA=public
```
This bypasses the hyphenated schema entirely. MySQL deployments do not require this step — the database name (`DATABASE_DATABASE`) serves as the namespace.

## Version pinning

> **Tip:** Pin specific major versions for all data layer images (`mysql:8.0`, `postgres:16`, `redis:7`). Floating tags like `:latest` or `:8` can introduce breaking changes during routine image pulls.

### MySQL

> **Warning:** Do **not** use `mysql:8`. That tag now floats to the latest MySQL, which removes the `default-authentication-plugin=mysql_native_password` startup flag the AI service relies on. The container crashloops with:

```
[ERROR] [MY-000067] [Server] unknown variable 'default-authentication-plugin=mysql_native_password'.
[ERROR] [MY-010119] [Server] Aborting
```
Pin to `mysql:8.0` in every manifest: `docker run`, Docker Compose, Kubernetes, Helm, ECS. Running newer MySQL versions with workarounds (removing the flag and switching to `caching_sha2_password`) is not a supported configuration.

## Database user privileges

On first boot the AI service runs schema migrations and creates roughly 32 tables across the following namespaces: `ai_assistant_*`, `environments_*`, `security_*`, `insights_*`, `blob_storage_*`, and `cs_migrations*`.

The database user needs enough privilege to create, alter, and operate on these tables.

### MySQL

```sql
CREATE USER 'ai_service'@'%' IDENTIFIED BY '<strong-password>';
GRANT SELECT, INSERT, UPDATE, DELETE,
      ALTER, CREATE, DROP, INDEX,
      TRIGGER, LOCK TABLES, REFERENCES
  ON ai_service.* TO 'ai_service'@'%';
FLUSH PRIVILEGES;
```

<!-- <details> -->
<!-- <summary> -->
Development shortcut
<!-- </summary> -->

```sql
GRANT ALL PRIVILEGES ON ai_service.* TO 'ai_service'@'%';
```

<!-- </details> -->

> **Note:** Some versions of the AI service image report false-positive "Not enough permissions to access database" errors even with `ALL PRIVILEGES`. If this occurs, grant the privileges globally rather than per-database, or use the MySQL `root` user for development.
> 
> On Cloud SQL MySQL, grant privileges to the service user **directly** — not via a role (e.g. `cloudsqlsuperuser`). The startup grant check runs `SHOW GRANTS FOR user` and does not resolve role-inherited grants.

### PostgreSQL

If `DATABASE_SCHEMA=public` was chosen (see [PostgreSQL schema prerequisite](#postgresql-schema-prerequisite)), substitute `public` for `"cs-on-premises"` in each statement below.

```sql
CREATE USER ai_service WITH PASSWORD '<strong-password>';
CREATE DATABASE ai_service OWNER ai_service;
\c ai_service
CREATE SCHEMA "cs-on-premises" AUTHORIZATION ai_service;
GRANT CREATE, USAGE ON SCHEMA "cs-on-premises" TO ai_service;
GRANT ALL ON ALL TABLES IN SCHEMA "cs-on-premises" TO ai_service;
GRANT ALL ON ALL SEQUENCES IN SCHEMA "cs-on-premises" TO ai_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA "cs-on-premises"
  GRANT ALL ON TABLES TO ai_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA "cs-on-premises"
  GRANT ALL ON SEQUENCES TO ai_service;
```

<!-- <details> -->
<!-- <summary> -->
Development shortcut
<!-- </summary> -->

```sql
GRANT ALL ON SCHEMA "cs-on-premises" TO ai_service;
```

<!-- </details> -->

> **Warning:** **Managed PostgreSQL TLS issue:** Amazon RDS, Cloud SQL, and Azure Database for PostgreSQL default to requiring TLS (`rds.force_ssl=1` / `require_secure_transport=ON`). When TLS is enforced and the AI service has not been configured with `DATABASE_SSL_CA`, the connection is rejected — but the error message surfaces as a generic "permissions" error, not a TLS error. If the service fails to start with a permissions-related database error after grants have been verified, check whether TLS is the underlying cause.

## Database setup

The sections below provide ready-to-use configuration for each database engine. Use the Docker Compose files for local evaluation; for production, provision managed database services (Amazon RDS, Azure Database, Cloud SQL) and pass the connection details as environment variables (see [[_connecting_the_ai_service]](#_connecting_the_ai_service)).

### Docker Compose (recommended for evaluation)

MySQL compose file
```yaml
services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: <root-password>
      MYSQL_DATABASE: ai_service
      MYSQL_USER: ai_service
      MYSQL_PASSWORD: <strong-password>
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 10s
      timeout: 5s
      retries: 5

  redis:
    image: redis:7
    ports:
      - "6379:6379"
    healthcheck:
      test: ["CMD", "redis-cli", "ping"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  mysql_data:
```
PostgreSQL compose file
```yaml
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: ai_service
      POSTGRES_USER: ai_service
      POSTGRES_PASSWORD: <strong-password>
    ports:
      - "5432:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ai_service -d ai_service"]
      interval: 10s
      timeout: 5s
      retries: 5

  redis:
    image: redis:7
    ports:
      - "6379:6379"
    healthcheck:
      test: ["CMD", "redis-cli", "ping"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  pg_data:
```
If using PostgreSQL and not using `DATABASE_SCHEMA=public`, after `docker compose up -d`, create the schema:

```bash
docker compose exec postgres psql -U ai_service -d ai_service \
  -c 'CREATE SCHEMA "cs-on-premises";'
```

### Docker single container (without Compose)

Use these `docker run` commands when Docker Compose is not available or when integrating into existing orchestration scripts.

<!-- <details> -->
<!-- <summary> -->
MySQL
<!-- </summary> -->

```bash
docker run -d \
  --name ai-mysql \
  -e MYSQL_ROOT_PASSWORD=<root-password> \
  -e MYSQL_DATABASE=ai_service \
  -e MYSQL_USER=ai_service \
  -e MYSQL_PASSWORD=<strong-password> \
  -p 3306:3306 \
  -v ai_mysql_data:/var/lib/mysql \
  mysql:8.0
```

<!-- </details> -->

<!-- <details> -->
<!-- <summary> -->
PostgreSQL
<!-- </summary> -->

```bash
docker run -d \
  --name ai-postgres \
  -e POSTGRES_DB=ai_service \
  -e POSTGRES_USER=ai_service \
  -e POSTGRES_PASSWORD=<strong-password> \
  -p 5432:5432 \
  -v ai_pg_data:/var/lib/postgresql/data \
  postgres:16
```
Then create the schema:

```bash
docker exec -i ai-postgres psql -U ai_service -d ai_service \
  -c 'CREATE SCHEMA "cs-on-premises";'
```

<!-- </details> -->

> **Tip:** For Podman, substitute `podman` for `docker` throughout. On rootless Podman, use named volumes rather than bind-mounted host paths to avoid SELinux and UID mapping issues.

### Native install (macOS)

<!-- <details> -->
<!-- <summary> -->
MySQL and PostgreSQL on macOS
<!-- </summary> -->
**MySQL:**

```bash
brew install mysql
brew services start mysql
mysql_secure_installation
mysql -u root -p <<'SQL'
CREATE DATABASE ai_service;
CREATE USER 'ai_service'@'%' IDENTIFIED BY '<strong-password>';
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, DROP,
      INDEX, TRIGGER, LOCK TABLES, REFERENCES
  ON ai_service.* TO 'ai_service'@'%';
FLUSH PRIVILEGES;
SQL
```
**PostgreSQL:**

```bash
brew install postgresql@16
brew services start postgresql@16
createuser -P ai_service
createdb -O ai_service ai_service
psql -d ai_service -c 'CREATE SCHEMA "cs-on-premises" AUTHORIZATION ai_service;'
```
Verify all services are running:

```bash
brew services list
```

<!-- </details> -->

### Native install (Linux)

<!-- <details> -->
<!-- <summary> -->
MySQL and PostgreSQL on Debian/Ubuntu
<!-- </summary> -->
**MySQL:**

```bash
sudo apt update
sudo apt install -y mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation
sudo mysql <<'SQL'
CREATE DATABASE ai_service;
CREATE USER 'ai_service'@'%' IDENTIFIED BY '<strong-password>';
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, DROP,
      INDEX, TRIGGER, LOCK TABLES, REFERENCES
  ON ai_service.* TO 'ai_service'@'%';
FLUSH PRIVILEGES;
SQL
```
To allow remote connections, edit `/etc/mysql/mysql.conf.d/mysqld.cnf`, set `bind-address = 0.0.0.0`, and restart with `sudo systemctl restart mysql`.

**PostgreSQL:**

```bash
sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo -u postgres psql <<'SQL'
CREATE USER ai_service WITH PASSWORD '<strong-password>';
CREATE DATABASE ai_service OWNER ai_service;
SQL
sudo -u postgres psql -d ai_service \
  -c 'CREATE SCHEMA "cs-on-premises" AUTHORIZATION ai_service;'
```
To allow remote connections, edit `/etc/postgresql/16/main/postgresql.conf` (`listen_addresses = '*'`) and add to `/etc/postgresql/16/main/pg_hba.conf`:

```
host    ai_service    ai_service    0.0.0.0/0    scram-sha-256
```
Restart with `sudo systemctl restart postgresql`.


<!-- </details> -->

### Managed cloud

The AI service handles schema migration automatically. The pre-steps are:

1. Provision the database instance (RDS, Cloud SQL, or Azure Database).
2. Create the database (`ai_service`).
3. Create a dedicated user with the privileges documented in [Database user privileges](#database-user-privileges).
4. **PostgreSQL only:** create the `cs-on-premises` schema or set `DATABASE_SCHEMA=public`.
5. Open the security group or firewall for the AI service on port `3306` (MySQL) or `5432` (PostgreSQL).

| Provider | MySQL | PostgreSQL | Redis |
| --- | --- | --- | --- |
| AWS | RDS for MySQL | RDS for PostgreSQL | ElastiCache for Redis |
| GCP | Cloud SQL (MySQL) | Cloud SQL (PostgreSQL) | Memorystore for Redis |
| Azure | Azure Database for MySQL | Azure Database for PostgreSQL | Azure Cache for Redis |

For production, enable Multi-AZ (or the equivalent zonal redundancy) and automated backups.

### Managed database TLS

Managed PostgreSQL services default to requiring TLS connections:

- **AWS RDS:** `rds.force_ssl=1` (default for new instances)
- **Azure Database for PostgreSQL Flexible Server:** `require_secure_transport=ON` (default)
- **Google Cloud SQL:** TLS required unless explicitly disabled

Without TLS configuration, the AI service connection fails with a generic error (commonly reported as a "permissions" issue). Configure `DATABASE_SSL_CA` with the provider’s CA certificate bundle:

```bash
# AWS RDS
DATABASE_SSL_CA=/certs/rds-combined-ca-bundle.pem

# Azure Database for PostgreSQL
DATABASE_SSL_CA=/certs/DigiCertGlobalRootG2.crt.pem

# Google Cloud SQL (when not using Cloud SQL Auth Proxy)
DATABASE_SSL_CA=/certs/server-ca.pem
```
Mount the certificate file into the container and reference the path in `DATABASE_SSL_CA`. Download the CA bundle from the cloud provider documentation.

> **Note:** Most managed database services require only the CA certificate (`DATABASE_SSL_CA`) for server verification. `DATABASE_SSL_CERT` and `DATABASE_SSL_KEY` are additionally required only for mutual TLS (mTLS).

> **Warning:** If the managed database requires TLS and `DATABASE_SSL_CA` is not set, the AI service logs a connection error that does not mention TLS. Verify the database’s TLS setting first when troubleshooting connection failures on managed services.

### Connecting to a host-local database from Docker

When the AI service runs in Docker but the database or Redis runs natively on the host, the container must resolve the host’s IP address.

**Docker Desktop (macOS, Windows)** and **Podman 4+** inject `host.docker.internal` automatically.

**Native Linux Docker** does not. Add `host-gateway` explicitly:

```yaml
services:
  ai-service:
    image: registry.containers.tiny.cloud/ai-service-tiny:latest
    extra_hosts:
      - "host.docker.internal:host-gateway"
    environment:
      DATABASE_HOST: host.docker.internal
      REDIS_HOST: host.docker.internal
```
Or with `docker run`:

```bash
docker run --add-host=host.docker.internal:host-gateway ...
```

## Redis

Every AI service instance must reach Redis. Redis holds session coordination, Server-Sent Events (SSE) delivery, and rate-limiting state. A temporary Redis outage degrades streaming but does not destroy persistent data.

### Setup

When using Docker Compose files, Redis is typically included alongside the database (see the compose examples above). For standalone setup:

```bash
docker run -d --name ai-redis -p 6379:6379 -v ai_redis_data:/data redis:7
```

<!-- <details> -->
<!-- <summary> -->
macOS / Linux native install
<!-- </summary> -->
**macOS:**

```bash
brew install redis
brew services start redis
```
**Linux (Debian/Ubuntu):**

```bash
sudo apt install -y redis-server
sudo systemctl enable --now redis-server
```

<!-- </details> -->

### Connection variables

| Variable | Required | Description |
| --- | --- | --- |
| `REDIS_HOST` | Yes | Hostname |
| `REDIS_PORT` | No | Default `6379` |
| `REDIS_PASSWORD` | No | Password |
| `REDIS_USER` | No | Username (Redis 6+ ACL) |
| `REDIS_DB` | No | Database number (default `1`) |
| `REDIS_IP_FAMILY` | No | Set to `6` for IPv6 |

### TLS

| Variable | Description |
| --- | --- |
| `REDIS_TLS_ENABLE` | `true` to enable TLS |
| `REDIS_TLS_CA` | Path to CA certificate |
| `REDIS_TLS_KEY` | Path to client key |
| `REDIS_TLS_CERT` | Path to client certificate |

### Cluster

| Variable | Description |
| --- | --- |
| `REDIS_CLUSTER_NODES` | Comma-separated `host:port[:password]` list |
| `REDIS_IP_FAMILY` | Set to `6` for IPv6 domains |

<!-- <details> -->
<!-- <summary> -->
Cluster examples
<!-- </summary> -->

```bash
# Standard cluster
REDIS_CLUSTER_NODES="redis1.example.com:7000,redis2.example.com:7001,redis3.example.com:7002"

# Cluster with per-node passwords
REDIS_CLUSTER_NODES="redis1.example.com:7000:pass1,redis2.example.com:7001:pass2"

# IPv6 cluster
REDIS_IP_FAMILY=6
REDIS_CLUSTER_NODES="[::1]:7000,[::1]:7001,[::1]:7002"
```

<!-- </details> -->

> **Important:** In production, always set `REDIS_PASSWORD` or use a managed Redis instance with authentication enabled.

## File storage

Separate from the SQL database, the AI service persists user file uploads (attachments, images). The storage back end is selected by the `STORAGE_DRIVER` environment variable.

| Driver | When to use | Notes |
| --- | --- | --- |
| `database` | Demos and smallest deployments | Stores files as SQL blobs. Hard cap around 4 GB total. No extra configuration required. |
| `filesystem` | Single-instance with a persistent volume | Requires a writable mounted volume. See [Filesystem](#filesystem-storage). |
| `s3` | Production on AWS, or S3-compatible (MinIO, Wasabi) | Use a same-region bucket. |
| `azure` | Production on Azure | Azure Blob Storage. |

### S3

```bash
STORAGE_DRIVER=s3
STORAGE_REGION=us-east-1
STORAGE_ACCESS_KEY_ID=ACCESS_KEY
STORAGE_SECRET_ACCESS_KEY=SECRET_KEY
STORAGE_BUCKET=BUCKET_NAME
STORAGE_ENDPOINT=https://custom-s3-endpoint   # optional, for S3-compatible
```
The S3 access key requires read, write, delete, and list permissions on the target bucket. For production, enable bucket versioning and server-side encryption (SSE-S3 or SSE-KMS).

> **Note:** The correct variable names are `STORAGE_BUCKET` and `STORAGE_REGION`, not `STORAGE_S3_BUCKET` or `STORAGE_S3_REGION`.

### Azure Blob

```bash
STORAGE_DRIVER=azure
STORAGE_ACCOUNT_NAME=ACCOUNT_NAME
STORAGE_ACCOUNT_KEY=ACCOUNT_KEY
STORAGE_CONTAINER=CONTAINER_NAME
STORAGE_ENDPOINT=https://custom-endpoint       # optional
```

### Filesystem

```bash
STORAGE_DRIVER=filesystem
STORAGE_LOCATION=/tmp/ai-storage
```

> **Important:** The AI service container runs as a non-root user. Mount a writable volume and point `STORAGE_LOCATION` at the mount path (for example, `-v ./ai-storage:/tmp/ai-storage`).

### Database

```bash
STORAGE_DRIVER=database
```
Files are stored in the SQL database as blobs, capped at roughly 4 GB total. This is the simplest option for initial evaluation.

## Connecting the AI service

After provisioning the database and Redis, pass the connection details to the AI service container through environment variables.

### MySQL connection

```bash
-e DATABASE_DRIVER='mysql' \
-e DATABASE_HOST='mysql' \
-e DATABASE_PORT='3306' \
-e DATABASE_USER='ai_service' \
-e DATABASE_PASSWORD='<strong-password>' \
-e DATABASE_DATABASE='ai_service' \
-e REDIS_HOST='redis' \
-e REDIS_PORT='6379'
```

### PostgreSQL connection

```bash
-e DATABASE_DRIVER='postgres' \
-e DATABASE_HOST='postgres' \
-e DATABASE_PORT='5432' \
-e DATABASE_USER='ai_service' \
-e DATABASE_PASSWORD='<strong-password>' \
-e DATABASE_DATABASE='ai_service' \
-e DATABASE_SCHEMA='cs-on-premises' \
-e REDIS_HOST='redis' \
-e REDIS_PORT='6379'
```
Set `DATABASE_SCHEMA` to `public` if the `cs-on-premises` schema was not created. See [PostgreSQL schema prerequisite](#postgresql-schema-prerequisite).

### Managed cloud with TLS

When connecting to managed database services (Amazon RDS, Azure Database, Cloud SQL), add the TLS certificate path:

```bash
-e DATABASE_SSL_CA='/certs/rds-combined-ca-bundle.pem'
```
Mount the certificate into the container with `-v /local/certs:/certs:ro` in `docker run` or a volume mount in the Kubernetes pod spec.

For Redis with authentication (ElastiCache, Azure Cache, Memorystore):

```bash
-e REDIS_PASSWORD='REDIS_AUTH_TOKEN' \
-e REDIS_TLS='true'
```
For Redis Cluster mode, use `REDIS_CLUSTER_NODES` instead of `REDIS_HOST`:

```bash
-e REDIS_CLUSTER_NODES='node1.cache.amazonaws.com:6379,node2.cache.amazonaws.com:6379'
```
When `REDIS_CLUSTER_NODES` is set, `REDIS_HOST` is ignored.

For a complete `docker run` command including all env vars, see the [Getting started](../tinymceai-on-premises-getting-started/) launch script or the [Production deployment](../tinymceai-on-premises-production/) manifests.

## Verification

### MySQL

```bash
mysql --host=DB_HOST --user=ai_service --password=<strong-password> \
  ai_service --port=3306 -e "SELECT 1"
```
Expected: a table with `1` in a single column.

### PostgreSQL

```bash
psql -h DB_HOST -U ai_service -d ai_service -c "SELECT 1"
```
Expected: `?column?` returning `1`.

### Redis

```bash
redis-cli -h REDIS_HOST ping
```
Expected: `PONG`.

### AI service migration

After starting the AI service, confirm it has connected and run migrations:

```bash
docker logs ai-service 2>&1 | grep -i 'migrat\|schema\|database'
```
Expected output (paraphrased):

```
Connecting to database (driver=postgres host=...)
Running migrations on schema "cs-on-premises"
Migrations complete: 32 tables ready
Server is listening on port 8000.
```
If `schema "cs-on-premises" does not exist` appears, return to [PostgreSQL schema prerequisite](#postgresql-schema-prerequisite). If `unknown variable 'default-authentication-plugin'` appears, return to [MySQL](#mysql-version-pinning).

To list the tables created by migration:

PostgreSQL
```sql
SELECT table_name FROM information_schema.tables
 WHERE table_schema = 'cs-on-premises'
 ORDER BY table_name;
```
MySQL
```sql
SHOW TABLES IN ai_service;
```
Tables prefixed `ai_assistant_`, `environments` *, `security`*, `insights` *, `blob_storage`*, and `cs_migrations` should appear.