If you were using an ORM, usually the ORM would expect to be sitting on top of the lower-level database/sql. (It's turtles all the way down!)
Dropping the database/sql compatibility requirement allows for more direct (and possibly faster) interaction with the database. This approach can unlock database-specific features and potentially improve performance, but it clearly deviates from the standard API envisioned in database/sql. You might also end up losing the ability to use an ORM, but usually you don't really want to mix ORM and non-ORM in the same codebase anyway.
However, adhering to database/sql also brings significant benefits, such as simplifing switching between different database systems with only small code alterations. While schema changes might still be necessary, they are generally external to the core codebase.
Pretty much the only thing the lower level wrapper does for you is simplify some of the memory management inherent to a C API (who allocates? who frees? for everything even something as trivial as an error message: GC+copying handles lightweight types like strings and errors, io.Closer is used for everything else).
At the same time, I also made a serious effort to make many SQLite features (like blob IO, nested transactions, JSON, time handling) easy to access for users of the database/sql driver.
A lot of the interesting features of SQLite are connection oriented, and compared to other databases, connections are cheap (because it's all local). So the things database/sql offers are (relatively) less valuable, and it makes other things harder.
Yet (IMO) connection pools still offer some performance benefits, not having to worry about (re)preparing statements on new connections is still helpful, and many of the connection oriented things you might want to do can be exposed through database/sql with some imagination.
I have a lot of respect for crawshaw and zombiezen; I started from their PoV. But I've since come around to database/sql.
A lot of what is mentioned in [1] has been fixed in database/sql or can be fixed in drivers: parameter names now work in database/sql; their approach to statement caching isn't necessarily better, but can be added to a driver, or live in a compatible framework; panics vs. errors is contentious; savepoints can be made to work just fine [2]; and for contexts, I started with crawshaw and built an improved version of it into my driver, that is both low overhead, interacts well with the Go scheduler, and doesn't start additional goroutines.
Living in database/sql land gives you access to other amazing tooling like SQLC, just to name a single one. And it doesn't need to be either/or: drivers should support both high and low level APIs.
[1]: https://crawshaw.io/blog/go-and-sqlite
[2]: https://pkg.go.dev/github.com/ncruces/go-sqlite3/driver#exam...
Not using a standard interface (i.e. database/sql) sounds dodgy, but maybe there's really no problem.
Apologies if this is a stupid question.