Does BIGINT auto increment work for SQLAlchemy with sqlite?

Question

I am trying to declare a table using SQLAlchemy. I'd like to include a BIGINT auto incrementing primary key in the table. This does not seem to work with sqlite as the DB backend. On the other hand, having INTEGER auto incrementing primary key works just fine.

I read that sqlite has ROWID that is a signed bigint. But is there a way to have a BIGINT auto increment field? This way I can swap backends without worrying about db specific issues (assuming MySQL and Postgres support bigint auto incrementing fields).

Thanks.

Answer 1

For others who get here via Google and just need a solution I have written the following code:

# SQLAlchemy does not map BigInt to Int by default on the sqlite dialect.
# It should, but it doesnt.
from sqlalchemy import BigInteger
from sqlalchemy.dialects import postgresql, mysql, sqlite

BigIntegerType = BigInteger()
BigIntegerType = BigIntegerType.with_variant(postgresql.BIGINT(), 'postgresql')
BigIntegerType = BigIntegerType.with_variant(mysql.BIGINT(), 'mysql')
BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), 'sqlite')

This will allow you to use BIGINT on a database, and INT for when you run unit tests.

Answer 2

Sqlite doesn't allow BIGINT used as an primary key with autoincrement.

But, due to dynamic nature of sqlite column types, you can make a backend-specific column type and use INTEGER type in case of sqlite backend, see SQLAlchemy: How to conditionally choose type for column by depending on its backend.

Hope that helps.