6 ms·

The comments here are full of people bucking against this advice. I've worked at TWO companies now where people said NO we want to use SQLIte in dev. Both switc

The comments here are full of people bucking against this advice. I've worked at TWO companies now where people said NO we want to use SQLIte in dev. Both switched in under a year despite fierce internal opposition. Everyone had changed their tune once they hit growth in users and complexity. Why? Because being ideologically right is not as nice as being sure you're writing code that works. The fact is if you have a serious site there's a good chance SOME specific feature will start looking appealing ( maybe array values, JSON , window functions, a pg extension...). These features are written because people use them, we aren't all writing dead simple CRUD apps. Or you your ORM will interact with one slightly differently than the other. I don't expect to convince any of you doubters, but the first time you get a bug in prod that slipped through the testing process but really could have been caught in dev, you'll ask yourself why you weren't using pg there too. There's no sane reason to complicate your life by running two databases unless you either have a dead simple app, or are one of the jabbering idealists I see on here. Productive programmers simplify the problem and minimize their tech stacks.

anthonybsd 11y ago
I'll buck against this advice any day. I've never used SQLite for mocking but I've used HSQLDB for functional tests of an extensive ORM-derived DAOs that talked to Oracle in production in the past. It was pretty flawless. I.e. when running in Oracle emulation mode HSQLDB was pretty close to the real thing for our purposes. For a few tiny differences we had automatic script that would convert from Oracle schema creation file to HSQLDB "Oracle" schema creation file any time changes to the real schema were introduced.
- batbomb 11y ago
  I was going to say this too: HSQLDB is more suitable, at least for me, testing environment than SQLite.
- electrum 11y ago
  For Java, see my other comment for how to run real PostgreSQL or MySQL from unit tests: https://news.ycombinator.com/item?id=10003789 https://news.ycombinator.com/item?id=10003789
darkxanthos 11y ago
I don't understand the argument against this. I have had even small sites behave differently when moving between Postgres and SQLite. I <3 SQLite but it's not a golden hammer.
- mhurron 11y ago
  > I don't understand the argument against this Write only pure, portable SQL. Using RDBMS extensions is evil. Enligtenment only comes to those who are pure(ly using SQL).
  - jeltz 11y ago
    The problem is that it is not jsut the extensions that differ, it is also quite fundamental parts. * SQLite is dynamically typed. * The text types differ in meaning between databases (text, varchar, nvarchar, varchar2, ...). I also beleive char works in different ways in different databases, but I do not use blank padded strings so I am not sure. * Time types and fucntions are very different. * Oracle and PostgreSQL run at a different default isolation level than MySQL and MSSQL. * MSSQL has read locks on rows, which can cause deadlocks which cannot happen in other databases. * Autoincrement is not implemented in the same way in different databases. * Text collations and text equality works differently in different datbases. (If they care about trailing blanks, case, etc and the order tuples are sorted in.) And these are jsut the ones I recall on top of my head.
    - mhurron 11y ago
      I am not supprting the position.
    - adrianmsmith 11y ago
      To add to your list: * Oracle treats the empty string and null as equivalent, PostgreSQL, MySQL and MSSQL treat the empty string and null as distinct.
  - hvs 11y ago
    We aren't discussing religion, we are discussing software development. There is no need to eschew extensions if that's the only database your company uses.
