0
# Flyway Database PostgreSQL
1

2
Flyway Database PostgreSQL provides specialized database support for PostgreSQL and CockroachDB databases in the Flyway migration framework. It extends Flyway's core functionality with database-specific implementations for connection management, schema handling, SQL parsing, and migration execution, enabling reliable database schema versioning and automated migrations in PostgreSQL environments.
3

4
## Package Information
5

6
- **Package Name**: flyway-database-postgresql
7
- **Package Type**: maven
8
- **Group ID**: org.flywaydb
9
- **Artifact ID**: flyway-database-postgresql
10
- **Language**: Java
11
- **Installation**: Add to your Maven `pom.xml`:
12

13
```xml
14
<dependency>
15
    <groupId>org.flywaydb</groupId>
16
    <artifactId>flyway-database-postgresql</artifactId>
17
    <version>11.8.2</version>
18
</dependency>
19
```
20

21
For Gradle:
22

23
```gradle
24
implementation 'org.flywaydb:flyway-database-postgresql:11.8.2'
25
```
26

27
## Core Imports
28

29
```java
30
import org.flywaydb.database.postgresql.PostgreSQLDatabaseType;
31
import org.flywaydb.database.postgresql.PostgreSQLDatabase;
32
import org.flywaydb.database.postgresql.PostgreSQLConnection;
33
import org.flywaydb.database.postgresql.PostgreSQLConfigurationExtension;
34
```
35

36
For CockroachDB support:
37

38
```java
39
import org.flywaydb.database.cockroachdb.CockroachDBDatabaseType;
40
import org.flywaydb.database.cockroachdb.CockroachDBDatabase;
41
```
42

43
## Basic Usage
44

45
The package is automatically registered with Flyway through the service provider interface. When Flyway encounters a PostgreSQL JDBC URL, it automatically uses the appropriate database type:
46

47
```java
48
import org.flywaydb.core.Flyway;
49

50
// Configure Flyway with PostgreSQL
51
Flyway flyway = Flyway.configure()
52
    .dataSource("jdbc:postgresql://localhost:5432/mydb", "user", "password")
53
    .locations("classpath:db/migration")
54
    .load();
55

56
// Migrate the database
57
flyway.migrate();
58
```
59

60
For CockroachDB:
61

62
```java
63
Flyway flyway = Flyway.configure()
64
    .dataSource("jdbc:postgresql://localhost:26257/mydb", "root", "")
65
    .locations("classpath:db/migration")
66
    .load();
67

68
flyway.migrate();
69
```
70

71
## Architecture
72

73
The package is built around several key components:
74

75
- **Database Type Registry**: Automatic detection and instantiation of PostgreSQL/CockroachDB support
76
- **Connection Management**: PostgreSQL-specific connection handling with advisory locking and role management
77
- **SQL Parser**: PostgreSQL-specific SQL parsing supporting COPY operations, dollar quoting, and transaction control
78
- **Schema Operations**: Full schema lifecycle management including cleaning, type management, and extension handling
79
- **Configuration Extensions**: PostgreSQL-specific configuration options and environment variable mapping
80

81
## Capabilities
82

83
### PostgreSQL Database Support
84

85
Core PostgreSQL database implementation providing connection management, schema operations, and migration table creation.
86

