[Feature Request] Option to implicitly create tables within a database schema based on their model's app

[Matt del Valle]

Hi,

So currently django seems to take the name of your models (either inferring it from the model class name or, if provided, it takes it from the model Meta options), and prepends the name of the app the model belongs to with an underscore to avoid table name conflicts. It then creates that table in the database's default schema ('public' for postgres, 'dbo' for SQL server, etc.). So, my model 'Example' in my app 'some_app' becomes, on postgres,  'public.some_app_example'.

I propose an alternate mode of behaviour that can be toggled with a default False (to preserve backwards-compatibility) setting. Something like SCHEMA_PARTITION_TABLES_BY_APP (I'm sure there's a better name).

When active, django would instead create a schema for each active app and then create the model tables for that app within that schema, rather than placing all tables within the default database schema. In my above example, django would check if the schema 'some_app' exists and if not would emit a 'CREATE SCHEMA some_app' statement. Then it would create my model as 'example' within the schema 'some_app' (the fully qualified name within the database is therefore 'some_app.example').

If this option is enabled and the database backend in use does not support schemas (such as sqlite), the behavior should be either:

1) explicitly raise an error prompting the user to change this setting

2) silently default to the current behaviour

Whichever one seems more in line with Django's design philosophy.

Is there any chance this could be done? The use of schemas have many advantages over the current behaviour. There are small ones like improved legibility and better consistency with Python and SQL namespaces, but also large advantages like better database organization and the the ability to assign different access permissions to the data held by individual apps within a large django application. This would allow the data of a security-critical app within a django project to only be accessed directly by a small number of admin users, while the data within other apps could be accessed and manipulated more easily by a larger base of database maintainers or data analysts.

I realize that you can currently sort-of achieve this by manually creating the schemas ahead of time and explicitly naming your tables making use of dot-notation. But this represents extra effort, extra verbosity, requires refactoring if you move a model from one app to another, and does not provide any way of schema-segregating the tables of community apps installed by pip that you're making use of, which means you cannot consistently use schemas as a tool for database organization, as your default schema will still end up cluttered.

I'd love for this to become a new feature. Thanks in advance for your consideration.

[Matt del Valle]

EDIT:

Actually, after some further digging it turns out it's worse than I thought. Unless I'm missing something it looks like there currently isn't any non-hacky way at all to set a table's schema in django. There are workarounds that involve SQL escaping hacks such as setting a model's db_name to 'some_schema\".\"some_table' (notice the escaped double quotes) that may sometimes work in some backends but not others and can probably break without warning as django updates.

I suppose then this request is maybe more generically just "Support DB schemas in django"

[Adam Johnson]

Schema support is this 12 year old ticket: https://code.djangoproject.com/ticket/6148

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/0e12e617-ce90-4541-a769-4eb2408049e4%40googlegroups.com.

--

Adam