Database Setup
For the database configuration, the following requirements were set:
- automatically update the database from build
- automatically keep database and ORM in sync from build
- database can be maintained via SQL scripts only
- open source database
- database of which you can get free (or near free) instances
- capable of running in docker
- compatible with spring boot
Tech Stack
Configuration
InitialConfiguration.java
Configuration class for JOOQ to make sure the connection to postgresql will work on every platform.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 | @Configuration
public class InitialConfiguration {
@Autowired
private DataSource dataSource;
@Bean
public DataSourceConnectionProvider connectionProvider() {
return new DataSourceConnectionProvider
(new TransactionAwareDataSourceProxy(dataSource));
}
@Bean
public DefaultDSLContext dsl() {
return new DefaultDSLContext(configuration());
}
public DefaultConfiguration configuration() {
DefaultConfiguration jooqConfiguration = new DefaultConfiguration();
jooqConfiguration.set(connectionProvider());
Settings settings =new Settings();
settings.setRenderNameStyle(RenderNameStyle.LOWER);
settings.setRenderSchema(true);
jooqConfiguration.set(settings);
jooqConfiguration.set(new DefaultExecuteListenerProvider(new ExceptionTranslator()));
return jooqConfiguration;
}
}
|
pom.xml
| <properties>
<flyway.baseline-on-migrate>true</flyway.baseline-on-migrate>
<postgres.driver.version>9.2-1002.jdbc4</postgres.driver.version>
<db.url>jdbc:postgresql://localhost:5432/kw</db.url>
<db.user>docker</db.user>
<db.password>docker</db.password>
<flyway.schemas>public</flyway.schemas>
</properties>
|
FLYWAY
For updating the Database via DB Migrations.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | <plugin>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-maven-plugin</artifactId>
<configuration>
<sqlMigrationSeparator>__</sqlMigrationSeparator>
<locations>
<location>filesystem:src/main/resources/db/migration</location>
</locations>
<url>${db.url}</url>
<user>${db.user}</user>
<password>${db.password}</password>
</configuration>
<dependencies>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.4-1206-jdbc42</version>
</dependency>
</dependencies>
<executions>
<execution>
<id>integration-test-database-setup</id>
<phase>initialize</phase>
<goals>
<goal>clean</goal>
<goal>migrate</goal>
</goals>
</execution>
</executions>
</plugin>
|
JOOQ
For generating Active Record classes from Database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49 | <plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${org.jooq.version}</version>
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<!-- Manage the plugin's dependency. In this example, we'll use a PostgreSQL database -->
<dependencies>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.4-1206-jdbc42</version>
</dependency>
</dependencies>
<!-- Specify the plugin configuration.
The configuration format is the same as for the standalone code generator -->
<configuration>
<!-- JDBC connection parameters -->
<jdbc>
<driver>org.postgresql.Driver</driver>
<url>${db.url}</url>
<user>${db.user}</user>
<password>${db.password}</password>
</jdbc>
<!-- Generator parameters -->
<generator>
<name>org.jooq.util.DefaultGenerator</name>
<database>
<name>org.jooq.util.postgres.PostgresDatabase</name>
<includes>.*</includes>
<excludes></excludes>
<inputSchema>public</inputSchema>
</database>
<target>
<packageName>com.github.joostvdg.keepwatching.model</packageName>
<directory>gensrc/main/java</directory>
</target>
</generator>
</configuration>
</plugin>
|
Build Helper
For adding generated sources (Active Record tabel classes) from JOOQ to the sources list.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 | <plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>build-helper-maven-plugin</artifactId>
<executions>
<execution>
<phase>process-sources</phase>
<goals>
<goal>add-source</goal>
</goals>
<configuration>
<sources>
<source>gensrc/main/java</source>
</sources>
</configuration>
</execution>
</executions>
</plugin>
|
application.yml
| spring:
datasource:
url: localhost:5432/kw
username: docker
password: docker
|
Docker Compose
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 | version: '2'
services:
db:
image: postgres
expose:
- 5432
ports:
- 15432:5432
environment:
- POSTGRES_PASSWORD=docker
- POSTGRES_USER=docker
- POSTGRES_DB=kw
db-local:
image: postgres
expose:
- 5432
ports:
- 15432:5432
environment:
- POSTGRES_PASSWORD=docker
- POSTGRES_USER=docker
- POSTGRES_DB=kw
volumes:
- /wolkje/volumes/kw/postgres:/var/lib/postgresql/data
backend:
image: caladreas/keep-watching-be
expose:
- 8080
ports:
- 8080:8080
links:
- db
|
Way of Working
The process of change goes as follows:
- make sure the default postgresql database is up
- create a db migration in src/main/resources/db.migration
- V{number}__{description_of_change}.sql format
- run Flyway migration to apply database changes
- run JOOQ generation to generate the classes of the active records (database tables)
- run maven build helper to make these generated class part of the source files
- compile normally
- run spring boot to test working database/application
| docker-compose up db-local
DB_IP=$(docker inspect --format '{{.NetworkSettings.Networks.keepwatching_default.IPAddress}}' keepwatching_db_1)
mvn clean test package -P db -Dspring.profiles.active=compose -Ddb.url=jdbc:postgresql://${DB_IP}:5432/kw -Dspring.datasource.url=jdbc:postgresql://${DB_IP}:5432/kw -Dspring.datasource.username=docker -Dspring.datasource.password=docker
|
Resources