Inquiry on Multi-Tenancy Schema Creation and Migration Using Piccolo

[Kokoserver]

I am currently engaged in the development of a SaaS application that employs a multi-tenant architecture with PostgreSQL as the database system. In this setup, each tenant is allocated a unique schema that contains all the necessary tables specific to their data.

Upon reviewing the available documentation, I have identified a section that describes the modification of the schema within the table definition by passing the schema as an argument to the table class. However, I seek further clarification on the following points:

Is there a provision within the piccolo to automatically generate a separate schema for each new tenant upon registration?
Can the Piccolo migration tools be utilized to facilitate the migration of the database schema for each tenant?
Your assistance is pivotal to the progress of my project, and I would greatly appreciate any guidance or resources you could provide on these matters.

Thank you for your attention to this inquiry.

[dantownsend]

It sounds like for each user you want to create a new schema, with the identical database tables.

While Piccolo does allow you to specify which schema a table belongs to:

class MyTable(Table, schema="schema_1"):
    ...

There isn't a way to tell the database migrations to create tables in multiple schemas simultaneously.

Maybe you can create a database per client instead.

You could then have a script like this (specifying which database credentials to use each time):

export PICCOLO_CONF=user_1
piccolo migrations forwards all

export PICCOLO_CONF=user_2
piccolo migrations forwards all

It's probably not what you're after though. I'll have a think if there's a better solution. There are probably some creative solutions which will make it work.

[dantownsend]

OK, I think I may have a solution.

When you specify your DB you can set the search path, which means it will look in that schema first for tables:

DB = PostgresEngine({
    'database': 'my_database',
    'server_settings': {
        'search_path': 'my_schema',
    }
})

You can specify 'extra_nodes' (see docs, so you could add one per schema):

DB = PostgresEngine({
    'database': 'my_database',
    'extra_nodes': {
        'customer_1': PostgresEngine({
            'database': 'my_database',
            'server_settings': {
                'search_path': 'customer_1_schema',
            },
        }),
        'customer_2': PostgresEngine({
            'database': 'my_database',
            'server_settings': {
                'search_path': 'customer_2_schema',
            },
        }),
        'customer_3': PostgresEngine({
            'database': 'my_database',
            'server_settings': {
                'search_path': 'customer_3_schema',
            },
        })
    }
})

Whenever you run a query, you can specify which node to run it on. For example:

await MyTable.select().run(node='customer_3')

For each of your tables, don't specify the schema arg:

class MyTable(Table):
    ...

For the migrations, create them as usual. You'll need a special wrapper script which runs them against all schemas though.

It feels a bit like a hack, but it will do something like:

import asyncio

from piccolo.apps.migrations.commands.forwards import run_forwards
from piccolo.table import TABLE_REGISTRY
from piccolo_conf import DB

async def run():
    for node in DB.extra_nodes:
        DB.config = node.config        
        await run_forwards("all")

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

I haven't had a chance to try this - but in theory it should all work 🤞

[Kokoserver]

Wow this make sense thanks... I will try this out and give you the feedback

[Kokoserver]

Is there functionality for creating schema.

New_schema = create_schema("NEWSCHEMA")

[dantownsend]

Yeah, check out SchemaManager.

from piccolo.schema import SchemaManager

>>> await SchemaManager().create_schema(schema_name="my_schema")

[Kokoserver]

Thanks

[Kokoserver]

hello @dantownsend , i keep getting partially initialized module 'piccolo_conf' has no attribute 'APP_REGISTRY' (most likely due to a circular import)
anywhere i use piccolo LazyTableReference because i have lot of apps, is there a better way to handle this?.

According to the example on the documentation on many to many both, the joining key must be added to both table that the joining key reference.

[Kokoserver]

you can give me an example of what you meant

[dantownsend]

Sorry, I wasn't very clear. Just this:

async def run():
    from piccolo_conf import DB

    for node in DB.extra_nodes:
        DB.config = node.config        
        await run_forwards("all")

[Kokoserver]

i have just done that, i think i'm getting something wrong it raises this error, when i reference another app tables using the lazyTableReference.
let me show an example of what i mean:

class Designation(TenantBasedTable, tablename="ex_designation"):
    name = col.Varchar(length=100)
    employees = col.M2M(
        col.LazyTableReference(
            "DesignationToEmployee",
            app_name="employee",
        )
    )
    salary_components = col.M2M(
        col.LazyTableReference("SalaryToDesignation", app_name="benefit")
    )
    leave_types = col.M2M(
        col.LazyTableReference("LeaveToDesignation", app_name="leave")
    )


class DesignationToEmployee(TenantBasedTable, tablename="designation_to_employee"):
    designation = col.ForeignKey(
        col.LazyTableReference("Designation", app_name="designation")
    )
    employee = col.ForeignKey(col.LazyTableReference("Employee", app_name="employee"))

[dantownsend]

I would get rid of as many of the LazyTableReference as you can - in some situations it can still lead to circular imports. For example, just use ForeignKey(Designation).

[Kokoserver]

alright... let me make some changes. Thanks