Pluggable database backends for Cot

[seqre]

Why open this

Right now Cot's database layer is monolithic: PostgreSQL, MySQL and SQLite are baked into core (cot/src/db/impl_*.rs), all sharing the SQLx + SeaQuery stack. It works, and it gives us nice compile-time guarantees, but it has some structural downsides:

• The core team has to implement and maintain every backend.
• There's no path for third parties to add a backend without patching core.
• Backend-specific logic ends up scattered across the db module.
• The community can't easily experiment with other stores.

We want to refactor this subsystem. This discussion is about the design - we're laying out some ideas and the open questions, and we want input before we lock the shape in.

What we want (goals)

• Pluggability - third-party backends as separate crates, no core changes required.
• Keep the type safety - we shouldn't lose Rust's compile-time guarantees to get there.
• Low overhead - abstraction shouldn't cost us at runtime, and ideally not much at compile time either.
• Clear seams - backend responsibilities split into focused pieces rather than one god-trait.

One possible shape

A trait-based, Django-inspired layout. The pieces worth discussing:

Split the layer into separate crates. A cot-db-core with the traits/types, pluscot-db-postgres / cot-db-mysql / cot-db-sqlite as the reference backends that core depends on by default. Third parties publish e.g. cot-db-clickhouse on the same contract.

Provider + registry, keyed by URL scheme. Each backend ships a "provider" that declares which URL schemes it handles (postgres://, redis://, …). The app registers providers, and Database::from_url(...) resolves the right one. Registration could be explicit on an app builder, e.g.:

CotApp::builder()
    .project(MyProject)
    .database_backend(cot_db_clickhouse::ClickHouseProvider)
    .build()

Split a backend into focused concern-traits instead of one trait:

• DatabaseFeatures - capability detection (supports_foreign_keys, supports_returning_clause, supports_json_fields, identifier limits, …), defaulting to false/conservative so backends opt in.
• DatabaseOperations - pure SQL/value formatting (quoting, datetime/bool formatting, limit-offset).
• SchemaEditor - DDL (create/drop/alter table, indexes, constraints).
• DatabaseIntrospection - read schema metadata back out.
• MigrationEngine and QueryBuilder - migrations and query construction.

Async only where there's actual I/O. A hard line: connections, queries, transactions, schema changes and introspection are async; feature detection, SQL string generation, config parsing and type conversion are sync. Keeps the pure-compute paths ergonomic and avoids needless async plumbing.

A custom-type escape hatch. A SqlValue enum with the standard types plus a Custom { type_name, data } variant and a SqlValueConvertible trait, so backends can expose things like PostGIS geometry, jsonb, enums, or user-defined types without core knowing about them. Apps could query features().supports_custom_type_by_name("geometry") and degrade gracefully.

The design questions we actually want input on

1. How wide should the abstraction be? The obvious temptation is to model everything - including Redis and ClickHouse - through a query(sql, params) shaped trait. That leaks: Redis isn't SQL, ClickHouse isn't OLTP. Do we (a) define a SQL-relational contract and accept that non-SQL stores don't fit, (b) define a narrower core contract with optional capability traits layered on, or (c) stay relational-only and not pretend otherwise?

2. Async-in-traits - how do we want to handle dyn? If we need dyn DatabaseBackend we hit the usual async-fn-in-traits friction. We now have AFIT on stable, plus trait-variant / dynosaur for the object-safe dyn case. Do we need dyn at all, or can backends be a generic type parameter / enum? That choice changes everything downstream, so it's worth settling early.

3. Relationship to SeaQuery / SQLx. We already lean on both. Does a QueryBuilder trait duplicate SeaQuery? Should the pluggable seam sit below SeaQuery (just connection + execution + dialect) and let SeaQuery keep doing query construction?

4. Capability detection: runtime methods vs types. Booleans like supports_upsert() are simple but push failures to runtime. Is there an appetite for encoding some capabilities in the type system, or is runtime detection the pragmatic sweet spot?

5. Migrations across backends. Our migration engine is one of the stronger parts of the current system. How much of it generalizes, and how much is inherently dialect-specific?

What would help

• Real use cases for a non-default backend (which one, and why core can't serve it today).
• Prior art you like from other Rust ORMs (Diesel's backend traits, SeaORM, sqlx itself) or from Django/SQLAlchemy that we should steal or deliberately avoid.

We'd start by refactoring the existing three backends behind a clean internal trait boundary, then open that boundary up as the public plugin contract once it's proven. That sequencing derisks the abstraction before we commit to anything third parties depend on.

[m4tx]

Regarding the questions:

1. I think we shouldn't assume the ORM runs on SQL. This also extends to naming structs such as "SqlValue" (DbValue instead?), or the supports_returning_clause capability (this should be handled internally by the implementations). We should only support relational databases by default, but if someone decides to shove something else into the system, we shouldn't actively block it. (but the user needs to acknowledge it's not a 100% supported scenario)
2. I think the backend should stay dynamic - mostly because we allow specifying the exact backend via a config file.
3. SeaQuery is just an implementation detail; we shouldn't generally expose it anywhere.
4. The database backend is pluggable via a config file, so we should probably just rely on runtime detection. The developer can always use asserts at the project initialization (maybe we should provide some built-in mechanism for this?)
5. The migrations should never rely on a specific database backend, unless the user specifically wants it (e.g. by writing a custom migration). Most of the Operation implementation and such should be handled by the implementations themselves.

Otherwise, the plan sound fairly sound to me, and in general should be mostly fine to implement as proposed.

One edge case I've bumped into recently: SQLite doesn't support ALTER FIELD, so we need to do "create-copy-drop-rename" table dance. We need to keep this in mind when implementing the new migration engine (specifically, the engine should probably keep the schema of each known model at each step, unlike now).

[m4tx]

One more use case that we currently don't support: there's no way to fetch a Model that physically doesn't exist in the database. This can be useful for executing complex queries that return columns that are not defined in any table. Model should get a new parent trait (FromDb or something similar) that just defines how to convert the data from the database into Rust structs, but doesn't denote an actual table.