3 ms·

While in general I agree, database libs are probably the worst example of this - in practice you can't just swap sqlite for postgres or mysql. I usually avoid t

While in general I agree, database libs are probably the worst example of this - in practice you can't just swap sqlite for postgres or mysql. I usually avoid the generic driver wherever possible and use one specific to my database.

impulser_ 3y ago
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.
- ilyt 3y ago
  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...
- chatmasta 3y ago
  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.
  - riceart 3y ago
    Database client libraries typically don’t parse SQL.
    - chatmasta 3y ago
      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).
- jrockway 3y ago
  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.
  - josephg 3y ago
    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.
fractalb 3y ago
Exactly. It's like a feature on a paper, but I don't think anyone would ever dare to make a database driver switch in production.
kitd 3y ago
Agreed. Wrapping all db access in its own class or module is one of the easiest architectural decisions to be made in a project. Allows you to use specific drivers but isolates changes to a few easily found locations. Makes it dead simple to test too.

impulser_ 3y ago

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.

ilyt 3y ago
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...
chatmasta 3y ago
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.
- riceart 3y ago
  Database client libraries typically don’t parse SQL.
  - chatmasta 3y ago
    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).
jrockway 3y ago
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.
- josephg 3y ago
  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.

fractalb 3y ago

Exactly. It's like a feature on a paper, but I don't think anyone would ever dare to make a database driver switch in production.

kitd 3y ago

Agreed. Wrapping all db access in its own class or module is one of the easiest architectural decisions to be made in a project. Allows you to use specific drivers but isolates changes to a few easily found locations. Makes it dead simple to test too.