If you do decide to use "modernc.org/sqlite" you should make it switchable with mattn's behind a build tag for those of us that prefer to use the system libsqlite3. I've noticed that the auto-converted package seems to have different behaviors in it's handling of concurrent updates, which makes me concerned.
I don't think you ever want to make the database backend switchable at compile time. Every database engine has slight semantic differences and it's crucial that your program handle that correctly. If there's a difference, like you mention, you want to tell the user to get the one that you've tested.
Something that has caused me a lot of trouble in the past and is now on my "never do" list is using SQLite for tests against an app that runs Postgres in production. It's pretty similar, but passing tests easily equal failing production. (The last straw for me was SQLite considering "f" true, while Postgres considers it false.) Your suggestion is a little different than that, but the same principle applies. Test your app against the database you're going to use. If you must support two database engines, then you need a test matrix that runs tests against both.
For an application being developed professionally I agree. At least until you have an good enough reason to deviate from that course.
I wrote my comment with the experience working with deploying open source software personally recently. They already had support for multiple database backends, with SQLite being one of them. They already had test suites, and while adding support for another SQLite implementation isn't free, it did only take a few minutes.
