Make a game server with Java Micronaut, Jooq + Posgtgres + Flyway
In the previous post, we had our java app store characters movements and send them to UE client.
We will be using some of these tools here:
- Java and Micronaut
- Docker
- Postgres
- Gradle
In this Youtube we briefly go over what each library is and its configuration:
Note the code will be available on Github with link at the end of this post. Read on to understand what it entails.
Now we need to start introducing some persistence into the models. Before we start saving the characters information, we need to sort out user accounts. To save account data, we will want to use a SQL database, in this case a PostgreSQL database. For the time being, we’ll only use the PostgreSQL database for accounts, and not for instance character information. The reason is that it doesn’t scale well, there’s other databases such as MongoDB that will be much more suited for that.
We need the SQL database for account generation because it’s “Structured Query Language”. We can make rules for columns, such as length and uniqueness. We can also make rules in SQL statement saying to create an account if there’s not one with same name. It can be possible with other NoSQL databases, but there’s more edge cases.
Let’s get started, this is a config heavy post.
Pre-requisite: You will need to install docker on your machine for this as we will have postgres run on that.
Build.gradle
First, we will look at your build.gradle file.
Here’s how it will look at the end:
buildscript {
apply from: 'docker.gradle'
}
plugins {
id("com.github.johnrengelman.shadow") version "6.1.0"
id("io.micronaut.application") version "1.4.2"
id 'nu.studer.jooq' version '5.2'
id "org.flywaydb.flyway" version "7.7.2"
id 'com.avast.gradle.docker-compose' version '0.12.1'
}
version = "0.1"
group = "mmo_server"
def dbDriver = System.getenv('DATABASE_DRIVER') ?: 'org.postgresql.Driver'
def dbUser = System.getenv('DATABASE_USER') ?: 'mmo_server'
def dbPassword = System.getenv('DATABASE_PASSWORD') ?: 'password123'
def dbHost = System.getenv('DATABASE_HOST') ?: '127.0.0.1'
def dbPort = System.getenv('DATABASE_PORT') ?: 5432
def dbName = System.getenv('DATABASE_NAME') ?: 'mmo_server'
def dbUrl = System.getenv('DATABASE_URL') ?: "jdbc:postgresql://$dbHost:$dbPort/$dbName"
jooq {
configurations {
main {
generationTool {
logging = org.jooq.meta.jaxb.Logging.WARN
jdbc {
driver = dbDriver
url = dbUrl
user = dbUser
password = dbPassword
properties {
// SSL disabled at the moment (only running locally)
property {
key = 'ssl'
value = 'false'
}
}
}
generator {
name = 'org.jooq.codegen.DefaultGenerator'
database {
name = 'org.jooq.meta.postgres.PostgresDatabase'
inputSchema = 'public'
forcedTypes {
forcedType {
name = 'varchar'
includeExpression = '.*'
includeTypes = 'JSONB?'
}
forcedType {
name = 'varchar'
includeExpression = '.*'
includeTypes = 'INET'
}
}
}
generate {
// JPA annotations required for micronaut to annotate the generated classes correctly
fluentSetters = true
validationAnnotations = true
jpaAnnotations = true
pojos = true
daos = true
}
target {
packageName = 'com.org.mmo_server.repository.model'
}
strategy.name = 'org.jooq.codegen.DefaultGeneratorStrategy'
}
}
}
}
}
task migratePrimaryDB(type: org.flywaydb.gradle.task.FlywayMigrateTask) {
locations = ['filesystem:src/main/resources/db/postgres/']
url = dbUrl
user = dbUser
password = dbPassword
baselineOnMigrate = true
validateOnMigrate = false
}
// make sure Jooq can only run after we've finished creating db and migrating it
tasks.generateJooq.mustRunAfter(tasks.migratePrimaryDB)
tasks.compileJava.dependsOn(tasks.migratePrimaryDB)
tasks.migratePrimaryDB.dependsOn(tasks.composeUp)
repositories {
mavenCentral()
}
micronaut {
runtime("netty")
testRuntime("junit5")
processing {
incremental(true)
annotations("mmo_server.*")
}
}
dependencies {
implementation("io.micronaut:micronaut-http-client")
implementation("io.micronaut:micronaut-runtime")
implementation("io.micronaut:micronaut-validation")
runtimeOnly("ch.qos.logback:logback-classic")
annotationProcessor("io.micronaut.data:micronaut-data-processor:2.3.1")
// Lombok:
compileOnly 'org.projectlombok:lombok:1.18.16'
annotationProcessor 'org.projectlombok:lombok:1.18.16'
// Micronaut inject
annotationProcessor(platform("io.micronaut:micronaut-bom:2.0.2"))
annotationProcessor("io.micronaut:micronaut-inject-java")
implementation(platform("io.micronaut:micronaut-bom:2.0.2"))
implementation("io.micronaut:micronaut-inject")
// database configs
implementation("io.micronaut.flyway:micronaut-flyway")
implementation("io.micronaut.sql:micronaut-jdbc-hikari")
implementation("io.micronaut.sql:micronaut-jooq")
jooqGenerator 'org.postgresql:postgresql:42.2.14'
implementation 'org.postgresql:postgresql:42.2.14'
}
application {
mainClass.set("mmo_server.Application")
}
java {
sourceCompatibility = JavaVersion.toVersion("1.8")
targetCompatibility = JavaVersion.toVersion("1.8")
}
dockerCompose {
useComposeFiles = ['./docker-compose.yml']
stopContainers = true
// do not wait for debug port
tcpPortsToIgnoreWhenWaiting = [5005]
}Build.gradle explained
Docker for Postgres
The first thing we’ll look at is the docker configuration in build.gradle.
We add a plugin:
id 'com.avast.gradle.docker-compose' version '0.12.1'
This allows us to use docker compose, which is defined close to end of file:
dockerCompose {
useComposeFiles = ['./docker-compose.yml']
stopContainers = true
// do not wait for debug port
tcpPortsToIgnoreWhenWaiting = [5005]
}This indicates that we’re using a docker-compose.yml file which has the following entry:
version: '2'
services:
mmo_server:
image: postgres:9.6
environment:
- POSTGRES_DB=mmo_server
- POSTGRES_USER=mmo_server
- POSTGRES_PASSWORD=password123
- PGDATA=/var/lib/pgsql_docker/data
ports:
- "5432:5432"Here, put all the database settings that you need. We don’t add a Dockerfile as we’re using the out-of-the-box image of postgres:9.6.
To make sure that we run this docker-compose, I add this following line:
tasks.migratePrimaryDB.dependsOn(tasks.composeUp)
Which means that if we want to run our flyway migrate script (will cover soon after), gradle will need to first run compose up (essentially starting docker and postgres database).
Note that if you have local version of psql running, or another dockerised postgres running, you should probably stop them as they will have port conflicts – it will just silently fail.
to do this just check running docker containers with docker ps and docker kill <container> any container you want to shut down.
and check if your local psql is running by typing psql and if it comes up you can do something like
which psql
/usr/local/opt/postgresql@11/bin/psql
brew services stop postgresql@11if you’re running on Mac on this example.
Flyway
Flyway is used to migrate our database (add new tables, columns etc), we add it with a plugin:
id "org.flywaydb.flyway" version "7.7.2"We then configure a couple variables – make sure they match those in docker.gradle – these are to configure connections to db for tools like flyway/jooq.
def dbDriver = System.getenv('DATABASE_DRIVER') ?: 'org.postgresql.Driver'
def dbUser = System.getenv('DATABASE_USER') ?: 'mmo_server'
def dbPassword = System.getenv('DATABASE_PASSWORD') ?: 'password123'
def dbHost = System.getenv('DATABASE_HOST') ?: '127.0.0.1'
def dbPort = System.getenv('DATABASE_PORT') ?: 5432
def dbName = System.getenv('DATABASE_NAME') ?: 'mmo_server'
def dbUrl = System.getenv('DATABASE_URL') ?: "jdbc:postgresql://$dbHost:$dbPort/$dbName"
...
task migratePrimaryDB(type: org.flywaydb.gradle.task.FlywayMigrateTask) {
locations = ['filesystem:src/main/resources/db/postgres/']
url = dbUrl
user = dbUser
password = dbPassword
baselineOnMigrate = true
validateOnMigrate = false
}
Take note of the variables, they are also used for Jooq too, which we cover soon after.
Important part is that the task name is migratePrimaryDB and that the location it looks to find is in:
locations = ['filesystem:src/main/resources/db/postgres/']We mentioned that this task must run after postgres database is initialised. We also created a migration file: resources/db/postgres/V1__create_user_table.sql
CREATE TABLE IF NOT EXISTS users
(
username varchar(50) not null,
email varchar(100) not null,
password varchar(100) not null,
enabled boolean not null default false,
created_at timestamp not null,
updated_at timestamp not null,
last_logged_in_at timestamp not null,
primary key(username)
);
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username_password ON users(username, password);
CREATE TABLE IF NOT EXISTS user_roles
(
user_role_id SERIAL PRIMARY KEY,
username varchar(50) not null,
role varchar(20) not null,
unique(username, role),
foreign key (username) references users (username)
);
insert into users(username, email, password, enabled, created_at, updated_at, last_logged_in_at) values ('test', 'test1', 'test3', true, NOW(), NOW(), NOW());
insert into user_roles(username, role) values ('test', 'ROLE_USER');this creates a users and user_roles table. Feel free to modify them as you wish – or create a table that you need entirely. In the near future I am planning to cover JWT authentication so I would like to enorporate user roles as well.
I also included 2 insert statements, one for test user and one for a role for that user. You don’t need to do this, this is only for test purposes.
Jooq
Jooq is one of the key components of this post, it is kind of like an ORM here (Object Relational Mapping).
From Jooq, we want to create the database POJOs, DAOs and DSLContext in order to read or write the data from database.
The jooq configuration is fairly log, but I will highlight the important bits:
property {
key = 'ssl'
value = 'false'
}Since this is using the dockerised default database, it will not have ssl by default so you will need this set to false.
generate {
fluentSetters = true
validationAnnotations = true
jpaAnnotations = true
pojos = true
daos = true
}
target {
packageName = 'com.org.mmo_server.repository.model'
}
Here we specify what we want jooq to generate.
For micronaut its required to have: jpaAnnotations = true. Set the things you require, here we will demo the daos and pojos. Update packageName to what you like, you will just need to reference this for java component scan.
To make Jooq work with Micronaut, we will need a couple additions in resources/application.yml
micronaut:
application:
name: mmo_server
server:
port: 8081
jooq:
datasources:
default:
sql-dialect: 'POSTGRES'
jpa:
default:
entity-scan:
classpath: true
packages:
- 'com.org.mmo_server.repository.model'
datasources:
default:
url: jdbc:postgresql://127.0.0.1/mmo_server
username: mmo_server
password: password123
driverClassName: org.postgresql.Driver
hikari:
pool-name: ca-pool
minimum-idle: 2
maximum-pool-size: 5
data-source-properties:
cachePrepStmts: true
prepStmtCacheSize: 250
prepStmtCacheSqlLimit: 2048
useServerPrepStmts: true
useLocalSessionState: true
rewriteBatchedStatements: true
cacheResultSetMetadata: true
cacheServerConfiguration: true
elideSetAutoCommits: true
maintainTimeStats: false
leak-detection-threshold: 60000
connection-test-query: "SELECT 1"
validation-timeout: 50000You can see here the jpa entity scan is set to the same package name that we referenced in the jooq target.
Note the datasources credentials are the same as the ones from build.gradle.
Here we used the hikari jdbc driver, which is considered performant, so note to use it we also have to add addition to build.gradle:
implementation("io.micronaut.sql:micronaut-jdbc-hikari")Implementation
Let’s combine the micronaut controller with persistence via Jooq. For this we need to create a controller for test: java/server/account/controller/AccountController.java
package server.account.controller;
import io.micronaut.context.annotation.Parameter;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;
import server.account.dto.Account;
import server.account.service.AccountService;
import javax.inject.Inject;
@Controller("/account")
public class AccountController {
@Inject
AccountService accountService;
@Get("/get-user")
public Account getAccountByUsername(@Parameter String username) {
return accountService.fetchAccount(username);
}
}We’re going to make the AccountService that we reference: java/server/account/service/AccountService.java
package server.account.service;
import com.org.mmo_server.repository.model.tables.pojos.Users;
import server.account.dto.Account;
import server.account.repository.AccountRepository;
import javax.inject.Inject;
import javax.inject.Singleton;
@Singleton
public class AccountService {
@Inject
AccountRepository accountRepository;
public Account fetchAccount(String username) {
Users user = accountRepository.fetchByUsername(username);
return new Account(user.getUsername(), user.getEmail());
}
}And finally, we’re creating the AccountRepository: java/server/account/repository/AccountRepository.java
package server.account.repository;
import com.org.mmo_server.repository.model.tables.daos.UsersDao;
import com.org.mmo_server.repository.model.tables.pojos.Users;
import javax.inject.Singleton;
import org.jooq.Configuration;
@Singleton
public class AccountRepository {
// Use DSL context for higher performance results
// @Inject
// DSLContext dslContext;
UsersDao usersDao;
AccountRepository(Configuration configuration) {
this.usersDao = new UsersDao(configuration);
}
public Users fetchByUsername(String username) {
return usersDao.fetchOneByUsername(username);
}
}This file references the DAO, make sure you can find it like so:
Note that the DAOs are a lot less efficient than using DSLContext directly, so only use it when performance is not required.
Note that the service translates the database object (with all fields) to our own object (Account). This is because often we don’t want to surface certain information, for instance user password.
Here’s a basic DTO (Data Transfer Object) that we created for this purpose: java/server/account/dto/Account.java
package server.account.dto;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
public class Account {
String username;
String email;
}Therefore when we return account information, we just return username and email – note this is only for template purposes at this time, fill this based on your requirements.
Let’s get testing
First of all, let’s build the project and make sure it completes succesfully, it should look similar to this:
Now you should be able to run/debug your application.java
Just make sure the application indicates its started and tells you where:
Now we will use Postman to test the endpoints. We combine the url with the controller function call:
http://localhost:8081/account/get-user?username=test
Note that we’ve added a URL parameter of username with value test.
Let’s cross reference this with our controller code
@Controller("/account")
public class AccountController {The @Controller("/account") adds /account to our base url
The @Get("/get-user") adds to the /account.
So basically we have <base_url>/account/get-user and we specified it as a GET request.
The function call specifies: public Account getAccountByUsername(@Parameter String username)
This means that it takes a URL parameter, which is a string type and is used as username.
Note in our migration file, we added this line:
insert into users(username, email, password, enabled, created_at, updated_at, last_logged_in_at) values ('test', 'test1', 'test3', true, NOW(), NOW(), NOW());This added a simple row into users table with username test and email of test1.
So in our API call when we get user by username, we expect to get this user entry back.
Voila, we get exactly what we want.
Git repository
As I think this post had a lot of information and most of it is playing with configuration and settings, I think it’s best to put this in a git repository.
I will separate out the details from first post (player controller) so just copy the packages manually if you’re deciding to clone this repository.
This repository will be a clean template of a micronaut + jooq + flyway + postgres project.