Using home manager to create a sqlite database

I want to create a home manager module for Ferdi.

When using Ferdi without an online account it creates a server.sqlite database to store inforation about its workspaces and services.

I already have a module that works but the way I create and populate the database is to directly generate it in the config folder.

I was wondering if there is a way to create and write to the database in the store and then symlink it instead of creating it in the config folder directly like I do currently.

configFolder = "${config.xdg.configHome}/Ferdi";
servicesDB = "${configFolder}/server.sqlite";
initDBTablesSqlFile = ./server.sql;
home.activation = {
      createDB = [ "writeBoundary" ] ''
        $DRY_RUN_CMD mkdir -p ${configFolder}

        $DRY_RUN_CMD rm -f ${servicesDB}
        $DRY_RUN_CMD sqlite3 ${servicesDB} ".read ${initDBTablesSqlFile}"
        $DRY_RUN_CMD sqlite3 ${servicesDB} ".read ${toFile "allInsertionQueries.sql" allInsertionQueries}"

Not a fully worked example, but you could write a derivation with stdenv.mkDerivation that just runs those commands in the buildPhase, then puts the file in $out/share/ferdi/server.sqlite or something during installPhase, and then specify:

xdg.configFile."Ferdi/server.sqlite".source = "${derivation}/share/ferdi/server.sqlite";

Or add a ln -s to the installPackages section or whatever it is called by hand. I don’t believe there is machinery to put auxilliary config files from packages like this in ~/.config automatically.

Would that tool accept to not being able to write to the database?

I remember that some versions of the sqlite library fail to open a read only database, if not explicitly opening it in read only mode.

Yes, Ferdi only writes to the database when editing services or workspaces and when no user information is found.
So there are no problems with the database being read only.

Good idea.
Searching the home manager repo I have found this module which seems to do something similar to what you suggested.
I will try that next.

It worked!
Thank you!

ferdiConfigDB = pkgs.stdenv.mkDerivation {
    name = "ferdiConfigDB";

    buildInputs = with pkgs; [ sqlite ];

    dontUnpack = true;
    dontInstall = true;

    buildPhase = ''
      sqlite3 $out ".read ${initDBTablesSqlFile}"
      sqlite3 $out ".read ${toFile "allInsertionQueries.sql" allInsertionQueries}"
home.file.${servicesDB}.source = ferdiConfigDB;
1 Like

Small tip - use runCommandNoCC instead of the “full” mkDerivation for something like this as it requires less boilerplate.

1 Like

The code below also works with runCommandCC and runCommand.
Is there a reason to prefer runCommandNoCC?

ferdiConfigDB = pkgs.runCommandNoCC "ferdiConfigDB"
      buildInputs = with pkgs; [ sqlite ];
    } ''
    sqlite3 $out ".read ${initDBTablesSqlFile}"
    sqlite3 $out ".read ${toFile "allInsertionQueries.sql" allInsertionQueries}"

You won’t have the full stdenv with C/C++ compilers etc. in the build-time closure.


Hah, thanks @peterhoeg. I’ll have to rip out a bunch of frivolous uses of stdenv.mkDerivation from my configurations too.

Minor nit-pick - sqlite should be in nativeBuildInputs and not buildInputs.

1 Like

If you have the bandwidth, please consider publishing it!