87
```java { .api }
88
public class PostgreSQLDatabaseType extends BaseDatabaseType {
89
    public String getName();
90
    public List<String> getSupportedEngines();
91
    public int getNullType();
92
    public boolean handlesJDBCUrl(String url);
93
    public String getDriverClass(String url, ClassLoader classLoader);
94
    public boolean handlesDatabaseProductNameAndVersion(String databaseProductName, String databaseProductVersion, Connection connection);
95
    public Database createDatabase(Configuration configuration, 
96
                                  JdbcConnectionFactory jdbcConnectionFactory, 
97
                                  StatementInterceptor statementInterceptor);
98
    public Parser createParser(Configuration configuration, ResourceProvider resourceProvider, ParsingContext parsingContext);
99
    public void setDefaultConnectionProps(String url, Properties props, ClassLoader classLoader);
100
    public Properties getExternalAuthProperties(String url, String username);
101
}
102

103
public class PostgreSQLDatabase extends Database<PostgreSQLConnection> {
104
    public PostgreSQLDatabase(Configuration configuration, 
105
                             JdbcConnectionFactory jdbcConnectionFactory, 
106
                             StatementInterceptor statementInterceptor);
107
    public void ensureSupported(Configuration configuration);
108
    public String getRawCreateScript(Table table, boolean baseline);
109
    public boolean supportsDdlTransactions();
110
    public String getBooleanTrue();
111
    public String getBooleanFalse();
112
    public String doQuote(String identifier);
113
    public String getEscapedQuote();
114
    public boolean catalogIsSchema();
115
    public boolean useSingleConnection();
116
    public String getSelectStatement(Table table);
117
    public String getDatabaseHosting();
118
    protected String doGetCurrentUser() throws SQLException;
119
}
120
```
121

122
[PostgreSQL Database Support](./postgresql-database.md)
123

124
### PostgreSQL Connection Management
125

126
PostgreSQL-specific connection implementation with role management, schema handling, and advisory locking.
127

128
```java { .api }
129
public class PostgreSQLConnection extends Connection<PostgreSQLDatabase> {
130
    protected PostgreSQLConnection(PostgreSQLDatabase database, 
131
                                  java.sql.Connection connection);
132
    public Schema doGetCurrentSchema() throws SQLException;
133
    public void changeCurrentSchemaTo(Schema schema);
134
    public Schema getSchema(String name);
135
    public <T> T lock(Table table, Callable<T> callable);
136
    public boolean isAwsRds();
137
}
138
```
139

140
[Connection Management](./postgresql-connection.md)
141

142
### PostgreSQL SQL Parsing
143

144
Specialized SQL parser for PostgreSQL with support for COPY operations, dollar quoting, and transaction control.
145

146
```java { .api }
147
public class PostgreSQLParser extends Parser {
148
    public PostgreSQLParser(Configuration configuration, ParsingContext parsingContext);
149
    protected char getAlternativeStringLiteralQuote();
150
    protected ParsedSqlStatement createStatement(PeekingReader reader, Recorder recorder,
151
        int statementPos, int statementLine, int statementCol, int nonCommentPartPos, 
152
        int nonCommentPartLine, int nonCommentPartCol, StatementType statementType, 
153
        boolean canExecuteInTransaction, Delimiter delimiter, String sql, 
154
        List<Token> tokens, boolean batchable) throws IOException;
155
}
156
```
157

158
[SQL Parsing](./postgresql-parser.md)
159

160
### PostgreSQL Schema Management
161

162
Schema operations including creation, cleanup, and management of PostgreSQL-specific objects.
163

164
```java { .api }
165
public class PostgreSQLSchema extends Schema<PostgreSQLDatabase, PostgreSQLTable> {
166
    protected PostgreSQLSchema(JdbcTemplate jdbcTemplate, 
167
                              PostgreSQLDatabase database, String name);
168
    protected boolean doExists() throws SQLException;
169
    protected boolean doEmpty() throws SQLException;
170
    protected void doCreate() throws SQLException;
171
    protected void doDrop() throws SQLException;
172
    protected void doClean() throws SQLException;
173
    protected PostgreSQLTable[] doAllTables() throws SQLException;
174
}
175
```
176

177
[Schema Management](./postgresql-schema.md)
178

179
### Configuration Extensions
180

181
PostgreSQL-specific configuration options and environment variable mapping.
182

