Skip to content

Blog

How to send batch SQL update with spring r2dbc

How to do a single statement SQL update using r2dbc ?

Probably, the most common way is the following

import org.springframework.r2dbc.core.DatabaseClient;
ConnectionFactory factory =
DatabaseClient client = DatabaseClient.create(factory);
Mono<Map<String, Object>> actor = client.sql("INSERT INTO t_actor (first_name, last_name ) VALUES (:fName, :lName")
.bind("fName", "First")
.bind("lNane", "last")
.fetch().first();

With java multi-line support, and bind method has common sql-injection protection. This is a reasonable interface to work with.

Batch Update

Bind the same parm multiple times org.springframework.r2dbc.core.DatabaseClient.sql wouldn’t yield a batch update.

Luckily, DatabaseClient has inConnectionMany method.

Combining with Statement, one can do batch in the following fashion.

import org.springframework.r2dbc.core.DatabaseClient;
import io.r2dbc.spi.Statement;
import reactor.core.publisher.Flux;
...
DatabaseClient databaseClient;
....
databaseClient.inConnectionMany(connection -> {
Statement statement = connection.createStatement("INSERT INTO t_actor (first_name, last_name ) VALUES (?fName, ?lName"));
statement.bind("fName", ...)
.bind("lName", ...);
// statement.add need to called for non-head non-tail element
statement.add();
statement.bind("fName", ...)
.bind("lName", ...);
return Flux.from(statments.execute());
});

Statement.add needs to invoked correctly.

Save the current binding and create a new one to indicate the statement should be executed again with new bindings provided through subsequent calls to bind and bindNull.

Otherwise, java.lang.IllegalStateException: Not all parameter values are provided yet. might occur.

Why not use Batch

Batch doesn’t support bind. It only can work with string. Unless the batch update only involve constant, which sounds unlikely, sql-injection should be a concern, using Statement should be a safer approach.

Compose batches

Flux.thenMany could be use to compose two Flux.from(Statement.execute)

Transaction

One can add transaction management around the batch update in the follow fashion.

import lombok.NonNull;
import lombok.Builder;
import lombok.Value;
import org.springframework.r2dbc.core.DatabaseClient;
import org.springframework.transaction.ReactiveTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.reactive.TransactionalOperator;
import org.springframework.transaction.support.DefaultTransactionDefinition;
@Value
@Builder
class Test {
@NonNull DatabaseClient databaseClient;
@NonNull ReactiveTransactionManager tm;
@Builder.Default int isolationLevel = TransactionDefinition.ISOLATION_REPEATABLE_READ;
@Builder.Default int propagationBehavior = TransactionDefinition.PROPAGATION_REQUIRED;
TransactionDefinition getTxnDfn() {
var txnDfn = new DefaultTransactionDefinition();
txnDfn.setIsolationLevel(config.isolationLevel);
txnDfn.setPropagationBehavior(config.propagationBehavior);
return txnDfn;
}
Flux<Result> update() {
return TransactionalOperator.create(tm, getTxnDfn())
.execute(
status -> {
return batchOp();
});
}
}

Project reactor - Mono zip with void

Mono<Void>

In reactor, sometime we want to give certain operation return type like Mono<Void>, like we don’t need any information from the operation, as long as the operation succeed. In this sense, we are using Void as Unit type

awkward case of Unit type in java

a unit type is a type that allows only one value.

https://en.wikipedia.org/wiki/Unit_type

In Java, the unit type is Void and its only value is null.

https://docs.oracle.com/javase/8/docs/api/java/lang/Void.html

The Void class is an uninstantiable placeholder class to hold a reference to the Class object representing the Java keyword void.

So Void supposes to be uninstantiable, but in practices, people use it as unit type along with null

implication in project reactor

In project reactor, there is Mono.zip

Aggregate given monos into a new Mono that will be fulfilled when all of the given Monos have produced an item, aggregating their values according to the provided combinator function. An error or empty completion of any source will cause other sources to be cancelled and the resulting Mono to immediately error or complete, respectively.

this method doesn’t work well with Mono<Void>

