

Stop using SQLite for Rails dev - nfm
http://streaming.nfm.id.au/stop-using-sqlite-for-rails-development-ndash

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

~~~
nfm
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>

~~~
petercooper
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.

~~~
tzs
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.

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

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

~~~
Vitaly
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

~~~
wccrawford
Not his test suite, his test environment.

------
ruffdev
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...](https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/)

------
prodigal_erik
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?

~~~
nbpoole
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...](http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html#method-
i-column)

    
    
        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.

~~~
prodigal_erik
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.

