Use the same schema and model for different tables (MySQL)

[Zannick]

I'm building a project where the library code uses generics, and auto-generated implementations will provide any specific types or other settings needed. This way I can avoid a lot of code duplication. I'm not sure how well this will work for adding a diesel table to the library, though.

Ideally, I'd use a single model and schema in the library code, and the dependent binary will set the table name (either statically or at runtime, I don't care). The database url is set at runtime, but in this case all binaries use the same database so users only need to do one-time MySQL setup even if they use multiple binaries (tables can be created automatically but not databases), and I can restrict the MySQL permissions a bit.

If that's not possible, which would be better?

• Separate databases for each binary? To avoid the need to add additional one-time setup, the MySQL user running these binaries would have to be granted the CREATE DATABASE permission so it can be done by the program.
• Separate schemas and models for each binary? This adds code duplication of the models and schema at least, and either I duplicate all the queries into the binaries (and expose them back to the library via traits) or I find a clean way to provide the types from the binary code to the library.

[weiznich]

This question is rather generic and therefore hard to answer with specific examples. Generally speaking it should be possible to use the same "model" (although even that term is problematic as it's not the classical ORM model, but more a 1:1 mapping with your queries rather than tables) for diesel with different schemas. After all anything that derives Queryable only maps query results to struct based on the order. That doesn't care about field names, etc as long as the order, number and type of types match it should just work.

For dynamic schemas you use diesel-dynamic-schema which allows you to define your schemas at runtime, which in turn should allow you to easily swap out the table name, etc.

Hopefully that gives you an idea how to approach your problem.

As for using traits: That's certainly also possible, but I personally would advice not to try that as that quickly leads to really nasty generic code.

[Zannick]

I'm using "model" in the sense of a struct in src/models.rs with a corresponding src/schema.rs, and my question is about using the same "model" for multiple tables using the same schema file (rather than making identical copies of src/schema.rs in each downstream with distinct names).

Surely if it's just a mapping of query fields I ought to be able to use it flexibly like you say but I don't see a way to change the table name in the API. Is there a way to do that in diesel without switching to dynamic schemas?

[Zannick]

A quick test of executing a query produced a runtime error for the table not existing. But I can't see a way to replace the compiled-in table name in the from_clause, except via using sql or sql_query to set it up that way.

[weiznich]

I'm not sure what else to add to what I've written above: If you want to have a dynamic schema you need to use `diesel-dynamic-schema' (or build your own extensions). It's an explicit design decision to not offer ways to dynamically change the table name of static schemas.

[Zannick]

It's an explicit design decision to not offer ways to dynamically change the table name of static schemas.

I didn't see this documented anywhere but it is the answer I was looking for (that it's not possible), so thank you.