import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import reactor.core.publisher.Mono;
import reactor.test.StepVerifier;
public class UnitTest {
@DisplayName("prove Mono void is not zip able, otherwise pipeline should have one element")
@Test
void voidIsNotzipable() {
StepVerifier.create(
Mono.zip(
Mono.<Void>fromCallable(
() -> {
return null;
}),
Mono.just(2))
.map(tuples -> 2))
.verifyComplete();
StepVerifier.create(Mono.zip(Mono.just(2).then(), Mono.just(2)).map(tuples -> 2))
.verifyComplete();
}
}

a simple workaround

we define our own unit type

import java.io.Serializable;
/** There is only one value of type Unit, () Void with null doesn't play well with Mono.zip */
public class Unit implements Serializable {
private Unit() {}
public static final Unit INSTANCE = new Unit();
}
StepVerifier.create(
Mono.zip(Mono.just(Unit.INSTANCE), Mono.just(Unit.INSTANCE)).map(tuples -> 2))
.assertNext(val -> Assertions.assertThat(val).isEqualTo(2))
.verifyComplete();

Using Tmux with Nix

TL;DR

I throw all my tmux configuration (along with some basic alacritty and starship configuration) into a nix module. I really enjoy using it so far. I hope it can give you some ideas about how to potentially improve your workflow

Longer version

Before using Nix, I had very limited experience with Tmux. I think I only used once when I ssh into some ec2 box for doing mutlipy long running jobs. After adpating Nix, I started to try out Alacirtty (I was having a hard time to get Kitty build on MacOS with Nix), I really like Alacirtty, but it doesn’t support tabs or split, the community recommends using window manager or terminal multiplexer. The idea didn’t bother me too much. (I brought a tmux 2 book long before that, never read it), I thought to myself: maybe this is a good oppurity to learn tmux. So I skip through the book, and set up tmux with basic configuration using nix home manager, and back to everyday work. I didn’t leverage tmux too much, and I often feel like I should spend some more time to tweak my configuration, so it suits my use case better. One day I came across waylonwalker’s blog, which introduce Chirs Toomy’s thoughtbot course on tmux to me. These materials really give me lots of ideas, and after some embarrassing long hours, I finally manage to put all my tmux configuration into a single nix module.

Here are some lessons and tricks I learned:

display-popup and display-menu

Most of tmux material I came cross are little bit of dated. The latest version of tmux at the moment of writing is 3.2a. I think “new” (I am not sure how new are they) commands like display-popup and display-menu are really cool. If you are using tmux, and not aware of them, I think you should give them a try. They might helps you to improve your workflow. waylonwalker’s blog has some cool ideas on how to use display-popup. There is an example how I use display-menu and display-popup

Basically diplay-menu allow you to display a menu on a specific position with a title. You choose items from Menu using arrow keys or shortcut, usually item is tmux command. You can optionally add an visual divider between items.

bind-key Tab display-menu -T "#[align=centre]Sessions" "Switch" . 'choose-session -Zw' Last l "switch-client -l" ${tmuxMenuSeperator} \
"Open Main Workspace" m "display-popup -E \" td ${cfg.mainWorkspaceDir} \"" "Open Sec Workspace" s "display-popup -E \" td ${cfg.secondaryWorkspaceDir} \"" ${tmuxMenuSeperator} \
"Kill Current Session" k "run-shell 'tmux switch-client -n \; tmux kill-session -t #{session_name}'" "Kill Other Sessions" o "display-popup -E \"tkill \"" ${tmuxMenuSeperator} \
Random r "run-shell 'tat random'" Emacs e "run-shell 'temacs'" ${tmuxMenuSeperator} \
Exit q detach"

Have a visual cue on tmux prefix press

You might want to hit whether you currently press tmux prefix key or not. I found this nice solution

Without press prefix

with prefix press

Mouse or no mouse

Maybe you think the point of tmux is to do mouse-free workflow, to enable mouse in tmux might seems wrong. But there are certain tasks like resizing panel are easier with Mouse. You can even set a command to toggle enabling mouse.

Use oh-my-zsh tmux plugin to start tmux automatically

I am using zsh and oh-my-zsh, it has a tmux plugin.

A single nix module

Nix module allows us to group all tmux related configurations (bash script, zsh and tmux) into a single place.

Integrate git hooks with treefmt and devshell

Background

I was looking for a way to integrate pre-commit-hooks.nix and numtide/devshell, I came across this github issue. It seems zimbatm added git.hooks extra module to support git hook integration in devshell. So I decide to give a try.

