Migrations and SQLite

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

On 5/24/06, Xavier N. [email protected] 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

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

On 5/24/06, Xavier N. [email protected] wrote:

On May 24, 2006, at 16:44, James L. wrote:

On 5/24/06, Xavier N. [email protected] 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.

On 5/24/06, Xavier N. [email protected] 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.

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

On 24/05/06, Rob S. [email protected] 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 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.

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 site
SQL Features That SQLite Does Not Implement
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…