0
# Database Initialization
1

2
Database initialization functionality for running SQL scripts and setup procedures when the MySQL container starts, inherited from JdbcDatabaseContainer.
3

4
## Capabilities
5

6
### Single Script Initialization
7

8
Initialize the database with a single SQL script file.
9

10
```java { .api }
11
/**
12
 * Sets a script for initialization that runs after container startup
13
 * @param initScriptPath path to the SQL script file (relative to classpath)
14
 * @return Container instance for method chaining
15
 */
16
public SELF withInitScript(String initScriptPath);
17
```
18

19
**Usage Examples:**
20

21
```java
22
MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
23
    .withDatabaseName("testdb")
24
    .withInitScript("init/setup.sql");
25

26
mysql.start();
27
```
28

29
Example initialization script (`src/test/resources/init/setup.sql`):
30

31
```sql
32
-- Create tables
33
CREATE TABLE users (
34
    id INT AUTO_INCREMENT PRIMARY KEY,
35
    username VARCHAR(50) NOT NULL UNIQUE,
36
    email VARCHAR(100) NOT NULL,
37
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
38
);
39

40
CREATE TABLE posts (
41
    id INT AUTO_INCREMENT PRIMARY KEY,
42
    user_id INT NOT NULL,
43
    title VARCHAR(200) NOT NULL,
44
    content TEXT,
45
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
46
    FOREIGN KEY (user_id) REFERENCES users(id)
47
);
48

49
-- Insert sample data
50
INSERT INTO users (username, email) VALUES 
51
    ('alice', 'alice@example.com'),
52
    ('bob', 'bob@example.com');
53

54
INSERT INTO posts (user_id, title, content) VALUES
55
    (1, 'First Post', 'This is Alice''s first post'),
56
    (2, 'Hello World', 'Bob says hello to the world');
57
```
58

59
### Multiple Scripts Initialization (Array)
60

61
Initialize the database with multiple SQL scripts executed in order.
62

63
```java { .api }
64
/**
65
 * Sets an ordered array of scripts for initialization
66
 * Scripts are executed in the order provided
67
 * @param initScriptPaths paths to the script files
68
 * @return Container instance for method chaining
69
 */
70
public SELF withInitScripts(String... initScriptPaths);
71
```
72

73
**Usage Examples:**
74

75
```java
76
MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
77
    .withDatabaseName("testdb")
78
    .withInitScripts(
79
        "init/01-schema.sql",
80
        "init/02-data.sql", 
81
        "init/03-indexes.sql"
82
    );
83

84
mysql.start();
85
```
86

87
Example script files:
88

89
`src/test/resources/init/01-schema.sql`:
90
```sql
91
-- Create database schema
92
CREATE TABLE categories (
93
    id INT AUTO_INCREMENT PRIMARY KEY,
94
    name VARCHAR(100) NOT NULL UNIQUE
95
);
96

97
CREATE TABLE products (
98
    id INT AUTO_INCREMENT PRIMARY KEY,
99
    category_id INT NOT NULL,
100
    name VARCHAR(200) NOT NULL,
101
    price DECIMAL(10,2) NOT NULL,
102
    FOREIGN KEY (category_id) REFERENCES categories(id)
103
);
104
```
105

106
`src/test/resources/init/02-data.sql`:
107
```sql
108
-- Insert master data
109
INSERT INTO categories (name) VALUES 
110
    ('Electronics'),
111
    ('Books'),
112
    ('Clothing');
113

114
INSERT INTO products (category_id, name, price) VALUES
115
    (1, 'Laptop', 999.99),
116
    (1, 'Mouse', 29.99),
117
    (2, 'Java Programming Guide', 49.99);
118
```
119

120
`src/test/resources/init/03-indexes.sql`:
121
```sql
122
-- Create performance indexes
123
CREATE INDEX idx_products_category ON products(category_id);
124
CREATE INDEX idx_products_name ON products(name);
125
CREATE INDEX idx_products_price ON products(price);
126
```
127

128
### Multiple Scripts Initialization (Collection)
129

130
Initialize the database with scripts from a collection, maintaining execution order.
131

132
```java { .api }
133
/**
134
 * Sets an ordered collection of scripts for initialization
135
 * Scripts are executed in the order they appear in the collection
136
 * @param initScriptPaths paths to the script files as an Iterable
137
 * @return Container instance for method chaining
138
 */
139
public SELF withInitScripts(Iterable<String> initScriptPaths);
140
```
141

142
**Usage Examples:**
143

