How should we initialize our postgres database/user?

[jennifer-bowser]

We removed our postgres_init.sql file during our refactor, which was just a very simple SQL file that initializes our database and database user:

DROP DATABASE IF EXISTS anyvar;
DROP USER IF EXISTS anyvar;

CREATE USER anyvar WITH PASSWORD 'anyvar-pw';
CREATE DATABASE anyvar WITH OWNER anyvar;

We also had a nearly identical version of this file for our test database/test user.

However, we haven't replaced these scripts with anything yet, so we need some mechanism to initialize these things. Our options are:

1. Set these as environment variables in our docker compose file + provide a short command that people can copy/paste in our docs if they're not using docker.
2. Re-add these files to our repo (though perhaps in a different location? More on this below)

I originally opted for option 2 and created a PR for this, but James brought up several excellent points that I believe merited further discussion. I'm re-posting his comments here:

A few thoughts

1. I would personally vote against checking files meant for testing into src/
2. personally I think it's easier to set this stuff either
   via env vars on the container, or since it's short, just as a single psql -c command to copy and paste out of the docs. In particular, the .sql files are probably less helpful to anyone installing via PyPI because they'll be buried in .venv/lib/python/site-packages/, so it might be nice to consider a more general solution
3. we'll need to make sure that package data is declared in pyproject.toml if it should be included in the distributed source https://stackoverflow.com/questions/69647590/specifying-package-data-in-pyproject-toml. If it should not be included in the distributed source then maybe there's a better home for it than /src?

[jennifer-bowser]

I'm now definitely in favor of Option 1, because a) this feels like the least amount of work for new users setting up AnyVar for the first time, and b) it avoids the where-should-these-scripts-live problem

[jsstevenson]

Done, we set via env vars where it's needed + include sql commands in docs