Code

I am configuring my devshell using nix, rather than toml file.

I think for toml version, you should just do

devshell.toml

imports = ["git/hooks"]
git.hooks.enable = true
git.hooks.pre-comment = "treefmt"

assuming you already added devshell overlay, we need to import git extra module, and enable git.hooks and add script for the hook we want to use. Here i am using numtide/treefmt.

devShell = pkgs.devshell.mkShell {
imports = [ (pkgs.devshell.extraModulesDir + "/git/hooks.nix") ];
git.hooks.enable = true;
git.hooks.pre-commit.text = "${pkgs.treefmt}/bin/treefmt";
}

assuming you have a treefmt.toml in your project root directory, you should be to ready to go

Getting started with Nix Flakes and devshell

Introduction

I finally converted my blog project to use Nix Flakes and numtide/devshell. I want to write down what I learnt about Nix Flakes and devshell.

What is Nix Flakes and Why you might care about it

Nix Flakes are a set of experimental features in the Nix package manager.

If you are not familiar with Flakes yet, here is a list of resources on it.

Some of goals of Flakes are

  • Standardized how we compose nix files and provide a single entry-point (You don’t have to have default.nix, ci.nix, shell.nix, of course you can break down your flake file into smaller nix files).

  • Standardized nix packages’ dependency management (I think with Flakes, one doesn’t need niv to pin down dependencies version. Although niv is great, and its commands are more user friendly than what Flakes offers right now)

  • a set of more user friendly nix commands (nix run, nix develop)

  • better reproducibility

How to install/uninstall Flakes

install

Right now, Nix Flakes is not enabled by default. We need to explicitly enable it.

NixOS

adding the following in the configuration.nix

{ pkgs, ... }: {
nix = {
package = pkgs.nixFlakes;
extraOptions = ''
experimental-features = nix-command flakes
'';
};
}

non-NixOS

Terminal window
nix-env -iA nixpkgs.nixFlakes

and add

Terminal window
experimental-features = nix-command flakes

to ~/.config/nix/nix.conf (if current shell user is nix trusted users) or /etc/nix/nix.conf

Install Nix Flakes installer I am not sure whether this step is still needed

