3 ms·

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,

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.
- imran-iq 3y ago
  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.
andrewchambers 3y ago
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.
lenkite 3y ago
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.

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.

imran-iq 3y ago
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.

andrewchambers 3y ago

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.

lenkite 3y ago

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.