# Migrations and SQLite **URL:** \ **Category:** Rails\ **Created:** [May 24, 2006, 4:39pm UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499 "2006-05-24T16:39:02Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![fxn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/77aa72/32.png) [@fxn](https://www.ruby-forum.com/u/fxn)\ **Post date:** [May 24, 2006, 4:39pm UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499/1 "2006-05-24T16:39:02Z")
I read in the instructions of Tracks that “upgrading via the rake migrate command is quite a bit more tricky currently with SQLite and SQLite3”. Is there any gotcha regarding migrations and SQLite3? – fxn ---
**Author:** ![fxn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/77aa72/32.png) [@fxn](https://www.ruby-forum.com/u/fxn)\ **Post date:** [May 24, 2006, 4:45pm UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499/2 "2006-05-24T16:45:29Z")
On 5/24/06, Xavier N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > I read in the instructions of Tracks that “upgrading via the rake > migrate command is quite a bit more tricky currently with SQLite and > SQLite3”. Is there any gotcha regarding migrations and SQLite3? I recently switched an app from MySQL 4.0 to SQLite 3. To my surprise, the migrate command worked perfectly. It’s a pretty simple set of migrations, but even the alter table steps worked fine. Obviously, this is just one data point and doesn’t really answer your question. My point is that you might want to just try it and see what happens. SQLite is so easy to setup that you really don’t lose any time in giving it a shot. – James ---
**Author:** ![fxn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/77aa72/32.png) [@fxn](https://www.ruby-forum.com/u/fxn)\ **Post date:** [May 24, 2006, 4:57pm UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499/3 "2006-05-24T16:57:46Z")
On May 24, 2006, at 16:44, James L. wrote: > question. My point is that you might want to just try it and see what > happens. SQLite is so easy to setup that you really don’t lose any > time in giving it a shot. Yes, I’ve used SQLite3 in a commercial Rails project and my experience is very positive, but that was SQL-based. Since I am working now with migrations in new projects and readed that warning, I would like to be aware of known gotchas, if any. – fxn ---
**Author:** ![fxn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/77aa72/32.png) [@fxn](https://www.ruby-forum.com/u/fxn)\ **Post date:** [May 24, 2006, 5:48pm UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499/4 "2006-05-24T17:48:20Z")
On 5/24/06, Xavier N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > On May 24, 2006, at 16:44, James L. wrote: > > > On 5/24/06, Xavier N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > > > I read in the instructions of Tracks that “upgrading via the rake > > > migrate command is quite a bit more tricky currently with SQLite and > > > SQLite3”. Is there any gotcha regarding migrations and SQLite3? I’ve never used SQLite for a production environment (I’m not really sure why you’d want to). It seems to me to be a better fit for quick development. Migrations easily allow dev targetting your SQLite database and then dropping to production MySQL or Postgres. I haven’t used _every_ feature of Migrations, but I’ve used a fairly hearty combination…and all was well when I switched from dev to production (MySQL). It was nice and transparent; Migrations really are cool and useful. I haven’t read the document you reference, though, so it’s possibly they are dropping out of Migrations and running some hard-coded SQL or such; which of course would break the db-agnostic purpose of Migrations. ---
**Author:** ![fxn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/77aa72/32.png) [@fxn](https://www.ruby-forum.com/u/fxn)\ **Post date:** [May 24, 2006, 6:08pm UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499/5 "2006-05-24T18:08:35Z")
On 5/24/06, Xavier N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > On May 24, 2006, at 17:43, Curtis wrote: > > In that case that’s an internal application that receives a handful > of requests per week probably. SQLite3 was a good option, because the > instructions to set up the database are reduced to 0 lines :-). Ah! Yes. I would use it in that sense then. I was thinking external client production mode…heehee… :: cough :: I say go for it, I don’t think you’ll have any problems with SQLite and Migrations unless you cause them yourself (such as going beyond what Migrations are meant to deal with). In other words, avoid custom SQL (which is a good practice for Migrations anyway) and you should be set. Of course I’m one of those that thinks a DB should be a DB… And have very little “logic” beyond common constraints and such. All of this works perfectly fine with Migrations. ---
**Author:** ![fxn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/77aa72/32.png) [@fxn](https://www.ruby-forum.com/u/fxn)\ **Post date:** [May 24, 2006, 6:01pm UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499/6 "2006-05-24T18:01:48Z")
On May 24, 2006, at 17:43, Curtis wrote: > sure why you’d want to). It seems to me to be a better fit for quick > development. In that case that’s an internal application that receives a handful of requests per week probably. SQLite3 was a good option, because the instructions to set up the database are reduced to 0 lines :-). – fxn ---
**Author:** ![fxn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/77aa72/32.png) [@fxn](https://www.ruby-forum.com/u/fxn)\ **Post date:** [May 25, 2006, 7:51am UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499/7 "2006-05-25T07:51:11Z")
On 24/05/06, Rob S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > The only problems that i have currently had when doing migrations with > SQLite3 is SQLite does not fully support the ALTER table syntax. > > > From [SQLite.org](http://SQLite.org) site > Complete ALTER TABLE support Only the RENAME TABLE and ADD COLUMN > variants of the ALTER TABLE command are supported. Other kinds of ALTER > TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and > so forth are omitted. > So for me when i was using rename\_colum command it was renaming my row > using some trick but it wasn’t keeping my data for that row. So you > gotta watch out when migrating to a newer version of the app that > already has data in the database and you rename columns etc… Rails gets around it by making a new table with the new columns, then renaming it. I’d have thought it would be able to copy the data (it did when I tried it a few months back, expecting it to fail). Raise a bug if you can reproduce it. ---
**Author:** ![fxn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/77aa72/32.png) [@fxn](https://www.ruby-forum.com/u/fxn)\ **Post date:** [May 24, 2006, 10:29pm UTC](https://www.ruby-forum.com/t/migrations-and-sqlite/61499/8 "2006-05-24T22:29:14Z")
Xavier N. wrote: > On May 24, 2006, at 16:44, James L. wrote: > > > question. My point is that you might want to just try it and see what > > happens. SQLite is so easy to setup that you really don’t lose any > > time in giving it a shot. > > Yes, I’ve used SQLite3 in a commercial Rails project and my > experience is very positive, but that was SQL-based. Since I am > working now with migrations in new projects and readed that warning, > I would like to be aware of known gotchas, if any. > > – fxn The only problems that i have currently had when doing migrations with SQLite3 is SQLite does not fully support the ALTER table syntax. From [SQLite.org](http://SQLite.org) site [SQL Features That SQLite Does Not Implement](http://www.sqlite.org/omitted.html) Complete ALTER TABLE support Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted. So for me when i was using rename\_colum command it was renaming my row using some trick but it wasn’t keeping my data for that row. So you gotta watch out when migrating to a newer version of the app that already has data in the database and you rename columns etc…