Terminal window
sh <(curl -L https://github.com/numtide/nix-flakes-installer/releases/download/nix-2.4pre20210126_f15f0b8/install)

You can type nix-env --version to verify. The Flakes version should looks like nix-env (Nix) 2.4pre20210126_f15f0b8. (the version was 3.0, and version rollbacked to 2.4)

uninstall

NixOS

just revert the change in configuration.nix and do nixos-rebuild switch

non-NixOS

nix-env -iA nixpkgs.nix should bring out nix to the mainline version, and we need to revert the nix.conf change. Of course, multi-user version needs to restart nix-daemon.

How to bootstrap a Nix Flakes project

use nix flake init to generate the flake.nix, nix flake update to generate flake.lock file.

An important thing about Flakes, to improve the reproducibility, Flakes requires us to git staging all the flake.nix changes.

(Selective) Anatomy of flake.nix

Beside description, flake.nix has 2 top-level attributes

  • inputs (the dependency management part)
  • outputs the function takes the all inputs we defined and evaluate a set of attributes. (Usually our build artifacts).

inputs

a typical input might look like

{
inputs = {
nixpkgs.url = "github:nixos/nixpkgs/nixpkgs-unstable";
flake-utils.url = "github:numtide/flake-utils/master";
};
}

here, it declares two dependencies nixpkgs and flake-utils. We can use nix flake update to lock down dependencies.

We can point to a branch: inputs.nixpkgs.url = "github:Mic92/nixpkgs/master";.

or revision: inputs.nix-doom-emacs.url = "github:vlaci/nix-doom-emacs?rev=238b18d7b2c8239f676358634bfb32693d3706f3";

for non-Flakes dependency, we need to declare that.

{
inputs.bar.url = "github:foo/bar/branch";
inputs.bar.flake = false;
}

Further, we can override a Flake dependency’s input

{
inputs.sops-nix.url = "github:Mic92/sops-nix";
inputs.sops-nix.inputs.nixpkgs.follows = "nixpkgs";
}

outputs

Schema

I skipped all the nixos related attributes.

{ self, ... }@inputs:
{
# Executed by `nix flake check`
checks."<system>"."<attr>" = derivation;
# Executed by `nix build .#<name>`
packages."<system>"."<attr>" = derivation;
# Executed by `nix build .`
defaultPackage."<system>" = derivation;
# Executed by `nix run .#<name>`
apps."<system>"."<attr>" = {
type = "app";
program = "<store-path>";
};
# Executed by `nix run . -- <args?>`
defaultApp."<system>" = { type = "app"; program = "..."; };
}

where

  • <system> is the name of the platform, such as “x8664-linux”, “x8664-darwin”
  • <attr> is the attribute name (package name)
  • <store-path> is a /nix/store... path

So for each <attr>, we can have

  • check (prerequisites for build the package)
  • package
  • app (executable)

and we can define a default <attr>.

flake-utils

flake-utils ,as its name indicates, is a utility package help us write flake.

For example, it has eachDefaultSystem function take a lambda and iterate through all the systems supported by nixpkgs an hydra. So we can reuse the same lambda to build for different systems.

Using flake-utils.lib.eachSystem [ "x86_64-linux" ], you target fewer systems.

flattenTree takes a tree of attributes and flatten them into a one level key-value (attribute to derivation), which is what Flakes packages outputs expects.

flattenTree { hello = pkgs.hello; gitAndTools = pkgs.gitAndTools }

returns

{
hello = «derivation»;
"gitAndTools/git" = «derivation»;
"gitAndTools/hub" = «derivation»;
}

mkApp is a helper function to construct nix app.

here is an example

{
description = "Flake utils demo";
inputs.flake-utils.url = "github:numtide/flake-utils";
outputs = { self, nixpkgs, flake-utils }:
flake-utils.lib.eachDefaultSystem (system:
let pkgs = nixpkgs.legacyPackages.${system};
in rec {
packages = flake-utils.lib.flattenTree {
hello = pkgs.hello;
gitAndTools = pkgs.gitAndTools;
};
defaultPackage = packages.hello;
apps.hello = flake-utils.lib.mkApp { drv = packages.hello; };
defaultApp = apps.hello;
});
}

Case Study 1: nix-tree

utdemir has this nice and concise example using Flakes with a Haskell project. I think it is a great starting point to understand Flakes.

in nix-tree, the outputs looks likes

{
outputs = { self, nixpkgs, flake-utils }: # list out the dependencies
let
overlay = self: super: { # a pattern of bring build artifacts to pkgs
haskellPackages = super.haskellPackages.override {
overrides = hself: hsuper: {
nix-tree = hself.callCabal2nix "nix-tree"
(self.nix-gitignore.gitignoreSourcePure [
./.gitignore
"asciicast.sh"
"flake.nix"
] ./.) { };
};
};
nix-tree =
self.haskell.lib.justStaticExecutables self.haskellPackages.nix-tree;
};
in {
inherit overlay;
} // flake-utils.lib.eachDefaultSystem (system: # leverage flake-utils
let
pkgs = import nixpkgs {
inherit system;
overlays = [ overlay ];
};
in {
defaultPackage = pkgs.nix-tree;
devShell = pkgs.haskellPackages.shellFor { # development environment
packages = p: [ p."nix-tree" ];
buildInputs = with pkgs.haskellPackages; [
cabal-install
ghcid
ormolu
hlint
pkgs.nixpkgs-fmt
];
withHoogle = false;
};
});
}

Let’s break down the function a little bit. The outputs have 2 dependencies nixpkgs and flake-utils.

First thing, it construct an overlay contains the local nix-tree as Haskell package and a derivation for the executable.

Next, for eachDefaultSystem, it initialize the new nixpkgs with relevant system and overlay, and construct defaultPackage and devShell. devShell is Nix Flakes’ version of nix-shell (without -p capability, if you want to use nix-shell -p, there is nix shell). We can start a development shell by nix develop command. There is nix develop integration with direnv

How to use non-flake dependency

Let’s say if I want to use easy-purescript-nix in my project. First I need to add it as inputs

{
inputs.easy-ps = {
url = "github:justinwoo/easy-purescript-nix/master";
flake = false;
};
}

there are more than one packages in easy-purescript-nix. I can added them into an overlay and add the overlay into the pkgs.

