Is there some way to specify the "schema" to install the Jupyterhub database into as part of a configuration property?

JupyterHub
1

We are running Jupyterhub’s database in Postgres 11+. Our DBAs have a requirement that all database tables must run in their own schema and not in the default “public” schema. Is there some way to specify the "schema to install the Jupyterhub database into (along with the alembic tables) as part of a configuration property?

Really do not relish the idea of having to modify code to accomplish this.

2

I’m not sure if this is quite what you are after, but from the sqlalchemy docs, you can dump the current creation sql to a string with:

from sqlalchemy import create_engine
from jupyterhub.orm import Base

def dump(sql, *multiparams, **params):
    print(sql.compile(dialect=engine.dialect))

engine = create_engine('postgresql://', strategy='mock', executor=dump)

Base.metadata.create_all(engine, checkfirst=False)

which gives this output for jupyterhub 1.3.0. That should be everything except the alembic table itself. That can be seen with:

python -m jupyterhub.dbutil alembic stamp --sql 4dc2d5a8c53c

Note: in general, python -m jupyterhub.dbutil alembic is a shortcut to run alembic:

  • loading the db url from your jupyterhub_config.py, and
  • locating the alembic revisions bundled with jupyterhub

Any alembic command subcommand or argument can be used there.

Assuming you are in a working directory with a juptyerhub_config.py that has:

c.JupyterHub.db_url = "postgres://"

which gives:

CREATE TABLE alembic_version (
    version_num VARCHAR(32) NOT NULL,
    CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);

That last bit is static and not sensitive to jupyterhub version, so it might be more work than it’s worth to autogenerate it, instead just adding it to the auto-generated jupyterhub schema.

For the upgrades once you are up and running, if you do upgrades with alembic via python -m jupyterhub.dbutil alembic upgrade from:to --sql, you can do the upgrades offline with:

# get the current revision of your db
$ python -m jupyterhub.dbutil alembic current
896818069c98
# get the latest version your db needs to  upgrade to
$ python -m jupyterhub.dbutil alembic heads
4dc2d5a8c53c (head)
# prepare the upgrade offline (emit sql, don't run it)
$ python -m jupyterhub.dbutil alembic upgrade 896818069c98:4dc2d5a8c53c --sql

However, I’ve never done offline upgrades, so it’s entirely possible that there are some assumptions in our upgrade/downgrade scripts that are not met when running offline (e.g. checking for the existence of tables) in which case the alembic scripts may need to be modified to do the upgrades.

3

This is awesome.

(THANK YOU SO MUCH)^1000.

1 Like