[andrewvc]

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.

[LoSboccacc]

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)

[drdaeman]

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.

[LoSboccacc]

> I believe neither maintaining a code that's compatible with multiple databases

me too! but there are soo many compatibility layers you can get and bolt on for free and they also work around version issues and things like that... of course if you need the latest version then you're in a different boat, but the generic problem of talking to a database is basically solved by now

[sirtaj]

What sort of constraints do you have that your dev environment can't be a VM running the same software as production?

[LoSboccacc]

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)

[darkxanthos]

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]

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

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.

[adrianmsmith]

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.

[mhurron]

I am not supprting the position.

[electrum]

Unfortunately, limiting yourself to only what is available in standard SQL is not practical. For example, doing idempotent inserts, insert-or-update, sequences, transactional behavior for DDL, data types, date/time manipulation, etc.

To paraphrase Tom Kyte from Oracle (Ask Tom): "Your company paid good money for Oracle and all of its features. Use them instead of wasting money reimplementing them poorly yourself."

There are many ways to achieve portability across databases, but limiting yourself to standard SQL is not the best way (at least for everything). And I say this as someone who develops an analytic database (Presto) that tries to follow standard SQL for everything.

[Mister_Snuggles]

> To paraphrase Tom Kyte from Oracle (Ask Tom): "Your company paid good money for Oracle and all of its features. Use them instead of wasting money reimplementing them poorly yourself."

This applies just as well to the open source world. You may not have paid money for PostgreSQL, but you did pay for it in that the time you spend maintaining it could be used for other things. Likewise, your time could be spent reinventing its features in your application or just using those features and spending your time building something better for your customers.

[ebiester]

And if you keep using them, you will keep loading Oracle until you need to license another 8 cores/$400,000 (list, which admittedly nobody pays.)

[hvs]

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.

[anthonybsd]

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]

I was going to say this too: HSQLDB is more suitable, at least for me, testing environment than SQLite.

[electrum]

For Java, see my other comment for how to run real PostgreSQL or MySQL from unit tests: https://news.ycombinator.com/item?id=10003789