Trying To Set Up Rails To Store Sessions In A Postgresql Database

Question

I have an existing Rails 3.2 app, and I would like to store sessions in a Postgresql database. I found this tutorial on Stackoverflow and followed Diego Pino's directions.

However, when I got to the rake db:migrate step, I got the following error:

PG::Error: ERROR: foreign key constraint "sessions_session_id_fkey" cannot be implemented DETAIL: Key columns "session_id" and "id" are of incompatible types: character varying and integer.

Here's the SQL that it's trying to execute:

CREATE TABLE "sessions" ("id" serial primary key, 
    "session_id" character varying(255) NOT NULL, 
    "data" text,
    "created_at" timestamp NOT NULL, 
    "updated_at" timestamp NOT NULL, 
    FOREIGN KEY ("session_id") REFERENCES "sessions" ("id"))

And here's the migration that was automatically created:

class AddSessionsTable < ActiveRecord::Migration                                    
  def change
    create_table :sessions do |t|
      t.string :session_id, :null => false
      t.text :date
      t.timestamps
    end
    add_index :sessions, :session_id
    add_index :sessions, :updated_at
  end
end

The confusing part to me is that I don't see any foreign key constraint declarations in the migration. So why is the generated sql trying to link together a text field and an integer field?

UPDATE 1

Kevin requested the contents of my config/initializers/session_store.rb file:

# Be sure to restart your server when you modify this file.

Calliope::Application.config.session_store :cookie_store, key: '_calliope_session'

# Use the database for sessions instead of the cookie-based default,
# which shouldn't be used to store highly confidential information
# (create the session table with "rails generate session_migration")
#Calliope::Application.config.session_store :active_record_store

I tried re-running the rake db:migrate command after uncommenting the :active_record_store line at the bottom, but that didn't change the outcome.

I also do not have an existing sessions table in either my Dev or Test databases.

Please post the contents of `/config/initializers/session_store.rb` — Kevin Bedell, May 17 '12 at 02:46
I am using schema_plus, but I don't know if it automatically adds foreign keys. I'll check that now. — Tom Purl, May 17 '12 at 07:58
Andrew was right. The problem was that I am using schema_plus, and that automatically tries to make a field a foreign key if it ends in "_id". Thankfully, schema_plus also makes it possible to override this behavior. — Tom Purl, May 19 '12 at 17:46
I know this is just a typo, but you should probably update `t.text :date` in your `AddSessionsTable` migration to `t.text :data`. Just an observation. — Dom, Aug 05 '12 at 04:12

Kevin Bedell · Answer 1 · 2012-05-17T02:46:19.543

Looking at the SQL generated, this jumps out:

FOREIGN KEY ("session_id") REFERENCES "sessions" ("id"))

Your table name is sessions, but it's referencing what looks like another table named sessions. For some reason it's indicating that the session_id column in this table is a foreign key to the id column in a table named sessions.

So it's clear that it can't make the session_id column in your sessions table a foreign key to the id column in the same table -- as the error tells you:

Key columns "session_id" and "id" are of incompatible types: character varying and integer.

According to the docs for ActiveRecord::SessionStore:

A session store backed by an Active Record class. A default class is provided, but any object duck-typing to an Active Record Session class with text session_id and data attributes is sufficient.

Here's the link to the page:

http://apidock.com/rails/ActiveRecord/SessionStore

Can you post the contents of your /config/initializers/session_store.rb file?

Thanks Kevin, I updated my question above with the contents of that file. I already have "sessions" controllers and views, but no models. I created these earlier to help manage sessions. Could those files be contributing to these problems? — Tom Purl, May 17 '12 at 07:52
Thanks Kevin for all of the insight, but it ended up being a feature of the schema_plus library that I was using. — Tom Purl, May 19 '12 at 17:47

score 1 · Accepted Answer · answered May 19 '12 at 17:52

I checked the docs for the schema_plus library using Andrew's advice above, and here's the root of my problem:

SchemaPlus adds support for foreign key constraints. In fact, for the common convention that you name a column with suffix _id to indicate that it’s a foreign key, SchemaPlus automatically defines the appropriate constraint.

Thankfully, this behavior can be overridden in your migration by using the :references => nil setting. Checkout the README for more information.

Trying To Set Up Rails To Store Sessions In A Postgresql Database

2 Answers2