pythonpostgresqlsqlitesqlalchemypartial-index

Generic partial Index support for sqlalchemy

Along the lines of this question, SQLAlchemy - SQLite for testing and Postgresql for development - How to port?

I realize, that the (above) the consensus is don't test with a db not used in production.

I want to abstract partial index support for sqlalchemy such that I could use either Postgres or Sqlite.

I've seen that with PostgreSQL I can use 

    Index('only_one_active_invoice', 
          invoice_id, active,
          unique=True,
          postgresql_where=(active)
    ),

But I see that partial index is also supported in sqlite https://sqlite.org/partialindex.html

Is there some sort of generic partial index support for sqlalchemy with which my module could work for either postgres or sqlite databases?

Solution

  • There is no general way, according to doc

    You should use for sqlite

    idx = Index('test_idx1', tbl.c.data,
            sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10))

    and for postgres:

    idx = Index('my_index', my_table.c.id, 
            postgresql_where=my_table.c.value > 10)

    in your case, you can check db engine and initialize index with kwargs