Use PostgreSQL Gem Instead of SQLite When Deploying Rails Applications to Heroku

Ruby on Rails logo

This is for both RoR 3.2 and 4.0.0 beta1/RC1!

When creating a new Ruby on Rails application, the Gemfile will include the gem for SQLite by default.

Example excerpt from Gemfile in your RoR application directory:

gem 'sqlite3', '1.3.7'

When running Rails server in development on your local computer (most likely a Mac or Linux), this works fine. However, if you wish to deploy your application to Heroku, you will not be able to include the SQLite gem, as Heroku only supports PostgreSQL.

What is necessary at this point is to change the Gemfile to use SQLite only for development (and test, if you wish) and use PostgreSQL for production.

Change the above code in the Gemfile to this:

group :development do
   gem 'sqlite3', '1.3.7'
end

group :production do
   gem 'pg', '0.14.1'
end

Save the Gemfile, and reinstall the application using

bundle install --without production


at the shell prompt.

Update the local git repository with

git commit -am "Gemfile.lock updated for Heroku deployment"

Optional: Update GitHub (or other remote repository) with

git push

Assuming that your Heroku account and application have been established already, deploy to Heroku. (If the name of the git branch is something other than “master” use the correct branch name instead of “master”.)

git push heroku master

Nota bene:
Because of the way that Heroku works with Rails 4.0, a route will need to be added for your page to appear properly in Heroku if you choose to use Rails 4.0. Rails 3.2 does not require this route to be configured.

Ruby 2.0.0 was recently released into production, and Rails 4.0.0 RC 1 was just released on May 1st.

If you want to use these versions for your Heroku deployment, add

ruby '2.0.0'


to the top of Gemfile, and change the Rails gem entry to

gem 'rails', '4.0.0.rc1'

If you have been using the 4.0.0.beta1 release of Rails, you will need to change the versions on some other gems to 4.0.0.rc1. (Coffee-rails is now up to 4.0.0.)

In changing from beta1 to RC1, replace the line

config.session_store :encrypted_cookie_store #...


with

config.session_store :cookie_store #...


in config/initializers/session_store.rb. The store will be automatically encrypted. Otherwise, “rails server” and the deployment to Heroku will fail.

Lastly, remember to “bundle install…” and “git commit…” before doing “git push heroku…”!

Issues with CFML Syntax when Switching from Adobe ColdFusion to Railo

Adobe ColdFusion logo

Railo is an open source CFML server that can replace Adobe (or Macromedia) ColdFusion. I ported several CFML-based apps from an old instance of Macromedia ColdFusion MX 6 to Railo 3.2.1 about two years ago, and it’s been relatively trouble-free since the change.

However, the change itself was fraught with difficulties. That was due, not to problems with Railo, but to the forgiving nature of ColdFusion (CF). If the CFML wasn’t written exactly right, CF would parse it fine, but Railo wouldn’t take it. An example of this is the following code:

Before:

	<option value="A" < cfif #Sec_Level# eq 'A'>selected
		</cfif>>Administrator
		<option value="D" <cfif #Sec_Level# eq 'D'>selected
		</cfif>>Data Entry/General User

After:

	<option value="A" <cfif #Sec_Level# eq 'A'>selected="selected"</cfif>>Administrator</option>
	<option value="D" <cfif #Sec_Level# eq 'D'>selected="selected"</cfif>>Data Entry/General User</option>

The “Before” code gave the error: “no matching start tag for end tag [cfif]”.

The key change that was required wasn’t the closing option tag or the change to the selected attribute; that was just good form. It was the space between the “<” and the “cfif” in the opening cfif statements that caused the error. Simply removing that space fixed the problem.

Seemingly small problems like this will cause Railo (at least version 3.2.1) to throw errors. If the code is written properly to begin with, this will likely not happen.

Do We Have to Use a SQL Cursor?

SQL Server logo