183
```java { .api }
184
public class PostgreSQLConfigurationExtension implements ConfigurationExtension {
185
    public boolean isTransactionalLock();
186
    public void setTransactionalLock(boolean transactionalLock);
187
    public String getConfigurationParameterFromEnvironmentVariable(String environmentVariable);
188
    public String getNamespace();
189
}
190
```
191

192
[Configuration](./postgresql-configuration.md)
193

194
### CockroachDB Support
195

196
Specialized support for CockroachDB databases with retry logic and version-specific features.
197

198
```java { .api }
199
public class CockroachDBDatabaseType extends BaseDatabaseType {
200
    public String getName();
201
    public int getNullType();
202
    public boolean supportsReadOnlyTransactions();
203
    public boolean handlesJDBCUrl(String url);
204
    public int getPriority();
205
    public String getDriverClass(String url, ClassLoader classLoader);
206
    public boolean handlesDatabaseProductNameAndVersion(String databaseProductName, 
207
                                                       String databaseProductVersion, 
208
                                                       Connection connection);
209
    public Database createDatabase(Configuration configuration, JdbcConnectionFactory jdbcConnectionFactory, StatementInterceptor statementInterceptor);
210
    public Parser createParser(Configuration configuration, ResourceProvider resourceProvider, ParsingContext parsingContext);
211
    public DatabaseExecutionStrategy createExecutionStrategy(Connection connection);
212
    public ExecutionTemplate createTransactionalExecutionTemplate(Connection connection, boolean rollbackOnException);
213
    public void setDefaultConnectionProps(String url, Properties props, ClassLoader classLoader);
214
    public Properties getExternalAuthProperties(String url, String username);
215
}
216

217
public class CockroachDBDatabase extends Database<CockroachDBConnection> {
218
    public CockroachDBDatabase(Configuration configuration, 
219
                              JdbcConnectionFactory jdbcConnectionFactory, 
220
                              StatementInterceptor statementInterceptor);
221
    public void ensureSupported(Configuration configuration);
222
    public String getRawCreateScript(Table table, boolean baseline);
223
    protected MigrationVersion determineVersion();
224
    boolean supportsSchemas();
225
    public boolean supportsDdlTransactions();
226
    public String getBooleanTrue();
227
    public String getBooleanFalse();
228
    public String doQuote(String identifier);
229
    public String getEscapedQuote();
230
    public boolean catalogIsSchema();
231
    public boolean useSingleConnection();
232
    protected String doGetCurrentUser() throws SQLException;
233
}
234
```
235

236
[CockroachDB Support](./cockroachdb-support.md)
237

238
## Types
239

240
```java { .api }
241
// Configuration model for transactional settings (Lombok @Data annotation provides getters/setters)
242
@Data
243
public class TransactionalModel {
244
    private Boolean lock = null;
245
}
246

247
// Common interfaces from Flyway core
248
interface Configuration {
249
    String getUrl();
250
    String getTablespace();
251
    // ... other configuration methods
252
}
253

254
interface JdbcConnectionFactory {
255
    // Factory for creating JDBC connections
256
}
257

258
interface StatementInterceptor {
259
    // Interceptor for SQL statement execution
260
}
261

262
interface ResourceProvider {
263
    // Provider for loading migration resources
264
}
265

266
interface ParsingContext {
267
    // Context information for SQL parsing
268
}
269

270
interface ExecutionTemplate {
271
    // Template for executing SQL statements
272
}
273

274
interface DatabaseExecutionStrategy {
275
    // Strategy for database operation execution
276
}
277

278
class Properties extends java.util.Properties {
279
    // Java Properties for connection configuration
280
}
281

282
class MigrationVersion {
283
    // Version information for database migrations
284
    public static MigrationVersion fromVersion(String version);
285
    public boolean isAtLeast(String version);
286
    public String getVersion();
287
}
288

289
interface Parser {
290
    // SQL statement parser interface
291
}
292

293
interface Connection {
294
    // JDBC connection wrapper
295
}
296
```