0
# CockroachDB Support
1

2
Specialized database support for CockroachDB databases, providing retry logic, version detection, and database-specific optimizations within the Flyway migration framework.
3

4
## Capabilities
5

6
### CockroachDB Database Type
7

8
Database type implementation that provides CockroachDB-specific functionality while maintaining PostgreSQL wire protocol compatibility.
9

10
```java { .api }
11
/**
12
 * CockroachDB database type implementation
13
 */
14
public class CockroachDBDatabaseType extends BaseDatabaseType {
15
    /**
16
     * Returns the database name identifier
17
     * @return "CockroachDB"
18
     */
19
    public String getName();
20
    
21
    /**
22
     * Returns the null type identifier for this database
23
     * @return Types.NULL
24
     */
25
    public int getNullType();
26
    
27
    /**
28
     * Indicates whether CockroachDB supports read-only transactions
29
     * @return false (CockroachDB does not support read-only transactions)
30
     */
31
    public boolean supportsReadOnlyTransactions();
32
    
33
    /**
34
     * Checks if this database type can handle the given JDBC URL
35
     * Uses PostgreSQL driver but with CockroachDB-specific detection
36
     * @param url JDBC URL to check
37
     * @return true for PostgreSQL URLs (detection happens later)
38
     */
39
    public boolean handlesJDBCUrl(String url);
40
    
41
    /**
42
     * Returns the priority for database type selection
43
     * @return 1 (higher than PostgreSQL to be checked first)
44
     */
45
    public int getPriority();
46
    
47
    /**
48
     * Returns the JDBC driver class for CockroachDB
49
     * @param url JDBC URL
50
     * @param classLoader Class loader for loading driver
51
     * @return PostgreSQL driver class name
52
     */
53
    public String getDriverClass(String url, ClassLoader classLoader);
54
    
55
    /**
56
     * Detects CockroachDB by checking database product name and version output
57
     * @param databaseProductName Database product name from JDBC metadata
58
     * @param databaseProductVersion Database version
59
     * @param connection JDBC connection for additional queries
60
     * @return true if SELECT VERSION() output contains "CockroachDB"
61
     */
62
    public boolean handlesDatabaseProductNameAndVersion(String databaseProductName, 
63
                                                       String databaseProductVersion, 
64
                                                       Connection connection);
65
    
66
    /**
67
     * Creates a CockroachDB database instance
68
     * @param configuration Flyway configuration
69
     * @param jdbcConnectionFactory Connection factory
70
     * @param statementInterceptor Statement interceptor
71
     * @return CockroachDBDatabase instance
72
     */
73
    public Database createDatabase(Configuration configuration, 
74
                                  JdbcConnectionFactory jdbcConnectionFactory, 
75
                                  StatementInterceptor statementInterceptor);
76
    
77
    /**
78
     * Creates a CockroachDB parser instance
79
     * @param configuration Flyway configuration
80
     * @param resourceProvider Resource provider for loading SQL files
81
     * @param parsingContext Parsing context
82
     * @return CockroachDBParser instance
83
     */
84
    public Parser createParser(Configuration configuration, 
85
                              ResourceProvider resourceProvider, 
86
                              ParsingContext parsingContext);
87
    
88
    /**
89
     * Creates CockroachDB-specific execution strategy with retry logic
90
     * @param connection JDBC connection
91
     * @return CockroachDBRetryingStrategy for handling transaction retries
92
     */
93
    public DatabaseExecutionStrategy createExecutionStrategy(Connection connection);
94
    
95
    /**
96
     * Creates transactional execution template with CockroachDB retry logic
97
     * @param connection JDBC connection
98
     * @param rollbackOnException Whether to rollback on exceptions
99
     * @return CockroachRetryingTransactionalExecutionTemplate
100
     */
101
    public ExecutionTemplate createTransactionalExecutionTemplate(Connection connection, 
102
                                                                 boolean rollbackOnException);
103
}
104
```
105

106
**Usage Examples:**
107

