---
url: https://ratchet.run/deployment/database-setup.md
description: >-
  Setting up MySQL, PostgreSQL, Oracle, SQL Server, or MongoDB for Ratchet:
  schema application, DataSource configuration, and connection pooling.
---

# Database Setup

Ratchet requires a database to persist jobs, execution history, and scheduling metadata. This guide covers setup for all supported stores.

SQL stores ship DDL as plain SQL files bundled inside each SQL store module JAR. There is no Flyway or Liquibase dependency: apply the schema using whatever mechanism your team prefers, **or** use Ratchet's built-in startup migrator (see [Auto-migration](#auto-migration) below). Spring Boot enables SQL migration by default; Jakarta EE and Quarkus leave it off by default, and require a CDI-discoverable `DataSource` when it is enabled. MongoDB has different startup modes: Spring Boot defaults to create-and-migrate and uses `ratchet.schema.auto-migrate=false` for validation only, while direct CDI/Jakarta EE and Quarkus Mongo store construction always creates collections and indexes. Named Mongo indexes are referenced by claim queries, so initialization is correctness-critical.

## PostgreSQL

### Create the Database

```bash
# Connect as superuser
psql -U postgres

# Create the database and user
CREATE USER ratchet WITH PASSWORD 'your-secure-password';
CREATE DATABASE ratchet OWNER ratchet ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE ratchet TO ratchet;

# Connect to the new database
\c ratchet

# Grant schema privileges
GRANT ALL ON SCHEMA public TO ratchet;
```

### Apply the Schema

The DDL file is at `stores/ratchet-store-postgresql/src/main/resources/ddl/postgresql-schema.sql` in the source, or `ddl/postgresql-schema.sql` inside the JAR.

```bash
# From the source tree
psql -U ratchet -d ratchet -f stores/ratchet-store-postgresql/src/main/resources/ddl/postgresql-schema.sql

# Or extract from the JAR
jar xf ratchet-store-postgresql-0.5.0.jar ddl/postgresql-schema.sql
psql -U ratchet -d ratchet -f ddl/postgresql-schema.sql
```

Using a migration framework:

```bash
# Copy the DDL into your migration directory
cp ddl/postgresql-schema.sql src/main/resources/db/migration/V1__ratchet_schema.sql

# Run with Flyway
flyway migrate

# Or with Liquibase
liquibase update
```

### Verify Installation

```sql
\dt scheduler_*
\dt ratchet_schema_version
```

You should see these scheduler tables plus `ratchet_schema_version`:

| Table | Purpose |
|-------|---------|
| `scheduler_job` | Cold job metadata, payload, and terminal state |
| `scheduler_job_queue` | Hot executable queue state |
| `scheduler_business_key_reservation` | Active business-key reservation guard |
| `scheduler_job_tag` | Tags for categorization |
| `scheduler_job_execution` | Per-attempt execution history |
| `scheduler_job_log` | Optional per-job log entries if your application persists `JobLogLine` events |
| `scheduler_batch` | Batch progress tracking |
| `scheduler_batch_metrics` | Batch performance metrics |
| `scheduler_job_archive` | Archived completed/failed jobs |
| `scheduler_recurring_job` | Recurring job masters |
| `scheduler_recurring_job_archive` | Archived recurring job masters |
| `scheduler_node` | Cluster node heartbeats |
| `scheduler_lock` | Distributed locks |
| `scheduler_resource_limit` | Resource concurrency config |
| `scheduler_resource_permit` | Active resource permits |
| `scheduler_workflow_condition` | Workflow branching conditions |
| `ratchet_schema_version` | Applied schema migration/checksum tracking |

### DataSource Configuration

#### WildFly

```bash
# WildFly CLI
/subsystem=datasources/data-source=RatchetDS:add( \
    jndi-name=java:/RatchetDS, \
    driver-name=postgresql, \
    connection-url=jdbc:postgresql://localhost:5432/ratchet, \
    user-name=ratchet, \
    password=your-secure-password, \
    min-pool-size=5, \
    max-pool-size=20, \
    valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker)
```

#### Open Liberty

```xml
<!-- server.xml -->
<dataSource id="RatchetDS" jndiName="java:/RatchetDS">
  <jdbcDriver libraryRef="postgresLib"/>
  <properties.postgresql
      serverName="localhost"
      portNumber="5432"
      databaseName="ratchet"
      user="ratchet"
      password="your-secure-password"/>
  <connectionManager minPoolSize="5" maxPoolSize="20"/>
</dataSource>

<library id="postgresLib">
  <fileset dir="${shared.resource.dir}/jdbc" includes="postgresql-*.jar"/>
</library>
```

