PostgreSQL Composite Types to reduce boilerplate code from you database schema

Simon Jakubowski published on 03.30.2023

PostgreSQL is a powerful and flexible open-source relational database management system. One of its most useful features is the ability to define custom composite types (Since Postgres 11), which are user-defined data types that combine multiple fields of different types into a single logical entity. In this article, we’ll explore the benefits of using composite types in PostgreSQL and how to use them in your database schema.

Create Composite Type

To define a composite type in PostgreSQL, you use the CREATE TYPE statement, followed by the name of the type and a list of its component fields and types. For example, here’s how you might define a audit type:

Zero downtime deployment with breaking DB-Schema change - by example

Simon Jakubowski published on 01.12.2023

When running multiple Server nodes, sharing the same Database, it becomes more and more important to handle backwards compatibility on the database especially during Deployments.

When we start da Deployment, a new Server node is being deployed to a cluster. This node will then migrate the Database to it’s designated schema version. If this new version is not backwards compatible, the currently running Server instances will run into several problems, reading or writing to the database.

Testcontainers | Postgres: Postgis with Spring and Spock

Simon Jakubowski published on 10.18.2022

A great way to include all the required resources for integration tests, Testcontainers provides an easy way to start docker containers from within your testcode. This removes the necessity to create test docker-compose.yml files and write README.md code on how to even run a test.

Here is a short example using testcontainers with Spring and Spock on a Postgis / Postgres database.

@SpringBootTest
@AutoConfigureMockMvc
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ContextConfiguration(initializers = [Initializer.class])
class FieldControllerTest extends IntegrationTest {

  @Shared
  public static JdbcDatabaseContainer postgreSQLContainer = new PostgisContainerProvider()
    .newInstance()
    .withDatabaseName("integration-tests-db")
    .withUsername("sa")
    .withPassword("sa");


  static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

    void initialize(ConfigurableApplicationContext configurableApplicationContext) {
      postgreSQLContainer.start()
      TestPropertyValues.of(
        "spring.datasource.url=" + postgreSQLContainer.getJdbcUrl(),
        "spring.datasource.username=" + postgreSQLContainer.getUsername(),
        "spring.datasource.password=" + postgreSQLContainer.getPassword()
      ).applyTo(configurableApplicationContext.getEnvironment());
    }
  }

  @Autowired
  MockMvc mockMvc

  @Autowired
  FieldRepository fieldRepository

  void setup() {
    fieldRepository.save(new Field(
      UUID.fromString('ad68f894-c16b-4953-b577-7cddb3e85ae5'), "initSampleField",
      new Polygon(
        PositionSequenceBuilders.variableSized(G2D.class)
          .add(5.8208837124389, 51.0596004663904)
          .add(5.83490292265498, 51.0571257015788)
          .add(5.87078646658134, 51.0451607414904)
          .add(5.79146302423308, 51.0612386272784)
          .add(5.8208837124389, 51.0596004663904)
          .toPositionSequence(),
        CoordinateReferenceSystems.WGS84
      )
    ))
  }

  ...
}

To integrate testcontainers into your project, you’ll need the following dependencies:

Postgres JSON beginner Class

Simon Jakubowski published on 07.10.2021

Operators see: https://www.postgresql.org/docs/9.5/functions-json.html

Create column using jsonb

CREATE TABLE article (
    id      UUID PRIMARY KEY,
    properties JSONB NOT NULL
);

Insert values

INSERT INTO article
VALUES (gen_random_uuid(), '{
  "price": 12.1,
  "name": "Kugelschreiber",
  "tags": {
    "manufacturer": "Siemens",
    "discounted": true
  }
}')

Select value from JSON

select
  properties -> 'price'
  properties ->> 'price'
from article

-> will extract the value as JSONB
->> will extract the value as text

Compare values

SELECT *
FROM article
WHERE CAST(properties ->> 'price' AS NUMERIC) > 10

Check where value is contained

SELECT *
FROM article
WHERE properties -> 'tags' ? 'discounted'

Groovy 3 Release Note Hightlights

Simon Jakubowski published on 07.09.2021

I’m a huge Fan of groovy and therefore always excited about new additions and changes to the groovy lang. Therefore, here’s a list of my personal highlights of the groovy 3 release.

The complete release-notes can be found here: Groovy 3 Release Notes:

`!in` Operator

4 !in [5, 6, 19] // true

`!instanceof` Operator

LocalDate.now() !instanceof Temporal // false
LocalDate.now() !instanceof Instant // true

`?=` Elvis Operator

def last = null
last ?= 'Doe'
last == 'Doe'

`===` and `!==` Identical Operators

def emp1 = new Employee(name: "Simon Jakubowski")
def emp2 = new Employee(name: "Simon Jakubowski")
def emp3 = emp1

emp1 == emp2 // true
emp1 === emp2 // false
emp1 === emp3 // true

safe map, list, array access

def emps = [
  "boss"     : ["joe"],
  "developer": ["sja", "sro", "tti"]
]

emps["boss"] // ["joe"]
employees["boss"] // throws NPE
employees?["boss"] // null

Support for lambda expressions + Method References

["test", "arba"]
  .stream()
  .map(String::toUpperCase)
  .collect(Collectors.toList())

Reduction of the main groovy package

org.codehaus.groovy:groovy:3.0.8
org.codehaus.groovy:groovy-json:3.0.8
* groovy.json.JsonSlurper
org.codehaus.groovy:groovy-xml:3.0.8
* groovy.xml.XmlSlurper

Creating a blog using Gatsby and host it on GitHub Pages in 15 Minutes for free

Simon Jakubowski published on 07.09.2020

Of course you would write the first blog post about creating the actual blog itself. So do I.

Create Gatsby blog

Install Gatsby

First of all, we need to install Gatsby which will generate the static blog pages

$ brew install gatsby-cli

Create a blog based on a Template

$ gatsby new <name_of_the_blog> <template_url>
//e.g.:
$ gatsby new blog https://github.com/niklasmtj/gatsby-starter-julia

Start the blog and customise it

The blog gets refreshed automatically when you save the files in your IDE