A PostgreSQL sandbox

A Nix-based sandbox to play with the Pagila example database using psql and ghci.

The database will be created and initialized the first time we enter nix develop:

postgresql-playground$ nix develop
waiting for server to start.... done
server started
(dev) postgresql-playground$ 

Once within nix develop, we can connect using psql:

(dev) postgresql-playground$ psql
psql (16.6)
Type "help" for help.

pagila=# \d actor

The Cabal project

The repo contains a Haskell Cabal project to experiment with the rel8 1.6.0.0 and persistent / esqueleto 3.5.14.0 SQL DSLs.

The Cabal project must be built separately while inside nix develop:

(dev) postgresql-playground$ cabal update
(dev) postgresql-playground$ cabal build

Playing with rel8

Start a ghci repl with module PagilaRel8 in scope:

(dev) postgresql-playground$ cabal repl lib:pagila-rel8
ghci> HasqlRun {hasqlRun, release'} <- acquire'
ghci> paymentsByCustomer & select & Rel8.run & hasqlRun

You can also run the postgresql-playground-rel8 executable that performs some example queries:

(dev) postgresql-playground$ cabal run postgresql-playground-rel8

Playing with esqueleto

Start a ghci repl with module PagilaEsqueleto in scope:

(dev) postgresql-playground$ cabal repl lib:pagila-esqueleto
ghci> selectSomeAddresses & run

You can also run the postgresql-playground-esqueleto executable that performs some example queries:

(dev) postgresql-playground$ cabal run postgresql-playground-esqueleto

To delete the database

Exit nix develop, then delete the folders .pg/ and pg_sockets/:

(dev) postgresql-playground$ exit
$ rm -rf .pg/ .pg_sockets/

Links

Pagila

• github
• Structure of the Salika database. tables. views.

Haskell SQL DSLs

• Haskell SQL DSLs, why do you use them?
• Haskell + SQLite - SQLite.Simple, or esqueleto, or something else?
• Zelenya—How to use PostgreSQL with Haskell

Rel8

• Rel8
• official documentation
• on Hackage

Persistent / Esqueleto

• persistent
• persistent-postgreql
• The Persistent tutorial in the Yesod book
• the Persistent entity syntax
• esqueleto
  • Database.Esqueleto.Experimental
• Hachyderm post

Other stuff

• Using PostgreSQL in a nix-shell.
• Postgresql/Postgis inside nix-shell with sqitch and default postgres user.
• Nix Recipe: Setup Postgresql.
• Unable to setup postgres in nix-shell.
• trap
• About glibc-2.40.

Some psql commands

explain (verbose true, format json) select actor_id, first_name from actor where actor_id = 1;
prepare foostmt (integer) as select actor_id, first_name from actor where actor_id = $1;
explain (verbose true, format json) execute foostmt(1);
deallocate foostmt;

Interesting that it catches type errors in the prepared statement's parameters:

foodb=# prepare foostmt2 (bytea) as select actor_id, first_name from actor where actor_id = $1;
ERROR:  operator does not exist: integer = bytea
LINE 1: ... select actor_id, first_name from actor where actor_id = $1;
                                                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Explaining and describing queries

• PREPARE and DEALLOCATE

• EXPLAIN

• PQdescribePrepared

  Submits a request to obtain information about the specified prepared statement

  On success, a PGresult with status PGRES_COMMAND_OK is returned. The functions PQnparams and PQparamtype can be applied to this PGresult to obtain information about the parameters of the prepared statement, and the functions PQnfields, PQfname, PQftype, etc provide information about the result columns (if any) of the statement.