Sakila: Sample app project setup

This sample application will integrate quite a few very nice open source tools available to every developer:

  • Postgresql – database
  • Flyway – database migration tool
  • Jooq – Java object oriented querying + HikariCP connection pool
  • Dagger 2 – dependency injection
  • SparkJava – fast and simple web server + GSON Json serializer
  • JavaScript Polymer SPA application framework
  • Vaadin Elements components

The application will consist of many modules :

Postgresql – database

Initialize sample database

For start we will install sample sakila database  in the local Postgresql database. Restore downloaded file into locally created database.

C:\Program Files\PostgreSQL\9.6\bin\pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "sakila" --no-owner --no-tablespaces --verbose "C:\\DVDRENTAL.TAR"

Sakila sample database is open source demo database and represent database model of DVD rental store. It  consist of  15 relational tables, 7 views and few other database details and it’s full of data.

Well that’s database with full of test data for development environment. If we need empty database (for production for example) we need to start initialization DDL script to build the database.

To create the script from the existing database use the command pg_dump which is capable of exporting database in the form of sql commands :

pg_dump -s -U postgres sakila > V1.0.1__sakila_init.sql

To export database without any data , only schema definitions we use “schema only” (-s) option.

Flyway migrations

Create flyway config file and “migrations” folder under the project root.


Add “fw” command somewhere on the path.

/c/Programs/flyway-4.2.0/flyway $@

Put the “V1.0.1__sakila_init.sql” file in the migrations folder. If everything works as expected the “info” command should report the pending migration.

Flyway migration and initial database state after database restore

After restoring the database with test data in it we need to “baseline” initial migration. Initial sql script to create empty database was bypassed with restore. The V1.0.1__sakila_init.sql migration script was still pending.

With the baseline command we agree that the migration is not needed and you mark the migration version as migrated.


Setup java server project

In the IDE (IntelliJ IDEA Community 2017.2) create new console type project “sakilaweb/server”.

Setup git-bash terminal as default intellij terminal

Jooq – object oriented querying

Create jooq config file and add jooq command somewhere on the path.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="">
    <!-- Configure the database connection here -->

        <!-- The default code generator. You can override this one, to generate your own code style.
             Supported generators:
             - org.jooq.util.JavaGenerator
             - org.jooq.util.ScalaGenerator
             Defaults to org.jooq.util.JavaGenerator -->

            <!-- The database type. The format here is:
                 org.util.[database].[database]Database -->

            <!-- The database schema (or in the absence of schema support, in your RDBMS this
                 can be the owner, user, database name) to be generated -->


            <!-- All elements that are generated from your schema
                 (A Java regular expression. Use the pipe to separate several expressions)
                 Watch out for case-sensitivity. Depending on your database, this might be important! -->

            <!-- All elements that are excluded from your schema
                 (A Java regular expression. Use the pipe to separate several expressions).
                 Excludes match before includes -->


            <!-- The destination package of your generated classes (within the destination directory) -->

            <!-- The destination directory of your generated classes. Using Maven directory layout here -->

Bash command:

java -classpath $CP org.jooq.util.GenerationTool jooq.xml

Add “jooq-3.10.1.jar” library to project dependencies. Add “postgresql-42.1.4.jar” if you use the same database.

Run code generation tool with “jooq” command in the terminal at the project root.

After code was successfully generated in the “./database” folder you will get a bunch of database related code ready made ( database schema, POJOs, and DAOs).

The project with generated code will now look like :

Setup Dagger 2

Configure IDEA for annotations processor.

Add dagger dependencies (dagger-compiler only as “Provided” because it is used only for code generation ).

Setup SparkJava web server

Add few references to the project dependencies and setup “hello-world” web sample just to be sure everything is setup us expected before start real coding.


Create main procedure as :

package com.bisaga.sakila;

import static spark.Spark.*;

public class Main {

