tessl/maven-org-testcontainers--mysql

Testcontainers module for creating throwaway MySQL database containers for integration testing

Overview

Eval results

Files

Database Initialization

Name: tessl/maven-org-testcontainers--mysql
Author: tessl

This document covers executing SQL initialization scripts to set up database schema and test data when the MySQL container starts.

Quick Reference

Initialization Methods (Fluent Interface, Return SELF):

withInitScript(String initScriptPath) - Execute single SQL script from classpath
withInitScripts(String... initScriptPaths) - Execute multiple scripts (varargs)
withInitScripts(Iterable<String> initScriptPaths) - Execute multiple scripts (iterable)

Execution Details:

Scripts execute after container starts but before it's marked ready
Scripts execute in the order specified
Scripts must be on the classpath (typically src/test/resources/)
Script failures cause container startup to fail with ContainerLaunchException or IllegalStateException
Paths use forward slashes regardless of OS
Paths are case-sensitive on case-sensitive filesystems

Constraints:

All initialization methods must be called before start()
Script paths must exist on classpath
Scripts must contain valid MySQL SQL syntax

Capabilities

Single Init Script

Execute a single SQL script from the classpath when the container starts.

/**
 * Sets a single SQL script to be executed when the container starts.
 * The script is loaded from the classpath and executed against the database
 * before the container is considered ready.
 *
 * If this method is called multiple times, only the last script is used.
 * To execute multiple scripts, use withInitScripts() instead.
 *
 * @param initScriptPath path to the SQL script file on the classpath (e.g., "schema.sql" or "db/schema.sql")
 * @return self for method chaining (SELF extends MySQLContainer<SELF>)
 * @throws IllegalArgumentException if initScriptPath is null or empty
 * @throws ContainerLaunchException if script file is not found on classpath or contains invalid SQL
 */
SELF withInitScript(String initScriptPath);

Usage Example:

import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.utility.DockerImageName;

// Assumes schema.sql is in src/test/resources/
MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
    .withInitScript("schema.sql");

mysql.start();
// Container is ready and schema.sql has been executed

Example schema.sql:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com');

Multiple Init Scripts (Varargs)

Execute multiple SQL scripts in a specified order using varargs.

/**
 * Sets multiple SQL scripts to be executed in order when the container starts.
 * Scripts are loaded from the classpath and executed sequentially.
 * Each script must complete successfully before the next one begins.
 *
 * @param initScriptPaths paths to SQL script files on the classpath (in execution order)
 * @return self for method chaining (SELF extends MySQLContainer<SELF>)
 * @throws IllegalArgumentException if initScriptPaths is null or contains null/empty elements
 * @throws ContainerLaunchException if any script file is not found on classpath or contains invalid SQL
 */
SELF withInitScripts(String... initScriptPaths);

Usage Example:

import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.utility.DockerImageName;

MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
    .withInitScripts(
        "schema.sql",           // Create tables
        "seed-data.sql",        // Insert test data
        "stored-procedures.sql" // Create stored procedures
    );

mysql.start();
// All three scripts have been executed in order

Multiple Init Scripts (Iterable)

Execute multiple SQL scripts from an iterable collection.

/**
 * Sets multiple SQL scripts to be executed in order when the container starts.
 * Scripts are loaded from the classpath and executed in iteration order.
 * Each script must complete successfully before the next one begins.
 *
 * @param initScriptPaths iterable collection of paths to SQL script files on the classpath
 * @return self for method chaining (SELF extends MySQLContainer<SELF>)
 * @throws IllegalArgumentException if initScriptPaths is null or contains null/empty elements
 * @throws ContainerLaunchException if any script file is not found on classpath or contains invalid SQL
 */
SELF withInitScripts(Iterable<String> initScriptPaths);

Usage Example:

import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.utility.DockerImageName;
import java.util.Arrays;
import java.util.List;

List<String> scripts = Arrays.asList(
    "01-schema.sql",
    "02-seed-users.sql",
    "03-seed-products.sql"
);

MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
    .withInitScripts(scripts);

mysql.start();

Complete Initialization Example

import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.utility.DockerImageName;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class InitializationTest {

    public void testWithInitializedDatabase() throws Exception {
        try (MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
                .withDatabaseName("app_test")
                .withInitScripts(
                    "db/schema.sql",
                    "db/test-data.sql"
                )) {

            mysql.start();

            // Database is ready with schema and test data
            try (Connection conn = mysql.createConnection("")) {
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
                rs.next();
                int userCount = rs.getInt(1);
                // userCount reflects data from test-data.sql
                System.out.println("Users in database: " + userCount);
            }
        }
    }
}

Script Execution Details

Execution Order:

Scripts are executed in the order specified
Each script is executed completely before the next one begins
Scripts are executed after the container starts but before it's marked ready
Container startup blocks until all scripts complete successfully
If any script fails, container startup fails with an exception

Script Location:

Scripts must be on the classpath (typically in src/test/resources/)
Paths are relative to the classpath root
Use forward slashes for directory separators regardless of OS
Paths are case-sensitive on case-sensitive filesystems
Paths should not start with leading slash

Script Content:

Scripts can contain any valid MySQL SQL statements
Multiple statements are supported (separated by semicolons)
Comments are supported (-- or /* */)
DDL and DML statements are both supported
Stored procedures, functions, and triggers are supported
Transactions are supported

