DEV Community: Remo

How to simply get your dependencies with Nix

Remo — Sun, 19 Jan 2020 09:33:02 +0000

If you haven't hear of Nix yet - that's perfect! If you have looked into it and came off it a bit bewildered... I understand. Nix can seem very unusual at first, but it can also be of tremendous value for your software projects. I hope we can start fresh for this post. 🙂

Managing dependencies and creating reproducible environments is a Very Hard™ problem

Managing dependencies in software development can be a major pain, and we created a plethora of solutions for it: We have apt, npm, brew, pip, gem, rpm, just to name a few... We also use containers like Docker to manage software packages and create relatively reproducible environments.

All of those solutions have their limitations. Either they are specific to some language (pip for Python, gem for Ruby, stack for Haskell...), to some OS (apt for Debian/Ubuntu, rpm for Fedora & co. ...), to some part of our stack (backend/frontend), or they come with extra complexity (containers). They can be more or less reliable (npm), their results are more or less reproducible. Few solutions are able to sensibly manage multiple versions of the same package.

The solution to all dependency management pains

The solution is obvious: We need another package manager to rule them all!

Nix is getting remarkably close to being an ideal cross-OS, cross-language, cross-stack package manager, while being extremely reliable and reproducible. Of course, it's not perfect and it has its own set of trade-offs and limitations. There is a lot to explain on Nix, how there is a functional programming language called Nix, a package repository called Nixpkgs, a whole operating system called NixOS... and a lot more. But for this post, I would like to show you a simple, minimal use-case that might already be very valuable to you!

Simple example: Managing development dependencies with Nix

Let's say that for hacking on one of our projects we need curl, jq, entr and pg_tmp. pg_tmp in turn depends on the Postgres binaries. This is how to get all that with Nix:

# Install Nix
curl https://nixos.org/nix/install | sh

# Create a `shell.nix` file
cat > shell.nix << EOF
let
  pkgs = import <nixpkgs> {};
in
pkgs.stdenv.mkDerivation {
  name = "my-env";

  buildInputs =
    [
      pkgs.curl
      pkgs.jq
      pkgs.entr
      pkgs.ephemeralpg
    ];
}
EOF

# Run `nix-shell`
nix-shell

# Have all dependencies that we need ready to go on your $PATH! 🎉

That's it!

Installing Nix as above is a relatively invasive change to your system, for example you will find a new '/nix/' directory in your root file system. I would recommend to try this out in a virtual machine or Docker container first.

I guess it makes sense for me to explain a little bit more on what is happening here and how you can use it.

Parts of the Nix ecosystem that we use in this example

We are using three parts of the Nix ecosystem.

1/3: The Nix programming language

It's a very small language, the syntax can be described on one page. If you are familiar with functional programming languages (especially from the ML family, Haskell, Elm), you will be right at home. If you are not, it might seem quite alien and restrictive (no loops?!).

Everything in the Nix language is an expression, that is everything you write evaluates to a value. The language is mostly pure: Most function calls have no side effects and will always return the same result for the same inputs. The remaining impurities are mostly taken care of with cryptographic hashes. Based on this, the results of any Nix expression are highly reproducible.

2/3: The Nix packages repository

Nixpkgs is one huge, but well organized Nix expression that defines how over 40,000 software packages can be built, including all their dependencies. It's currently over 17 mb in total.

The Nix programming language keeps working with this huge expression efficient by being lazy and only evaluating the parts of it that you currently need.

Nixpkgs is where a lot of the complexity that you might encounter with Nix comes from. Abstractions like mkDerivation, callPackage etc. are great for taking out repetition and keeping Nixpkgs maintainable, but their abstractions can be quite difficult to understand. Thinking too much about some of them makes my head hurt.

3/3: The `nix-shell` utility

