Fix schema creation for non-default PostgreSQL schemas

[Pritish053]

Fix PostgreSQL schema creation for non-default schemas

Summary

Fixes #1671 by implementing automatic PostgreSQL schema creation and schema-qualified SQL generation.

Previously, Tortoise ORM would fail when trying to generate tables in non-default PostgreSQL schemas because:

1. Schemas weren't automatically created
2. Table references weren't schema-qualified

This PR resolves both issues, enabling seamless use of custom PostgreSQL schemas.

Key Changes

Core Implementation

• Automatic Schema Creation: Added CREATE SCHEMA IF NOT EXISTS generation for PostgreSQL
• Schema-Qualified References: All SQL now uses proper schema qualification ("schema"."table")
• Foreign Key Support: FK references work correctly across schemas
• M2M Table Support: Many-to-many tables respect schema settings

Files Modified

• tortoise/backends/base/schema_generator.py - Added schema qualification methods
• tortoise/backends/base_postgres/schema_generator.py - PostgreSQL schema creation logic
• tests/schema/test_schema_creation.py - Comprehensive test coverage
• CHANGELOG.rst - Added entry for the fix

Before vs After

Before (fails):

class User(Model):
    name = fields.CharField(max_length=50)
    class Meta:
        schema = "pgdev"

# Tortoise.generate_schemas() would fail:
# relation "pgdev.users" does not exist

After (works):

CREATE SCHEMA IF NOT EXISTS "pgdev";
CREATE TABLE IF NOT EXISTS "pgdev"."users" (
    "id" BIGSERIAL NOT NULL PRIMARY KEY,
    "name" VARCHAR(50) NOT NULL
);

Testing

• ✅ New test suite validates schema SQL generation
• ✅ Live PostgreSQL testing confirms end-to-end functionality
• ✅ All existing tests continue to pass
• ✅ Backward compatibility verified

Checklist

• [+] My code follows the code style of this project
• [+] My change requires a change to the documentation
• [+] I have updated the documentation accordingly
• [+] I have added the changelog accordingly
• [+] I have read the CONTRIBUTING document
• [+] I have added tests to cover my changes
• [+] All new and existing tests passed

Impact: Fully backward compatible - no breaking changes for existing code.

[Pritish053]

Can anyone approve this for the workflow ? @henadzit

[codspeed-hq[bot]]

CodSpeed Performance Report

Merging #1979 will not alter performance

_{Comparing Pritish053:fix-postgresql-schema-creation (5c35bc7) with develop (65bc23e)}

Summary

✅ 16 untouched benchmarks

[aver-develop]

Hi! What the progress on this MR?

[Pritish053]

Hi! What the progress on this MR?

Will pick this up on the weekend

[Pritish053]

Thank you for the contribution!

Looks like your IDE reformatted a few file, and this makes it hard to review the PR. Can you please rollback the formatting changes?

Sure

[anjafr]

@Pritish053 any update on this? 🙏🏻 Sorely missed feature, this 😭