#### Payara / GlassFish

```bash
# asadmin
create-jdbc-connection-pool \
    --datasourceclassname=org.postgresql.ds.PGSimpleDataSource \
    --restype=javax.sql.DataSource \
    --property=serverName=localhost:portNumber=5432:databaseName=ratchet:user=ratchet:password=your-secure-password \
    RatchetPool

create-jdbc-resource --connectionpoolid=RatchetPool java:/RatchetDS
```

### PostgreSQL-Specific Notes

* Ratchet uses `SKIP LOCKED` for lock-free job claiming across multiple nodes
* Generated columns extract `target_class` and `method_name` from the JSON payload
* Business key uniqueness for active jobs is enforced by the `scheduler_business_key_reservation` table, not by an index on `scheduler_job`
* The payload column uses `JSONB`, so you can query parameters directly: `payload ->> 'target'`

## MySQL

### Create the Database

```bash
mysql -u root -p

CREATE DATABASE ratchet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'ratchet'@'%' IDENTIFIED BY 'your-secure-password';
GRANT ALL PRIVILEGES ON ratchet.* TO 'ratchet'@'%';
FLUSH PRIVILEGES;
```

### Apply the Schema

```bash
# From the source tree
mysql -u ratchet -p ratchet < stores/ratchet-store-mysql/src/main/resources/ddl/mysql-schema.sql

# Or extract from the JAR
jar xf ratchet-store-mysql-0.5.0.jar ddl/mysql-schema.sql
mysql -u ratchet -p ratchet < ddl/mysql-schema.sql
```

### Verify Installation

```sql
SHOW TABLES LIKE 'scheduler_%';
SHOW TABLES LIKE 'ratchet_schema_version';
```

You should see the same core tables as PostgreSQL, with MySQL-specific column types (ENUM, JSON, GENERATED ALWAYS columns).

### DataSource Configuration

#### WildFly

```bash
/subsystem=datasources/data-source=RatchetDS:add( \
    jndi-name=java:/RatchetDS, \
    driver-name=mysql, \
    connection-url=jdbc:mysql://localhost:3306/ratchet, \
    user-name=ratchet, \
    password=your-secure-password, \
    min-pool-size=5, \
    max-pool-size=20, \
    valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker)
```

#### Open Liberty

```xml
<!-- server.xml -->
<dataSource id="RatchetDS" jndiName="java:/RatchetDS">
  <jdbcDriver libraryRef="mysqlLib"/>
  <properties.mysql
      serverName="localhost"
      portNumber="3306"
      databaseName="ratchet"
      user="ratchet"
      password="your-secure-password"/>
  <connectionManager minPoolSize="5" maxPoolSize="20"/>
</dataSource>
```

