my blog. for you.

Let’s talk digital.

I’m an independent IT consultant and entrepreneur in the Internet and software business. I’m interested in design, enterprise applications, web apps and SaaS products. I design and develop business solutions and applications. I help companies in terms of software quality and knowledge transfer, e.g. with Angular and Spring Boot.

Rarely Used But Useful PostgreSQL Features

I'm an avid user of SQL and the variety of - frequently overlooked - features it provides. However, unfortunately the possibilities of SQL and modern, advanced open source incarnations of RDBMS such as PostgreSQL aren't utilised to the extent they could be when developing web or enterprise applications today. More often than not SQL database are perceived as a mere data store for persisting object-oriented data, which can give rise to the famous object-relational impedance mismatch. SQL servers, PostgreSQL in particular, are capable ... Read more

R2DBC: Reactive Relational Database Connectivity for Java and Spring

As I mentioned in a blog post last week with Reactor and Spring WebFlux reactive programming has come to Java and the Spring platform. R2DBC is relatively new project that aims to "to bring a reactive programming API to relational data stores". While currently still considered experimental, R2DBC endeavours to introduce reactive programming to how we access relational databases (reactive relational, hence the R2 in the name). This means that instead of the traditional way of querying SQL databases imperatively with prepared statements ... Read more

Modern SQL by Markus Winand

Last year, I attended an event during which SQL trainer and consultant Markus Winand gave an aptly titled talk on Modern SQL: Evolution of a dinosaur (the linked video is not from that event but it shows the same talk given at another event). Markus runs a website called Modern SQL, which provides an impressive range of SQL tips, best practices, descriptions of both highly useful and lesser known SQL features and comparisons between the various relational database management systems (RDBMS) available ... Read more

Using Spring JdbcTemplate Instead of Object-Relational Mapping

Spring JDBC and Spring JdbcTemplate in particular has become my go-to tool for accessing relational databases for quite some time now and for good reason in my opinion: The common way for using data from an RDBMS in an object-oriented context used to be and for the most part still is object-relational mapping (ORM) frameworks like the Java Persistence API (JPA) or Rails' ActiveRecord. As the name suggests these frameworks attempt to map data (and concepts) from relational, set-oriented data structures, which ... Read more

Keeping database schemas up-to-date with Flyway

At least ever since Ruby on Rails' Active Record Migrations put an emphasis on keeping database schemas consistent with your software's source code both during development and in production database migration tools have become a staple in modern software development. Database migration (or database refactoring as this technique is sometimes called as well) tools allow you to apply data definition language (DDL) statements like "CREATE TABLE ..." or "ALTER ..." to databases in an automated, consistent and traceable manner instead of either ... Read more

BedquiltDB – A JSON document store for PostgreSQL

BedquiltDB is a recently released PostgreSQL extension (written mostly in PL/pgSQL) that leverages PostgreSQL's relatively new jsonb column type (available since version 9.4) to provide a MongoDB-like NoSQL document store and API on top of a PostgreSQL database. There are a few benefits to this approach. First, this allows you to start a project with a comparatively simple document-oriented API that lends itself to many use cases in terms of modern web applications. While you get this with your usual NoSQL, document-oriented databases ... Read more

Sharing and exploring data models and database patterns

Somewhat in the vein of last week's post, I'd like to share a new service called dbpatterns with you. dbpatterns allows you to create ER models in the browser and share them with other users. If a particular pattern or model suits your needs you can directly export its DDL for PostgreSQL, SQLite, MySQL or Oracle RDBMS. I really appreciate the idea of promoting and sharing best practices for database systems. While object-oriented software design patterns are rather common (though some particular ... Read more

Hands-on SQL Tips: Sentinels And Window Functions

Sometimes when displaying data in an application you want the amount of data (rows, individual data points etc.) to be limited for the app to stay responsive and to allow the user to more easily scan and browse the data set. In SQL this is commonly done using a LIMIT statement: SELECT * FROM table LIMIT 100 Now, what if you want to not only display the data but also inform the user in case the total number of rows in the table ... Read more

FnordMetric and ChartSQL: Create Charts Directly From SQL

FnordMetric is an interesting new software package that allows you to create good-looking charts (in SVG format) directly from SQL using a query language called ChartSQL. The are quite a few examples available here. A typical ChartSQL query for a timeline series chart for instance looks like this: DRAW LINECHART WITH AXIS BOTTOM AXIS LEFT; SELECT 'data' AS series, FROM_TIMESTAMP(time) AS x, value2 * 1000 AS y FROM example_data WHERE series = "measurement2"; The software currently is ... Read more

ZenQuery is now open source

Today I made ZenQuery available as open source (under the Apache License). ZenQuery is an 'Enterprise Backend as a Service' and allows you to easily access databases through a REST API without any programming knowledge required. Get SQL result sets as JSON, XML or CSV data, which can easily be processed by tools such as MS Excel or in your own applications. ZenQuery provides you with an instant REST API for your SQL queries. Anything that can be expressed by SELECT * FROM ... Read more
Next Page »