docs: document local devel postgresql usage

1 files changed, 74 insertions, 0 deletions

diff --git a/readme.md b/readme.md
index 795b00e..4b6e222 100644
--- a/readme.md
+++ b/readme.md
@@ -72,6 +72,80 @@ retained.
 * `./flake.nix`: project runtime and development environment description
 
 
+## Development environment
+
+The development and execution environment is fully described using the [Nix]
+language and package manager as a [Nix Flake] in `./flake.nix`. This allows
+creating reproducible environments containing all the software dependencies of
+the program itself, as well as optional development tools.
+
+(_Note: this projects requires Nix version 2.4 or higher_).
+
+[Nix]: https://nixos.org/guides/how-nix-works.html
+[Nix Flake]: https://www.tweag.io/blog/2020-05-25-flakes/
+
+This environment can be used on NixOS, MacOS, or any Linux system having Nix
+installed. In principle, Nix should also be usable on Windows through the
+Windows Subsystem for Linux ([WSL]) compatibility layer. Full virtual machines
+and containers can also be derived from the same description file.
+
+[WSL]: https://docs.microsoft.com/en-us/windows/wsl/about
+
+All the commands in this section have to be run within the provided development
+shell, which can be entered by running the following command at the root
+directory of the project:
+
+```sh
+nix develop
+```
+
+### Local database
+
+The Nix Flake development shell provides its own self-contained PostgreSQL
+server, configured to operate independently of any other instances running on
+the same system.
+
+All data are written to the `./development_database/pgadata` directory. The
+database server can be initialised by running the following command:
+
+```sh
+initdb --no-locale --encoding UTF8 --auth-host reject --auth-local peer
+```
+
+The local development PostgreSQL server can then be started by running the
+following command, with the `$PGHOST` environment variable automatically set
+by the development shell:
+
+```sh
+postgres -h "" -k "$PGHOST" -d 2
+```
+
+This server listens to local requests through a UNIX domain socket located at
+`./development_database/.s.PGSQL.5432`, to which programs run in the
+development shell will implicitly automatically connect to.
+
+The development shell ships with both the `psql` and [`pgcli`][pgcli] tools to
+interact directly with the database. The latter provides additional features
+such as syntax highlighting and better auto-completion.
+
+[pgcli]: https://www.pgcli.com/
+
+A new local database for the application can be created and its table can be
+initialised with:
+
+```sh
+createdb app
+psql app < ./sql/tables.sql
+```
+
+Should the need arise, this database can be deleted with the following command
+before being created and initialised again:
+
+```sh
+dropdb app
+```
+
+
 ## Copyright and licensing
 
 Copyright (C) 2021 Pacien TRAN-GIRARD.