LoSboccacc 11y ago
I am for both: we run a brutaly different dev environment than production: windows, 32 bit, hsqldb, windows codepage against linux, 64bit, postgres, utf8 codepage we also have a beta environment that's a perfect mirror of production down to the vm vendor and package version and an alpha environment that's on a cheaper vendor and uses a more updated version of production os/packages (and has experimental features of our software enabled) this maybe slows down development a little, but catches a brand whole class of interesting error that are normally hidden and wait to happen till the next time you need to upgrade something in the production environment then again we do have some sane lib that hides the horrible differences between databases so we have a whole class of bugs that's managed by that layer. (no it's not an ORM I hate those)
- sirtaj 11y ago
  What sort of constraints do you have that your dev environment can't be a VM running the same software as production?
  - LoSboccacc 11y ago
    people get accustomed to do things a certain way, then we upgrade to a newer postgres or a newer whatever everything breaks down and we have to track down dozens of little papercuts. if anyone has ever tried to upgrade a ruby app to a newer ubuntu release, he should know that building no variance in the deployment environment is a recipe to never upgrade it. (no I'm not using rails currently, I have to build a thing that needs to live more than two years)
- drdaeman 11y ago
  Seems that someone's running wild, downvoting every comment they disagree with. I believe neither maintaining a code that's compatible with multiple databases, nor using a single one is a mistake per se. Whatever rocks your boat is the attitude - one just needs to be aware of consequences (both good and bad ones) of chosen path. But, yeah, the linked article's warning is completely valid - testing on a single platform, then deploying to another is likely to encounter some issues one day.

anthonybsd 11y ago

I'll buck against this advice any day. I've never used SQLite for mocking but I've used HSQLDB for functional tests of an extensive ORM-derived DAOs that talked to Oracle in production in the past. It was pretty flawless. I.e. when running in Oracle emulation mode HSQLDB was pretty close to the real thing for our purposes. For a few tiny differences we had automatic script that would convert from Oracle schema creation file to HSQLDB "Oracle" schema creation file any time changes to the real schema were introduced.

batbomb 11y ago
I was going to say this too: HSQLDB is more suitable, at least for me, testing environment than SQLite.
electrum 11y ago
For Java, see my other comment for how to run real PostgreSQL or MySQL from unit tests: https://news.ycombinator.com/item?id=10003789 https://news.ycombinator.com/item?id=10003789

darkxanthos 11y ago

I don't understand the argument against this. I have had even small sites behave differently when moving between Postgres and SQLite. I <3 SQLite but it's not a golden hammer.

mhurron 11y ago
> I don't understand the argument against this Write only pure, portable SQL. Using RDBMS extensions is evil. Enligtenment only comes to those who are pure(ly using SQL).
- jeltz 11y ago
  The problem is that it is not jsut the extensions that differ, it is also quite fundamental parts. * SQLite is dynamically typed. * The text types differ in meaning between databases (text, varchar, nvarchar, varchar2, ...). I also beleive char works in different ways in different databases, but I do not use blank padded strings so I am not sure. * Time types and fucntions are very different. * Oracle and PostgreSQL run at a different default isolation level than MySQL and MSSQL. * MSSQL has read locks on rows, which can cause deadlocks which cannot happen in other databases. * Autoincrement is not implemented in the same way in different databases. * Text collations and text equality works differently in different datbases. (If they care about trailing blanks, case, etc and the order tuples are sorted in.) And these are jsut the ones I recall on top of my head.
  - mhurron 11y ago
    I am not supprting the position.
  - adrianmsmith 11y ago
    To add to your list: * Oracle treats the empty string and null as equivalent, PostgreSQL, MySQL and MSSQL treat the empty string and null as distinct.
- hvs 11y ago
  We aren't discussing religion, we are discussing software development. There is no need to eschew extensions if that's the only database your company uses.

LoSboccacc 11y ago

I am for both: we run a brutaly different dev environment than production: windows, 32 bit, hsqldb, windows codepage against linux, 64bit, postgres, utf8 codepage we also have a beta environment that's a perfect mirror of production down to the vm vendor and package version and an alpha environment that's on a cheaper vendor and uses a more updated version of production os/packages (and has experimental features of our software enabled) this maybe slows down development a little, but catches a brand whole class of interesting error that are normally hidden and wait to happen till the next time you need to upgrade something in the production environment then again we do have some sane lib that hides the horrible differences between databases so we have a whole class of bugs that's managed by that layer. (no it's not an ORM I hate those)

sirtaj 11y ago
What sort of constraints do you have that your dev environment can't be a VM running the same software as production?
- LoSboccacc 11y ago
  people get accustomed to do things a certain way, then we upgrade to a newer postgres or a newer whatever everything breaks down and we have to track down dozens of little papercuts. if anyone has ever tried to upgrade a ruby app to a newer ubuntu release, he should know that building no variance in the deployment environment is a recipe to never upgrade it. (no I'm not using rails currently, I have to build a thing that needs to live more than two years)
drdaeman 11y ago
Seems that someone's running wild, downvoting every comment they disagree with. I believe neither maintaining a code that's compatible with multiple databases, nor using a single one is a mistake per se. Whatever rocks your boat is the attitude - one just needs to be aware of consequences (both good and bad ones) of chosen path. But, yeah, the linked article's warning is completely valid - testing on a single platform, then deploying to another is likely to encounter some issues one day.