Hacker News Reader

Comment by impulser_ | Hacker News Reader

You actually can, you might have to update some of your queries but if you use basic SQL queries you can switch drivers without changing code.

View on HN

I would highly recommend against this. For example, postgres and sqlite differ in what they accept as meaning 'true' or 'false'. Try this with both databases, 'create table foo (whatever bool not null)', then insert 't', 'true', 1, true, 'f', 'false', 0, false. Postgres will reject 0 and 1. Now query for true and false values, like 'select count(1) from foo where whatever=true'. Sqlite returns 2! Postgres returns 3. Do the same for false, same results. Now ask Sqlite the count of 'select count(1) from foo where whatever != true and whatever != false'. It returns 4.

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 what its worth, I think it would be great if standards groups wrote up robust test suites to go along with their standards.

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.

You've actually described what true TDD is, you test requirements which leaves you free to change your implementation without fear that you have broken anything. Also (as you have pointed out) makes writing a faster implementation much easier.

Another downside is you lose access to all your database specific functionality, or at best makes it more complicated to use as you need to unwrap the abstraction layer.

Yeah well, if you read the doc, you know that SQLite doesn't support the boolean type.

"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.

SQLite to Postgres ? maybe

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...

Parts of the library are interchangeable, but inevitably much of it is not. A Postgres and SQLite client might be able to share some query parsing logic between the two databases, but the wire protocol (or lack thereof for SQLite) is not the same, nor is the exact sub/super-set of the SQL standard implemented by each. Basically the only code you could reliably share is an AST parser for the most common subset of the SQL standard that's implemented by both.

Database client libraries typically don’t parse SQL.

Yeah, that's true, so that's one less feature they'd be able to share. Unless you're creating an ORM, there's not much left aside from connecting to the database (different for all of them), sending packets (different for all of them), and maybe parameterizing queries. So basically you can make a query builder. Then of course you can have "drivers" for each database, but I'd consider that philosophically different from using the same library "without switching code" (IMO separate drivers implies "switching code" since each driver needs to be maintained separately and depends on a different upstream dependency).

Reply on news.ycombinator.com