108
```java
109
import org.flywaydb.database.cockroachdb.CockroachDBDatabaseType;
110

111
// Database type is automatically registered and detected
112
// CockroachDB connections use PostgreSQL JDBC URLs
113
Flyway flyway = Flyway.configure()
114
    .dataSource("jdbc:postgresql://localhost:26257/mydb", "root", "")
115
    .locations("classpath:db/migration")
116
    .load();
117

118
// CockroachDB is automatically detected via version string
119
CockroachDBDatabaseType dbType = new CockroachDBDatabaseType();
120
boolean hasHigherPriority = dbType.getPriority() > 0; // true - checked before PostgreSQL
121

122
// Verify database capabilities
123
boolean supportsReadOnly = dbType.supportsReadOnlyTransactions(); // false
124
```
125

126
### CockroachDB Database
127

128
Main database implementation providing CockroachDB-specific operations and version detection.
129

130
```java { .api }
131
/**
132
 * CockroachDB database implementation
133
 */
134
public class CockroachDBDatabase extends Database<CockroachDBConnection> {
135
    /**
136
     * Creates a new CockroachDB database instance
137
     * Automatically detects and stores the CockroachDB version
138
     * @param configuration Flyway configuration
139
     * @param jdbcConnectionFactory Connection factory
140
     * @param statementInterceptor Statement interceptor
141
     */
142
    public CockroachDBDatabase(Configuration configuration, 
143
                              JdbcConnectionFactory jdbcConnectionFactory, 
144
                              StatementInterceptor statementInterceptor);
145
    
146
    /**
147
     * Creates a CockroachDB connection wrapper
148
     * @param connection Raw JDBC connection
149
     * @return CockroachDBConnection instance
150
     */
151
    protected CockroachDBConnection doGetConnection(Connection connection);
152
    
153
    /**
154
     * Ensures the CockroachDB version is supported by Flyway
155
     * @param configuration Flyway configuration
156
     * @throws FlywayException if version is not supported
157
     */
158
    public void ensureSupported(Configuration configuration);
159
    
160
    /**
161
     * Generates the SQL script for creating the Flyway schema history table
162
     * Uses CREATE TABLE IF NOT EXISTS for CockroachDB compatibility
163
     * @param table The table to create
164
     * @param baseline Whether to include baseline entry
165
     * @return SQL script for table creation with CockroachDB-specific syntax
166
     */
167
    public String getRawCreateScript(Table table, boolean baseline);
168
    
169
    /**
170
     * Determines the CockroachDB version from database metadata
171
     * @return Parsed version (e.g., "22.1" from "v22.1.2")
172
     */
173
    protected MigrationVersion determineVersion();
174
    
175
    /**
176
     * Checks if this CockroachDB version supports schemas
177
     * @return true for version 20.2 and later
178
     */
179
    boolean supportsSchemas();
180
    
181
    /**
182
     * Gets the current database user
183
     * @return Current user name from SHOW SESSION_USER
184
     * @throws SQLException if user cannot be determined
185
     */
186
    protected String doGetCurrentUser() throws SQLException;
187
    
188
    /**
189
     * Indicates whether CockroachDB supports DDL transactions
190
     * @return false (CockroachDB limitation)
191
     */
192
    public boolean supportsDdlTransactions();
193
    
194
    /**
195
     * Returns the CockroachDB boolean true literal
196
     * @return "TRUE"
197
     */
198
    public String getBooleanTrue();
199
    
200
    /**
201
     * Returns the CockroachDB boolean false literal
202
     * @return "FALSE"
203
     */
204
    public String getBooleanFalse();
205
    
206
    /**
207
     * Indicates whether catalog equals schema in CockroachDB
208
     * @return false
209
     */
210
    public boolean catalogIsSchema();
211
    
212
    /**
213
     * Determines connection usage strategy for CockroachDB
214
     * @return false (always use multiple connections due to retry requirements)
215
     */
216
    public boolean useSingleConnection();
217
}
218
```
219

220
### CockroachDB Connection Management
221

