Repository: fhcrc/taxtastic (language: python)

Issue:
Improve efficiency of postgres taxonomy database creation
Creation of an sqlite database completes in just a few minutes; I suspect that the bottleneck for the postgres load is the foreign key indexes. See if we can create the indexes after the initial data load. 


Discussion / hints:

Fix patch:
diff --git a/.github/workflows/docker-publish.yml b/.github/workflows/docker-publish.yml
index b3212237..954e536a 100644
--- a/.github/workflows/docker-publish.yml
+++ b/.github/workflows/docker-publish.yml
@@ -14,10 +14,6 @@ jobs:
       -
         name: Checkout
         uses: actions/checkout@v3
-      -
-        run: |
-          python setup.py sdist
-          mv dist/taxtastic-*.tar.gz taxtastic.tar.gz
       -
         name: Set up Docker Buildx
         uses: docker/setup-buildx-action@v1
diff --git a/.github/workflows/gh-pages.yml b/.github/workflows/gh-pages.yml
index 6164b7ec..da2c4ee0 100644
--- a/.github/workflows/gh-pages.yml
+++ b/.github/workflows/gh-pages.yml
@@ -14,8 +14,11 @@ jobs:
       -
         name: Build Sphinx
         run: |
-          pip install sphinx
-          python setup.py build_sphinx --source-dir docs --build-dir .
+          python -m pip install -r requirements.txt
+          python -m pip install .
+          python -m pip install sphinx
+          python dev/helptext.py taxit.py -d docs/_helptext
+          sphinx-build docs html
       -
         name: Deploy
         uses: peaceiris/actions-gh-pages@v3
diff --git a/.gitignore b/.gitignore
index e026c77a..b4ede9b1 100644
--- a/.gitignore
+++ b/.gitignore
@@ -12,6 +12,7 @@ html
 MANIFEST
 TODO
 src/
+.DS_Store
 
 # due to using tox and pytest
 .tox
diff --git a/CHANGES.rst b/CHANGES.rst
index 8ee067a0..638f7cd2 100644
--- a/CHANGES.rst
+++ b/CHANGES.rst
@@ -2,9 +2,14 @@
  change log for taxtastic
 ==========================
 
-0.9.5-dev
-=========
+0.10.0
+======
 
+* drop support for Python versions before 3.8
+* works with sqlalchemy 2+, drop support for sqlalchemy 1.x series
+* drop indexes and constraints before creating Postgresql database
+* restructure Dockerfile, uses python:3.11-slim-bullseye base image
+* simplify setup.py and remove deprecated features
 * major speed improvements to subcommand namelookup
 * added support text file support for update_taxids
 
diff --git a/DEVELOPING.rst b/DEVELOPING.rst
index 397d853d..54be3bd5 100644
--- a/DEVELOPING.rst
+++ b/DEVELOPING.rst
@@ -10,7 +10,6 @@ additional dependencies. In your active virtualenv::
 
   pip3 install -r requirements-dev.txt
 
-
 Git workflow
 ============
 
@@ -28,6 +27,22 @@ for development. Briefly (for those working on the main fork):
   result, the head of the `master` branch is always on a release
   version).
 
+Unit tests
+==========
+
+Run all unit tests from an active virtualenv as follows::
+
+  python -m unittest discover
+
+Run a subset of tests by providing a pattern::
+
+  % python -m unittest discover -k test_subcommands.TestAddNode.test_new_nodes03 -v
+  test_new_nodes03 (tests.test_subcommands.TestAddNode) ... ok
+
+  ----------------------------------------------------------------------
+  Ran 1 test in 0.151s
+
+  OK
 
 Preparing a release
 ===================
@@ -40,8 +55,6 @@ Preparing a release
 1. checkout master and merge dev
 1. tag the commit to reflect the new version number:
    `git tag -a -m "some message" vX.Y.Z`
-1. update the docs (details below): `(cd docs && make html)`
-1. publish the updated docs: `ghp-import -p html`
 1. `git push origin master`
 1. `git push --tags`
 1. update PyPi (see below)
@@ -78,8 +91,8 @@ Note that the html directory needs to contain a file named `.nojekyll`
 to prevent GitHub from ignoring pages with leading underscores (like
 `_static`), so the Makefile adds one
 
-Thankfully, publishing to the GitHub page for the bioscons repository
-is easy using `ghp-import`::
+Manual deployment should not be necessary since there is a GH action
+to build and deploy. However, is manual deployment is necessary, use
+`ghp-import`::
 
   ghp-import -p html
-
diff --git a/Dockerfile b/Dockerfile
index 689310e7..f103f570 100644
--- a/Dockerfile
+++ b/Dockerfile
@@ -1,15 +1,17 @@
-FROM python:3.9-bullseye
+FROM python:3.11-slim-bullseye
 
-COPY taxtastic.tar.gz /src/taxtastic.tar.gz
-COPY dev/install_pplacer.sh /usr/local/bin/install_pplacer.sh
+RUN apt-get -y update && \
+    apt-get install -y unzip wget
 