    public static void main(String[] args) {
        get("/hello", (req, res)-> "Hello !");

Now if you run the application you should already get the first page:

Publish to the github

First enable VSC support in the local project and add .gitignore file to the project. Next we add files to the local git repository created in the project.

If we want  to push code to the remote repository we need to create it to have repository to commit to. Login to the github and create new empty repository.

The code for the server side project is available here.



Next : In the next installment I will put the generated database layer into the use and expose first REST service.






Data migrations in Node applications

Setup db-migrate tool

As your application grow your database model evolve too.  The changes from one version to another is made by migrations. Each migration in the db-migrate tool is in essence  a small javascript program. You can code your migration changes manually in the javascript syntax or generate small javascript program (with –sql-file option) to execute your SQL script files. One for the UP and one for the DOWN function of migration.

up: you upgrade your database schema to the new version
down: you reverse latest changes to the previous version
create: create new migration, with an –sql-file option create a SQL file runner


We install db-migrate module and corresponding database driver with npm.

npm install db-migrate
npm install db-migrate-pg

Depends where you install it the command is available as:

$ ./node_modules/.bin/db-migrate
$ db-migrate

It is possible to add local ./node_module/.bin folder to the local path variable as I described in this article and call it the same way as you would if you install it in to the global space.


Minimal config file (database.json) to work with postgres database in the development environment:

  "dev": "postgresql://postgres:postgres@localhost:5432/dbsam"  

The file should be in the main project folder. More about configuration.

Migrations with SQL files

To create new empty migration use the command:

$ db-migrate create new_mig_name --sql-file

By default your migration javascript files reside in the “./migrations” folder and sql files in the “./migrations/sqls”  subfolder.

Three new files are created, all files are prefixed with the timestamp which represent order of execution.

Additional two files with the suffix sql are prepared for your upgrade script and downgrade script. If you wish to have ability to downgrade database to the previous level make sure you write down script to.

Example of upgrade script (it is just for the sake of example, you basically write DDL statements with the familiar SQL syntax):

* Table: Currency  
* Contains currencies 
CREATE TABLE currency (
	code                varchar(60) not null,               -- code 
	abbreviation        varchar(60),						-- ex. EUR, USD
	description         text,								
	row_id              serial primary key,                 -- row identifier, autonumber, pk 
	org_id				smallint not null,					-- multitenant identifier 
	co_id				int NOT NULL,						-- company identifier  
	created_at          timestamptz not null default now(),
	created_by          varchar(60) not null,
	modified_at         timestamptz,
	modified_by         varchar(60),
	CONSTRAINT currency_sys_org_fk FOREIGN KEY (org_id) REFERENCES sys_org (row_id),
	CONSTRAINT currency_sys_co_fk FOREIGN KEY (co_id) REFERENCES sys_co (row_id)
) WITHOUT OIDS;                                               -- don't create object identifier field 
CREATE INDEX currency_org_id_fkix ON currency (org_id);
CREATE INDEX currency_co_id_fkix ON currency (co_id);
CREATE INDEX currency_created_at ON currency (created_at DESC) ;
CREATE INDEX currency_modified_at ON currency (modified_at DESC) ;

Example of downgrade script:

DROP TABLE currency;

Run “db-migrate up” command and all your migrations which are not yet executed will run.

The migration log is kept in the database migrations table. The table is automatically created at first run.

Other useful commands

reset: will rewind your database to the state before first migration is called

Using db-migrate with typescript

If you write node programs with the typescript you probably wish to use it with migration to. I didn’t go in this direction simply because I write my scripts in SQL language and runners are just perfect already in javascript. Because the migration are already in the javascript (the runner part), you should exclude migrations folder from the typescript compiler path.

Sample tsconfig.json file with excluded migrations folder :

    "compilerOptions": {
        "target": "es2017",
        "module": "commonjs",
        "moduleResolution": "node",
        "noEmitOnError": true,
        "noImplicitAny": true,
        "experimentalDecorators": true,
        "sourceMap": true,
        "baseUrl": ".",
        "allowJs": true,
        "paths": {
        "outDir": "dist"
    "typeRoots": [
    "include": [
    "exclude": [

I didn’t include migration scripts to production deployment code yet, that step would be necessary if you wan’t to upgrade database automatically after deployment.

External links

Database migration tool: Db-migrate.
Gui prototyping and drawing tool: The pencil.

Java Spring Boot project setup

You can get source code of this project from github repository “SpringBootMyApp” .

Create Spring Boot Maven project

Go to Spring Boot project generator web site and select minimal project definition with dependencies :

  • Web:  dependency for embedded tomcat server
  • jOOQ: integrated SQL query language and data model code generator
  • Flyway: database model migration tool
  • Postgresql JDBC driver

After downloading ZIP project file, unzip it to some folder and open folder in IntelliJ IDE environment.

Startup class

The project will automatically include embedded tomcat server and spring application start will configure whole application at startup.

package com.bisaga.myapp;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication(scanBasePackages = { "com.bisaga" })
public class MyappApplication {

	public static void main(String[] args) {, args);

Setup project properties

Under src/main/resources find file and add configuration for database access  and flyway configuration. Your application will start migration procedure at every application start and syncronize to the correct version automatically.


# Database connection

# Jooq dialect

# Flyway

Flyway migration files

You need a folder where your SQL migration files will reside:


Add first migration file, for example “create currency table” will be saved in file V1_0_0__currency.sql:

 * Currency
CREATE TABLE currency (
	code 						VARCHAR(60) not null,
	abbreviation 				varchar(60),
	description 				text,
	row_id 						serial primary key,					-- row identifier
	created_at 					timestamptz not null default now(),
	created_by 					varchar(60) not null,
	modified_at 				timestamptz,
	modified_by 				varchar(60)
CREATE INDEX currency_created_at ON currency (created_at) ;
CREATE INDEX currency_modified_at ON currency (modified_at) ;

INSERT INTO public.currency (code, abbreviation, description, created_by)
VALUES('EUR', 'Eur', 'Euro', 'admin');

INSERT INTO public.currency (code, abbreviation, description, created_by)
VALUES('USD', 'Usd', 'US Dollar', 'admin');

Be extra careful with names , first part (V1_0_0) is version of migration file, second part of the name is a description, separated with two underline characters.

The scripts must be correct SQL! Validate it first in database environment. If script is not valid, migration procedure will break and you will probably have hard time to figure out what was wrong.

Now you run your application and your database must be prepared according to migration script changes automatically. Flyway start automatically with your application.

More information on this video tutorial for flyway and spring boot..

Maven plugin settings

If you want more control around code generation and migration life cycle, you add flyway and jooq plugins:

Add this properties to the properties section in pom.xml file:

        <!-- Database -->
        <!-- FlyWayDB -->
        <!-- jOOQ -->
  • Database connection settings in pom.xml will be used by jooq code generator and flyway migration maven plugins.

Flyway and Jooq dependency and version

You probably already have it from generated pom.xml, add it if you don’t have it.


Version was not required by spring boot but I put version in because I encounter differences between plugin version and spring boot version of the libraries.

Now you need to define plugin definitions as :


                <!-- Note that we're executing the Flyway plugin in the "generate-sources" phase -->
                <!-- Note that we need to prefix the db/migration path with filesystem:
                    to prevent Flyway from looking for our migration scripts only on the classpath -->

                <!-- Use org.jooq for the Open Source Edition for commercial
                    editions, for commercial editions with Java 6 support,
                    org.jooq.trial for the free trial edition Note: Only the Open Source Edition
                    is hosted on Maven Central. Import the others manually from your distribution -->
                <!-- The jOOQ code generation plugin is also executed in the generate-sources phase,
                     prior to compilation -->

                <!-- This is a minimal working configuration. See the manual's section about the code generator
                     for more details -->

Flyway and Jooq maven commands

After you add plugin definitions to maven pom.xml file you get new lifecycle commands :

When flyway commands are used directly they search for SQL files in the target “classes” folder and not in the source tree (“src”). When project compiled the files, target folder is synchronized with the current version of code in the source tree.

After compiling project you could run “flyway:migrate” command for example. You can always check files in the target folder and delete it if you are not sure you have the latest version of the files.

Jooq code generation

Jooq generate data model code from your database. The code is added to the project in the namespace as you defined in plugin definition.  It’s wise to split plugin definition to properties and plugin definition part.

<!-- jOOQ -->

Every time you compile project, database model code is regenerated. Check here for more information about jooq.

You can check how database model code is regenerated simply by deleting a line from one of generated files and recompile the project again.

More about jooq & spring boot can be found here.

Create JSON service

Now I need to test the environment if everything is set in place as should be. I will create simple currency service as sample application and test basic CRUD operations.

Create simple data container class

To be able to send JSON payload back and forth from client to server we create a simple transport class:

package com.bisaga.myapp;

 * Created by igorb on 17. 06. 2017.
public class CurrencyDto {
    private String code;
    private String abbreviation;
    private String description;
    private Integer rowId;

    public String getCode() {
        return code;

    public void setCode(String code) {
        this.code = code;

    public String getAbbreviation() {
        return abbreviation;

    public void setAbbreviation(String abbreviation) {
        this.abbreviation = abbreviation;

    public String getDescription() {
        return description;

    public void setDescription(String description) {
        this.description = description;

    public Integer getRowId() {
        return rowId;

    public void setRowId(Integer rowId) {
        this.rowId = rowId;

Service class and database interaction with Jooq

Now we create service class with which we will interact with database :

*  MIT License
*  Copyright (c) 2017 Igor Babic
package com.bisaga.myapp;

import static com.bisaga.myapp.database.model.tables.Currency.CURRENCY;

import com.bisaga.myapp.database.model.tables.records.CurrencyRecord;
import org.jooq.DSLContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

class CurrencyService {
    private static final Logger LOG = LoggerFactory.getLogger(CurrencyService.class);

    private DSLContext db;

     * List all currencies in the currency table
     * @return Return list of currency records
    List<CurrencyDto> getAll() {
        List<CurrencyDto> currList;
        currList =

        return currList;

     * Search for a currency record by currency code
     * @param code  currency code as parameter
     * @return Return currency record
    CurrencyDto getByCode(String code) {
        List<CurrencyDto> currList;
        currList =
        return currList.get(0);

     * Delete currency record by row identifier
     * @param rowId rowId row identifier
     * @return Return number of deleted records
    Integer delete(Integer rowId) {
        return db.delete(CURRENCY).where(CURRENCY.ROW_ID.eq(rowId)).execute();

     * Save currency execute insert or update command if currency already exist.
     * @param currency instance of CurrencyDto class as parameter
    CurrencyDto saveCurrency(CurrencyDto currency) {
        if (currency.getRowId() == null) {
            return this.insert(currency);
        } else {
            return this.update(currency);

     * Insert new currency record to database
     * @param currency Currency record as parameter
     * @return Return currency record filled with database defaults after insert
    private CurrencyDto  insert(CurrencyDto currency)  {
        CurrencyRecord record = db.newRecord(CURRENCY, currency);
        record.insert();                // insert to database
        record.into(currency);			// return changes made by database
        return currency;

     * Update currency record in database
     * @param currency currency record as parameter
     * @return Return currency record filled with changes from database update
    private CurrencyDto update(CurrencyDto currency) {
        CurrencyRecord record = db.newRecord(CURRENCY, currency);
        record.update();                // update to database
        record.into(currency);			// return changes made by database
        return currency;


REST controller with router mapping

On the end we need a controller class to expose access points for REST service:

*  MIT License
*  Copyright (c) 2017 Igor Babic
package com.bisaga.myapp;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RequestMapping(value = "/api/currency", produces = "application/json")
public class CurrencyController {

    @Autowired private CurrencyService service;

    public List<CurrencyDto> findAll() {
        return service.getAll();

    @RequestMapping(value="/{code}", method=RequestMethod.GET)
    public CurrencyDto findByCode(@PathVariable("code") String code) {
        return service.getByCode(code);

    public CurrencyDto saveCurrency(@RequestBody CurrencyDto currency) {
        return service.saveCurrency(currency);

    public Integer deleteCurrency(@RequestParam(value = "rowid", required = true) Integer rowId ) {
        return service.delete(rowId);


Don’t forget to instantiate services with DI (dependency injection) with @Autowire annotation. This simplify development a lot !

Interactive testing

The service should in this point work as expected, just run application and navigate to “localhost:8080/api/currency”.

You can search for a specific currency with added path variable appended to Url address:

Postman application for Http API testing

To inspect service in more details on the client side, you can use Postman application.

Look at example ofadding a currency with a POST message:

In the headar I add “content-type” variable with value “application/json” and in the payload a json message with a new currency json structure. On the right side I received “200-OK” and a currency record with a record and new row identifier registered in the database.


How to add data migration support to Silex

Add Doctrine data migrations to Silex application

There are quite a few blog articles about using doctrine migrations in different project types and this article was closest to what we need in Silex project.

Install doctrine migration module

We add a few required components to composer.json and execute update command.

     "doctrine/dbal": "~2.2",
     "symfony/console": "^2.7",
     "doctrine/migrations": "^1.1"

We need to have symfony/console module already installed and prepared for extending with new commands, as I wrote in this article.

Add migration commands to console.php program.  You can find them in vendor folder under:  “/lib/Doctrine/DBAL/Migrations/Tools/Console/Command” path.

// application.php

require __DIR__.'/vendor/autoload.php';

use Symfony\Component\Console\Application;

$application = new Application();

//Migrations commands 
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\DiffCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\ExecuteCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\GenerateCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\MigrateCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\StatusCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\VersionCommand());


Add migrations configuration files:

Migrations configuration define base settings for migration generation and execution environment.

name: Doctrine Migrations
migrations_namespace: Bisaga\Migrations
table_name: doctrine_migration_versions
migrations_directory: ./app/Migrations

Database configuration is needed that migrations can really do the job.

return array(
        'host'      => 'localhost',
        'port'      => '3306',
        'driver'    => 'pdo_mysql',
        'charset'   => 'utf8mb4',
        'dbname'    => 'bisagasamples',
        'user'      => 'dbuser',
        'password'  => 'dbpassword',
        'defaultTableOptions' => [ 'charset'=> 'utf8mb4', 'collate' => 'utf8mb4_slovenian_ci'],

Default table options

How to set default create options for tables and columns such as default collation sequence and character set ?

‘defaultTableOptions’ => [
‘charset’=> ‘utf8mb4’,
‘collate’ => ‘utf8mb4_slovenian_ci’

To set custom character set and collation just add “defaultTableOptions” array with your specific  default settings in connection configuration array (file: migrations-db.php).

Console command interface

You can test it immediately with console command:

>php console.php list migration

2015-11-18 21_47_07-Command Prompt

With data migrations you can easily update your project database with a programmatic interface with version support.  Your database schema can grow without risk of loosing control over it.

Sample script

Empty class is generated with “migrations:generate” command. After initial generation you code migration changes manually.


namespace Bisaga\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

 * Auto-generated Migration: Please modify to your needs!
class Version20151117184031 extends AbstractMigration
     * @param Schema $schema
    public function up(Schema $schema)
        $worklogtable = $schema->createTable('worklogtable');
        $worklogtable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement'=>true]);
        $worklogtable->addColumn('workdate', 'date', ['notnull'=>true]);
        $worklogtable->addColumn('location', 'string', ['length' => 60]);
        $worklogtable->addColumn('milage', 'decimal', ['precision' => 8, 'scale'=>2]);
        $worklogtable->addColumn('starttime', 'time');
        $worklogtable->addColumn('status', 'string', ['length' => 30]);
        $worklogline = $schema->createTable('worklogline');
        $worklogline->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement'=>true]);
        $worklogline->addColumn('fromtime', 'time');
        $worklogline->addColumn('totime', 'time');
        $worklogline->addColumn('logtime', 'time');
        $worklogline->addColumn('description', 'string', ['length' => 250]);
        $worklogline->addColumn('taskcode', 'string', ['length' => 150]);
        $worklogline->addColumn('worklogid', 'integer', ['unsigned'=>true]);
        $worklogline->addForeignKeyConstraint($worklogtable, array('worklogid'), 
                        array('id'), array('onUpdate'=>'CASCADE', 'onDelete'=>'CASCADE'));

     * @param Schema $schema
    public function down(Schema $schema)
        // this down() migration is auto-generated, please modify it to your needs

After generating first migration file the file is saved in configured migrations folder (./app/Migrations/).

2015-11-19 00_53_25-silex03 - NetBeans IDE 8.0.2

Run migrations

Your migration scripts will not running automatically, you need to do it by yourself.  To execute open migrations you need to run command:  “migrations:migrate” .

>console migrations:migrate