The nix-shell binary loads a Nix expression from a file (by default from the files shell.nix or, if that one doesn't exist, default.nix), evaluates that expression and then drops us in a shell where all dependencies defined in the expression are (magically) available.

More explanations and details

With that said, let's go through the example above in more detail.

The `shell.nix` file explained

Let's have another look at the shell.nix file that we created earlier:

let
  pkgs = import <nixpkgs> {};
in
pkgs.stdenv.mkDerivation {
  name = "my-env";

  buildInputs =
    [
      pkgs.curl
      pkgs.jq
      pkgs.entr
      pkgs.ephemeralpg
    ];
}

The let [DEFINITIONS] in [EXPRESSION] expression allows us to define variables in a local scope in the first part after let, and then evaluates the second part after in with that scope. Accordingly, the variable pkgs defined in the first part can be used in the second part.

import <nixpkgs> {} on the second line is how we import the Nixpkgs expression that is available in our Nix installation by default. I'll explain in a separate post how to 'pin' the Nixpkgs to one specific, reproducible version.

pkgs.stdenv.mkDerivation is a complex function that is used all over Nixpkgs to define how different packages can be built. All we need to know about it for now, is that it takes a set ({...}) as an argument and returns a derivation (that is 'something that can be built' in Nix parlance). The build inputs will be able on our path if we run this expression in nix-shell.

With name = "my-env"; we set the name attribute of the set we use as an argument to mkDerivation. It doesn't really matter what we put here.

buildInputs is the most important part: We set it to a list of derivations that we want to have available in our nix-shell. The items of our list are separated by spaces. We simply use attributes from the pkgs value. Remember that pkgs resulted from importing the huge Nixpkgs Nix expression, so it contains all of the over 40,000 packages in its attributes. We just pick out the right ones.

Finding the right attributes on Nixpkgs is not always simple, but a bit of googling goes a long way. For example, Google told me that pg_tmp is part of the ephemeralpg package in Nix.

The `nix-shell` utility explained

A lot of wonderful magic happens when we run nix-shell. Let's go through the most significant steps it performs:

nix-shell will look for a shell.nix file in the current directory, and immediately find it in our example.
It evaluates the Nix expression contained in the file. In most cases, the expression is quite huge as the whole Nixpkgs expression with over 40,000 packages is being imported. Keep in mind that Nix keeps this reasonably efficient by only evaluating the parts that we actually need.
Now that the Nix expression is evaluated, nix-shell knows exactly which dependencies are needed. It checks in the local cache located in the /nix/store directory, if the individual dependencies are already built. If not, it will try to get them from a binary cache at cache.nixos.org. Only as a last resort will it actually build the required packages itself.
When all dependencies are successfully cached in /nix/store, nix-shell puts together a $PATH environment variable that ties all the requested dependencies together. It then launches a new shell with that path set.

If you only want to run a single command instead of being dropped into a shell, you can use the --run flag. For example, nix-shell --run "curl http://google.com/" will query Google using the curl version that was installed by Nix.

As soon as you leave the Nix shell again with Ctrl-D, any of the changes that nix-shell made to our environment will disappear. It will look like the entr package that we defined as a dependency was never installed, with exception of the cached binary in /nix/store. The latter can be cleaned up with nix-collect-garbage or kept for a faster start up of nix-shell the next time.

More to come

Congratulations on surviving your first toe-dipping with Nix! With the example above, you already have a usable first piece that can easily be extended with additional packages. In an upcoming post, I'd like to show you how to pin your version of Nixpkgs in order to make your dependencies reproducible across time and different systems!

λ➕ Sum Types in relational databases: Yet another (IMO pretty neat) way to model them in SQL

Remo — Sat, 18 Jan 2020 09:20:48 +0000

Sum types

Sum types are an incredibly useful data-modelling feature that is predominantly used in functional programming languages. For example in Elm we can write something like:

type Animal
    = Dog { name : String, age : Integer }
    | Bird { song : String }

(It's a silly example, I know...)

The type we defined here restricts the possible values of an Animal to exactly Dog or Bird with the respective attributes. Those restrictions can make it much easier to reason about code and to check it for correctness. We can extend the type by as many constructors as we need (anyone up for Cat?).

Relational models for sum types

So how do we model this kind of type in SQL? There is no perfect solution, but you can find many good suggestions here:

While modeling a sum type with several normalized tables is arguably cleaner from a relational standpoint (e.g. one central table for Animal and related ones for Dog and Bird), it comes with a lot of complexity.

Yet another way to model sum types in SQL

The solution I want to demonstrate here is the 'nullable columns' approach from the blog posts above, with a more compact formulation of the not null constraints.

Data definition

create type animal_type
    as enum
        ( 'dog'
        , 'bird'
        );

create table animals
    ( animal_id serial primary key
    , animal_type animal_type not null
    , dog_name text
        check ((animal_type = 'dog') = (dog_name is not null))
    , dog_age integer
        check ((animal_type = 'dog') = (dog_age is not null))
    , bird_song text
        check ((animal_type = 'bird') = (bird_song is not null))
    );

First, we created a new Postgres type in which we exhaustively enumerate all constructors (dog and bird in our example).

Then we define a table where the constructor is tracked in the animal_type column. The fields of the constructors are all listed in the same table. With the constraint check ((animal_type = 'dog') = (dog_name is not null)), we make sure that the column is not null when the animal_type is dog, and that it is always null otherwise - i.e. only valid combinations.

Referring to other columns in a check constraint of a column (as we do here with animal_type) is non-standard SQL and will not be portable. I opted for it in this case, as having the constraints attached to each column makes it quite obvious that they complete and correct.

Data modification

Inserting values into this table is straightforward:

insert into animals(animal_type, bird_song) values ('bird', 'lalala');
insert into animals(animal_type, dog_name, dog_age) values ('dog', 'rex', 7);

Note that the null columns will not waste any significant amount of space, as Postgres will efficiently pack each record.

The constraints we set will make sure that only valid values can be inserted or updated:

-- This will fail as we also set the `dog_name`attribute, which belongs
-- to another constructor
insert into animals(animal_type, bird_song, dog_name)
    values ('bird', 'lalala', 'rex');

-- Fails as the `dog_age` column is missing, but it's required for `dog`
insert into animals(animal_type, dog_name) values ('dog', 'rex');

Data query

Querying the table is simple:

select * from animals;

select animal_id, dog_name, dog_age from animals where animal_type = 'dog';

On the functional side, decoding the rows back into sum types is relatively simple, we can either use a oneOf construct or something like Decode.string "animal_type" |> Decode.andThen ... in Elm.

Discussion

There is no perfect way to model sum types in relational databases. Some are very complex to set up and handle, others are more pragmatic and simpler to handle, but less normalized.

The solution proposed here will not bring any joy to normalization affictionadoes, but it is easy to handle and, due to its simplicity, also performs well.

What do you think?

♻️🐘 Disposable local Postgres databases (without containers!)

Remo — Fri, 17 Jan 2020 14:35:29 +0000

In this post, my aim is to show you how to get 'disposable' Postgres databases whenever you need them, e.g. for iterative development and testing. I will also give an example on how to use them to get automatic code reload on your database schema.

I'll take you through the individual steps of setting up custom local instances of Postgres, as they also helped to understand Postgres a lot better. If you'd like to take a shortcut and skip this learning opportunity, you can also directly use the awesome pg_tmp tool! 🙂

The approach to running custom local instances of Postgres that I'll demonstrate here is easy, fast and reliable - so much so, that you should be able to reduce the complexity of your development setup in several ways:

It will eliminate the need for using databases in containers in many cases. Containers are super convenient, but they come with some extra complexity that we should avoid when we can.
If you currently substitute your production database with something like SQLite for development and testing, this approach will allow you to work in an environment that is much closer to production and enables you to use the full power of Postgres.
It will completely eliminate the need for fiddling around with the database cluster that comes with your OS (e.g. with the postgresql package in Debian and derivatives).

Running a local one-off Postgres database

I invite you to follow along with the steps below in your shell! We will put all of them into a convenient script later, but going through them one by one will give us the best opportunity to experiment and learn.

Step 1 / 4: Create a temporary directory

Let's create a temporary directory in which our one-off database cluster will live:

tmpdir=$(mktemp -d)

mktemp -d creates a new directory and prints the new path to stdout. We capture that output with $(...) and assign it to the tmpdir variable.

Everything that our Postgres instance does will happen in this directory, it will not save any configuration, data or logs anywhere else. If we want the directory to be automatically deleted when we are done with that database, we can set up the following trap:

trap "rm -rf $tmpdir" exit

Traps are a special feature of our shell. In this case, we tell the shell to make sure that rm -rf $tmpdir will always be run when it receives the exit signal.

Step 2 / 4: Set up environment

export PGDATA="$tmpdir"

This is the only environment variable that we need for now, initdb, postgres and pg_ctl will all use it to find the directory of our one-off database cluster.

Step 3 / 4: Initialize the database cluster

With the Postgres binaries on our $PATH, we can set up our new database cluster:

initdb --no-locale

This will set up the basic directory and file structure that Postgres needs in our temporary directory (which initdb finds via $PGDATA).

If you don't have the Postgres binaries on your $PATH yet, your OS should have them in a package. On Debian, for example, you can get them with sudo apt install postgresql.

You might not need the --no-locale option, but it makes the initialization more reliable on some systems, in my experience.

Step 4 / 4: Run the database server

postgres -c listen_addresses="" -k "$PGDATA"

This tells Postgres to not listen on any TCP port. Instead, we ask it to listen on a Unix domain socket in the directory we specify with -k. For simplicity, we just reuse the $PGDATA directory. As we will be in a unique temporary directory on each run, we will never have any port or socket collisions (or any data left over from previous runs!).

That's it! Our shiny new database cluster is ready to use. 🎉🎉🎉

As a bonus, on most Linux distributions it will run in memory (and therefore extremely fast!), as the temporary directory we use will usually be on a tmpfs.

Connecting to our new database

To connect over the Unix domain socket, we need to use a URI that refers to our socket directory (for which we lazily reused $PGDATA). Let's set it as a environment variable for convenience:

export DB_URI="postgresql:///postgres?host=$PGDATA"

Let's test if we can connect with psql:

> psql "$DB_URI"
psql (12.1)
Type "help" for help.

postgres=#

Success! That URI should also work with any other tool or library that takes a Postgres connection URI.

Loading our database schema

We could load our database schema now, but it's actually a bit cleaner if we do that before we start the server. That way, we can avoid that other services in our stack get a connection before the database is fully ready. So let's insert an intermediate step:

Step 3.5 / 4: Load our database schema

mkdir "$PGDATA/setupsocket"

pg_ctl start -o "-c listen_addresses=\"\" -k $PGDATA/setupsocket"

psql "postgresql:///postgres?host=$PGDATA/setupsocket" -f app.sql

pg_ctl stop

Using a separate socket will make sure that none of the other services that we might have will be able to connect before our database is ready. The pg_ctl utility is useful here, as it will wait for the database startup and shutdown to complete before returning. When starting our database server up with pg_ctl start, we pass it the same arguments as we do to postgres as a string with the -o option.

Putting everything together in one script

Here's the full example:

tmpdir=$(mktemp -d)

trap 'rm -rf "$tmpdir"' exit

export PGDATA="$tmpdir"
export DB_URI="postgresql:///postgres?host=$PGDATA"

initdb --no-locale

mkdir "$PGDATA/setupsocket"
pg_ctl start -o "-c listen_addresses=\"\" -k $PGDATA/setupsocket"
psql "postgresql:///postgres?host=$PGDATA/setupsocket" -f app.sql
pg_ctl stop

postgres -c listen_addresses="" -k "$PGDATA"

Starting up a new Postgres instance this way takes less than a second on my machine. That's of course not as fast as SQLite, but it's probably good enough for most use-cases! And we get to use the full power of Postgres, including its extensions and the possibility to have more than one write connection.

To run tests immediately after the database is ready, e.g. a run-tests.sh script that connects to the database, we can replace the last line with the following:

postgres -c listen_addresses="" -k "$PGDATA" &

cleanup() {
    rm -rf "$tmpdir"
    kill 0
}

trap cleanup exit

./run-tests.sh $DB_URI

With this amendment, we first start Postgres in the background with &. Then, we need to adjust our trap to also kill that background process on exit. We do this by defining a function that both deletes the temporary directory and kills all background processes that are children of our current process. We then reset our earlier exit trap with that function. Finally, we can run our tests and be sure that we have a fresh, one-off database at the ready.

Reloading your database on code changes

Based on the script we created (let's save it as run-db.sh), we can easily get a database that is reloaded for every change in our code. For example, you can use entr to restart the database and rerun the tests for any change in the current directory:

find . | entr -r ./run-db.sh

Taking a shortcut with `pg_tmp`

pg_tmp is a neat tool that makes the whole process of setting up temporary Postgres databases even easier:

db_uri=$(pg_tmp)
psql $db_uri -f app.sql
./run-tests.sh $db_uri

This feels like cheating - but it works! In my opinion this is also much simpler than fiddling around with containers. As in our more manual setup we implemented before, you will never experience port collisions or remnants from earlier test runs with pg_tmp. In the background, pg_tmp pretty much does what we implemented ourselves, while adding some more optimizations to make the startup-process even faster! The manual approach I guided you through above can, however, be useful if you need more customization and control (like in this example).

I hope you will enjoy and make good use of your many new free, recyclable Postgres databases! ♻️🐘

Many thanks to Eric Radman for writing the awesome entr and pg_tmp tools!

In an upcoming post, I would also like to show how to easily get all dependencies (in this case, the Postgres binaries, pg_tmp etc.) with Nix. It's not that scary :-)

Writing SQL for humans with Literate SQL

Remo — Thu, 16 Jan 2020 17:00:51 +0000

In this post, I'll briefly explain Literate Programming and show how you can apply that concept to make your SQL scripts easier to understand (for humans!) and more maintainable.

Literate Programming

Literate programming means writing code primarily for humans instead of for machines.

The first step towards Literate Programming is to switch the comments and code in our source code. That might sound weird at first... 😛 The easiest way to explain this is with an example. Let's start with the following Python script (please don't mind any details, we'll get to SQL soon!):

# The best kind of jokes are Chuck Norris jokes, so we create a function
# to get them from the ICNDB API.

def random_joke():
     '''Get a random Chuck Norris joke from the ICNDB API.'''
     return requests.get('https://api.icndb.com/jokes/random').json()

# Next, we set up a web server to show the jokes to our users.

...

In a literate program, we would write something like this instead:

The best kind of jokes are Chuck Norris jokes, so we create a function
to get them from the ICNDB API.

> def random_joke():
>     '''Get a random Chuck Norris joke from the ICNDB API.'''
>     return requests.get('https://api.icndb.com/jokes/random').json()

Next, we set up a web server to show the jokes to our users.

...

Note that the comments are now regular plain text, and that the code to be executed has been specially marked. As a result, our explanations to humans have taken precedence over the code!

This literate style will not make sense for all kinds of code and might seem a bit silly in this small example, but it can be very useful for programs where explaining your thoughts and approach is more important than the code itself.

The concept of Literate Programming was introduced ages ago (in programming time 🙃) by Donald E. Knuth. Back in 1983 he suggested:

Instead of imagining that our main task is to instruct a computer what to do, let us concentrate rather on explaining to human beings what we want a computer to do.

Since then, a few programming languages got native support for running literate programs. For example:

Haskell: The GHC compiler also works with literate .lhs files.
Python supports a literate style in doctests.

Beyond this, 'Notebooks' have become quite popular and are very close to the concept of literate programming:

Jupyter Notebooks support mixing Python and other languages with text and Markdown.
The Azure Data Studio is doing something similar for SQL.

Literate SQL

SQL is a very expressive language where you often put much thought and testing into writing a terse query. Accordingly, applying literate programming to SQL can make a lot of sense!

I was not able to find any existing tooling that supports Literate Programming in SQL (please let me know if you do!). The Notebook solutions are close and they are fine for querying an existing schema, but they are not a good fit if we want to create a new schema or a whole database application. So let's create our own simple tooling that works for SQL combined with Markdown files.

Time for an example with SQL! Let's convert the following script into a literate program:

-- POSTS TABLE

-- Create a table in which we will track the posts created by our users.

create table posts
    ( post_id  serial primary key
    , title    text
    );

comment on table posts is
    'Posts with a title.';

comment on column posts.title is
    'Title of the post';

Note that we used comments for a few different purposes in this script:

Creating a structure or outline for our script (-- POSTS TABLE)
Explaining what we are doing and why (-- Create a table...)
Attaching comments directly to database objects (comment on .. is ...;).

The comment on ... statements are very similar to docstrings in Python, which can be accessed interactively or used to generate documentation. Likewise, the comments in SQL can be very useful for people exploring our schema (e.g. with the family of \d commands in psql, in GUI tools like DBeaver or via the OpenAPI spec generated by PostgREST).

The Markdown markup language is a good fit for turning this script into a literate SQL file:

We can describe our outline using Markdown headers (#, ## etc.)
Our explanations can be written in plain text with Markdown markup where needed (e.g. for links)
We can put our SQL code into Markdown code blocks.

So we can turn our SQL script from above into the following .md file:

# Posts table

Create a table in which we will track the posts created by our users.

    create table posts
        ( postid serial primary key
        , title text
        );

    comment on table posts is
        'Posts with a title.';

    comment on column posts.title is
        'Title of the post';

This mechanical conversion is only the starting point, of course, and we would now add further explanations, structure and links as needed.

We left the comment on ... statements as they were in the original script, as they are unchangedly useful the users of our schema. Note that we did the same with the docstrings in the Python example that we started with.

Many text editors will automatically provide the right syntax highlighting for the SQL code blocks that we embedded in the Markdown file, for example Visual Studio Code does it out of the box. It would be even better if we used fenced Markdown code blocks, where we can explicitly set the sql language for highlighting (see the full example below).

It's very easy to render the Markdown file into HTML or PDF for documentation and comfortable reading. GitHub and GitLab will, for example, automatically display pretty previews for our literate code.

Complete example of a Literate SQL script

The value of Literate Programming in SQL becomes clearer with larger scripts. The following file is a complete example of a literate SQL script:

app.sql.md

This example is much more verbose than setting up a schema would usually warrant, as it's intended to be a tutorial for new PostgREST users at some point. Please keep that in mind and don't be that be that chatty in your literal SQL files! :-)

Even though this script is used to set up a relatively complex application with multiple schemas, indexes, stored procedures and tests, it should be possible for a new reader to understand how the application works relatively quickly. The option to set links to other parts of the code and to external resources also turned out to be very useful.

But, how can we now run the SQL script that has been 'buried' in that file? We'll solve that small problem in the next section.

Tooling for Literate SQL

Literate SQL is not directly supported by the usual SQL tools like the psql utility from Postgres, but we can easily create our own tooling in order to run literate SQL programs. Short of using a full Markdown parser (e.g. in a Python script), we can use the sed utility that is available on most Unix machines to pull out the SQL code blocks from our Markdown files.

For example, if we wanted to convert a literate SQL/Markdown file app.sql.md into a regular SQL script, we can run:

sed -f md2sql.sed <app.sql.md >app.sql

You can get md2sql.sed here. It's only a two-liner sed-script to which I added many comments, as this is one of the very expressive but cryptic examples that sed is notorious for 😜.

Now that we have turned our literate app.sql.md file into a regular SQL script app.sql, we can run it directly in our database, as we would if we had written a plain SQL script in the first place:

psql -f app.sql

We are using the psql utility from Postgres in this example, but it will work similarly for any other relational database. Let's do everything in one command:

sed -f md2sql.sed app.sql.md | psql

This pulls out the SQL code blocks out of our Markdown file and uses psql to directly run them in our database.

That's all you need to write and use literate SQL scripts! 🎉

Writing SQL for humans

I'm a big fan of tools like PostgREST and of leveraging the full power of relational databases when developing applications. Using a literate style in Postgres applications (like the example for session-based authentication with PostgREST) goes a long way in making the code easier to understand and to validate.

The tooling for literate SQL that we created here is very simple and Markdown is a markup language that most developers are familiar with, so I don't think that there are any significant downsides to using Literate SQL as shown in this post. The upside of having clearer, more maintainable code can, however, be huge!

Question: Would you use literate SQL in your projects?

Please comment, I'm curious to hear your opinion and looking forward to discussing use-cases with you. Let's figure out how to make this as easy and useful as possible!

In upcoming posts, I plan to write about maintaining SQL applications without annoying migrations and how to easily create disposable Postgres databases for development and testing without containers. Please stay tuned!