Postgres to SQLite ? Maybe too

Any of those to MySQL ? Yeah nah I got bitten way too many times by that. And massively surprised that SQLite had some features MySQL didn't...

You are in for a world of hurt if you think your SQL statement parsing means that your application is going to work. Changing database backends, in my mind, amounts to a full rewrite of the data layer. Whatever quirks you have built around are likely to subtly change.

Oh, and one unsolicited tip. If you're using Go, don't bother with database/sql. Just use pgx + pgxpool from the get go. You aren't going to be able to plug some other database implementation in and get a working app. So the abstraction layer is basically unnecessary.

For example, commonmark has a test suite with hundreds of machine readable input / output examples. Each example has some input markdown text, and an expected parsing result. Any compliant commonmark implementation can use that as a test / conformance suite during development. As a user, result is you can be pretty sure every spec-conforming implementation will interpret the same markdown text the same way.

During Chrome's development they built something similar. They gathered the HTML from hundreds of real websites and made browser-independent data sets showing how that HTML must be parsed. That freed up the team to try out wild optimizations, with certainty that they wouldn't break compatibility. When Microsoft copied Javascript into IE, I assume they did something similar to make sure they parsed javascript in an identical way to how Netscape Navigator parsed JS.

It would be harder for a complex protocol like bluetooth, but it would be fantastic to have a "bluetooth tester" device which tried out a lot of the strange things real bluetooth devices do to make sure any implementation is compatible with any other implementation. You can tell a device like this doesn't exist (or its not very good) because of how abysmal cross-device bluetooth compatibility is in general. (Linux doesn't work properly with either my bluetooth keyboard or mouse because, while both devices apparently work on windows and macos, they don't properly adhere to the spec.)

If SQL were a better written spec, it wouldn't have random incompatibilities like that.

"SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true)."

If you choose the right SQL and right data-types, you can definitely have database independence. Our integration product (>10 years old) supports 4 diff databases for customers with a single, common code base. As long as you have the test-suites, its definitely doable. But you need to use a language which has a standard for interacting with databases.

[0]: https://github.com/jackc/pgx#choosing-between-the-pgx-and-da...

notice that the SQLite package isn't used directly, its only imported to handle the database behind the scenes. all the actual code is written using only the standard library

I haven't done any web stuff in Go, but it definitely looks like the standard library is much better suited than any other language.

> This is huge for things like database drivers which might become outdated or not support certain features. In Go switching database drivers in as simple as importing the new library. You dont have to change your code.

The effect is that every database driver becomes outdated and doesn't support certain features, instead of just a few. Python people have a saying that the standard library is where modules go to die. Java's database drivers massively lag behind (e.g. there's still no good async support for most of them), because JDBC is a lowest common denominator that every driver has to be dumbed down to, but is established enough that it sucks all the oxygen away from any efforts to write better drivers.

Far better to keep the standard library small and allow modules to update on their own terms and their own schedule. If the library you're using does what you need, you can stick with it, but a better alternative (which always means a new interface in practice - the idea that you can make substantial improvement without changing the interface is mostly a myth, because the interface is the most important part of a library) can appear and compete on its merits, and if the improvements are worth migrating to then it will win out.

Very Hard Disagree. JDBC is pretty amazing actually. Thats why you find so many Database tools and wizards written in Java as opposed to Rust.

Regarding async - With Java 19 virtual threads , you have newer versions of JDBC drivers support async with none to minimal changes. Example: https://medium.com/oracledevs/introduction-to-oracle-jdbc-21...

I am very, very glad that human civilisation standardised on one system for gears instead of every manufacturer making their own special snowflake.

Java is much older and bigger than Rust. I don't think its level of database tooling is noticeably better than other big languages with less of a standard database API (e.g. Python).

> Regarding async - With Java 19 virtual threads , you have newer versions of JDBC drivers support async with none to minimal changes.

So it's only taken what, 15 years, and even then it's only been done because they found a way to make it happen without having to actually change the API (which it remains to be seen how well that actually works in practice - I'm pretty skeptical because other platforms have tried and failed with that approach).

DB-API 2.0 (PEP 249) is very much a standard database API.

That's just a quote from a 10 years old talk by Kenneth Reitz, and it's quite an overstatement.

(JDBC isn't actually in the standard library IIRC but it's a JSR, so it's an "official" part of the platform and that creates a lot of pressure to follow that)

I rather have outdated code that works, than not having anything at all.

Only if your dependency management is bad.

And if you're stuck running on e.g. an old install of the language, there's at least a chance that the third party library will support it, whereas if you want a new standard library feature you're SOL.

And on top of that download half of the Internet for doing an hello world with error handling or asynchronous code.

Please give an example of good dependency management in any language. It seems to be one of these subjects draining constant displeasure.

I'm not advocating against third party libraries, I just think a reasonably rich standard library sets a good foundation for them.

If your dependency management is any good then there's nothing to lose and a lot to gain from splitting your libraries out and allowing them to have independent release cycles. You can always do "platform" releases that aggregate together a bunch of finer-grained libraries with versions that are known together if you have a use case for that; going the other direction is a lot harder.

I would say so, although splitting an existing standard library is still extremely painful and cumbersome thing to do (I don't think any other major language has seriously attempted it) - exactly why it's better to avoid having a large standard library to start with.

> Is Java's standard library small ?

I'd say no, but the parts of it that are used nowadays are. For example there is an HTTP client implementation in the standard library, but it's very rarely used by today's Java programs since its design is quite dated.

Same as Java. (JDBC)

Same as Python. (DB-API)

Same as Perl. (DBI)

Of course, you work as the lowest common denominator and have abstractions that don't quite match the implementation. And let's be honest, how often do you switch databases.

But yeah this is very common.

Java does, but apart from JDBC for databases, and the servlet-API, I haven't seen many cases where that really improved the world of programming. Java-EE relied heavily on such abstractions, but nowadays those would be replaced by simpler, less abstract constructs with less redirections.

> This is huge for things like database drivers which might become outdated or not support certain features. In Go switching database drivers in as simple as importing the new library. You don't have to change your code.

Database drivers are an abstraction that tends to leak it's underlying technology very fast.

The idea of just being to swap your database out under the hood is neat, but generally holds only for simple projects.

Also the abstraction that helps you here is SQL really, not the driver.

Spring, Quarkus, Helidon, Liberty, Wildfly, TomEE, Payara all support it.

It turns out such standards are quite valuable for the industry at scale, instead of playing tetris with third party library stuff of various quality.

So, standard interfaces, or dynamic or structural typing? Lots of languages have those (some all three.)