Category: PostgreSQL

ETL With Scala and Native SQL – Part 1

For almost as long as software engineers have been using object-oriented programming languages to access relational databases, we have been lamenting the “impedance mismatch” between the two. The lack of straightforward language constructs to allow data to move cleanly back and forth between objects and tables led to the rise of object-relational mapping tools. ORM tools provide the illusion that the programmer does not need to know SQL. But for any nontrivial data access, the ORM just obscures the engineers’ view of the native SQL of the RDBMS which they are trying to generate indirectly by manipulating JPA annotations or Spring Data method names.

Functional programming can be the solution to the impedance mismatch. It allows for the wiring between objects and tables to be expressed cleanly and concisely without the unwieldy amount of code needed for using native SQL with the JDBC API. In short, OOP + FP + SQL = Better Programs.

This requires a language that truly supports both OOP and FP, along with a database library which does not try to be an ORM. In this article we look at using Scala with the Anorm library.

Continue reading

Modeling Hierarchical Data in Postgres

Hierarchical data has historically been a challenge with relational databases. There are well-known solutions for implementing a hierarchy in a purely relational fashion, but their complexity and performance are not generally desirable. To overcome this, some modern RDBMSs have a special data type for hierarchical values. In the case of Postgres, this data type is the ltree.

The ltree data type allows for storing paths similar to filesystem paths or DNS records, and provides the ability to perform hierarchical queries which are much cleaner and more efficient than their purely relational counterparts. But often we want to have further constraints, such as requiring the existence of all ancestors in each path, and this requires additional work. Here we develop a complete implementation of the common use case of an entity hierarchy with the same properties as a filesystem directory tree.

ltree Basics

The ltree documentation refers to each ltree value as a path, and each component of the path as a label. A path is a series of labels separated by Continue reading