In migrating data from a legacy budget application to its replacement, my requirement was to take the first and last years in a budget from the old application and update the corresponding table in the “new” app table with those dates as the beginning and ending of the project. One possible solution is to use a SQL cursor to loop through each record. When there are relatively few records, and the databases/tables are in the same location, this is an easy choice as the performance hit will be minimal.

However, in my case, there were 10000 records and the servers were on separate LANs. The cursor below did produce the desired result in terms of data, but it was horribly slow. It would have taken hours to completely copy all of the data, given the locations of the servers and possibly not the best indexing on the databases. (I didn’t design the indices! ๐Ÿ˜€ )

DECLARE WidgetBudgetCursor CURSOR
FOR SELECT [PSProjectID],[WidgetID] FROM [LinkedServername].[DbName].[Owner].[TableName]
OPEN WidgetBudgetCursor
FETCH NEXT FROM WidgetBudgetCursor INTO @ProjectID, @WidgetID
WHILE @@FETCH_STATUS = 0
BEGIN
	-- ...

	DECLARE @BudgetYearMin INT
	DECLARE @BudgetYearMax INT

	SELECT @BudgetYearMin=MIN([BudgetYear]), @BudgetYearMax=MAX([BudgetYear]) FROM [tWidgetbudgetbreakdown]
		WHERE [RequestId] = @WidgetID

	UPDATE [LinkedServername].[DbName].[Owner].[TableName]
		SET [ProjectStartDate] = CONVERT(DATETIME, '01-01-' + CAST(@BudgetYearMin AS CHAR(4)) + ' 00:00:00')
		WHERE [ProjectID] = @ProjectID

	UPDATE [LinkedServername].[DbName].[Owner].[TableName]
		SET [InServiceDate] = CONVERT(DATETIME, '12-31-' + CAST(@BudgetYearMax AS CHAR(4)) + ' 23:59:59')
		WHERE [ProjectID] = @ProjectID		

	-- ...

	FETCH NEXT FROM WidgetBudgetCursor INTO @ProjectID, @WidgetID
END
CLOSE WidgetBudgetCursor
DEALLOCATE WidgetBudgetCursor

After some initial attempts to copy small portions of the data took much longer than I expected it to, I redesigned the queries to be updates on joined tables (see below). This was much faster than using the cursor. While I was not able to eliminate the cursor for some other required functions, moving these update queries out of the cursor made the data move use less resources and take much less time.

UPDATE pm2
	SET [ProjectStartDate] =
	a.MinBudgetYear FROM
	(SELECT CONVERT(DATETIME, '01-01-' + CAST(MIN(Widget.[BudgetYear]) AS CHAR(4)) + ' 00:00:00') AS MinBudgetYear, pm.[WidgetID] AS WidgetID
	FROM [LinkedServername].[DbName].[Owner].[TableName] pm INNER JOIN
	[tWidgetbudgetbreakdown] Widget ON pm.[WidgetID] = Widget.[RequestID] GROUP BY pm.[WidgetID] HAVING LEN(MIN(Widget.[BudgetYear]))=4) a INNER JOIN
	[LinkedServername].[DbName].[Owner].[TableName] pm2 ON a.[WidgetID] = pm2.[WidgetID]	

UPDATE pm2
	SET [InServiceDate] =
	a.MaxBudgetYear FROM
	(SELECT CONVERT(DATETIME, '12-31-' + CAST(MAX(Widget.[BudgetYear]) AS CHAR(4)) + ' 23:59:59') AS MaxBudgetYear, pm.[WidgetID] AS WidgetID
	FROM [LinkedServername].[DbName].[Owner].[TableName] pm INNER JOIN
	[tWidgetbudgetbreakdown] Widget ON pm.[WidgetID] = Widget.[RequestID] GROUP BY pm.[WidgetID] HAVING LEN(MAX(Widget.[BudgetYear]))=4) a INNER JOIN
	[LinkedServername].[DbName].[Owner].[TableName] pm2 ON a.[WidgetID] = pm2.[WidgetID]