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

61–70 of 110 posts

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

#61

Post by jammycakes »

gitaarik wrote: post #20

If you're writing unit tests to test the business logic of your app, you shouldn't need a database at all. You should write your business logic so that it isn't dependent on a database, so you can really test the business logic and you don't have to mock the database. If you're talking integration tests, then of course you should use an environment as close to production as possible.

Unfortunately it is not always possible to maintain a clean separation between "business logic" and "data access logic." Considerations such as performance, what is and isn't supported, or data access semantics often mean that changing one necessitates changing the other.

Attempting to separate out the two often means that you end up miscategorising business logic as data access logic (resulting in poor test coverage) or data access logic as business logic (resulting in poor performance and unnecessary complexity).

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

#62

Post by pothibo »

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'm sorry but I really don't agree with this. One of the point the OP made is that if you develop in SQLite and deploy on a Postgresql database, it means you discard all the features that postgresql has over SQLite (data types and SQL Queries for example).

You wouldn't test your Facebook consuming API code with a Twitter endpoint, why do you apply the same logic to your DBMS?

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

#63

Post by empthought »

[deleted] wrote: post #55
empthought wrote: post #49

Earlier quoted context omitted.

> When you are using an ORM, most of the arguments are obsolete, too. At the cost of pretty much everything you get out of using something that isn't SQLite.

[deleted]

I don't know what you're talking about; is there even a downvote button on Hacker News? I certainly don't see one.

Some features that ORMs make unnecessarily difficult (or impossible without simply avoiding the use of the ORM) to use, off the top of my head:

* transactional DDL

* temporary tables

* user-defined data types

* asynchronous notifications

* dynamic SQL

* non-serial keys

* multi-attribute keys

* temporal schema

* window functions (really all manner of analytics queries)

Those were just the ones off of the top of my head, ones that are not "independent of SQL features."

Enjoy your flat-files, because that's actually what ORM gets you close to: a procedural abstraction inversion over a rich and capable declarative interface and first-order-logic engine.

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

#64

Post by justinclift »

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…

Just to point out... there's no capital G in PostgreSQL's name. It's either "PostgreSQL" or "Postgres". ;)

Also to be super pedantic... it's SQLite, not SQLLite. ;)

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

#65

Post by [deleted] »

empthought wrote: post #63
[deleted] wrote: post #55

Earlier quoted context omitted.

[deleted]

I don't know what you're talking about; is there even a downvote button on Hacker News? I certainly don't see one. Some features that ORMs make unnecessarily difficult (or impossible without simply avoiding the use of the ORM) to use, off the top of my head: * transactional DDL * temporary tables * user-defined data types * asynchronous notifications * dynamic SQL * non-serial keys * multi-attribute keys * temporal s…

[deleted]

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

#66

Post by serve_yay »

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.

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

#67

Post by hvs »

mhurron wrote: post #47
darkxanthos wrote: post #34

Earlier quoted context omitted.

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.

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

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.

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

#68

Post by empthought »

[deleted] wrote: post #65
empthought wrote: post #63

Earlier quoted context omitted.

I don't know what you're talking about; is there even a downvote button on Hacker News? I certainly don't see one. Some features that ORMs make unnecessarily difficult (or impossible without simply avoiding the use of the ORM) to use, off the top of my head: * transactional DDL * temporary tables * user-defined data types * asynchronous notifications * dynamic SQL * non-serial keys * multi-attribute keys * temporal s…

[deleted]

You wrote, "when you are using an ORM, most of the arguments [against using a different DBMS for testing than production] are obsolete, too," which is false, unless the point of the ORM is to bring the production DBMS down to the test DBMS's level of capability.

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

#69

Post by XorNot »

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 big dbms in their own little environments (5 versions of mysql on the same server for a migration project).

Post reply on HN