Is it possible to use multiple schemas with SQLiteEngine?

[aabmets]

I have a situation, where my Python app uses Postgres as the production database, but I would like to pytest my app functionality in the CI/CD pipeline using a sqlite database. My app however needs to read and write to multiple schemas. How can I achieve this desired result with Piccolo?

[dantownsend]

Running the tests against SQLIte instead of Postgres should be fairly straightforward. In your CI set the PICCOLO_CONF environment variable, so it points to a different piccolo_conf.py file where DB is a SQLiteEngine.

This is what we do with Piccolo's test suite:

https://github.com/piccolo-orm/piccolo/blob/master/scripts/test-sqlite.sh

Which means it uses this file to get the database credentials:

https://github.com/piccolo-orm/piccolo/blob/master/tests/sqlite_conf.py

The difficult bit is the schemas, as SQLite doesn't support schemas like Postgres.

You could write something like this, so when using SQLite, all of the tables are treated as being in the same schema:

from piccolo.engine import engine_finder
from piccolo.engine.sqlite import SQLiteEngine
from piccolo.table import Table


def get_schema(schema_name: str) -> str | None:
    return None if isinstance(engine_finder(), SQLiteEngine) else schema_name


class MyTable(Table, schema=get_schema('schema_1')):
    ...

Alternatively, you could have multiple SQLite engines, and use those in place of schemas.

from piccolo.engine import engine_finder
from piccolo.engine.sqlite import SQLiteEngine
from piccolo.table import Table

DB_1 = SQLiteEngine('db_1.sqlite')
DB_2 = SQLiteEngine('db_1.sqlite')

TESTING = isinstance(engine_finder(), SQLiteEngine)

class MyTable(Table, db=DB_1 if TESTING else engine_finder(), schema=None if TESTING else 'schema_1'):
    ...

class MyTable2(Table, db=DB_2 if TESTING else engine_finder(), schema=None if TESTING else 'schema_2'):
    ...

[aabmets]

@dantownsend Thank you for the solution!
Also, I recognize that I probably should have created this topic as a discussion,
so if you deem it necessary, maybe it can be converted from an issue to a discussion.