Multi-tenant database per tenant setup

[pavdwest]

Hi,

Reading through the docs and using the playground I really like Piccolo's design, however for most of my use cases I need a database per tenant. I'm sure I'm just missing something obvious but I can't figure out how to get started, e.g.

from piccolo.engine.sqlite import SQLiteEngine
from piccolo.table import Table
from piccolo.columns import Varchar, Integer


DB1 = SQLiteEngine(path='db/db1.sqlite')
DB2 = SQLiteEngine(path='db/db2.sqlite')


class Band(Table):
    name = Varchar()
    popularity = Integer()


async def main():
    # Create the table in both databases - How to specify DB1?
    await Band.create_table().run()

    # Create the table in both databases - How to specify DB2?
    await Band.create_table().run()

    # Insert some data into the first database - How to specify DB1?
    pythonistas = Band(name='Pythonistas', popularity=100)
    await pythonistas.save().run()

    # Insert some data into the second database - How to specify DB2?
    rustaceans = Band(name='Rustaceans', popularity=90)
    await rustaceans.save().run()

    # Read the data from the first database - How to specify DB1?
    pythonistas = await Band.select().run()

    # Read the data from the second database - How to specify DB2?
    rustaceans = await Band.select().run()


if __name__ == '__main__':
    import asyncio
    asyncio.run(main())

Some guidance would be greatly appreciated.

[sinisaos]

@pavdwest I don't know if there is a better way to do it, but you can use a transaction. Something like this works in my case

from piccolo.engine.sqlite import SQLiteEngine
from piccolo.table import Table
from piccolo.columns import Varchar, Integer

DB1 = SQLiteEngine(path="db/db1.sqlite")
DB2 = SQLiteEngine(path="db/db2.sqlite")


class Band(Table):
    name = Varchar()
    popularity = Integer()


async def main():
    async with DB1.transaction():
        # set first engine
        Band._meta.db = DB1
        await Band.create_table(if_not_exists=True).run()
        # insert data to first databse
        pythonistas = Band(name="Pythonistas", popularity=100)
        await pythonistas.save().run()
        # Read the data from the first database
        pythonistas = await Band.select().run()
        print(pythonistas)

    async with DB2.transaction():
        # set second engine
        Band._meta.db = DB2
        # insert data to second databse
        await Band.create_table(if_not_exists=True).run()
        rustaceans = Band(name="Rustaceans", popularity=90)
        await rustaceans.save().run()
        # Read the data from the second database
        rustaceans = await Band.select().run()
        print(rustaceans)


if __name__ == "__main__":
    import asyncio

    asyncio.run(main())

[pavdwest]

Thanks @sinisaos, much appreciated!

[dantownsend]

If it was Postgres, you can use the extra_nodes feature:

https://piccolo-orm.readthedocs.io/en/latest/piccolo/engines/postgres_engine.html#source

But for SQLite, we don't really have anything like that.

You could create your tables on demand with the relevant DB using piccolo.tables.create_table_class.

Here are a couple of other discussions which might be helpful:

#904
#993

[dantownsend]

Maybe we could add extra_nodes to SQLiteEngine in the future - the feature was added for read replicas, but multi tenancy has come up a few times.

[pavdwest]

Thanks, much appreciated @dantownsend!