Migration table schema

[illyria1]

How can I change the default schema of "migration" table? In our application dont have access to public schema and when I run "piccolo migrations forwards" it gives "asyncpg.exceptions.InsufficientPrivilegeError: permission denied for schema public"
We're using currently piccolo 0.121.0

[dantownsend]

It's a good point - currently migrations assume the default schema is being used.

I'll have to add this as a new feature. Probably allowing the user to specify it in piccolo_conf.py.

In the mean time, the solutions are:

Modify your local copy of Piccolo

Change this line:

https://github.com/piccolo-orm/piccolo/blob/28fe3a4fe3266d17c7a37406dc7fdbd92223d5f6/piccolo/apps/migrations/tables.py#L10C1-L10C24

To this instead:

class Migration(Table, schema="my_schema"):

Copy the migrations app into your project

Copy this entire folder into your project:

https://github.com/piccolo-orm/piccolo/tree/master/piccolo/apps/migrations

You might have to change this line to something like 'my_migrations':

piccolo/piccolo/apps/migrations/piccolo_app.py

Line 10 in 28fe3a4

app_name="migrations",

And then register this new app in piccolo_conf.py

[illyria1]

That worked thanks. Looking forward for the update!

[aabmets]

@dantownsend
Would this solution work as well?

from piccolo.apps.migrations import tables
from myapp import config

class Migration(tables.Migration, schema=config.MY_SCHEMA):
    pass

tables.Migration = Migration

Would this solution correctly override the schema of the migrations table by replacing the original Migrations table with a custom one which has its schema explicitly defined from a configuration constant? This solution would not require pulling piccolo source code into user projects, so its cleaner.

[dantownsend]

That's a clever idea - I think it might work.

[aabmets]

I decided to go with this function in my utils.py module:

import os
import sys
import importlib

def patch_piccolo(migrations_schema_override: str) -> None:
    os.environ['PICCOLO_CONF'] = 'db.piccolo_conf'
    path = search_upwards("src").as_posix()
    sys.path.append(path)
    
    tables = importlib.import_module(
        "piccolo.apps.migrations.tables"
    )

    class CustomMigration(
        tables.Migration,
        tablename="piccolo_migrations",
        schema=migrations_schema_override
    ):
        pass

    tables.Migration = CustomMigration

In my db_migrate.py script, it is invoked as such:

import asyncio
import argparse
from argparse import Namespace
from src.config import AppMeta, AppConfig
from src.common import utils

utils.patch_piccolo(migrations_schema_override=AppConfig.DB_SCHEMA)

from piccolo.apps.migrations.commands import (
    forwards, backwards, new
)

# file contents omitted for irrelevance

Note: this is an elegant hack, but a hack nonetheless.
It would be pleasant, if Piccolo provided this feature out-of-the-box.

[dantownsend]

What do you think of a solution like this?

#928

[AlexanderMakarov]

I've achieved the same with just

from piccolo.apps.migrations.tables import Migration
Migration._meta.schema = migrations_schema_override

But unfortunately it (actually all approaches listed here so far) forces me to update all migrations with replacing schema=None to schema="custom-schema". While it is viable for just renaming the default schema I'm searching a way to implement multi-tenancy with tenant-per-schema approach where I need to change schema per-request, not "per applying migrations for single tenant".