144
```java
145
import java.util.List;
146
import java.util.Arrays;
147

148
List<String> scriptPaths = Arrays.asList(
149
    "init/database-schema.sql",
150
    "init/test-data.sql",
151
    "init/stored-procedures.sql"
152
);
153

154
MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
155
    .withDatabaseName("testdb")
156
    .withInitScripts(scriptPaths);
157

158
mysql.start();
159
```
160

161
### Script Execution Timing
162

163
Initialization scripts are executed after the container starts and the database is ready, but before the container is considered fully started for test execution.
164

165
**Execution Order:**
166

167
1. Container starts with MySQL server
168
2. Database and user credentials are configured
169
3. Container passes health check (SELECT 1 query succeeds)
170
4. Initialization scripts are executed in specified order
171
5. Container is marked as ready for test execution
172

173
**Script Execution Environment:**
174

175
- Scripts run with the configured database user credentials
176
- The specified database is selected as the current database
177
- Full MySQL SQL syntax is supported
178
- Scripts can create tables, insert data, create stored procedures, etc.
179
- Any SQL errors will cause container startup to fail
180

181
### Advanced Initialization Examples
182

183
**Complex Database Setup:**
184

185
```java
186
MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
187
    .withDatabaseName("ecommerce_test")
188
    .withUsername("app_user")
189
    .withPassword("app_password")
190
    .withInitScripts(
191
        "schema/01-users-table.sql",
192
        "schema/02-products-table.sql", 
193
        "schema/03-orders-table.sql",
194
        "data/01-test-users.sql",
195
        "data/02-sample-products.sql",
196
        "procedures/01-user-procedures.sql",
197
        "procedures/02-order-procedures.sql"
198
    );
199

200
mysql.start();
201
```
202

203
**Conditional Initialization:**
204

205
```java
206
List<String> scripts = new ArrayList<>();
207
scripts.add("init/base-schema.sql");
208

209
if (includeTestData) {
210
    scripts.add("init/test-data.sql");
211
}
212

213
if (includeDemoData) {
214
    scripts.add("init/demo-data.sql");
215
}
216

217
MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
218
    .withDatabaseName("testdb")
219
    .withInitScripts(scripts);
220
```
221

222
### Script Content Guidelines
223

224
**Supported SQL Features:**
225

226
- DDL: CREATE TABLE, CREATE INDEX, ALTER TABLE, etc.
227
- DML: INSERT, UPDATE, DELETE statements
228
- Stored procedures and functions
229
- Views and triggers
230
- User-defined variables
231
- Comments (-- and /* */)
232

233
**Best Practices:**
234

235
```sql
236
-- Use IF NOT EXISTS for idempotent scripts
237
CREATE TABLE IF NOT EXISTS users (
238
    id INT AUTO_INCREMENT PRIMARY KEY,
239
    name VARCHAR(100) NOT NULL
240
);
241

242
-- Handle potential duplicate data
243
INSERT IGNORE INTO categories (name) VALUES ('Electronics');
244

245
-- Use transactions for data consistency
246
START TRANSACTION;
247
INSERT INTO users (name) VALUES ('Test User');
248
INSERT INTO user_profiles (user_id, email) VALUES (LAST_INSERT_ID(), 'test@example.com');
249
COMMIT;
250

251
-- Set character encoding for proper text handling
252
SET NAMES utf8mb4;
253
```
254

255
### Error Handling
256

257
If any initialization script fails, the container startup will fail with a clear error message.
258

259
**Common Script Errors:**
260

261
```java
262
try {
263
    MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
264
        .withInitScript("init/invalid-syntax.sql"); // Contains SQL syntax error
265
    
266
    mysql.start(); // Will throw exception due to script failure
267
} catch (Exception e) {
268
    // Handle initialization failure
269
    System.err.println("Database initialization failed: " + e.getMessage());
270
    // Check container logs for detailed SQL error information
271
}
272
```
273

274
**Script Debugging:**
275

276
- Container logs contain detailed SQL execution output
277
- Use MySQL-specific error codes to diagnose issues
278
- Scripts should be tested independently before container integration
279
- Consider using smaller, focused scripts rather than large monolithic files
280

281
### Integration with Testing Frameworks
282

283
**JUnit 5 Example:**
284

285
```java
286
@Testcontainers
287
class DatabaseIntegrationTest {
288
    
289
    @Container
290
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
291
        .withDatabaseName("test")
292
        .withInitScript("test-schema.sql");
293
    
294
    @Test
295
    void testDatabaseSetup() {
296
        // Database is already initialized with test-schema.sql
297
        try (Connection conn = mysql.createConnection("")) {
298
            Statement stmt = conn.createStatement();
299
            ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
300
            rs.next();
301
            assertTrue(rs.getInt(1) >= 0); // Table exists and is queryable
302
        }
303
    }
304
}