Using home manager to create a sqlite database

Help
1

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 = lib.hm.dag.entryAfter [ "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}"
      '';
    };
4 Likes
2

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.

3

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.

4

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.

5

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.

6

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
7

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

1 Like
8

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}"
  '';
9

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

2 Likes
10

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

11

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

1 Like
12

If you have the bandwidth, please consider publishing it!