{
outputs = {self, nixpkgs, easy-ps}: {
overlay = final: prev: {
purs = (prev.callPackage easy-ps {}).purs;
spago = (prev.callPackage easy-ps {}).spago;
} // (
flake-utils.lib.eachDefaultSystem (system:
let
pkgs = import nixpkgs {
inherit system;
overlays = [self.overlay];
};
in rec {
devShell = {
packages = [
pkgs.purs
pkgs.spago
];
};
};
));

On the another hand, you can use flake-compat to use Flakes project from mainline (legacy) Nix.

Case Study 2: todomvc-nix

todomvc-nix is a much more complex example. It needs to build Haskell (even ghcjs, which usually is more chanlleing to build) and rust source code.

You can checkout the code yourself to see how one can override different haskell packages and using numtide/devshell to customize the nix develop experience.

numtide/devshell

devshell (not to confuse with Nix Flakes devShell) is numtide project to customize per-project developer environments. The marketing slogan is “like virtualenv, but for all the languages”.

I think it is fair to say that devshel is still early stage of development. (Although one can argue almost every thing mentioned in this article is in the early stage of development.) Lots of usages are subject to future changes. Using devshell probably requires you to read throught the source code. But I think devshell is a really exicting project.

How to “install” devshell

devshell does aim to support non-Flakes and Flakes Nix. I am only going to cover the Flakes version, the non-Flakes usage is covered at the devshell’s doc.

First thing is to declare devshell as an input, and we need to import devshell overlay into our instance of nixpkgs.

flake-utils.lib.eachSystem [ "x86_64-darwin" ] (system:
let
pkgs = import nixpkgs {
inherit system;
overlays = [ devshell.overlay overlay ];
};

the overlay would bring devshell attribute into the pkgs. devshell has functions like mkShell and fromTOML. fromTOML allows us to configure the devshell using TOML file.

{
# assuming we import devshell.overlay
# and there is devshell.toml file
devShell = pkgs.devshell.fromTOML ./devshell.toml;
}

mkShell allows us to use Nix experssions.

{ devShell = pkgs.devshell.mkShell { name = "blog-dev-shell"; }; }

Here is My devshell config. devshell document page has a list of configuration options.

environments variables

This is kind of like shellHook in the old mkShell function. We can define environment variables in our devshell.

TOML version looks like

[[env]]
name = "GO111MODULE"
value = "on"

Nix version looks little verbose

{
devshell = pkgs.devshell.mkShell {
env = [{
name = "NODE_ENV";
value = "development";
}];
};
}

packages

Of course, we can define packages for our devshell TOML version

[devshell]
packages = [
"go"
]

Nix counterpart is more flexible, imagine I have a custom haskellPackages with lots of overlays, I can reference it in flake.nix pretty easily.

{
devshell =
pkgs.devshell.mkShell { packages = [ myHaskellEnv pkgs.nixpkgs-fmt ]; };
}

commands

I think this is a cool feature in devshell. Using Nix expressions we can define some common commands for your project.

{
devshell = pkgs.devshell.mkShell {
commands = [
{
name = "cssWatch";
category = "css";
command =
"ls tailwind/*.css | ${pkgs.entr}/bin/entr ${pkgs.yarn}/bin/yarn build";
}
{
name = "siteClean";
category = "static site";
help = "clean static site files";
command = "${pkgs.blog}/bin/blog clean";
}
{
name = "yarn";
category = "javascript";
package = "yarn";
}
];
};
}

Everytime, you can enter devshell, all commands and a motd (message of the day) will be displayed. the commands are grouped by their category. packages won’t show up in there.

Terminal window
🔨 Welcome to blog-dev-shell
[css]
cssWatch
[general commands]
menu - prints this menu
[javascript]
node - Event-driven I/O framework for the V8 JavaScript engine
yarn - Fast, reliable, and secure dependency management for javascript
[purescript]
purs
spago
[static site]
siteClean - clean static site files
siteWatch - Watch static site files
[utility]
entr - Run arbitrary commands when files change

modules

Right now, all the build-in modules are in devshell/extra directory.

  • git hook
  • locale
  • c
  • go
  • rust

One can write custom module. For example, nixpkgs haskell-modules has a nice shellFor function, we can turn it into a haskell module for devshell.