-RUN apt-get update && \
-    apt-get install -y unzip --no-install-recommends && \
-    /usr/local/bin/install_pplacer.sh /usr/local 1.1.alpha19 && \
-    rm -rf /var/lib/apt/lists/* && \
-    apt-get purge -y --auto-remove unzip && \
-    pip install /src/taxtastic.tar.gz
+WORKDIR /opt/build
+COPY dev/install_pplacer.sh /opt/build/install_pplacer.sh
+RUN /opt/build/install_pplacer.sh /usr/local
 
+COPY setup.py MANIFEST.in README.rst requirements.txt /opt/build
+COPY taxtastic /opt/build/taxtastic
+RUN python3 -m pip install --constraint requirements.txt .
+
+WORKDIR /opt/run
 RUN mkdir -p /app /fh /mnt /run/shm
 
 CMD ["taxit", "-h"]
diff --git a/MANIFEST.in b/MANIFEST.in
index 155a35c1..576a0efc 100644
--- a/MANIFEST.in
+++ b/MANIFEST.in
@@ -1,20 +1,11 @@
-exclude docker docker/*
-exclude singularity singularity/*
-exclude dev dev/*
-exclude README.txt readme.txt
-include *.py
-include *.in
-include *.rst
-include *.txt
-include LICENSE
-include Makefile
-include setup.py
+recursive-include taxtastic/data *
+recursive-include taxtastic/scripts *
+recursive-include taxtastic/subcommands *
+recursive-include taxtastic/templates *
 
-global-exclude *.pyd
-global-exclude *.pyc
-global-exclude .git*
-global-exclude .DS_Store
+recursive-exclude .DS_Store
+global-exclude */__pycache__/*
 
-# added by check-manifest
-include Dockerfile
-recursive-include pplacer *.zip
+exclude Dockerfile
+exclude tests
+recursive-exclude testfiles/*
diff --git a/README.rst b/README.rst
index 5a431ed0..6363b6cd 100644
--- a/README.rst
+++ b/README.rst
@@ -3,30 +3,27 @@
 ===========
 
 Taxtastic is a python package used to build and maintain reference
-packages-- i.e. collections of reference trees, reference alignments,
+packages, i.e. collections of reference trees, reference alignments,
 profiles, and associated taxonomic information.
 
 .. image:: https://travis-ci.org/fhcrc/taxtastic.svg?branch=master
     :target: https://travis-ci.org/fhcrc/taxtastic
 
-We love it, but what is it?
-===========================
-
 * quickstart_
 * `full documentation`_
 
 A script named ``taxit`` provides a command line interface::
 
-  % taxit  --help
+  % taxit -h
   usage: taxit [-h] [-V] [-v] [-q]
-	       {help,add_nodes,add_to_taxtable,check,composition,create,extract_nodes,findcompany,get_lineage,info,lonelynodes,new_database,refpkg_intersection,reroot,rollback,rollforward,rp,strip,taxids,taxtable,update,update_taxids}
-	       ...
+               {help,add_nodes,add_to_taxtable,check,composition,create,extract_nodes,findcompany,get_descendants,get_lineage,info,lineage_table,lonelynodes,namelookup,new_database,refpkg_intersection,reroot,rollback,rollforward,rp,strip,taxids,taxtable,update,update_taxids}
+               ...
 
   Creation, validation, and modification of reference packages for use with
   `pplacer` and related software.
 
   positional arguments:
-    {help,add_nodes,add_to_taxtable,check,composition,create,extract_nodes,findcompany,get_lineage,info,lonelynodes,new_database,refpkg_intersection,reroot,rollback,rollforward,rp,strip,taxids,taxtable,update,update_taxids}
+    {help,add_nodes,add_to_taxtable,check,composition,create,extract_nodes,findcompany,get_descendants,get_lineage,info,lineage_table,lonelynodes,namelookup,new_database,refpkg_intersection,reroot,rollback,rollforward,rp,strip,taxids,taxtable,update,update_taxids}
       help                Detailed help for actions using `help <action>`
       add_nodes           Add nodes and names to a database
       add_to_taxtable     Add nodes to an existing taxtable csv
@@ -35,55 +32,49 @@ A script named ``taxit`` provides a command line interface::
       create              Create a reference package
       extract_nodes       Extract nodes from a given source in yaml format
       findcompany         Find company for lonely nodes
+      get_descendants     Returns given taxids including descendant taxids
       get_lineage         Calculate the taxonomic lineage of a taxid
       info                Show information about reference packages.
+      lineage_table       Create a table of lineages as taxonimic names for a
+                          collection of sequences
       lonelynodes         Extracts tax ids of all lonely nodes in a taxtable
+      namelookup          Find primary name and tax_id from taxonomic names
       new_database        Download NCBI taxonomy and create a database
       refpkg_intersection
-			  Find the intersection of a taxtable and a refpkg's
-			  taxonomy.
+                          Find the intersection of a taxtable and a refpkg's
+                          taxonomy.
       reroot              Taxonomically reroots a reference package
       rollback            Undo an operation performed on a refpkg
       rollforward         Restore a change to a refpkg immediately after being
-			  reverted
+                          reverted
       rp                  Resolve path; get the path to a file in the reference
-			  package
+                          package
       strip               Remove rollback and rollforward information from a
-			  refpkg
+                          refpkg
       taxids              Convert a list of taxonomic names into a recursive
-			  list of species
+                          list of species
       taxtable            Create a tabular representation of taxonomic lineages
       update              Add or modify files or metadata in a refpkg
       update_taxids       Update obsolete tax_ids
 
-  optional arguments:
+  options:
     -h, --help            show this help message and exit
     -V, --version         Print the version number and exit
     -v, --verbose         Increase verbosity of screen output (eg, -v is
-			  verbose, -vv more so)
+                          verbose, -vv more so)
     -q, --quiet           Suppress output
 
-
-.. Targets ..
-.. _quickstart: https://fhcrc.github.io/taxtastic/quickstart.html
-.. _full documentation: https://fhcrc.github.io/taxtastic/index.html
-
-
 Installation
 ============
 
-``taxtastic`` requires Python 3.5+. Python 2 is no longer
-supported. The simplest method of installing is using `pip
-<http://pip-installer.org>`_::
+``taxtastic`` requires Python 3.8+. The simplest method of installing
+is using `pip <http://pip-installer.org>`_::
 
   pip install taxtastic
 
-We strongly recommend installation into a virtualenv. On a clean
-Ubuntu 16.04+ system, complete instructions for installing the
-``taxtastic`` package and the ``taxit`` command line entry point in a
-virtualenv are below.
-
-Create a virtualenv and install ``taxtastic``::
+We strongly recommend installation into a virtualenv. Instructions for
+installing the ``taxtastic`` package and the ``taxit`` command line
+entry point in a virtualenv are as follows::
 
   python3 -m venv taxtastic-env
   source taxtastic-env/bin/activate
@@ -94,24 +85,24 @@ If you prefer to install from the git repository::
 
   git clone https://github.com/fhcrc/taxtastic.git
   cd taxtastic
-  python -m venv taxtastic-env
+  python3 -m venv taxtastic-env
   source taxtastic-env/bin/activate
   pip install .
 
-If you want to live dangerously and install the package to the system
-despite our pleas not to do so::
+Finally, ``taxit`` can be run from a Docker image hosted in the GitHub
+Container Registry. For example, to create a new sqlite database::
 
-  sudo apt-get install python-pip
-  sudo pip install taxtastic
+  docker run --rm -it -v $(pwd):/opt/run --platform=linux/amd64 ghcr.io/fhcrc/taxtastic:latest taxit new_database ncbi_taxonomy.db
 
-If you are not familiar with python virtual environments, the
-following post is helpful:
-https://realpython.com/blog/python/python-virtual-environments-a-primer/
+Note that initial database creation (at least on MacOS using amd64
+emulation) is very slow using Docker and is not recommended.
 
-Finally, ``taxit`` can be run from a docker image hosted from Docker
-Hub (https://hub.docker.com/r/nghoffman/taxtastic/), for example::
+A note on databases
+===================
 
-  docker run --rm -it -v $(pwd):$(pwd) -w $(pwd) nghoffman/taxtastic:latest taxit -v new_database
+This project supports both sqlite3 and postgresql as database
+backends. For most applications, we recommend sqlite3: some operations
+(particularly initial database creation) are much faster using sqlite3.
 
 sqlite3
 -------
@@ -122,45 +113,18 @@ is built against sqlite3 library version of 3.8.3 or higher
 (http://www.sqlite.org/releaselog/3_8_3.html). You can check the
 version like this::
 
-  python -c 'import sqlite3; print sqlite3.sqlite_version'
-
-``python setup.py`` will exit with an error if the sqlite3 library
-dependency is not met. On older systems (and for python2 only), it is
-possible to replace the builtin ``sqlite3`` module by installing
-``pysqlite2`` with updated sqlite3 libraries **before** installing the
-package with pip using a provided script in the ``taxtastic`` git
-repository (assuming an active virtualenv)::
+  python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'
 
-  dev/install_pysqlite.sh
+postgresql
+----------
 
-Or, to avoid cloning the repo::
+Despite some recent optimizations as of version v0.10 (in which
+indexes and constraints are dropped before creating the taxonomy
+database), operations in Postgres are somewhat slower. Note that the
+default Postgres configuration on MacOS is likely to be quite resource
+constrained; consider tuning your database configuration by consulting
+a site such as PGTune (https://pgtune.leopard.in.ua).
 
-  curl https://raw.githubusercontent.com/fhcrc/taxtastic/master/dev/install_pysqlite.sh | bash
-
-After the script completes, confirm that ``pysqlite2`` was installed::
-
-  python -c 'from pysqlite2 import dbapi2; print dbapi2.sqlite_version'
-
-At this point, taxtastic may be installed as described above.
-
-Note that ``pysqlite2`` is available for python2 only, so there really
-is no good option for using python3 on older systems like Ubuntu 14.04
-and earlier, unless you want to compile a version of the python3
-interpreter linked against updated sqlite3 libraries. If you must use
-an older system, stick with python2, or use the Docker image.
-
-A note on databases
-===================
-
-This project supports both sqlite3 and postgresql as database
-backends. For most applications, we recommend sqlite3: some operations
-(particularly initial database creation) are much faster using sqlite3
-due to the details of how postgresql enforces database constraints (we
-may try to optimize this in the future - in theory, postgresql can be
-made to be at least as fast). If you do want to use postgresql, note
-that some of the queries consume a lot of memory, and the default
-configuration tends to be memory constrained (and this *really* slows
-things down). On a reasonably new mac laptop, we found that the
-optimizations suggested here
-(http://big-elephants.com/2012-12/tuning-postgres-on-macos/) do the
-trick.
+.. Targets ..
+.. _quickstart: https://fhcrc.github.io/taxtastic/quickstart.html
+.. _full documentation: https://fhcrc.github.io/taxtastic/index.html
diff --git a/TESTING.rst b/TESTING.rst
deleted file mode 100644
index f694c493..00000000
--- a/TESTING.rst
+++ /dev/null
@@ -1,57 +0,0 @@
-=============================
- unit tests for this package
-=============================
-
-Two shell scripts in this directory can be used to run some or all of
-the unit tests defined in ./tests. The shell script `./test` can be
-used to run all tests, with optional flags controlling verbosity::
-
-  ./test -q # be as quiet as possible (loglevel = logging.ERROR) 
-  ./test    # not much output         (loglevel = logging.WARNING) 
-  ./test -v # a bit more verbose      (loglevel = logging.INFO)
-  ./test -v # you get the idea        (loglevel = logging.DEBUG)
-
-Individual unit tests can be run using `./testone` using the package
-hierarchy in the tests directory::
-
-  % ./testone tests.test_taxtastic.TestHelp.test01 
-  --> ./taxtastic.py -h
-  .
-  ----------------------------------------------------------------------
-  Ran 1 test in 0.219s
-
-  OK
-
-  % ./testone -v tests.test_taxtastic.TestHelp.test01
-  test01 (tests.test_taxtastic.TestHelp) ... WARNING test_taxtastic 35 --> ./taxtastic.py -h
-  INFO test_taxtastic 37 usage: taxtastic.py [-h] [-V] {create,help,check} ...
-
-  To Be Named -- Creation, validation, and modification of reference packages
-  for use with `pplacer` and related software.
-
-  positional arguments:
-    {create,help,check}
-      help               Detailed help for actions using `help <action>`
-      create             Create a reference package
-      check              The check action is not yet implemented
-
-  optional arguments:
-    -h, --help           show this help message and exit
-    -V, --version        Print the version number and exit
-  ok
-
-  ----------------------------------------------------------------------
-  Ran 1 test in 0.228s
-
-  OK
-
-Output of unit tests are written to ./test_output.
-
-unit tests for scripts
-======================
-
-Note that (as in the above example) scripts are tested as well. A base
-class inheriting from unittest.TestBase is defined in
-tests/config.py - see the docstring and existing unit tests for
-instructions and examples.
-
diff --git a/requirements.txt b/requirements.txt
index 16231fb5..49277833 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -1,10 +1,12 @@
 decorator==5.1.1
-DendroPy==4.5.2
-fastalite==0.3
-greenlet==1.1.2
-Jinja2==3.1.1
-MarkupSafe==2.1.1
-psycopg2-binary==2.9.3
+DendroPy==4.6.0
+fastalite==0.4.1
+Jinja2==3.1.2
+MarkupSafe==2.1.2
+psycopg==3.1.9
+psycopg-binary==3.1.9
+psycopg2-binary==2.9.6
 PyYAML==6.0
-six==1.16.0
-SQLAlchemy==1.4.35
+SQLAlchemy==2.0.12
+sqlparse==0.4.4
+typing_extensions==4.5.0
diff --git a/setup.cfg b/setup.cfg
deleted file mode 100644
index c39d1c85..00000000
--- a/setup.cfg
+++ /dev/null
@@ -1,3 +0,0 @@
-[egg_info]
-tag_build =
-tag_date = false
diff --git a/setup.py b/setup.py
index ce248e87..db76b9ad 100644
--- a/setup.py
+++ b/setup.py
@@ -5,33 +5,16 @@
 
 import subprocess
 import os
-from distutils.version import LooseVersion
 from setuptools import setup, find_packages
 # To use a consistent encoding
-from codecs import open
 from os import path
 
-try:
-    from pysqlite2 import dbapi2 as sqlite3
-except ImportError:
-    import sqlite3
-
-print('using {}, sqlite3 version {}'.format(sqlite3.__name__, sqlite3.sqlite_version))
-
-min_sqlite3_version = '3.8.3'
-if LooseVersion(sqlite3.sqlite_version) < LooseVersion(min_sqlite3_version):
-    raise ImportError(('the sqlite3 library version for this python interpreter is '
-                       '{}, but a version >= {} is required; '
-                       'see https://github.com/fhcrc/taxtastic#installing').format(
-                           sqlite3.sqlite_version, min_sqlite3_version))
-
-
 subprocess.call(
     ('mkdir -p taxtastic/data && '
      'git describe --tags --dirty > taxtastic/data/ver.tmp'
      '&& mv taxtastic/data/ver.tmp taxtastic/data/ver '
      '|| rm -f taxtastic/data/ver.tmp'),
-    shell=True, stderr=open(os.devnull, "w"))
+    shell=True, stderr=open(os.devnull, 'w', encoding='utf-8'))
 
 # must import __version__ *after* running 'git describe' above
 from taxtastic import __version__
@@ -49,35 +32,37 @@
           'maintainer_email': 'crosenth@uw.edu',
           'description': 'Tools for taxonomic naming and annotation',
           'long_description': long_description,
-          'packages': find_packages(
-              exclude=['tests', 'testfiles', 'devtools', 'docs']),
-          'python_requires': '>=3.4',
+          'packages': find_packages(exclude=['testfiles', 'tests']),
+          'package_dir': {'taxtastic': 'taxtastic'},
+          'python_requires': '>=3.8',
           'url': 'https://github.com/fhcrc/taxtastic',
           'version': __version__,
           'license': 'GPL',
           'classifiers': [
               'License :: OSI Approved :: GNU General Public License (GPL)',
               'Development Status :: 3 - Alpha',
-              'Programming Language :: Python :: 3.4',
-              'Programming Language :: Python :: 3.5',
-              'Programming Language :: Python :: 3.6',
-              'Programming Language :: Python :: 3.7',
               'Programming Language :: Python :: 3.8',
+              'Programming Language :: Python :: 3.9',
+              'Programming Language :: Python :: 3.10',
+              'Programming Language :: Python :: 3.11',
               'Topic :: Scientific/Engineering :: Bio-Informatics'],
           'download_url': 'https://github.com/fhcrc/taxtastic',
-          'package_data': {
-              'taxtastic': ['data/*']},
-          'entry_points': {'console_scripts': ['taxit = taxtastic.scripts.taxit:main']},
+          # 'package_data': {
+          #     'taxtastic': ['data/*']},
+          'include_package_data': True,
+          'entry_points': {
+              'console_scripts': ['taxit = taxtastic.scripts.taxit:main']},
           'test_suite': 'tests',
           'install_requires': [
-              'DendroPy>=4.3.0',
-              'PyYAML>=3.12',
-              'decorator>=4.1.2',
-              'fastalite>=0.3',
-              'jinja2>=2.9',
-              'psycopg2-binary>=2.7.3.1',
-              'six',
-              'sqlalchemy>=1.0',
+              'DendroPy',
+              'PyYAML',
+              'decorator',
+              'fastalite',
+              'jinja2',
+              'psycopg2-binary',
+              'psycopg[binary]',
+              'sqlalchemy>=2',
+              'sqlparse',
           ]}
 
 setup(**params)
diff --git a/singularity/Singularity b/singularity/Singularity
deleted file mode 100644
index 6535c8fd..00000000
--- a/singularity/Singularity
+++ /dev/null
@@ -1,18 +0,0 @@
-Bootstrap:docker
-From:python:3.6.7-stretch
-
-%setup
-  mkdir ${SINGULARITY_ROOTFS}/src
-
-%files
-  taxtastic.tar.gz /src/taxtastic.tar.gz
-  install_pplacer.sh /usr/local/bin/install_pplacer.sh
-
-%post
-  apt-get update
-  apt-get install -y unzip --no-install-recommends
-  /usr/local/bin/install_pplacer.sh /usr/local 1.1.alpha19
-  rm -rf /var/lib/apt/lists/*
-  apt-get purge -y --auto-remove unzip
-  pip install /src/taxtastic.tar.gz
-  mkdir -p /app /fh /mnt /run/shm  # create bind points
diff --git a/singularity/build.sh b/singularity/build.sh
deleted file mode 100755
index 3ec0d92f..00000000
--- a/singularity/build.sh
+++ /dev/null
@@ -1,28 +0,0 @@
-#!/bin/bash
-
-if [[ -z $1 ]]; then
-    echo "usage: ./build.sh TAG [<outdir>]"
-    exit 1
-fi
-
-outdir=$(readlink -f ${2-.})
-
-version=$1
-singularity_version=$(singularity --version)
-img=$(readlink -f $outdir/taxtastic-${version}-singularity${singularity_version}.img)
-
-if [[ -f $img ]]; then
-    echo "$img already exists"
-    exit 1
-fi
-
-echo $img
-
-rm -rf dist
-(cd .. && python setup.py sdist --dist-dir singularity/dist)
-tarball=$(ls dist/taxtastic*)
-name=$(basename ${tarball%.tar.gz})
-
-cp $tarball taxtastic.tar.gz
-cp ../dev/install_pplacer.sh .
-sudo singularity build $img Singularity
diff --git a/singularity/build_docker.sh b/singularity/build_docker.sh
deleted file mode 100755
index 408d5523..00000000
--- a/singularity/build_docker.sh
+++ /dev/null
@@ -1,32 +0,0 @@
-#!/bin/bash
-
-# build from a local docker image
-
-set -e
-
-if [[ -z $1 ]]; then
-    echo "usage: ./build.sh TAG [<outdir>]"
-    echo
-    echo "choose from one of the following tags:"
-    echo
-    docker image list taxtastic
-    exit 1
-fi
-
-outdir=$(readlink -f ${2-.})
-
-version=$1
-docker_image=taxtastic:$1
-docker_name=$(echo $docker_image | tr : _)_$RANDOM
-img=$(readlink -f $outdir/taxtastic-${version}.img)
-
-if [[ -f $img ]]; then
-    echo "$img already exists"
-    exit 1
-fi
-
-singularity create --size 1500 $img
-docker run --name $docker_name $docker_image /bin/true
-docker export $docker_name | sudo singularity import $img
-docker rm $docker_name
-sudo singularity exec --writable $img mkdir -p /fh /app /mnt
diff --git a/taxtastic/ncbi.py b/taxtastic/ncbi.py
index 5fd528c9..afef675a 100644
--- a/taxtastic/ncbi.py
+++ b/taxtastic/ncbi.py
@@ -20,18 +20,19 @@
 import logging
 import os
 import re
-from six.moves.urllib import request
-# import urllib.parse
-# import urllib.error
+from urllib import request
 import zipfile
 import io
 from operator import itemgetter
 
-import sqlalchemy
+from jinja2 import Environment, PackageLoader
+import sqlparse
+
+import sqlalchemy as sa
 from sqlalchemy import (Column, Integer, String, Boolean,
                         ForeignKey, Index, MetaData, PrimaryKeyConstraint)
 from sqlalchemy.orm import relationship
-from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import declarative_base
 
 from taxtastic.utils import random_name
 
@@ -171,11 +172,29 @@
 UNCLASSIFIED_REGEX = re.compile('|'.join(UNCLASSIFIED_REGEX_COMPONENTS))
 
 
+def execute_template(engine, template, **kwargs):
+    """Execute sql commands in taxtastic/templates/{template}"""
+
+    env = Environment(
+        loader=PackageLoader('taxtastic'),
+        autoescape=False
+    )
+
+    script = env.get_template(template).render(**kwargs)
+    commands = sqlparse.split(sqlparse.format(script, strip_comments=True))
+
+    with engine.connect() as conn:
+        for cmd in commands:
+            log.info(cmd)
+            conn.exec_driver_sql(cmd)
+        conn.commit()
+
+
 def define_schema(Base):
 
     class Node(Base):
         __tablename__ = 'nodes'
-        tax_id = Column(String, primary_key=True, nullable=False)
+        tax_id = Column(String, primary_key=True, nullable=False, index=True)
 
         # TODO: temporarily remove foreign key constratint on parent
         # TODO: (creates order depencence during insertion); may need to
@@ -183,10 +202,10 @@ class Node(Base):
 
         # parent_id = Column(String, ForeignKey('nodes.tax_id'))
         parent_id = Column(String, index=True)
-        rank = Column(String, ForeignKey('ranks.rank'))
+        rank = Column(String, ForeignKey('ranks.rank'), index=True)
         embl_code = Column(String)
         division_id = Column(String)
-        source_id = Column(Integer, ForeignKey('source.id'))
+        source_id = Column(Integer, ForeignKey('source.id'), index=True)
         is_valid = Column(Boolean, default=True)
         names = relationship('Name')
         ranks = relationship('Rank', back_populates='nodes')
@@ -195,13 +214,14 @@ class Node(Base):
     class Name(Base):
         __tablename__ = 'names'
         # id = Column(Integer, primary_key=True)
-        tax_id = Column(String, ForeignKey('nodes.tax_id', ondelete='CASCADE'))
+        tax_id = Column(
+            String, ForeignKey('nodes.tax_id', ondelete='CASCADE'), index=True)
         node = relationship('Node', back_populates='names')
         tax_name = Column(String)
         unique_name = Column(String)
         name_class = Column(String)
         source_id = Column(Integer, ForeignKey('source.id'))
-        is_primary = Column(Boolean)
+        is_primary = Column(Boolean, index=True)
         is_classified = Column(Boolean)
         sources = relationship('Source', back_populates='names')
         __table_args__ = (
@@ -217,7 +237,7 @@ class Merge(Base):
 
     class Rank(Base):
         __tablename__ = 'ranks'
-        rank = Column(String, primary_key=True)
+        rank = Column(String, primary_key=True, index=True)
         height = Column(Integer, unique=True, nullable=False)
         nodes = relationship('Node')
 
@@ -241,18 +261,18 @@ def db_connect(engine, schema=None, clobber=False):
         base = declarative_base()
     else:
         try:
-            engine.execute(sqlalchemy.schema.CreateSchema(schema))
-        except sqlalchemy.exc.ProgrammingError as err:
-            logging.warn(err)
+            engine.execute(sa.schema.CreateSchema(schema))
+        except sa.exc.ProgrammingError as err:
+            log.warning(err)
         base = declarative_base(metadata=MetaData(schema=schema))
 
     define_schema(base)
 
     if clobber:
-        logging.info('Clobbering database tables')
+        log.info('Clobbering database tables')
         base.metadata.drop_all(bind=engine)
 
-    logging.info('Creating database tables')
+    log.info('Creating database tables')
     base.metadata.create_all(bind=engine)
 
     return base
@@ -351,7 +371,11 @@ def __init__(self, engine, schema=None, ranks=RANKS):
         self.tables = {name: self.prepend_schema(name)
                        for name in ['merged', 'names', 'nodes', 'ranks', 'source']}
         self.ranks = ranks
-        self.placeholder = {'pysqlite': '?', 'psycopg2': '%s'}[engine.driver]
+        self.placeholder = {
+            'pysqlite': '?',
+            'psycopg2': '%s',
+            'psycopg': '%s',  # psycopg3
+        }[engine.driver]
 
     def prepend_schema(self, name):
         """Prepend schema name to 'name' when a schema is specified
@@ -405,19 +429,19 @@ def load_archive(self, archive):
         )
 
         # nodes
-        logging.info('loading nodes')
+        log.info('loading nodes')
         nodes_rows = read_nodes(
             read_archive(archive, 'nodes.dmp'), source_id=source_id)
         self.load_table('nodes', rows=nodes_rows)
 
         # names
-        logging.info('loading names')
+        log.info('loading names')
         names_rows = read_names(
             read_archive(archive, 'names.dmp'), source_id=source_id)
         self.load_table('names', rows=names_rows)
 
         # merged
-        logging.info('loading merged')
+        log.info('loading merged')
         merged_rows = read_merged(read_archive(archive, 'merged.dmp'))
         self.load_table('merged', rows=merged_rows)
 
@@ -536,10 +560,10 @@ def fetch_data(dest_dir='.', clobber=False, url=DATA_URL):
 
     if os.access(fout, os.F_OK) and not clobber:
         downloaded = False
-        logging.info(fout + ' exists; not downloading')
+        log.info(f'{fout} exists; not downloading')
     else:
         downloaded = True
-        logging.info('downloading {} to {}'.format(url, fout))
+        log.info(f'downloading {url} to {fout}')
         request.urlretrieve(url, fout)
 
     return (fout, downloaded)
@@ -551,10 +575,10 @@ def read_archive(archive, fname):
     * archive - path to the zip archive.
     * fname - name of the compressed file within the archive.
 
-    """
+    Lines are deduplicated (equivalent to an upsert/ignore) but avoids
+    requirement for a database-specific implementation.
 
-    # Note that deduplication here is equivalent to an upsert/ignore,
-    # but avoids requirement for a database-specific implementation.
+    """
 
     zfile = zipfile.ZipFile(archive)
     contents = zfile.open(fname, 'r')
@@ -566,11 +590,3 @@ def read_archive(archive, fname):
         if line not in seen:
             yield line.split('\t|\t')
             seen.add(line)
-
-# def read_dmp(fname):
-#     seen = set()
-#     for line in open(fname, 'r'):
-#         line = line.rstrip('\t|\n')
-#         if line not in seen:
-#             yield line.split('\t|\t')
-#             seen.add(line)
diff --git a/taxtastic/subcommands/add_nodes.py b/taxtastic/subcommands/add_nodes.py
index 8bc297a9..589aba68 100644
--- a/taxtastic/subcommands/add_nodes.py
+++ b/taxtastic/subcommands/add_nodes.py
@@ -14,8 +14,10 @@
 #    along with taxtastic.  If not, see <http://www.gnu.org/licenses/>.
 """Add nodes and names to a database
 
-The input file specifies new nodes (type: node) and names (type:
-name) in yaml format (see http://fhcrc.github.io/taxtastic/commands.html#add-nodes).
+The input file specifies new nodes (type: node) and names (type: name)
+in yaml format (see
+http://fhcrc.github.io/taxtastic/commands.html#add-nodes).
+
 """
 
 import sys
@@ -55,17 +57,17 @@ def action(args):
     log.info('adding new nodes')
     retval = None
     for rec in records:
+        tax_id = rec['tax_id']
         try:
             record_type = rec.pop('type')
             if record_type not in {'node', 'name'}:
                 raise ValueError
         except (KeyError, ValueError):
-            log.error(('Error in record for tax_id {tax_id}: "type" is '
-                       'required and must be one of "node" or "name"').format(**rec))
+            log.error((f'Error in record for tax_id {tax_id}: "type" is '
+                       'required and must be one of "node" or "name"'))
             retval = 1
             continue
 
-        tax_id = rec['tax_id']
         rec['source_name'] = rec.get('source_name') or args.source_name
 
         try:
@@ -75,26 +77,28 @@ def action(args):
                         pprint.pformat(rec)))
                     raise ValueError
                 if tax.has_node(tax_id):
-                    log.info('updating *node* "{tax_id}"'.format(**rec))
+                    log.info(f'updating *node* "{tax_id}"')
                     tax.update_node(**rec)
                 else:
-                    log.info('new *node* "{tax_id}"'.format(**rec))
+                    log.info(f'new *node* "{tax_id}"')
                     tax.add_node(**rec)
             elif record_type == 'name':
                 for name in rec['names']:
                     name['tax_id'] = tax_id
                     # source_name may be provided at the record or name level
-                    name['source_name'] = name.get('source_name') or rec['source_name']
+                    name['source_name'] = (
+                        name.get('source_name') or rec['source_name'])
                     if not name['source_name']:
                         log.error(
                             'Error: record has no source_name:\n {}'.format(
                                 pprint.pformat(rec)))
                         raise ValueError
 
-                    log.info('new *name* for "{tax_id}": "{tax_name}"'.format(**name))
+                    log.info('new *name* for "{tax_id}": "{tax_name}"'
+                             .format(**name))
                     tax.add_name(**name)
         except (ValueError, TypeError):
-            log.error('Error in record with tax_id {}'.format(rec['tax_id']))
+            log.error('Error in record with tax_id {tax_id}')
             log.error(''.join(traceback.format_exception(*sys.exc_info())))
             retval = 1
 
diff --git a/taxtastic/subcommands/extract_nodes.py b/taxtastic/subcommands/extract_nodes.py
index a4e2d8b9..a5efa55a 100644
--- a/taxtastic/subcommands/extract_nodes.py
+++ b/taxtastic/subcommands/extract_nodes.py
@@ -18,7 +18,7 @@
 
 import sys
 import logging
-import sqlalchemy
+import sqlalchemy as sa
 from itertools import groupby
 from operator import itemgetter
 # from collections import OrderedDict
@@ -32,10 +32,21 @@
 log = logging.getLogger(__name__)
 
 
-def clean_dict(keys, vals):
+# def clean_dict(keys, vals):
+#     # outdict = OrderedDict()
+#     outdict = {}
+#     for k, v in zip(keys, vals):
+#         if k == 'source_id' or v is None:
+#             continue
+#         outdict[k] = bool(v) if k.startswith('is_') else v
+
+#     return outdict
+
+
+def clean_dict(d):
     # outdict = OrderedDict()
     outdict = {}
-    for k, v in zip(keys, vals):
+    for k, v in d.items():
         if k == 'source_id' or v is None:
             continue
         outdict[k] = bool(v) if k.startswith('is_') else v
@@ -46,32 +57,32 @@ def clean_dict(keys, vals):
 def build_parser(parser):
     parser = add_database_args(parser)
     parser.add_argument('source_name',
-                        help='name of source identifying names and nodes to extract')
+                        help=('name of source identifying names '
+                              'and nodes to extract'))
     parser.add_argument('-o', '--outfile', type=Opener('w'), default=sys.stdout)
 
 
 def action(args):
-    engine = sqlalchemy.create_engine(args.url, echo=args.verbosity > 2)
+    engine = sa.create_engine(args.url, echo=args.verbosity > 2)
     tax = Taxonomy(engine, schema=args.schema)
 
     with engine.connect() as con:
         # TODO: need to order nodes so that parents are always created first
 
-        cmd = """
+        cmd = sa.text("""
         select nodes.*, source.name as source_name
         from {nodes}
         join {source} on nodes.source_id = source.id
-        where source.name = {x}
-        """.format(x=tax.placeholder, nodes=tax.nodes, source=tax.source)
+        where source.name = :name
+        """.format(**tax.tables))
 
-        result = con.execute(cmd, (args.source_name,))
-        keys = list(result.keys())
-        nodes = [clean_dict(keys, vals) for vals in result.fetchall()]
+        results = tax.fetchall(cmd, name=args.source_name)
+        nodes = [clean_dict(row._asdict()) for row in results]
 
         # get the complete lineage for each node, and provide an
         # ordering for all nodes so that children may be placed after
         # parents.
-        tax_ids = list(map(itemgetter('tax_id'), nodes))
+        tax_ids = [node['tax_id'] for node in nodes]
         lineages = tax._get_lineage_table(tax_ids)
         ordering = {}
         for i, lineage in enumerate(lineages):
@@ -81,16 +92,16 @@ def action(args):
 
         nodes = sorted(nodes, key=lambda n: ordering[n['tax_id']])
 
-        cmd = """
+        cmd = sa.text("""
         select names.*, source.name as source_name
         from {names}
         join {source} on names.source_id = source.id
-        where source.name = {x}
-        """.format(x=tax.placeholder, names=tax.names, source=tax.source)
+        where source.name = :name
+        """.format(**tax.tables))
+
+        results = tax.fetchall(cmd, name=args.source_name)
+        names = [clean_dict(row._asdict()) for row in results]
 
-        result = con.execute(cmd, (args.source_name,))
-        keys = list(result.keys())
-        names = [clean_dict(keys, vals) for vals in result.fetchall()]
         namedict = {key: list(grp)
                     for key, grp in groupby(names, itemgetter('tax_id'))}
 
@@ -115,5 +126,9 @@ def action(args):
                 'names': names
             })
 
-        yaml.safe_dump_all(remaining_names, args.outfile, default_flow_style=False,
-                           explicit_start=True, indent=2)
+        yaml.safe_dump_all(
+            remaining_names,
+            args.outfile,
+            default_flow_style=False,
+            explicit_start=True,
+            indent=2)
diff --git a/taxtastic/subcommands/get_descendants.py b/taxtastic/subcommands/get_descendants.py
index bf02fde5..e98e934d 100644
--- a/taxtastic/subcommands/get_descendants.py
+++ b/taxtastic/subcommands/get_descendants.py
@@ -37,6 +37,7 @@ def build_parser(parser):
 
 
 def action(args):
+    # TODO: move this logic into the taxonomy.py object file
     if os.path.isfile(args.taxids):
         taxids = [i.strip() for i in open(args.taxids) if i]
     else:
@@ -57,5 +58,6 @@ def action(args):
     JOIN names using(tax_id)
     WHERE is_primary;
     """.format(','.join("'{}'".format(t) for t in taxids))
-    for i in engine.execute(cmd):
-        args.out.write(i[0] + '\n')
+    with engine.connect() as conn:
+        for i in conn.exec_driver_sql(cmd):
+            args.out.write(i[0] + '\n')
diff --git a/taxtastic/subcommands/new_database.py b/taxtastic/subcommands/new_database.py
index b4f930ef..7e762fe8 100644
--- a/taxtastic/subcommands/new_database.py
+++ b/taxtastic/subcommands/new_database.py
@@ -41,6 +41,12 @@ def build_parser(parser):
         help=('If database exists keep current data '
               'and append new data. [False]'))
 
+    parser.add_argument(
+        '-n', '--no-load',
+        action='store_false', default=True,
+        dest='load',
+        help=('Create schema and exit'))
+
     download_parser = parser.add_argument_group(title='download options')
     download_parser.add_argument(
         '-z', '--taxdump-file',
@@ -79,20 +85,34 @@ def action(args):
 
     engine = sqlalchemy.create_engine(args.url, echo=args.verbosity > 2)
 
+    # sqlite, postgresql
+    dialect = engine.dialect.name
+
     # creates database schema
-    taxtastic.ncbi.db_connect(engine, schema=args.schema, clobber=args.clobber)
+    base = taxtastic.ncbi.db_connect(engine, schema=args.schema, clobber=args.clobber)
 
-    ncbi_loader = taxtastic.ncbi.NCBILoader(engine, args.schema)
-    ncbi_loader.load_archive(zfile)
-    ncbi_loader.set_names_is_classified()
-    ncbi_loader.set_nodes_is_valid()
+    if dialect == 'postgresql':
+        taxtastic.ncbi.execute_template(engine, 'drop_pg_constraints.sql')
 
-    # print_sql(args.out, engine.name, base.metadata)
+    if args.load:
+        ncbi_loader = taxtastic.ncbi.NCBILoader(engine, args.schema)
+        ncbi_loader.load_archive(zfile)
 
+        if dialect == 'postgresql':
+            taxtastic.ncbi.execute_template(engine, 'add_pg_indexes.sql')
+
+        ncbi_loader.set_names_is_classified()
+        ncbi_loader.set_nodes_is_valid()
+
+        if dialect == 'postgresql':
+            taxtastic.ncbi.execute_template(engine, 'add_pg_constraints.sql')
+
+    # print_sql(args.out, engine.name, base.metadata)
 
 def print_sql(out, engine_name, metadata):
     def dump(sql, *multiparams, **params):
         out.write(str(sql.compile(dialect=dump.dialect)).strip() + ';\n')
+
     engine = sqlalchemy.create_engine(
         engine_name + '://', strategy='mock', executor=dump)
 
diff --git a/taxtastic/subcommands/update_taxids.py b/taxtastic/subcommands/update_taxids.py
index eb8592ab..081a3412 100644
--- a/taxtastic/subcommands/update_taxids.py
+++ b/taxtastic/subcommands/update_taxids.py
@@ -22,12 +22,13 @@
 
 import csv
 import logging
-import sqlalchemy
 import sys
-import taxtastic
+
+import sqlalchemy as sa
 
 from fastalite import Opener
 
+import taxtastic
 from taxtastic.taxonomy import Taxonomy
 
 log = logging.getLogger(__name__)
@@ -36,8 +37,8 @@
 def build_parser(parser):
     parser.add_argument(
         'infile', type=Opener('r'),
-        help=('Input CSV file to process, minimally containing the field `tax_id`. '
-              'Use "-" for stdin.'))
+        help=('Input CSV file to process, minimally containing the field '
+              '`tax_id`. Use "-" for stdin.'))
     parser = taxtastic.utils.add_database_args(parser)
     parser.add_argument(
         '-o', '--outfile', default=sys.stdout, type=Opener('wt'),
@@ -55,7 +56,8 @@ def build_parser(parser):
         help=('optional output file containing rows with unknown tax_ids '
               'having no replacements in merged table'))
     parser.add_argument(
-        '-a', '--unknown-action', choices=['drop', 'ignore', 'error'], default='error',
+        '-a', '--unknown-action', choices=['drop', 'ignore', 'error'],
+        default='error',
         help=('action to perform for tax_ids with no replacement '
               'in merged table [%(default)s]'))
 
@@ -97,17 +99,18 @@ def action(args):
         if not args.tax_id_file:
             unknowns.writeheader()
 
-    engine = sqlalchemy.create_engine(args.url, echo=args.verbosity > 3)
+    engine = sa.create_engine(args.url, echo=args.verbosity > 3)
     tax = Taxonomy(engine, schema=args.schema)
 
     with tax.engine.connect() as con:
         log.info('reading table merged')
-        result = con.execute(
-            'select old_tax_id, new_tax_id from {merged}'.format(merged=tax.merged))
+        result = con.execute(sa.text(
+            'select old_tax_id, new_tax_id from {merged}'.format(**tax.tables)))
         mergedict = dict(result.fetchall())
 
-        log.info('reading tax_ids from table {nodes}'.format(nodes=tax.nodes))
-        result = con.execute('select tax_id from {nodes}'.format(nodes=tax.nodes))
+        log.info('reading tax_ids from table nodes')
+        result = con.execute(sa.text(
+            'select tax_id from {nodes}'.format(**tax.tables)))
         all_tax_ids = {x[0] for x in result.fetchall()}
 
     log.info('reading input file')
@@ -127,6 +130,6 @@ def action(args):
             elif drop:
                 continue
             elif error:
-                sys.exit('Error: tax_id {} is unknown'.format(tax_id))
+                sys.exit(f'Error: tax_id {tax_id} is unknown')
 
         writer.writerow(row)
diff --git a/taxtastic/taxonomy.py b/taxtastic/taxonomy.py
index 25e2136a..424affd0 100644
--- a/taxtastic/taxonomy.py
+++ b/taxtastic/taxonomy.py
@@ -21,11 +21,11 @@
 
 from jinja2 import Template
 
-import sqlalchemy
+import sqlalchemy as sa
 from sqlalchemy import MetaData, and_, or_
 from sqlalchemy.sql import select
 from sqlalchemy.exc import IntegrityError
-from sqlalchemy.orm.session import sessionmaker
+from sqlalchemy.orm import Session
 
 from taxtastic.ncbi import UNORDERED_RANKS
 from taxtastic.utils import random_name
@@ -37,16 +37,13 @@ class TaxonIntegrityError(Exception):
     '''
     Raised when something in the Taxonomy is not structured correctly
     '''
-    pass
 
 
 class Taxonomy(object):
 
     def __init__(self, engine, schema=None):
-
-        """
-        The Taxonomy class defines an object providing an interface to
-        the taxonomy database.
+        """The Taxonomy class defines an object providing an interface
+        to the taxonomy database.
 
         * engine - sqlalchemy engine instance providing a connection to a
           database defining the taxonomy
@@ -69,14 +66,9 @@ def __init__(self, engine, schema=None):
         self.engine = engine
         self.meta = MetaData(schema=schema)
         self.meta.bind = self.engine
-        self.meta.reflect()
-
+        self.meta.reflect(bind=self.engine)
         self.schema = schema
 
-        # TODO: table names should probably be provided in a dict as a
-        # single attribute self.tablenames. This will avoid name
-        # collisions (eg with self.ranks) and allow the idiom
-        # cmd = 'select * from {<table>}'.format(**self.tablenames)
         self.nodes = self._get_table('nodes')
         self.names = self._get_table('names')
         self.source = self._get_table('source')
@@ -84,54 +76,54 @@ def __init__(self, engine, schema=None):
         ranks_table = self._get_table('ranks')
         self.ranks_table = ranks_table
 
-        ranks = select([ranks_table.c.rank, ranks_table.c.height]).execute().fetchall()
-        ranks = sorted(ranks, key=lambda x: int(x[1]))  # sort by height
-        self.ranks = [r[0] for r in ranks]  # just the ordered ranks
+        # intended for constructing text queries using the pattern
+        # cmd = 'select * from {<table>}'.format(**self.tablenames)
+        # to ensure that the schema is prepended to the table name when defined
+        self.tables = {name: self.prepend_schema(name) for name in [
+            'nodes', 'names', 'source', 'merged', 'ranks']}
+
+        ranks = self.fetchall(
+            select(self.ranks_table.c.rank).order_by(ranks_table.c.height))
+        self.ranks = [r[0] for r in ranks]
+
         self.unordered_ranks = set(self.ranks) & set(UNORDERED_RANKS)
 
-        # TODO: can probably remove this check at some point;
-        # historically the root node had tax_id == parent_id ==
-        # '1', but with a recursive CTE, parent_id must be None for
-        # the recursive expression to terminate.
-        with self.engine.connect() as con:
-            cmd = "select parent_id from {nodes} where rank = 'root'".format(
-                nodes=self.nodes)
-            result = con.execute(cmd)
-            if result.fetchone()[0] is not None:
-                raise TaxonIntegrityError('the root node must have parent_id = None')
+        # parent_id must be None for the recursive CTE for calculating
+        # lineages to terminate.
+        parent_node = self.fetchone(
+            select(self.nodes).filter_by(rank='root'))
+
+        if parent_node.parent_id is not None:
+            raise TaxonIntegrityError(
+                'the root node must have parent_id = None')
 
         self.placeholder = '%s' if self.engine.name == 'postgresql' else '?'
 
     def _get_table(self, name):
         try:
             val = self.meta.tables[self.prepend_schema(name)]
-        except KeyError:
+        except KeyError as ex:
             raise ValueError(
-                'Table "{}" is missing from the input database.'.format(name))
+                f'Table "{name}" is missing from the input database.') from ex
         else:
             return val
 
-    def execute(self, statements, exc=IntegrityError, rasie_as=ValueError):
-        """Execute ``statements`` in a session, and perform a rollback on
-        error. ``exc`` is a single exception object or a tuple of
-        objects to be used in the except clause. The error message is
-        re-raised as the exception specified by ``raise_as``.
+    def execute(self, statements, exc=IntegrityError, raise_as=ValueError,
+                errormsg=''):
+        """Execute list of ``statements`` in a transaction, and
+        perform a rollback on error. ``exc`` is a single exception
+        object or a tuple of objects to be used in the except clause.
+        The error message is re-raised as the exception specified by
+        ``raise_as``.
 
         """
 
-        Session = sessionmaker(bind=self.engine)
-        session = Session()
-
         try:
-            for statement in statements:
-                session.execute(statement)
-        except exc as err:
-            session.rollback()
-            raise rasie_as(str(err))
-        else:
-            session.commit()
-        finally:
-            session.close()
+            with self.engine.begin() as conn:
+                for stmt in statements:
+                    conn.execute(stmt)
+        except exc as ex:
+            raise raise_as(errormsg) from ex
 
     def _node(self, tax_id):
         """
@@ -140,19 +132,19 @@ def _node(self, tax_id):
         FIXME: expand return rank to include custom 'below' ranks built when
                get_lineage is caled
         """
-        s = select([self.nodes.c.parent_id, self.nodes.c.rank],
-                   self.nodes.c.tax_id == tax_id)
-        res = s.execute()
-        output = res.fetchone()
+
+        output = self.fetchone(
+            select(self.nodes.c.parent_id, self.nodes.c.rank)
+            .filter_by(tax_id=tax_id))
+
         if not output:
-            msg = 'value "{}" not found in nodes.tax_id'.format(tax_id)
-            raise ValueError(msg)
+            raise ValueError(f'value "{tax_id}" not found in nodes.tax_id')
         else:
-            return output  # parent_id, rank
+            return output
 
     def id_from_names(self, tax_names):
-        """
-        Return tax_ids corresponding to tax_names.
+        """Return tax_ids corresponding to tax_names.
+
         """
         names = self.names
         s = select([names.c.tax_name, names.c.tax_id],
@@ -160,20 +152,19 @@ def id_from_names(self, tax_names):
         return s.execute()
 
     def primary_from_id(self, tax_id):
+        """Returns primary taxonomic name associated with tax_id
+
         """
-        Returns primary taxonomic name associated with tax_id
-        """
-        s = select([self.names.c.tax_name],
-                   and_(self.names.c.tax_id == tax_id,
-                        self.names.c.is_primary))
-        res = s.execute()
-        output = res.fetchone()
 
-        if not output:
-            msg = 'value "{}" not found in names.tax_id'.format(tax_id)
-            raise ValueError(msg)
-        else:
+        output = self.fetchone(
+            select(self.names.c.tax_name)
+            .where(and_(self.names.c.tax_id == tax_id,
+                        self.names.c.is_primary)))
+
+        if output:
             return output[0]
+        else:
+            raise ValueError(f'"{tax_id}" not found in names.tax_id')
 
     def primary_from_ids(self, tax_ids):
         names = self.names
@@ -186,14 +177,11 @@ def primary_from_name(self, tax_name):
         """
         Return tax_id and primary tax_name corresponding to tax_name.
         """
-        names = self.names
 
-        s1 = select([names.c.tax_id, names.c.is_primary],
-                    names.c.tax_name == tax_name)
+        res = self.fetchone(
+            select(self.names.c.tax_id, self.names.c.is_primary)
+            .filter_by(tax_name=tax_name))
 
-        log.debug(str(s1))
-
-        res = s1.execute().fetchone()
         if res:
             tax_id, is_primary = res
         else:
@@ -201,30 +189,29 @@ def primary_from_name(self, tax_name):
             raise ValueError(msg)
 
         if not is_primary:
-            tax_name = self.primary_from_id(names.c.tax_id)
+            tax_name = self.primary_from_id(self.names.c.tax_id)
 
         return tax_id, tax_name, bool(is_primary)
 
     def _get_merged(self, tax_id):
-        """Returns tax_id into which `tax_id` has been merged or `tax_id` of
-        not obsolete.
+        """Returns tax_id into which `tax_id` has been merged or
+        `tax_id` if not obsolete.
 
         """
 
-        cmd = """
+        cmd = sa.text("""
         SELECT COALESCE(
         (SELECT new_tax_id FROM {merged}
-         WHERE old_tax_id = {x}), {x})
-        """.format(x=self.placeholder, merged=self.merged)
+         WHERE old_tax_id = :tax_id), :tax_id)
+        """.format(merged=self._get_table('merged')))
 
-        with self.engine.connect() as con:
-            result = con.execute(cmd, (tax_id, tax_id))
-            return result.fetchone()[0]
+        return self.fetchone(cmd, tax_id=tax_id)[0]
 
     def _get_lineage(self, tax_id, merge_obsolete=True):
         """Return a list of [(rank, tax_id)] describing the lineage of
-        tax_id. If ``merge_obsolete`` is True and ``tax_id`` has been
-        replaced, use the corresponding value in table merged.
+        tax_id from root to tip. If ``merge_obsolete`` is True and
+        ``tax_id`` has been replaced, use the corresponding value in
+        table merged.
 
         """
 
@@ -238,27 +225,28 @@ def _get_lineage(self, tax_id, merge_obsolete=True):
         # sort explicitly if this is the expected behavior, but it
         # seems like for the most part, the lineage is converted to a
         # dict and the order is irrelevant.
-        cmd = """
+
+        nodes = self._get_table('nodes')
+        ranks = self._get_table('ranks')
+
+        cmd = sa.text(f"""
         WITH RECURSIVE a AS (
          SELECT tax_id, parent_id, rank
           FROM {nodes}
-          WHERE tax_id = {}
+          WHERE tax_id = :tax_id
         UNION ALL
          SELECT p.tax_id, p.parent_id, p.rank
           FROM a JOIN {nodes} p ON a.parent_id = p.tax_id
         )
         SELECT a.rank, a.tax_id FROM a
         JOIN {ranks} using(rank)
-        """.format(self.placeholder, nodes=self.nodes, ranks=self.ranks_table)
+        """)
 
         # with some versions of sqlite3, an error is raised when no
         # rows are returned; with others, an empty list is returned.
         try:
-            with self.engine.connect() as con:
-                result = con.execute(cmd, (tax_id,))
-                # reorder so that root is first
-                lineage = result.fetchall()[::-1]
-        except sqlalchemy.exc.ResourceClosedError:
+            lineage = self.fetchall(cmd, tax_id=tax_id)[::-1]
+        except sa.exc.ResourceClosedError:
             lineage = []
 
         if not lineage:
@@ -282,53 +270,46 @@ def _get_lineage_table(self, tax_ids, merge_obsolete=True):
         try:
             with self.engine.connect() as con:
                 # insert tax_ids into a temporary table
-
                 temptab = self.prepend_schema(random_name(12))
 
-                cmd = 'CREATE TEMPORARY TABLE "{tab}" (old_tax_id text)'.format(
-                    tab=temptab)
-                con.execute(cmd)
+                cmd = f'CREATE TEMPORARY TABLE "{temptab}" (old_tax_id text)'
+                con.execute(sa.text(cmd))
 
                 log.info('inserting tax_ids into temporary table')
-                # TODO: couldn't find an equivalent of "executemany" - does one exist?
-                cmd = 'INSERT INTO "{tab}" VALUES ({x})'.format(
-                    tab=temptab, x=self.placeholder)
-                for tax_id in tax_ids:
-                    con.execute(cmd, tax_id)
+                cmd = sa.text(f'INSERT INTO "{temptab}" VALUES (:tax_id)')
+                con.execute(cmd, [{'tax_id': tax_id} for tax_id in tax_ids])
 
                 log.info('executing recursive CTE')
-                cmd = Template("""
+                cmd = sa.text(Template("""
                 WITH RECURSIVE a AS (
                  SELECT tax_id as tid, 1 AS ord, tax_id, parent_id, rank
-                  FROM {{ nodes }}
+                  FROM "{{ nodes }}"
                   WHERE tax_id in (
                   {% if merge_obsolete %}
                   SELECT COALESCE(m.new_tax_id, "{{ temptab }}".old_tax_id)
-                    FROM "{{ temptab }}" LEFT JOIN {{ merged }} m USING(old_tax_id)
+                    FROM "{{ temptab }}"
+                    LEFT JOIN {{ merged }} m USING(old_tax_id)
                   {% else %}
                   SELECT * from "{{ temptab }}"
-                  {% endif %}
-                  )
+                  {% endif %})
                 UNION ALL
                  SELECT a.tid, a.ord + 1, p.tax_id, p.parent_id, p.rank
-                  FROM a JOIN {{ nodes }} p ON a.parent_id = p.tax_id
+                  FROM a JOIN "{{ nodes }}" p ON a.parent_id = p.tax_id
                 )
                 SELECT a.tid, a.tax_id, a.parent_id, a.rank, tax_name FROM a
-                JOIN {{ names }} using(tax_id)
+                JOIN "{{ names }}" using(tax_id)
                 WHERE names.is_primary
                 ORDER BY tid, ord desc
                 """).render(
                     temptab=temptab,
                     merge_obsolete=merge_obsolete,
-                    merged=self.merged,
-                    nodes=self.nodes,
-                    names=self.names,
-                )
+                    **self.tables))
 
                 result = con.execute(cmd)
                 rows = result.fetchall()
 
-                con.execute('DROP TABLE "{}"'.format(temptab))
+                con.execute(sa.text(f'DROP TABLE "{temptab}"'))
+
                 log.info('returning lineages')
                 if not rows:
                     raise ValueError('no tax_ids were found')
@@ -345,7 +326,7 @@ def _get_lineage_table(self, tax_ids, merge_obsolete=True):
 
                 return rows
 
-        except sqlalchemy.exc.ResourceClosedError:
+        except sa.exc.ResourceClosedError:
             raise ValueError('tax id "{}" not found'.format(tax_id))
 
     def is_below(self, lower, upper):
@@ -369,21 +350,22 @@ def synonyms(self, tax_id=None, tax_name=None):
         names = self.names
 
         if tax_name:
-            s1 = select([names.c.tax_id], names.c.tax_name == tax_name)
-            res = s1.execute().fetchone()
+            res = self.fetchone(
+                select(names.c.tax_id)
+                .filter_by(tax_name=tax_name))
 
             if res:
                 tax_id = res[0]
             else:
-                msg = '"{}" not found in names.tax_names'.format(tax_name)
+                msg = f'"{tax_name}" not found in names.tax_names'
                 raise ValueError(msg)
 
-        s = select([names.c.tax_name, names.c.is_primary],
-                   names.c.tax_id == tax_id)
-        output = s.execute().fetchall()
+        output = self.fetchall(
+            select(names.c.tax_name, names.c.is_primary)
+            .filter_by(tax_id=tax_id))
 
         if not output:
-            raise ValueError('"{}" not found in names.tax_id'.format(tax_id))
+            raise ValueError(f'"{tax_name}" not found in names.tax_id')
 
         return output
 
@@ -417,6 +399,24 @@ def lineage(self, tax_id=None, tax_name=None):
 
         return ldict
 
+    def fetchone(self, statement, **params):
+        """Return first result of select statement 'statement'. If
+        statement is a text statement, variable substitutions can be
+        provided as keyword arguments.
+
+        """
+        with Session(self.engine) as session:
+            return session.execute(statement, params).fetchone()
+
+    def fetchall(self, statement, **params):
+        """Return all results of select statement 'statement'. If
+        statement is a text statement, variable substitutions can be
+        provided as keyword arguments.
+
+        """
+        with Session(self.engine) as session:
+            return session.execute(statement, params).fetchall()
+
     def add_source(self, source_name, description=None):
         """Adds a row to table "source" if "name" does not
         exist. Returns (source_id, True) if a new row is created,
@@ -424,50 +424,49 @@ def add_source(self, source_name, description=None):
 
         """
 
-        # TODO: shoud be able to do this inside a transaction
-
         if not source_name:
             raise ValueError('"source_name" may not be None or an empty string')
 
-        sel = select([self.source.c.id], self.source.c.name == source_name).execute()
-        result = sel.fetchone()
+        result = self.fetchone(
+            select(self.source.c.id).filter_by(name=source_name))
+
         if result:
-            return result[0], False
+            return (result[0], False)
         else:
-            ins = self.source.insert().execute(
-                name=source_name, description=description)
-            return ins.inserted_primary_key[0], True
+            with self.engine.begin() as conn:
+                stmt = sa.insert(self.source).values(
+                    name=source_name, description=description)
+                result = conn.execute(stmt)
+                return (result.inserted_primary_key[0], True)
 
     def get_source(self, source_id=None, source_name=None):
-        """Returns a dict with keys ['id', 'name', 'description'] or None if
-        no match. The ``id`` field is guaranteed to be an int that
-        exists in table source. Requires exactly one of ``source_id``
-        or ``source_name``. A new source corresponding to
-        ``source_name`` is created if necessary.
+        """Returns a dict with keys ('id', 'name', 'description'). The
+        ``id`` field is guaranteed to be an int that exists in table
+        source. Requires exactly one of ``source_id`` or
+        ``source_name``.
 
         """
 
         if not (bool(source_id) ^ bool(source_name)):
-            raise ValueError('exactly one of source_id or source_name is required')
+            raise ValueError(
+                'exactly one of source_id and source_name is required')
 
         if source_id:
             try:
-                source_id = int(source_id)
-            except (ValueError, TypeError):
-                raise ValueError(
-                    'source_id must be an int or a string representing one')
+                condition = {'id': int(source_id)}
+            except (ValueError, TypeError) as ex:
+                raise ValueError(f'"{source_id}" is not an integer') from ex
+        elif source_name:
+            condition = {'name': source_name}
 
-            sel = select([self.source], self.source.c.id == source_id).execute()
-        else:
-            sel = select([self.source], self.source.c.name == source_name).execute()
+        result = self.fetchone(select(self.source).filter_by(**condition))
 
-        result = sel.fetchone()
         if not result:
             raise ValueError(
                 'there is no source with id {} or name {}'.format(
                     source_id, source_name))
 
-        return dict(list(zip(list(sel.keys()), result)))
+        return result._asdict()
 
     def verify_rank_integrity(self, tax_id, rank, parent_id, children):
         """Confirm that for each node the parent ranks and children ranks are
@@ -496,19 +495,20 @@ def _lower(n1, n2):
         return True
 
     def has_node(self, tax_id):
-        result = select([self.nodes], self.nodes.c.tax_id == tax_id).execute()
-        return bool(result.fetchone())
+        result = self.fetchone(
+            select(self.nodes)
+            .filter_by(tax_id=tax_id))
+        return bool(result)
 
-    def add_node(self, tax_id, parent_id, rank, names, source_name, children=None,
-                 is_valid=True, execute=True, **ignored):
+    def add_node(self, tax_id, parent_id, rank, names, source_name,
+                 children=None, is_valid=True, execute=True, **ignored):
         """Add a node to the taxonomy.
 
         ``source_name`` is added to table "source" if necessary.
-
         """
 
         if ignored:
-            log.info('some arguments were ignored: {} '.format(str(ignored)))
+            log.info(f'some arguments were ignored: {ignored} ')
 
         children = children or []
         self.verify_rank_integrity(tax_id, rank, parent_id, children)
@@ -547,25 +547,29 @@ def add_node(self, tax_id, parent_id, rank, names, source_name, children=None,
 
         # add children and update source_id
         for child in children:
-            statements.append(self.nodes.update(
-                whereclause=self.nodes.c.tax_id == child,
-                values={'parent_id': tax_id, 'source_id': source_id}))
+            statements.append(
+                sa.update(self.nodes)
+                .where(self.nodes.c.tax_id == child)
+                .values(parent_id=tax_id, source_id=source_id))
 
         if execute:
             self.execute(statements)
         else:
             return statements
 
-    def update_node(self, tax_id, source_name, parent_id=None, rank=None, names=None,
-                    children=None, is_valid=None, execute=True, **ignored):
+    def update_node(self, tax_id, source_name, parent_id=None, rank=None,
+                    names=None, children=None, is_valid=None, execute=True,
+                    **ignored):
 
         children = children or []
         source_id, __ = self.add_source(source_name)
 
         statements = []
 
-        result = select([self.nodes], self.nodes.c.tax_id == tax_id).execute()
-        current = dict(list(zip(list(result.keys()), result.fetchone())))
+        result = self.fetchone(
+            select(self.nodes)
+            .where(self.nodes.c.tax_id == tax_id))
+        current = result._asdict()
 
         # update node
         values = dict(source_id=source_id)
@@ -582,8 +586,10 @@ def update_node(self, tax_id, source_name, parent_id=None, rank=None, names=None
             assert isinstance(is_valid, bool)
             values['is_valid'] = is_valid
 
-        statements.append(self.nodes.update(
-            whereclause=self.nodes.c.tax_id == tax_id, values=values))
+        statements.append(
+            sa.update(self.nodes)
+            .where(self.nodes.c.tax_id == tax_id)
+            .values(values))
 
         # add names if any are provided; names are assumed to be new;
         # there is no checking for primary names.
@@ -597,9 +603,10 @@ def update_node(self, tax_id, source_name, parent_id=None, rank=None, names=None
 
         # add children
         for child in children:
-            statements.append(self.nodes.update(
-                whereclause=self.nodes.c.tax_id == child,
-                values={'parent_id': tax_id}))
+            statements.append(
+                sa.update(self.nodes)
+                .where(self.nodes.c.tax_id == child)
+                .values(parent_id=tax_id))
 
         if execute:
             self.execute(statements)
@@ -609,7 +616,6 @@ def update_node(self, tax_id, source_name, parent_id=None, rank=None, names=None
     def add_name(self, tax_id, tax_name, source_name=None, source_id=None,
                  name_class='synonym', is_primary=False, is_classified=None,
                  execute=True, **ignored):
-
         """Add a record to the names table corresponding to
         ``tax_id``. Arguments are as follows:
 
@@ -639,27 +645,30 @@ def add_name(self, tax_id, tax_name, source_name=None, source_id=None,
         assert isinstance(is_primary, bool)
         assert is_classified in {None, True, False}
         if ignored:
-            log.info('some arguments were ignored: {} '.format(str(ignored)))
+            log.info(f'some arguments were ignored: {ignored} ')
 
         source_id = self.get_source(source_id, source_name)['id']
 
         statements = []
 
         if is_primary:
-            statements.append(self.names.update(
-                whereclause=self.names.c.tax_id == tax_id,
-                values={'is_primary': False}))
-
-        statements.append(self.names.insert().values(
-            tax_id=tax_id,
-            tax_name=tax_name,
-            source_id=source_id,
-            is_primary=is_primary,
-            name_class=name_class,
-            is_classified=is_classified))
+            statements.append(
+                sa.update(self.names)
+                .where(self.names.c.tax_id == tax_id)
+                .values(is_primary=False))
+
+        statements.append(
+            sa.insert(self.names)
+            .values(tax_id=tax_id,
+                    tax_name=tax_name,
+                    source_id=source_id,
+                    is_primary=is_primary,
+                    name_class=name_class,
+                    is_classified=is_classified))
 
         if execute:
-            self.execute(statements)
+            self.execute(
+                statements, errormsg=f'{tax_id}, {tax_name} already exists')
         else:
             return statements
 
@@ -697,19 +706,20 @@ def sibling_of(self, tax_id):
         returns None if there is no sibling.
         """
         parent_id, rank = self._node(tax_id)
-        s = select([self.nodes.c.tax_id],
-                   and_(self.nodes.c.parent_id == parent_id,
+
+        output = self.fetchone(
+            select(self.nodes.c.tax_id)
+            .where(and_(self.nodes.c.parent_id == parent_id,
                         self.nodes.c.tax_id != tax_id,
-                        self.nodes.c.rank == rank))
-        res = s.execute()
-        output = res.fetchone()
-        if not output:
-            msg = 'No sibling of tax_id {} with rank {} found in taxonomy'
-            msg = msg.format(tax_id, rank)
-            log.warning(msg)
-            return None
-        else:
+                        self.nodes.c.rank == rank)))
+
+        if output:
             return output[0]
+        else:
+            log.info(
+                f'No sibling of tax_id {tax_id} '
+                f'with rank {rank} found in taxonomy')
+            return None
 
     def is_ancestor_of(self, node, ancestor):
         if node is None or ancestor is None:
@@ -736,40 +746,41 @@ def child_of(self, tax_id):
         a proper rank below that of tax_id (i.e., genus, species, but
         not no_rank or below_below_kingdom).
         """
-        _, rank = self._node(tax_id)
-        s = select([self.nodes.c.tax_id],
-                   and_(self.nodes.c.parent_id == tax_id,
+        __, rank = self._node(tax_id)
+
+        output = self.fetchone(
+            select(self.nodes.c.tax_id)
+            .where(and_(self.nodes.c.parent_id == tax_id,
                         or_(*[self.nodes.c.rank == r
-                              for r in self.ranks_below(rank)])))
-        res = s.execute()
-        output = res.fetchone()
-        if not output:
-            msg = ('No children of tax_id {} with '
-                   'rank below {} found in database')
-            msg = msg.format(tax_id, rank)
-            log.warning(msg)
-            return None
-        else:
+                              for r in self.ranks_below(rank)]))))
+
+        if output:
             r = output[0]
             assert self.is_ancestor_of(r, tax_id)
             return r
+        else:
+            log.warning(f'No children of tax_id {tax_id} with '
+                        f'rank below {rank} found in database')
+            return None
 
     def children_of(self, tax_id, n):
-        # TODO: replace with recursive CTE
-        parent_id, rank = self._node(tax_id)
-        s = select([self.nodes.c.tax_id],
-                   and_(self.nodes.c.parent_id == tax_id,
+
+        # TODO: replace with recursive CTE?
+        __, rank = self._node(tax_id)
+        output = self.fetchall(
+            select(self.nodes.c.tax_id)
+            .where(and_(self.nodes.c.parent_id == tax_id,
                         or_(*[self.nodes.c.rank == r
-                              for r in self.ranks_below(rank)]))).limit(n)
-        res = s.execute()
-        output = res.fetchall()
-        if not output:
-            return []
-        else:
+                              for r in self.ranks_below(rank)])))
+            .limit(n))
+
+        if output:
             r = [x[0] for x in output]
             for x in r:
                 assert self.is_ancestor_of(x, tax_id)
             return r
+        else:
+            return []
 
     def parent_id(self, tax_id, rank=None):
         parent_id, tax_rank = self._node(tax_id)
diff --git a/taxtastic/templates/add_pg_constraints.sql b/taxtastic/templates/add_pg_constraints.sql
new file mode 100644
index 00000000..5c30f815
--- /dev/null
+++ b/taxtastic/templates/add_pg_constraints.sql
@@ -0,0 +1,15 @@
+-- statements were created by pg_dump in this order
+{% set schema = schema or 'public' %}
+
+ALTER TABLE ONLY {{ schema }}.merged ADD CONSTRAINT merged_pkey PRIMARY KEY (old_tax_id);
+ALTER TABLE ONLY {{ schema }}.names ADD CONSTRAINT names_pkey PRIMARY KEY (tax_id, tax_name, name_class);
+ALTER TABLE ONLY {{ schema }}.nodes ADD CONSTRAINT nodes_pkey PRIMARY KEY (tax_id);
+ALTER TABLE ONLY {{ schema }}.ranks ADD CONSTRAINT ranks_height_key UNIQUE (height);
+ALTER TABLE ONLY {{ schema }}.ranks ADD CONSTRAINT ranks_pkey PRIMARY KEY (rank);
+ALTER TABLE ONLY {{ schema }}.source ADD CONSTRAINT source_name_key UNIQUE (name);
+ALTER TABLE ONLY {{ schema }}.source ADD CONSTRAINT source_pkey PRIMARY KEY (id);
+ALTER TABLE ONLY {{ schema }}.merged ADD CONSTRAINT merged_new_tax_id_fkey FOREIGN KEY (new_tax_id) REFERENCES {{ schema }}.nodes(tax_id) ON DELETE CASCADE;
+ALTER TABLE ONLY {{ schema }}.names ADD CONSTRAINT names_source_id_fkey FOREIGN KEY (source_id) REFERENCES {{ schema }}.source(id);
+ALTER TABLE ONLY {{ schema }}.names ADD CONSTRAINT names_tax_id_fkey FOREIGN KEY (tax_id) REFERENCES {{ schema }}.nodes(tax_id) ON DELETE CASCADE;
+ALTER TABLE ONLY {{ schema }}.nodes ADD CONSTRAINT nodes_rank_fkey FOREIGN KEY (rank) REFERENCES {{ schema }}.ranks(rank);
+ALTER TABLE ONLY {{ schema }}.nodes ADD CONSTRAINT nodes_source_id_fkey FOREIGN KEY (source_id) REFERENCES {{ schema }}.source(id);
diff --git a/taxtastic/templates/add_pg_indexes.sql b/taxtastic/templates/add_pg_indexes.sql
new file mode 100644
index 00000000..e2966dff
--- /dev/null
+++ b/taxtastic/templates/add_pg_indexes.sql
@@ -0,0 +1,14 @@
+{% set schema = schema or 'public' %}
+
+CREATE INDEX ix_ranks_rank ON {{ schema }}.ranks (rank);
+
+CREATE INDEX ix_nodes_tax_id ON {{ schema }}.nodes (tax_id);
+CREATE INDEX ix_nodes_parent_id ON {{ schema }}.nodes (parent_id);
+CREATE INDEX ix_nodes_source_id ON {{ schema }}.nodes (source_id);
+CREATE INDEX ix_nodes_rank ON {{ schema }}.nodes (rank);
+
+CREATE INDEX ix_names_is_primary ON {{ schema }}.names (is_primary);
+CREATE INDEX ix_names_tax_id ON {{ schema }}.names (tax_id);
+CREATE INDEX ix_names_tax_id_is_primary ON {{ schema }}.names (tax_id, is_primary);
+
+CREATE INDEX ix_merged_old_tax_id ON {{ schema }}.merged (old_tax_id);
diff --git a/taxtastic/templates/drop_pg_constraints.sql b/taxtastic/templates/drop_pg_constraints.sql
new file mode 100644
index 00000000..ceb27407
--- /dev/null
+++ b/taxtastic/templates/drop_pg_constraints.sql
@@ -0,0 +1,28 @@
+{% set schema = schema or 'public' %}
+
+DROP INDEX ix_ranks_rank;
+DROP INDEX ix_nodes_tax_id;
+DROP INDEX ix_nodes_parent_id;
+DROP INDEX ix_nodes_source_id;
+DROP INDEX ix_nodes_rank;
+DROP INDEX ix_names_is_primary;
+DROP INDEX ix_names_tax_id;
+DROP INDEX ix_names_tax_id_is_primary;
+DROP INDEX ix_merged_old_tax_id;
+
+ALTER TABLE ONLY {{ schema }}.merged DROP CONSTRAINT merged_new_tax_id_fkey;
+ALTER TABLE ONLY {{ schema }}.merged DROP CONSTRAINT merged_pkey;
+
+ALTER TABLE ONLY {{ schema }}.names DROP CONSTRAINT names_pkey;
+ALTER TABLE ONLY {{ schema }}.names DROP CONSTRAINT names_source_id_fkey;
+ALTER TABLE ONLY {{ schema }}.names DROP CONSTRAINT names_tax_id_fkey;
+
+ALTER TABLE ONLY {{ schema }}.nodes DROP CONSTRAINT nodes_pkey;
+ALTER TABLE ONLY {{ schema }}.nodes DROP CONSTRAINT nodes_rank_fkey;
+ALTER TABLE ONLY {{ schema }}.nodes DROP CONSTRAINT nodes_source_id_fkey;
+
+ALTER TABLE ONLY {{ schema }}.ranks DROP CONSTRAINT ranks_height_key;
+ALTER TABLE ONLY {{ schema }}.ranks DROP CONSTRAINT ranks_pkey;
+
+ALTER TABLE ONLY {{ schema }}.source DROP CONSTRAINT source_name_key;
+ALTER TABLE ONLY {{ schema }}.source DROP CONSTRAINT source_pkey;
diff --git a/taxtastic/utils.py b/taxtastic/utils.py
index f8ee2c09..4bdf71c9 100644
--- a/taxtastic/utils.py
+++ b/taxtastic/utils.py
@@ -20,9 +20,9 @@
 import subprocess
 import string
 import random
+import configparser
 from collections import OrderedDict
 
-from six.moves import configparser
 
 log = logging
 
@@ -344,7 +344,7 @@ def add_database_args(parser):
 
 def sqlite_default():
     '''
-    Prepend default scheme if none is specified. This helps provides backwards
+    Prepend default schema if none is specified. This helps provides backwards
     compatibility with old versions of taxtastic where sqlite was the automatic
     default database.
     '''
@@ -354,7 +354,7 @@ def parse_url(url):
             if not url.startswith('sqlite:///'):
                 url = 'sqlite:///' + url
         elif url.endswith('.cfg') or url.endswith('.conf'):
-            conf = configparser.SafeConfigParser(allow_no_value=True)
+            conf = configparser.ConfigParser(allow_no_value=True)
             conf.optionxform = str  # options are case-sensitive
             conf.read(url)
             url = conf.get('sqlalchemy', 'url')


Test patch:
diff --git a/.github/workflows/unittests.yml b/.github/workflows/unittests.yml
index 0e6d75ee..e7a6e414 100644
--- a/.github/workflows/unittests.yml
+++ b/.github/workflows/unittests.yml
@@ -1,16 +1,32 @@
+---
 name: Run Taxtastic unittests
 
 on:
   push:
-    branches: [ master ]
+    branches:
+      - "*"
   pull_request:
-    branches: [ master ]
+    branches:
+      - "master"
 
 jobs:
   build:
+
     runs-on: ubuntu-latest
-    container:
-      image: ghcr.io/fhcrc/taxtastic:latest
+    strategy:
+      matrix:
+        python-version: ["3.8", "3.9", "3.10", "3.11"]
+
     steps:
-    - uses: actions/checkout@v3
-    - run: python -m unittest discover
+      - uses: actions/checkout@v3
+      - name: Set up Python ${{ matrix.python-version }}
+        uses: actions/setup-python@v4
+        with:
+          python-version: ${{ matrix.python-version }}
+      - name: Install package and dependencies
+        run: |
+          python -m pip install --upgrade pip wheel
+          python -m pip install .
+      - name: Run tests
+        run: |
+          python -m unittest discover
diff --git a/tests/test_ncbi.py b/tests/test_ncbi.py
index c7b887e4..75655ec6 100644
--- a/tests/test_ncbi.py
+++ b/tests/test_ncbi.py
@@ -4,7 +4,8 @@
 import os
 from os import path
 import logging
-import sqlalchemy
+
+import sqlalchemy as sa
 
 import taxtastic
 import taxtastic.ncbi
@@ -24,11 +25,11 @@
 class TestDbconnect(TestBase):
 
     def test01(self):
-        engine = sqlalchemy.create_engine('sqlite:///' + ncbi_master_db)
+        engine = sa.create_engine('sqlite:///' + ncbi_master_db)
         taxtastic.ncbi.db_connect(engine)
         with engine.begin() as con:
-            result = con.execute(
-                'select name from sqlite_master where type = "table"')
+            result = con.execute(sa.text(
+                'select name from sqlite_master where type = "table"'))
             tables = set(i[0] for i in result)
             self.assertTrue(
                 set(['nodes', 'names', 'merged', 'source']).issubset(tables))
@@ -44,7 +45,7 @@ def setUp(self):
     def test01(self):
         # we should be starting from scratch
         self.assertFalse(path.isfile(self.db_path))
-        engine = sqlalchemy.create_engine(self.url)
+        engine = sa.create_engine(self.url)
 
         taxtastic.ncbi.db_connect(engine)
         self.assertTrue(path.isfile(self.db_path))
diff --git a/tests/test_subcommands.py b/tests/test_subcommands.py
index 8b18bbb4..361e2573 100644
--- a/tests/test_subcommands.py
+++ b/tests/test_subcommands.py
@@ -8,7 +8,7 @@
 import csv
 import sys
 
-import sqlalchemy
+import sqlalchemy as sa
 
 from taxtastic import refpkg
 from taxtastic.subcommands import (
@@ -399,8 +399,9 @@ def test06(self):
 class TestAddNode(TestBase):
 
     def setUp(self):
-        self.suppress_stdout()
-        self.suppress_stderr()
+        if '-v' not in sys.argv:
+            self.suppress_stdout()
+            self.suppress_stderr()
 
         self.outdir = self.mkoutdir()
         self.dbname = os.path.join(self.outdir, 'taxonomy.db')
@@ -418,49 +419,55 @@ def test_new_nodes01(self):
 
     def test_new_nodes02(self):
         # fails without --source-name
-        args = ['add_nodes', self.dbname, data_path('new_nodes_ok_nosource.yml')]
+        args = ['add_nodes', self.dbname,
+                data_path('new_nodes_ok_nosource.yml')]
         self.assertNonZeroExitStatus(main(args))
 
     def test_new_nodes03(self):
-        args = ['add_nodes', self.dbname, data_path('new_nodes_ok_nosource.yml'),
+        args = ['add_nodes', self.dbname,
+                data_path('new_nodes_ok_nosource.yml'),
                 '--source-name', 'some_source']
         self.assertZeroExitStatus(main(args))
 
     def test_new_nodes04(self):
-        args = ['add_nodes', self.dbname, data_path('staph_species_group.yml'),
+        args = ['add_nodes', self.dbname,
+                data_path('staph_species_group.yml'),
                 '--source-name', 'foo']
         self.assertZeroExitStatus(main(args))
 
-        tax = Taxonomy(sqlalchemy.create_engine('sqlite:///' + self.dbname))
+        tax = Taxonomy(sa.create_engine('sqlite:///' + self.dbname))
         with tax.engine.connect() as con:
-            result = con.execute(
-                'select * from nodes where parent_id = ?', ('stapha_sg',))
-            keys = list(result.keys())
-            nodes = [dict(list(zip(keys, row))) for row in result.fetchall()]
+            result = tax.fetchall(
+                sa.text('select source_id from nodes '
+                        'where parent_id = :parent_id'),
+                parent_id='stapha_sg'
+            )
 
-        self.assertEqual(len(nodes), 5)
-        self.assertEqual([row['source_id'] for row in nodes], [2] * len(nodes))
+        self.assertEqual(len(result), 5)
+        self.assertEqual([row[0] for row in result], [2] * len(result))
 
     def test_new_nodes05(self):
         args = ['add_nodes', self.dbname, data_path('staph_species_group2.yml')]
         self.assertZeroExitStatus(main(args))
 
-        tax = Taxonomy(sqlalchemy.create_engine('sqlite:///' + self.dbname))
+        tax = Taxonomy(sa.create_engine('sqlite:///' + self.dbname))
         with tax.engine.connect() as con:
-            result = con.execute(
-                'select * from nodes where parent_id = ?', ('stapha_sg',))
-            keys = list(result.keys())
-            nodes = [dict(list(zip(keys, row))) for row in result.fetchall()]
+            result = tax.fetchall(
+                sa.text('select source_id from nodes '
+                        'where parent_id = :parent_id'),
+                parent_id='stapha_sg'
+            )
 
-        self.assertEqual(len(nodes), 5)
-        self.assertEqual([row['source_id'] for row in nodes], [2] * len(nodes))
+        self.assertEqual(len(result), 5)
+        self.assertEqual([row[0] for row in result], [2] * len(result))
 
 
 class TestExtractNodes(TestBase):
 
     def setUp(self):
-        self.suppress_stdout()
-        self.suppress_stderr()
+        if '-v' not in sys.argv:
+            self.suppress_stdout()
+            self.suppress_stderr()
 
         self.outdir = self.mkoutdir()
         self.dbname = os.path.join(self.outdir, 'taxonomy.db')
diff --git a/tests/test_taxonomy.py b/tests/test_taxonomy.py
index 4a47577f..1990922c 100644
--- a/tests/test_taxonomy.py
+++ b/tests/test_taxonomy.py
@@ -3,7 +3,9 @@
 from os import path
 import logging
 import shutil
+from itertools import groupby
 
+import sqlalchemy as sa
 from sqlalchemy import create_engine
 
 from . import config
@@ -37,7 +39,7 @@ class TestAddNode(TestTaxonomyBase):
 
     def setUp(self):
         self.dbname = path.join(self.mkoutdir(), 'taxonomy.db')
-        log.info(self.dbname)
+        # log.info(self.dbname)
         shutil.copyfile(dbname, self.dbname)
         super(TestAddNode, self).setUp()
 
@@ -161,33 +163,34 @@ def test08(self):
 
 class TestAddName(TestTaxonomyBase):
     """
-    test tax.add_node
+    test tax.add_name
     """
 
-    def count_names(self, tax_id):
+    def query(self, query, **params):
         with self.tax.engine.connect() as con:
-            result = con.execute(
-                'select count(*) from names where tax_id = ?', (tax_id,))
-            return result.fetchone()[0]
+            result = con.execute(sa.text(query), params)
+            return result.fetchone()
+
+    def count_names(self, tax_id):
+        result = self.query(
+            'select count(*) from names where tax_id=:tax_id', tax_id=tax_id)
+        return result[0]
 
     def count_primary_names(self, tax_id):
-        with self.tax.engine.connect() as con:
-            result = con.execute(
-                'select count(*) from names where tax_id = ? and is_primary',
-                (tax_id,))
-            return result.fetchone()[0]
+        result = self.query(
+            'select count(*) from names where tax_id=:tax_id and is_primary',
+            tax_id=tax_id)
+        return result[0]
 
     def primary_name(self, tax_id):
-        with self.tax.engine.connect() as con:
-            result = con.execute(
-                'select tax_name from names where tax_id = ? and is_primary',
-                (tax_id,))
-            val = result.fetchone()
-            return val[0] if val else None
+        result = self.query(
+            'select tax_name from names where tax_id=:tax_id and is_primary',
+            tax_id=tax_id)
+        return result[0] if result else None
 
     def setUp(self):
         self.dbname = path.join(self.mkoutdir(), 'taxonomy.db')
-        log.info(self.dbname)
+        # log.info(self.dbname)
         shutil.copyfile(dbname, self.dbname)
         super(TestAddName, self).setUp()
 
@@ -219,6 +222,63 @@ def test_name03(self):
         self.assertEqual(self.primary_name('1280'), 'SA')
 
 
+class TestAddNames(TestTaxonomyBase):
+    """
+    test tax.add_names
+    """
+
+    def setUp(self):
+        self.dbname = path.join(self.mkoutdir(), 'taxonomy.db')
+        # log.info(self.dbname)
+        shutil.copyfile(dbname, self.dbname)
+        super(TestAddNames, self).setUp()
+
+    def test01(self):
+
+        names = [
+            dict(tax_id='1280', tax_name='SA', is_primary=True,
+                 source_name='ncbi'),
+            dict(tax_id='1280', tax_name='SA2', is_primary=False,
+                 source_name='ncbi'),
+        ]
+
+        count_before = self.tax.fetchone(
+            sa.text("select count(*) from names where tax_id = :tax_id"),
+            tax_id='1280')[0]
+
+        self.tax.add_names(tax_id='1280', names=names)
+
+        count_after = self.tax.fetchone(
+            sa.text("select count(*) from names where tax_id = :tax_id"),
+            tax_id='1280')[0]
+
+        self.assertEqual(count_before + 2, count_after)
+
+    def test02(self):
+
+        names = [
+            dict(tax_id='1280', tax_name='SA', is_primary=True,
+                 source_name='ncbi'),
+            dict(tax_id='1280', tax_name='SA2', is_primary=True,
+                 source_name='ncbi'),
+        ]
+
+        self.assertRaises(
+            ValueError, self.tax.add_names, tax_id='1280', names=names)
+
+    def test03(self):
+
+        names = [
+            dict(tax_id='1280', tax_name='SA', is_primary=True,
+                 source_name='ncbi'),
+            dict(tax_id='1280', tax_name='SA', is_primary=False,
+                 source_name='ncbi'),
+        ]
+
+        self.assertRaises(
+            ValueError, self.tax.add_names, tax_id='1280', names=names)
+
+
 class TestGetSource(TestTaxonomyBase):
     def setUp(self):
         self.dbname = dbname
@@ -249,7 +309,7 @@ def test05(self):
 class TestAddSource(TestTaxonomyBase):
     def setUp(self):
         self.dbname = path.join(self.mkoutdir(), 'taxonomy.db')
-        log.info(self.dbname)
+        # log.info(self.dbname)
         shutil.copyfile(dbname, self.dbname)
         super(TestAddSource, self).setUp()
 
@@ -258,15 +318,17 @@ def tearDown(self):
 
     def sources(self):
         with self.tax.engine.connect() as con:
-            result = con.execute('select * from source')
+            result = con.execute(sa.text('select * from source'))
             return result.fetchall()
 
     def test01(self):
-        self.tax.add_source('foo')
+        res = self.tax.add_source('foo')
+        self.assertEqual(res, (2, True))
         self.assertEqual(self.sources()[1], (2, 'foo', None))
 
     def test02(self):
-        self.tax.add_source('ncbi')
+        res = self.tax.add_source('ncbi')
+        self.assertEqual(res, (1, False))
         self.assertEqual(
             self.sources(),
             [(1, 'ncbi', 'ftp://ftp.ncbi.nih.gov/pub/taxonomy/taxdmp.zip')])
@@ -292,6 +354,8 @@ def test_sibling_of(self):
     def test_child_of(self):
         self.assertRaises(ValueError, self.tax.child_of, None)
         self.assertEqual(self.tax.child_of('1239'), '91061')
+
+    def test_children_of(self):
         self.assertEqual(self.tax.children_of('90964', 2), ['1279', '45669'])
 
     def test_is_ancestor_of(self):
@@ -333,3 +397,27 @@ def test_nary_subtree(self):
         self.assertEqual(
             self.tax.nary_subtree('1239'),
             ['1280', '1281', '45670', '138846'])
+
+
+class TestGetLineageTable(TestTaxonomyBase):
+
+    def setUp(self):
+        self.dbname = path.join(self.mkoutdir(), 'taxonomy.db')
+        # log.info(self.dbname)
+        shutil.copyfile(dbname, self.dbname)
+        super(TestGetLineageTable, self).setUp()
+
+    def tearDown(self):
+        pass
+
+    def test01(self):
+        lineage = self.tax._get_lineage_table(['1280', '1379'])
+
+        species = {}
+        for tax_id, grp in groupby(lineage, lambda row: getattr(row, 'tid')):
+            species[tax_id] = list(grp)[-1]
+
+        self.assertEqual(species['1280'].tax_name, 'Staphylococcus aureus')
+        self.assertEqual(species['1379'].tax_name, 'Gemella haemolysans')
+
+