5 ms·

Stop using SQLite for Rails dev

7 points by nfm 15y ago

petercooper 15y ago
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 15y ago
  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 http://www.sqlite.org/lockingv3.html
  - petercooper 15y ago
    SQLite isn't (typically) slow. It's incredibly performant and used in all sorts of speed critical systems - http://www.sqlite.org/famous.html 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 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.
    - nfm 15y ago
      Thanks for the info - I was always under the impression SQLite was hella slow. You learn something new every day!
    - tzs 15y ago
      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.
wccrawford 15y ago
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.
- nfm 15y ago
  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.
- Vitaly 15y ago
  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 15y ago
    Not his test suite, his test environment.
prodigal_erik 15y ago
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 15y ago
  The developer's ;-) SQLite doesn't have strict types (see http://www.sqlite.org/datatype3.html 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/ConnectionAdapters/TableDefinition.html#method-i-column 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.
  - prodigal_erik 15y ago
    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.
ruffdev 15y ago
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.
ruffdev 15y ago
Just for the info, for all those using SQLLite with Rails, check this add-on https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ https://addons.mozilla.org/en-US/firefox/addon/sqlite-manage...

7 points by nfm 15y ago

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 15y ago
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 http://www.sqlite.org/lockingv3.html
- petercooper 15y ago
  SQLite isn't (typically) slow. It's incredibly performant and used in all sorts of speed critical systems - http://www.sqlite.org/famous.html 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 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.
  - nfm 15y ago
    Thanks for the info - I was always under the impression SQLite was hella slow. You learn something new every day!
  - tzs 15y ago
    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.

wccrawford 15y ago

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.

nfm 15y ago
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.
Vitaly 15y ago
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 15y ago
  Not his test suite, his test environment.

prodigal_erik 15y ago

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 15y ago
The developer's ;-) SQLite doesn't have strict types (see http://www.sqlite.org/datatype3.html 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/ConnectionAdapters/TableDefinition.html#method-i-column 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.
- prodigal_erik 15y ago
  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.

ruffdev 15y ago

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.

ruffdev 15y ago

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