222
CockroachDB-specific connection handling with schema support and database version awareness.
223

224
```java { .api }
225
/**
226
 * CockroachDB connection implementation
227
 */
228
public class CockroachDBConnection extends Connection<CockroachDBDatabase> {
229
    /**
230
     * Creates a new CockroachDB connection wrapper
231
     * @param database The CockroachDB database instance
232
     * @param connection The raw JDBC connection
233
     */
234
    public CockroachDBConnection(CockroachDBDatabase database, java.sql.Connection connection);
235
    
236
    /**
237
     * Gets a schema by name (requires CockroachDB 20.2+)
238
     * @param name Schema name
239
     * @return CockroachDBSchema instance
240
     */
241
    public Schema getSchema(String name);
242
    
243
    /**
244
     * Gets the current schema for this connection
245
     * @return Current schema or null if schemas not supported
246
     * @throws SQLException if current schema cannot be determined
247
     */
248
    public Schema doGetCurrentSchema() throws SQLException;
249
}
250
```
251

252
### CockroachDB SQL Parsing
253

254
CockroachDB-specific SQL parser that extends PostgreSQL parsing capabilities.
255

256
```java { .api }
257
/**
258
 * CockroachDB SQL parser implementation
259
 */
260
public class CockroachDBParser extends Parser {
261
    /**
262
     * Creates a new CockroachDB parser
263
     * @param configuration Flyway configuration
264
     * @param parsingContext Parser context information
265
     */
266
    public CockroachDBParser(Configuration configuration, ParsingContext parsingContext);
267
}
268
```
269

270
**Usage Examples:**
271

272
```java
273
import org.flywaydb.database.cockroachdb.CockroachDBDatabase;
274

275
// Database is typically created via CockroachDBDatabaseType.createDatabase()
276
CockroachDBDatabase database = (CockroachDBDatabase) flyway.getConfiguration()
277
    .getDatabaseType()
278
    .createDatabase(config, connectionFactory, interceptor);
279

280
// Check version-specific capabilities
281
boolean hasSchemaSupport = database.supportsSchemas(); // true for v20.2+
282
boolean supportsDdlTx = database.supportsDdlTransactions(); // false
283

284
// Version information
285
MigrationVersion version = database.getVersion();
286
System.out.println("CockroachDB version: " + version.getVersion());
287

288
// Table creation with CockroachDB syntax
289
Table historyTable = database.getTable("flyway_schema_history");
290
String createScript = database.getRawCreateScript(historyTable, false);
291
// Uses "CREATE TABLE IF NOT EXISTS" syntax
292
```
293

294
### Retry Logic
295

296
CockroachDB requires special handling for transaction retries due to its distributed nature:
297

298
```java { .api }
299
/**
300
 * CockroachDB-specific execution strategy with retry logic
301
 */
302
public class CockroachDBRetryingStrategy implements DatabaseExecutionStrategy {
303
    // Implementation handles CockroachDB transaction retry logic
304
}
305

306
/**
307
 * Transactional execution template with CockroachDB retry handling
308
 */
309
public class CockroachRetryingTransactionalExecutionTemplate extends ExecutionTemplate {
310
    /**
311
     * Creates retry-enabled transactional execution template
312
     * @param connection JDBC connection
313
     * @param rollbackOnException Whether to rollback on exceptions
314
     */
315
    public CockroachRetryingTransactionalExecutionTemplate(Connection connection, 
316
                                                           boolean rollbackOnException);
317
}
318
```
319

320
## Version Support
321

322
### Supported Versions
323

324
- **Minimum Version**: CockroachDB 1.1
325
- **Schema Support**: CockroachDB 20.2+ (schemas were introduced in this version)
326
- **Latest Recommended**: CockroachDB 22.1+
327

328
### Version Detection
329

330
```java
331
// Version detection from CockroachDB system tables
332
// SELECT value FROM crdb_internal.node_build_info WHERE field='Version'
333
// or field='Tag' as fallback
334

335
// Example version strings:
336
// "v22.1.2" -> parsed as MigrationVersion "22.1"
337
// "v21.2.15" -> parsed as MigrationVersion "21.2"
338
```
339