:::info MySQL isolation level
The current MySQL store supports default `REPEATABLE READ` and `READ COMMITTED`.
No datasource isolation override is required. Existing `READ COMMITTED` configurations
remain supported. See [MySQL isolation](./mysql.md#transaction-isolation) for verification
and guidance when upgrading older builds.
:::

### MySQL-Specific Notes

* Uses `ENUM` types for status, job type, and backoff policy columns
* Uses `JSON` column type for payload, params, and result data
* `GENERATED ALWAYS AS ... STORED` columns extract `target_class` and `method_name` from payload JSON
* Business key uniqueness for active jobs is enforced by the `scheduler_business_key_reservation` table, not by a column on `scheduler_job`
* All tables use `InnoDB` engine with `utf8mb4_unicode_ci` collation

## Oracle

### Create the Database

Create a user (schema) and grant it the usual application privileges:

```sql
CREATE USER ratchet IDENTIFIED BY "your-secure-password"
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO ratchet;
```

### Apply the Schema

```bash
# From the source tree
sqlplus ratchet/your-secure-password@//localhost:1521/FREEPDB1 \
  @stores/ratchet-store-oracle/src/main/resources/ddl/oracle-schema.sql

# Or extract from the JAR
jar xf ratchet-store-oracle-0.5.0.jar ddl/oracle-schema.sql
sqlplus ratchet/your-secure-password@//localhost:1521/FREEPDB1 @ddl/oracle-schema.sql
```

### Verify Installation

```sql
SELECT table_name FROM user_tables
WHERE table_name LIKE 'SCHEDULER\_%' ESCAPE '\' ORDER BY table_name;
```

You should see the same core tables as the other SQL stores, with Oracle-specific column types (`RAW(16)` ids, `CLOB` JSON, native `BOOLEAN`, virtual `JSON_VALUE` columns).

### DataSource Configuration

Oracle's JDBC driver (`ojdbc11`) is published under the Free Use Terms and Conditions, not an OSI-approved license, so Ratchet does not bundle it — supply your own on the application or server module path.

```bash
# WildFly: register the driver module, then add the data source
/subsystem=datasources/data-source=RatchetDS:add( \
    jndi-name=java:/RatchetDS, \
    driver-name=oracle, \
    connection-url=jdbc:oracle:thin:@//localhost:1521/FREEPDB1, \
    user-name=ratchet, \
    password=your-secure-password, \
    min-pool-size=5, \
    max-pool-size=20, \
    transaction-isolation=TRANSACTION_READ_COMMITTED)
```

See [Oracle Deployment](/deployment/oracle) for the persistence-unit mapping (including the `orm-oracle.xml` mapping file for EclipseLink) and the UTC time-zone requirement.

### Oracle-Specific Notes

* Requires Oracle Database 23ai for the native `BOOLEAN` type and `CREATE TABLE IF NOT EXISTS`
* Stores UUIDs as `RAW(16)`; JSON payloads as `CLOB` (native `JSON` reorders keys and breaks payload encryption framing)
* `target_class`/`method_name`/`trace_id_extracted` are virtual columns computed with `JSON_VALUE`
* The claim path is two-phase: an unlocked candidate select then a `FOR UPDATE SKIP LOCKED` lock over those ids, since Oracle rejects `FETCH FIRST` with `FOR UPDATE SKIP LOCKED` (ORA-02014)
* Timestamp columns hold UTC wall-clock; run the JVM in UTC

## MongoDB

### Create the Database

```bash
mongosh

use ratchet

db.createUser({
  user: "ratchet",
  pwd: "your-secure-password",
  roles: [{ role: "readWrite", db: "ratchet" }]
});
```

### Initialize Collections and Indexes

MongoDB does not require a DDL file. Direct CDI/Jakarta EE and Quarkus Mongo store construction creates collections and indexes automatically on startup. Spring Boot and Micronaut also create them by default; set `ratchet.schema.auto-migrate=false` there to validate a pre-provisioned schema without writing. You can pre-create the same collections and indexes for faster initial startup:

```javascript
// mongosh
use ratchet;

db.createCollection("scheduler_job");
db.createCollection("scheduler_business_key_reservation");
db.createCollection("scheduler_batch");
db.createCollection("scheduler_batch_metrics");
db.createCollection("scheduler_job_execution");
db.createCollection("scheduler_job_log");
db.createCollection("scheduler_job_archive");
db.createCollection("scheduler_recurring_job");
db.createCollection("scheduler_recurring_job_archive");
db.createCollection("scheduler_node");
db.createCollection("scheduler_lock");
db.createCollection("scheduler_workflow_condition");
db.createCollection("scheduler_resource_permit");
db.createCollection("scheduler_resource_limit");

// Key indexes
db.scheduler_job.createIndex({ status: 1, priority: -1, scheduled_time: 1 }, { name: "idx_job_poll_composite" });
db.scheduler_job.createIndex({ status: 1, job_type: 1, priority: -1, scheduled_time: 1, _id: 1 }, { name: "idx_job_claim_exec" });
db.scheduler_job.createIndex({ idempotency_key: 1 }, { name: "idx_job_idempotency_key", unique: true });
db.scheduler_job.createIndex(
  { business_key: 1 },
  {
    name: "idx_job_active_business_key",
    unique: true,
    partialFilterExpression: {
      status: { $in: ["PENDING", "RUNNING", "PAUSED", "WAITING"] },
      business_key: { $type: "string" }
    }
  }
);
db.scheduler_job.createIndex({ tags: 1 }, { name: "idx_job_tags" });
db.scheduler_business_key_reservation.createIndex({ owner_job_id: 1 }, { name: "idx_bk_owner" });
db.scheduler_job_archive.createIndex({ original_job_id: 1 }, { name: "idx_archive_original_job_id" });
db.scheduler_job_execution.createIndex({ job_id: 1 }, { name: "idx_execution_job_id" });
db.scheduler_job_execution.createIndex({ status: 1, started_at: 1 }, { name: "idx_execution_status_started" });
db.scheduler_node.createIndex({ heartbeat_ts: 1 }, { name: "idx_node_heartbeat" });
db.scheduler_lock.createIndex({ expires_at: 1 }, { name: "idx_lock_ttl", expireAfterSeconds: 0 });
db.scheduler_job_log.createIndex({ job_id: 1, ts: 1 }, { name: "idx_log_job_ts" });
```

### Connection Configuration

Ratchet does not define its own MongoDB URI property. Configure the connection through your application runtime and expose a `MongoDatabase` bean. For example:

```java
@Produces
@ApplicationScoped
public MongoDatabase mongoDatabase() {
    return MongoClients.create("mongodb://ratchet:password@localhost:27017")
        .getDatabase("ratchet");
}
```

## Connection Pool Sizing

The connection pool should be sized based on the number of executor threads plus overhead for the polling engine and administrative queries.

### Formula

```
pool_size = worker_threads + polling_threads + admin_overhead
```

A reasonable starting point:

| Executor Threads | Recommended Pool Size |
|-----------------|----------------------|
| 20 (default) | 15-20 |
| 16 | 25-30 |
| 32 | 40-50 |
| 64+ | executor threads \* 1.5 |

### PostgreSQL Connection Limits

Check the server's max connections:

```sql
SHOW max_connections;  -- Default: 100
```

Increase if needed:

```sql
ALTER SYSTEM SET max_connections = 200;
-- Restart PostgreSQL
```

### MySQL Connection Limits

```sql
SHOW VARIABLES LIKE 'max_connections';  -- Default: 151
```

```ini
# my.cnf
[mysqld]
max_connections = 200
```

## Schema Upgrades

Ratchet uses `CREATE TABLE IF NOT EXISTS` in its DDL on both MySQL and PostgreSQL. On PostgreSQL it also uses `CREATE INDEX IF NOT EXISTS`; on MySQL indexes are declared inline within `CREATE TABLE IF NOT EXISTS` (MySQL has no partial indexes), so MySQL re-run safety relies on `CREATE TABLE IF NOT EXISTS` across the 18 tables. This means you can safely re-run the schema file against an existing database, and it will create any missing tables or indexes without modifying existing ones.

For schema changes between Ratchet versions:

1. Check the release notes for migration instructions
2. Back up your database
3. Apply any migration SQL provided in the release
4. Re-run the full schema DDL to create any new tables/indexes

Since Ratchet does not bundle a Flyway/Liquibase runtime dependency, you are free to manage schema changes using whatever tool your team already uses (Flyway, Liquibase, manual scripts, container init scripts, etc.).

## Auto-migration

For SQL stores, Ratchet ships a built-in startup migrator. It applies the `V###__description.sql` scripts each SQL store JAR carries under `ddl/migrations/<dialect>`, for example `ddl/migrations/postgresql`. **Spring Boot and Micronaut enable it by default** for their SQL integrations. Jakarta EE and Quarkus use the CDI lifecycle hook, where it is **OFF by default**; production deployments commonly keep that default and run migrations through their existing pipelines. Enable it there with a single configuration value for development, CI, or embedded deployments.

When using Spring Boot or Micronaut with `ratchet.schema.auto-migrate=false`, follow the migration-ledger guidance in the [Spring Boot](/deployment/spring-boot#transactions-and-schema) or [Micronaut](/deployment/micronaut#transactions-and-schema) guide after applying the supplied SQL scripts. The scripts create an empty `ratchet_schema_version` ledger, which must be dropped when an external tool owns migration history; retain any populated ledger.

### Enable

```bash
RATCHET_SCHEMA_AUTO_MIGRATE=true
```

Or via configuration:

```properties
ratchet.schema.auto-migrate=true
```

When enabled, `SchemaMigrationLifecycleHook` runs during scheduler startup (before the poller is initialized), acquires an advisory lock (`GET_LOCK` on MySQL, `pg_advisory_lock` on PostgreSQL), records each applied script in `ratchet_schema_version`, and verifies SHA-256 checksums on subsequent runs. Concurrent startups converge: exactly one node applies migrations while the others wait on the lock.

The migrator reads only the selected dialect's folder, so an application with more than one SQL store JAR on its classpath doesn't mix their scripts. To apply scripts from a different classpath folder, set `ratchet.schema.migration-prefix` (`RATCHET_SCHEMA_MIGRATION_PREFIX`) to that folder. Ratchet's native-image resource rules cover only `ddl/migrations` (all of `ddl/` on Spring Boot), so a native image needs any other folder registered as a resource by the application.

### DataSource binding

In the Jakarta EE or Quarkus SQL lifecycle-hook path, enabled auto-migration requires a CDI-discoverable `javax.sql.DataSource`. Most application servers expose this automatically; if yours doesn't, produce one explicitly:

```java
@ApplicationScoped
class RatchetDataSourceProducer {
  @Resource(lookup = "java:/RatchetDS")
  private DataSource dataSource;

  @Produces
  @ApplicationScoped
  DataSource dataSource() {
    return dataSource;
  }
}
```

If no `DataSource` bean is available when `auto-migrate=true`, deployment fails fast with a clear error.

### Supported dialects

| Database | `ratchet.schema.migration-dialect` value | Auto-detected? |
|----------|------------------------------------------|----------------|
| MySQL ≥ 8 | `mysql` | yes |
| MariaDB | `mysql` | yes |
| PostgreSQL | `postgresql` | yes |
| Oracle ≥ 23ai | `oracle` | yes |
| Anything else (incl. CockroachDB) | unsupported | no |

The dialect is auto-detected from `DatabaseMetaData.getDatabaseProductName()`. Look-alike products such as **CockroachDB** report a PostgreSQL wire protocol but lack `pg_advisory_lock`, so they are explicitly rejected even though the wire is compatible. Override the auto-detected value with `RATCHET_SCHEMA_MIGRATION_DIALECT=mysql` (or `postgresql`, `oracle`, `sqlserver`) only if you have verified your driver-product combination.

Each dialect serializes concurrent migrators differently: MySQL via `GET_LOCK`, PostgreSQL via `pg_advisory_lock`, Oracle via an `EXCLUSIVE` lock on a dedicated `ratchet_schema_lock` table held on a second connection (Oracle has no grant-free session-level advisory lock and its DDL auto-commits), and SQL Server via a session-scoped `sp_getapplock`. Oracle auto-migration therefore needs a connection pool maximum of at least 2.

### Enabling auto-migrate on a database that already has the schema

The bundled migrations are idempotent (`CREATE TABLE IF NOT EXISTS`, `CREATE INDEX IF NOT EXISTS`), so `auto-migrate=true` is safe to enable against a database whose `scheduler_*` tables already exist (for example, one provisioned directly from the consolidated `*-schema.sql`). On the first run the migrator re-applies each script as a no-op and records it in `ratchet_schema_version`; every subsequent run skips by checksum. If you would rather manage the schema entirely through external tooling, keep `auto-migrate=false`.

### `CREATE INDEX CONCURRENTLY`

PostgreSQL rejects `CREATE INDEX CONCURRENTLY` inside a transaction block, and the auto-migrator wraps each script in a JDBC transaction. The bundled migrations therefore use plain `CREATE INDEX`. Operators who need to avoid the brief table lock during a large-table reindex can apply the migration script manually with `CONCURRENTLY` before flipping `auto-migrate=true`; `ratchet_schema_version` records the version regardless of how the DDL ran.

### MongoDB

In Spring Boot and Micronaut, `ratchet.schema.auto-migrate=true` is the default and creates MongoDB collections and required indexes and runs data migrations. With `false`, startup validates existing collections, indexes, and migration markers without writing; provision the schema before starting the application. Direct CDI/Jakarta EE and Quarkus Mongo store startup always creates collections and indexes, regardless of this flag.

## Backup Strategy

### PostgreSQL

```bash
# Logical backup
pg_dump -Fc -v ratchet > ratchet-backup.dump

# Restore
pg_restore -d ratchet ratchet-backup.dump
```

### MySQL

```bash
# Logical backup
mysqldump -u ratchet -p ratchet > ratchet-backup.sql

# Restore
mysql -u ratchet -p ratchet < ratchet-backup.sql
```

### MongoDB

```bash
# Backup
mongodump --db ratchet --out /backup/

# Restore
mongorestore --db ratchet /backup/ratchet/
```

For all databases, schedule regular backups and test restoration periodically. In production, consider point-in-time recovery using WAL archiving (PostgreSQL), binary log (MySQL), archived redo logs (Oracle), transaction log backups (SQL Server), or oplog (MongoDB).

## See Also

* [PostgreSQL Deployment](/deployment/postgresql) -- PostgreSQL-specific tuning and monitoring
* [MySQL Deployment](/deployment/mysql) -- MySQL-specific tuning and monitoring
* [Configuration](/deployment/configuration) -- Full configuration reference
* [Deployment Overview](/deployment/overview) -- General deployment guidance