Is it possible to work with different schemas in Fluent with PostgreSQL?

Question

I need to create tables with different number of fields for different users. I want to create a schema for each user, and in it a custom set of tables. I can do this with direct database queries. This will create a categories table in the schema with the given name.:

    func create(in schema: String, on db: Database) async throws {
        let query: SQLQueryString = """
        CREATE TABLE IF NOT EXISTS \(raw: schema).categories (
            id      uuid PRIMARY KEY,
            name    text NOT NULL,
            ...
        );
        """
        if let sql = db as? SQLDatabase {
            try await sql.raw(query).run()
        }
    }

But I would like to do this with Fluent. (the my_schema schema and the necessary rights to it were previously created in the database) But the following code creates the "my_schema.categories" table in the public schema:

    func create(in schema: String, on db: Database) async throws {
        try await db.schema("\(schema).categories")
            .id()
            .field("name", .string, .required)
            ...
            .create()
    }

Is it possible to work with different schemas in Fluent and how to query tables from different schemas? I will be grateful for any ideas.

Answer 1

Yes, this was recently introduced to Fluent. Update to the latest version and then you can add a new static property on your model:

final class MyModel: Model {
  static let schema = "table_name"
  static let space = "schema_name"
  // ...
}

See the docs for more details.

Answer 2

Hopefully I am understanding your exact request correctly. If so, using multiple schemas is pretty simple.

(1) First in your configuration define your schema connections, making sure to specify DatabaseID and defaults:

app.databases.use(.postgres(connection_info), as: .psql, isDefault: true)
app.databases.use(.postgres(connection_info), as: .otherDB, isDefault: false)

(2) Run your create table migrations specifying the database schema to add the table to:

app.migrations.add(MigrateCategories())
app.migrations.add(MigrateSomeOtherTable(), to: .otherDB)

(3) Query tables from different database schemas:

categories.create(on: req.db())
someOtherTable.create(on: req.db(.otherDB))

Now you've got two databases

1. .psql is default and has one table called categories
2. .otherDB has one table called someOtherTable

Use the DatabaseID to indicate where you want to create new tables and where you want to run queries.