Error Handling:

If any script fails, the container startup will fail
The exception will indicate which script failed and why
Failed containers will be stopped and removed automatically
Script errors throw ContainerLaunchException or IllegalStateException
Common errors: syntax errors, constraint violations, missing dependencies

Performance Considerations:

Large scripts slow down container startup
Consider breaking large scripts into smaller ones
Use transactions for atomic operations
Avoid unnecessary data in initialization scripts
Script execution time is included in startup timeout

Advanced Initialization Patterns

Conditional Initialization

import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.utility.DockerImageName;
import java.util.ArrayList;
import java.util.List;

boolean includeTestData = true;

List<String> scripts = new ArrayList<>();
scripts.add("schema.sql"); // Always include schema

if (includeTestData) {
    scripts.add("test-data.sql"); // Conditionally include test data
}

MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
    .withInitScripts(scripts);

Environment-Specific Initialization

import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.utility.DockerImageName;

String environment = System.getProperty("test.env", "default");

MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
    .withInitScripts(
        "schema.sql",
        "data-" + environment + ".sql" // e.g., data-default.sql or data-integration.sql
    );

Large Dataset Initialization

For large datasets, consider:

Breaking into multiple smaller script files
Using compressed SQL dumps (though these may need extraction first)
Generating data programmatically after container startup instead of using init scripts
Using batch inserts for better performance

Example:

// Instead of large init script, generate data after startup
MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
    .withInitScript("schema.sql"); // Only schema

mysql.start();

// Generate test data programmatically
try (Connection conn = mysql.createConnection("")) {
    PreparedStatement stmt = conn.prepareStatement(
        "INSERT INTO users (username, email) VALUES (?, ?)");
    for (int i = 0; i < 10000; i++) {
        stmt.setString(1, "user" + i);
        stmt.setString(2, "user" + i + "@example.com");
        stmt.addBatch();
        if (i % 1000 == 0) {
            stmt.executeBatch();
        }
    }
    stmt.executeBatch();
}

Combining with Configuration Override

Initialization scripts work alongside configuration overrides:

import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.utility.DockerImageName;

MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
    .withDatabaseName("myapp")
    .withConfigurationOverride("mysql-config") // Custom my.cnf settings
    .withInitScripts(
        "schema.sql",
        "seed-data.sql"
    );

mysql.start();
// Container has custom MySQL configuration AND initialized database

Best Practices

Keep Scripts Idempotent: Use IF NOT EXISTS clauses where possible to make scripts rerunnable
Organize by Purpose: Separate schema definition from data seeding
Version Scripts: Number scripts to indicate execution order (e.g., 01-schema.sql, 02-data.sql)
Use Transactions: Wrap statements in transactions for atomicity
Keep Scripts Fast: Large initialization scripts slow down test execution
Test Scripts Independently: Ensure scripts work correctly before using them in tests
Use Relative Paths: Keep paths relative to classpath root for portability
Document Scripts: Add comments explaining script purpose and dependencies
Handle Dependencies: Ensure scripts that depend on others are executed in correct order
Validate Scripts: Test SQL syntax before using in container initialization

Example Idempotent Script

-- schema-idempotent.sql

-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS myapp;
USE myapp;

-- Create tables if they don't exist
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(36) PRIMARY KEY,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert test data only if it doesn't exist
INSERT INTO users (username, email)
SELECT 'testuser', 'test@example.com'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'testuser');

Transaction Example

-- transactional-init.sql

START TRANSACTION;

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0
);

INSERT INTO accounts (name, balance) VALUES
    ('Alice', 1000.00),
    ('Bob', 500.00);

COMMIT;

Error Handling Example

import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.utility.DockerImageName;
import org.testcontainers.containers.ContainerLaunchException;

try {
    MySQLContainer<?> mysql = new MySQLContainer<>(DockerImageName.parse("mysql:8.0"))
        .withInitScript("invalid-script.sql");
    mysql.start();
} catch (ContainerLaunchException e) {
    // Script execution failed
    System.err.println("Init script failed: " + e.getMessage());
    // Check script syntax and content
}

Troubleshooting

Script Not Found:

Verify script is in src/test/resources/ directory
Check path is relative to classpath root
Ensure path uses forward slashes
Check file is included in test classpath
Verify path is case-correct on case-sensitive filesystems

Script Execution Fails:

Check MySQL syntax is correct
Verify table names and column names are valid
Ensure foreign key constraints are satisfied
Check for reserved keyword conflicts
Verify script encoding (UTF-8 recommended)
Check for missing dependencies (tables, databases)

Performance Issues:

Break large scripts into smaller ones
Use batch inserts instead of individual INSERT statements
Consider generating data programmatically
Use transactions to reduce overhead
Avoid unnecessary data in initialization scripts

Timeout Issues:

Increase startup timeout if scripts are large: withStartupTimeoutSeconds(300)
Optimize script execution time
Consider breaking scripts into smaller chunks

Install with Tessl CLI

npx tessl i tessl/maven-org-testcontainers--mysql

docs

container-configuration.md

container-customization.md

container-lifecycle.md

database-initialization.md

index.md

jdbc-connection.md

r2dbc-support.md

url-parameters-timeouts.md

tile.json

tessl/maven-org-testcontainers--mysql