Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Don't test with SQLite when you use Postgres in Production

michael.robellard.com

Post reply on HN
Print view View original

91–100 of 110 posts

Re: Don't test with SQLite when you use Postgres in Production

#91

Post by jeltz »

DiThi wrote: post #58
pimterry wrote: post #25

Better: Don't test only with SQLLite when you use Postgres in Production. His points are all valid, you definitely shouldn't release something to production that you haven't tested thoroughly in a separate identical environment. That doesn't mean you should never test with SQLLite though. A good pattern I see all the time is to have a final stage of system tests that run slowly but very accurately in a production-equ…

I came here to say something along those lines. Our project has a SQLite backend for hassle-free local development, and we use Postgres in all remote servers. Tests run both in local and remote.

What hassle did you expereince when using PostgreSQL locally? On the projects I have been in we have ran PostgreSQL in local development almost painlessly. PostgreSQL is quite developer firendly, at least for my use cases.

I suggest using syncrhonous_commit=off to sped up the test suite and to use unix sockets (assuming you do not run Windows).

Re: Don't test with SQLite when you use Postgres in Production

#92

Post by code_duck »

I've run Linux as my desktop for a long time, so I've always found it natural to set up my desktop as closely matched to the deployment server environment as possible. It's very simple to set up MySQL on Debian or Ubuntu, and only slightly more trouble on OSX.

Using SQLite would actually be more trouble and I've never seen a need to use it all for development. Any time I've tried to use SQLite, it's been pointless as some of my queries written to run on MySQL or Postgres fail due to lack of support for some feature or another. And I already have plenty of MySQL and pg running on my systems, so...

Re: Don't test with SQLite when you use Postgres in Production

#93

Post by serve_yay »

XorNot wrote: post #69
serve_yay wrote: post #66

I agree. Bite the bullet and learn to work with Pg in your dev environment. The dev ergonomics aren't worth all the other stuff listed here. Think about all the time you'll spend reasoning about the differences between the two. This has nothing to do with your project.

In the age of containers this able thing is baffling to me. I have a couple of things that need postgres in production, and a single script which launches a two docker containers with tmux running postgres and apache with live scrolling logs, and which binds the postgres port to my local host. I simply cannot understand where the friction on this is existing today. Even pre-docker its fairly easy to setup any of the…

Yeah, same here, I don't see how avoiding Pg (or other "real" RDBMS) makes things easier.

Re: Don't test with SQLite when you use Postgres in Production

#94

Post by msluyter »

On the project I work on, we face a similar problem. We use H2 for an in memory test database, but now we're frequently running into the problem where we'd love to use a specific postgres feature (say, json support) that isn't supported by H2.

That, and occasionally we find syntax differences that cause a headache when doing a database setup/teardown. A single minor SQL difference requires us to create separate H2/Postgres flyway configurations.

I think a better option than H2 or SQLite, that we're currently investigating, is using Docker to bring up a local postgres instance for testing.

(All of that assumes a certain dedication to using Postgres. If you want to be database agnostic, then you may in fact be better off not using Postgres in dev/test just to force yourself to remain compatible with other DBs.)

Re: Don't test with SQLite when you use Postgres in Production

#95

Post by ebiester »

electrum wrote: post #86
mhurron wrote: post #47

Earlier quoted context omitted.

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

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

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

Re: Don't test with SQLite when you use Postgres in Production

#96

Post by adrianmsmith »

jeltz wrote: post #52
mhurron wrote: post #47

Earlier quoted context omitted.

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

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…

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.

Re: Don't test with SQLite when you use Postgres in Production

#97

Post by ahachete »

Testing with a different database would only be good if you:

a) Also test with your real production database b) You restrict yourself to using the lowest common feature denominator of both databases (which is probably a pretty low figure)

Still, different databases behave differently in many aspects. And despite how cool SQLite is, PostgreSQL is so much more advanced (specially in SQL querying) that I don't see the point losing all those features. As I have already mentioned in HN, check this amazing presentation: http://use-the-index-luke.com/blog/2015-02/modern-sql before deciding to restrict yourself to a subset of the SQL supported by PostgreSQL.

Given that it's easy to start PostgreSQL from a unit test, and how lightweight PostgreSQL is, I see no real point in using SQLite for testing. Use PostgreSQL!

Re: Don't test with SQLite when you use Postgres in Production

#98

Post by K0nserv »

Extending his argument further. Strive to have a local environments that is as close to production as possible. Use Memcache in production? Use Memcache for dev/testing too! Use Elasticsearch in production? Use Elasticsearch in dev/testing!

This is all really simple to setup to. Think about how a new developer gets up to speed and starts coding in your company. Does it involve downloading and installing Postgres.app[0]? If it does it's no good. Starting a local environment should require a single command. `vagrant up` is one option if you use vagrant. Local environments should ideally use the same method of provisioning as production servers. It can however be simpler, one of our codebases has a bootstrap.sh file that sets everything up, it works surprisingly well. No version conflicts, no weird bugs due to slightly varying versions for different developers and no fake service such as SQLLite.

For the life of me I can't understand the test speed issues that people talk about. We have a pretty small codebase with some 2k test(94% coverage). That takes about 6-7 minutes to run inside a vagrant VM using all the same services as production. 6-7 minutes is a long time, but you shouldn't be running your whole test suite during development. During development only run the one or two tests that are relevant for the feature you are building or the bug you are fixing. These are typically really fast even with a proper database. The whole test suite should be ran before pushing and by CI. If your database is still too slow look at using a RAMdisk or tweaking the configuration like people have suggested in the comments

0: http://postgresapp.com/

Re: Don't test with SQLite when you use Postgres in Production

#99

Post by spacemanmatt »

lkrubner wrote: post #45

I will tell a story that I think is very strange. Last year I worked at a small startup that was focused on medical records. They used PostGres in production, but SQLLite in development. The frontend was pure AngularJS. They had a massive Python code base for pulling data from the database, turning it into JSON, and then sending it to the frontend. But then things began to change. PostGreSQL gained the ability to han…

You're not wrong. If I were managing that startup I would hit the ceiling about hacking JSON into SQLite. That is very unlikely to be relevant to the mission.

Re: Don't test with SQLite when you use Postgres in Production

#100

Post by spacemanmatt »

StavrosK wrote: post #50
sgt wrote: post #38

Postgres is relatively light weight. We use Postgres heavily in integration tests and it's quite fast. I don't see that SQLite would speed things up significantly other than causing other potential issues due to it being a different database.

You just made me wonder whether disabling fsync for postgres would make your tests behave non-identically to a database that does fsync, and how much faster they would be. Interesting question...

I'm pretty sure the behavior would be identical except for durability.
Post reply on HN