Forum: Ruby on Rails migrations and SQLite

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Xavier N. (Guest)
on 2006-05-24 18:39
(Received via mailing list)
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
James L. (Guest)
on 2006-05-24 18:45
(Received via mailing list)
On 5/24/06, Xavier N. <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
Xavier N. (Guest)
on 2006-05-24 18:57
(Received via mailing list)
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
Curtis S. (Guest)
on 2006-05-24 19:48
(Received via mailing list)
On 5/24/06, Xavier N. <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> 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.
Xavier N. (Guest)
on 2006-05-24 20:01
(Received via mailing list)
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
Curtis S. (Guest)
on 2006-05-24 20:08
(Received via mailing list)
On 5/24/06, Xavier N. <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.
Rob S. (Guest)
on 2006-05-25 00:29
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
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..
Dick D. (Guest)
on 2006-05-25 09:51
(Received via mailing list)
On 24/05/06, Rob S. <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 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.
This topic is locked and can not be replied to.