Stop using SQLite for Rails dev | Hacker News Reader

Stop using SQLite for Rails dev

streaming.nfm.id.au

7 pointsnfm14 comments

This article focuses on the disparity of using SQLite in development and something else (like MySQL) in production. That's a genuine concern.

However, I think the headline could be misleading on its own. There's nothing wrong with using SQLite in Rails dev and production. If you're using SQLite at both ends and know how to treat it right, it's great (and scales up a pretty long way - indeed, I'd argue the majority of Rails apps out there would be fine with it given how little traffic they get).

Thanks for your thoughts. I'd argue that SQLite isn't appropriate for production because of its slow speed and highly granular concurrency[1].

I'm all for keeping SQLite as the default database for development - it lowers the barrier for entry to Rails.

But for any app that you hope will someday get real traffic, using MySQL or Postgres in production is sensible and easy.

[1] http://www.sqlite.org/lockingv3.html

SQLite isn't (typically) slow. It's incredibly performant and used in all sorts of speed critical systems - http://www.sqlite.org/famous.html. That aside, SQLite isn't going to be a key bottleneck in most Rails applications where the less-than-speedy ActiveRecord is involved ;-)

SQLite's own site - http://www.sqlite.org/whentouse.html - notes that it is ideal for "the database engine for low to medium traffic websites (which is to say, 99.9% of all websites)" where they're considering this being under 100k requests per day.

It does depend greatly on what you're doing, but SQLite can vary from being a bit slower to somewhat faster than something like MySQL (many concurrent writes is where it's going to fall to its knees, not a common small web application scenario). It certainly isn't universally "slow." Searching Google for "sqlite is fast" brings up many good citations.

Thanks for the info - I was always under the impression SQLite was hella slow. You learn something new every day!

I'm curious. Which of the examples at your first link are for speed critical systems? They all seemed to be client side stuff that isn't particularly speed critical, and a couple big companies using it in some unknown way.

SQLite is a good engine, but it's still an in process (or desktop) style engine. In process engines have inherent weaknesses in terms of concurrency that make them a fundamentally poor choice over server-based engines like MySQL for web sites or other scenarios with the potential for a lot of simultaneous write access.

I knew to look for this:

"because I’m using SQLite in my test environment too"

WHY DOES YOUR TEST ENVIRONMENT NOT MIMIC YOUR LIVE ENVIRONMENT?

Seriously, that's really stupid. It's not a test environment, it's a playground. Fix that, or continue to have stupid issues like this plague you forever.

That's the point of my post.

There are subtle differences between SQLite and MySQL/Postgres. You should use the same database in dev, test and production.

It is important to 'regularly' execute tests in an environment similar to production.

But...

Having your test suite run as fast as possible is also a big priority in development. even with autotest/guard and spork tests might be quite slow if they hit the db (I'm prefer them to indeed hit the db and not to mock every possible db call).

So one solution can be to - use mysql in dev & production mode - use sqlite in test - run a CI server with mysql as the db

this way you can run your tests fast, but still have them fail if there is a mysql/sqlite incompatibility somewhere

Not his test suite, his test environment.

Just for the info, for all those using SQLLite with Rails, check this add-on https://addons.mozilla.org/en-US/firefox/addon/sqlite-manage...

So whose bug was this? Did SQLite fail to round off a DECIMAL(n, 0) column, or did ActiveRecord make the scale value optional without guaranteeing that you always get zero (or some other reliable value) by default?

The developer's ;-)

SQLite doesn't have strict types (see http://www.sqlite.org/datatype3.html). MySQL does. It's not SQLite's fault or ActiveRecord's fault: it's one of the many issues you have to take into account when developing software that runs on two very different database systems.

Edit: ActiveRecord specifically notes this behavior in the documentation:

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAd...

    Please be aware of different RDBMS implementations behavior with :decimal columns:

    The SQL standard says the default scale should be 0, :scale <= :precision, and makes no comments about the requirements of :precision.

    MySQL: :precision [1..63], :scale [0..30]. Default is (10,0).

    PostgreSQL: :precision [1..infinity], :scale [0..infinity]. No default.

    SQLite2: Any :precision and :scale may be used. Internal storage as strings. No default.

    SQLite3: No restrictions on :precision and :scale, but the maximum supported :precision is 16. No default.

    Oracle: :precision [1..38], :scale [-84..127]. Default is (38,0).

    DB2: :precision [1..63], :scale [0..62]. Default unknown.

    Firebird: :precision [1..18], :scale [0..18]. Default (9,0). Internal types NUMERIC and DECIMAL have different storage rules, decimal being better.

    FrontBase?: :precision [1..38], :scale [0..38]. Default (38,0). WARNING Max :precision/:scale for NUMERIC is 19, and DECIMAL is 38.

    SqlServer?: :precision [1..38], :scale [0..38]. Default (38,0).

    Sybase: :precision [1..38], :scale [0..38]. Default (38,0).

    OpenBase?: Documentation unclear. Claims storage in double.

SQLite should reject DDL that specifies DECIMAL(n, 0) unless that type is implemented correctly. Until now I only expected MySQL to display reckless disregard for users' stated requirements in favor of guessing what they might want.

Reply on news.ycombinator.com