Difference in table schemas following upgrade produced (possibly) by different alembic versions

[baileythegreen]

I have written a wrapper around alembic for a tool I maintain that is backed by an sqlite3 database. I am now working on the test suite for this wrapper, but am being stymied by a potential difference in the SQL run in different environments.

I have tests passing locally, but not on CircleCI, and have been trying to track down why. It now appears that the issue may be caused by a single line that appears in a .dump file when the file is generated on my local computer, but which is missing when the file is generated on CircleCI.

The diff between the two files is this:

! diff -y --suppress-common-lines local_version circleci_version
	CHECK (maxmatch IN (0, 1)), 			      <
	FOREIGN KEY(query_id) REFERENCES genomes (genome_id), |		FOREIGN KEY(subject_id) REFERENCES genomes (genome_id
	FOREIGN KEY(subject_id) REFERENCES genomes (genome_id |		FOREIGN KEY(query_id) REFERENCES genomes (genome_id)

The base version of the table that is being upgraded (and where the difference occurs):

CREATE TABLE comparisons (
	comparison_id INTEGER NOT NULL, 
	query_id INTEGER NOT NULL, 
	subject_id INTEGER NOT NULL, 
	aln_length INTEGER, 
	sim_errs INTEGER, 
	identity FLOAT, 
	cov_query FLOAT, 
	cov_subject FLOAT, 
	program VARCHAR, 
	version VARCHAR, 
	fragsize INTEGER, 
	maxmatch BOOLEAN, 
	PRIMARY KEY (comparison_id), 
	CONSTRAINT fastani_reqs UNIQUE (query_id, subject_id, program, version, fragsize, maxmatch, kmersize, minmatch),  
	FOREIGN KEY(query_id) REFERENCES genomes (genome_id), 
	FOREIGN KEY(subject_id) REFERENCES genomes (genome_id)
);

and the upgraded version:

CREATE TABLE IF NOT EXISTS "comparisons" (
	comparison_id INTEGER NOT NULL, 
	query_id INTEGER NOT NULL, 
	subject_id INTEGER NOT NULL, 
	aln_length INTEGER, 
	sim_errs INTEGER, 
	identity FLOAT, 
	cov_query FLOAT, 
	cov_subject FLOAT, 
	program VARCHAR, 
	version VARCHAR, 
	fragsize INTEGER, 
	maxmatch BOOLEAN, 
	kmersize INTEGER, 
	minmatch FLOAT, 
	PRIMARY KEY (comparison_id), 
	CHECK (maxmatch IN (0, 1)), 
	CONSTRAINT fastani_reqs UNIQUE (query_id, subject_id, program, version, fragsize, maxmatch, kmersize, minmatch), 
	FOREIGN KEY(query_id) REFERENCES genomes (genome_id), 
	FOREIGN KEY(subject_id) REFERENCES genomes (genome_id)
);

There's a

	CHECK (maxmatch IN (0, 1)), 

being inserted on my local machine, and I don't understand why.

The actual upgrade I am running with alembic is here, and does not alter maxmatch:

def upgrade():
    with op.batch_alter_table("comparisons") as batch_op:
        batch_op.drop_constraint("base_reqs")
        batch_op.add_column(sa.Column("kmersize", sa.Integer, default=None))
        batch_op.add_column(sa.Column("minmatch", sa.Float, default=None))
        batch_op.create_unique_constraint(
            "fastani_reqs",
            [
                "query_id",
                "subject_id",
                "program",
                "version",
                "fragsize",
                "maxmatch",
                "kmersize",
                "minmatch",
            ],
        )

The only differences I am aware of are in the alembic version being run, and the operating system.

machine	sqlite3	alembic	os
local	3.36.0	1.7.7	macOS 10.13
circleci	3.36.0	1.7.5	see below

I'm unsure which of this is relevant for Linux, so:

! uname -a
Linux f346c99b0b5c 5.13.0-1017-aws #19~20.04.1-Ubuntu SMP Mon Mar 7 12:53:12 UTC 2022 x86_64 GNU/Linux

Can this difference be explained by the different alembic versions (in which case, maybe I can just pin it)? Does that make sense?

[zzzeek]

that looks like an upgrade from SQLAlchemy 1.3 to 1.4 where the default value for Boolean.create_constraint defaults to False (previously defaulted to True). Locate the Boolean datatypes in your code and set them to either create_constraint=True or False; True would give you the backwards compatibility with 1.3.

[baileythegreen]

Looks like you got it in one! I do indeed have 1.3 locally and 1.4 on CircleCI. I should have checked sqlalchemy versions and provided that, too.

Will make that change, and hopefully everything will work now!