Possible to use a different runtime dialect than was used for generation?

[parnic]

I realize this would put the onus on the application to ensure its schema matches and doesn't have any incompatible settings (such as columns of array types between psql/sqlite), but how much work would it be to enable an application to tell sqlboiler to use a different dialect at runtime than the one that generated it? If I'd like to use postgres to generate models, for its strong enum typing, then use sqlite at runtime, say for running my application's unit tests, that would be ideal.

It seems like most of the library uses query mods anyway, so it would theoretically be pretty simple, with the appropriate API exposed (maybe a new major version?), but there are a few hardcoded raw sql statements in the generated code, such as in the Find and Exists methods, that would need to be addressed.

[stephenafamo]

All the query functions and methods generated uses a boil.Executor which is an interface satisfied by *sql.DB.

We can use anything that satisfies the interface, such as an *sql.DB opened with a different dialect. Of course, it has to support the same query structure, e.g. same quoting runes.

[stephenafamo]

AFAIK, there's no clean way to do this.

[parnic]

Consider it a request, then. I assume it would end up being a breaking change if it was something the project wanted to add. Query builders like https://github.com/gocraft/dbr and https://github.com/doug-martin/goqu can do this, but of course you lose the ORM parts to, for example, eager load associations, which sucks. Sqlboiler already has its own query builder of sorts, but I realize it would be a significant undertaking to make it compatible for runtime dialect changes, and qm.SQL() would essentially become super dangerous.

[stephenafamo]

There are many things to improve about SQLBoiler's query builder, as a matter of fact, I've been playing around with how I would approach it if I wanted to do it all afresh and I ended up with Bob.

Interestingly, if anything, I am moving even farther from dialect interoperability. I think it causes more issues than it solves. For example, in SQLBoiler here are several issues linked to dialect-specific features:

• Incorrect query builder syntax for TOP + DISTINCT on MS SQL #858
• Support for CockroachDB temporal queries #952
• Mysql SELECT ... LOCK IN SHARE MODE #214
• While there is a query mod for DISTINCT there is no clean way to do DISTINCT ON which is Postgres specific

If we ever want to support even more dialects, the interoperability issues will grow exponentially. We will either have lots of unimplemented query features that then force users to break out of the type-safe query building, or we give people tools to generate queries that do not work for their dialect.

I can see how being able to switch dialects is useful for testing, but I think the most correct thing to do is to test against the DB that you use in production.
Options that I consider are:

• TXDB
• Testcontainers

[parnic]

Neat - I saw the post about Bob in the golang subreddit a few months back. Didn't know it was yours. :)

If SQLBoiler would rather head the other direction on dialects, then fair enough. :) I appreciate the replies as it helps me plan how to use sqlboiler in my projects going forward.

The other thing to consider is rapid iteration local development where it's handy to be able to blow away the db just by deleting files. Or projects like https://github.com/go-gitea/gitea where you want to provide the ability for your users to choose their own db. Gitea uses a native migration implementation, as opposed to something like golang-migrate that generally requires .sql files, so they can more or less guarantee that all dialects behave identically at the schema level. I agree that it does require some forethought on the application developer to use dbms-neutral features or find some other workaround.

[stephenafamo]

It feels to me like we have to pick 2 out of:

1. Type Safety
2. Extensive dialect support
3. DIalect interoperability

Personally, since I've never needed to support multiple dialects at the same time. I prefer 1&2 (Bob's system). However, that makes it impossible to support projects like Gitea so there is merit in picking say 1&3.

SQLBoiler's current system does not do 2&3 well so a decision has to be made about what direction to go. The query building needs a lot of improvments.