340
## Database Differences from PostgreSQL
341

342
### DDL Transactions
343

344
```java
345
// CockroachDB does NOT support DDL in transactions
346
boolean supportsDdl = database.supportsDdlTransactions(); // returns false
347

348
// This means schema changes cannot be rolled back automatically
349
// Each DDL statement is committed immediately
350
```
351

352
### Schema Support
353

354
```java
355
// Schema support is version-dependent
356
if (database.supportsSchemas()) {
357
    // CockroachDB 20.2+ supports schemas
358
    Schema schema = connection.getSchema("my_schema");
359
    schema.create();
360
} else {
361
    // Pre-20.2 versions use default schema only
362
    System.out.println("Schema operations not supported in this CockroachDB version");
363
}
364
```
365

366
### Connection Management
367

368
```java
369
// CockroachDB always uses multiple connections
370
boolean singleConnection = database.useSingleConnection(); // always false
371

372
// This is required for proper retry logic handling
373
// Retry operations need separate connection contexts
374
```
375

376
## Migration Best Practices
377

378
### Handling Retries
379

380
```java
381
// CockroachDB migrations automatically handle retries
382
// No special code needed - handled by CockroachRetryingTransactionalExecutionTemplate
383

384
Flyway flyway = Flyway.configure()
385
    .dataSource("jdbc:postgresql://localhost:26257/mydb", "root", "")
386
    .locations("classpath:db/migration")
387
    .load();
388

389
// Migrations automatically retry on serialization failures
390
flyway.migrate();
391
```
392

393
### DDL Considerations
394

395
```sql
396
-- Each DDL statement is auto-committed in CockroachDB
397
-- Good practice: Keep DDL statements atomic and independent
398

399
-- Good: Simple, atomic operations
400
CREATE TABLE users (id SERIAL PRIMARY KEY, name STRING);
401
CREATE INDEX idx_users_name ON users(name);
402

403
-- Avoid: Complex multi-step DDL in single migration
404
-- Better to split across multiple migration files
405
```
406

407
### Schema Usage
408

409
```sql
410
-- For CockroachDB 20.2+
411
CREATE SCHEMA app_schema;
412
CREATE TABLE app_schema.users (id SERIAL PRIMARY KEY, name STRING);
413

414
-- For older versions, use default schema
415
CREATE TABLE users (id SERIAL PRIMARY KEY, name STRING);
416
```
417

418
## Error Handling
419

420
```java
421
// CockroachDB-specific error handling
422
try {
423
    flyway.migrate();
424
} catch (FlywayException e) {
425
    if (e.getMessage().contains("restart transaction")) {
426
        System.out.println("Transaction retry handled automatically");
427
    } else if (e.getMessage().contains("schema operations not supported")) {
428
        System.out.println("Upgrade CockroachDB to 20.2+ for schema support");
429
    }
430
}
431
```
432

433
## Integration Example
434

435
```java
436
import org.flywaydb.core.Flyway;
437
import org.flywaydb.database.cockroachdb.CockroachDBDatabase;
438

439
// Complete CockroachDB integration
440
Flyway flyway = Flyway.configure()
441
    .dataSource("jdbc:postgresql://localhost:26257/mycompany", "root", "")
442
    .locations("classpath:db/migration")
443
    .table("schema_version")  // Custom history table name
444
    .load();
445

446
// Migrate with automatic CockroachDB detection and retry handling
447
flyway.migrate();
448

449
// Access CockroachDB-specific functionality
450
CockroachDBDatabase database = (CockroachDBDatabase) flyway.getConfiguration()
451
    .getDatabaseType()
452
    .createDatabase(flyway.getConfiguration(), null, null);
453

454
System.out.println("CockroachDB version: " + database.getVersion());
455
System.out.println("Schema support: " + database.supportsSchemas());
456
```