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.