Change Autoincrement values in migration (PostgreSQL and SQLite3)

Question

I have a project hosted on Heroku and would like to change a table's autoincrement start value. I'm using SQLite3 locally and Heroku uses PostgreSQL This is what I have in the migration:

class CreateMytable < ActiveRecord::Migration

  def self.up
    create_table :mytable do |t|
      t.text :mytext
    end

    case ActiveRecord::Base.connection.adapter_name 
      when 'PostgreSQL'
        execute 'ALTER SEQUENCE mytable_id_seq RESTART WITH 1000;'
      when 'SQLite'
        execute 'update sqlite_sequence set seq = 1000 where name = "mytable";'
      else
    end 
  end

  def self.down
    drop_table :mytable
  end
end

Locally the migration runs but SQLite seems to just ignore the change, it works on Heroku though. What am I doing wrong?

Somewhat OT but you really should set up PostgreSQL locally, developing on one database and deploying on another is a recipe for frustration and panicked calls at 02:00. — mu is too short, Mar 26 '11 at 06:22

score 15 · Accepted Answer · edited May 23 '17 at 11:43

15

Honestly, it doesn't sound like this belongs in a migration. You could add the following to an initializer to make a handy Base class method to call as part of a task, though:

ActiveRecord::Base.class_eval do
  def self.reset_autoincrement(options={})
    options[:to] ||= 1
    case self.connection.adapter_name
      when 'MySQL'
        self.connection.execute "ALTER TABLE #{self.table_name} AUTO_INCREMENT=#{options[:to]}"
      when 'PostgreSQL'
        self.connection.execute "ALTER SEQUENCE #{self.table_name}_id_seq RESTART WITH #{options[:to]};"
      when 'SQLite'
        self.connection.execute "UPDATE sqlite_sequence SET seq=#{options[:to]} WHERE name='#{self.table_name}';"
      else
    end
  end
end

Then just run the following as part of a task or right in the console:

Mytable.reset_autoincrement(:to => 1000)

Make sure to check this handy answer as to why the sqlite may not be working.

SQLite Reset Primary Key Field

edited May 23 '17 at 11:43

Community

1
1

answered Mar 26 '11 at 05:39

20man

1,419
13
11

Thanks, that's a better way of doing it. Since I want the process to be automated I will make a rake task for it. For some reason though SQLite wouldn't work until I changed the query to `self.connection.execute "UPDATE sqlite_sequence SET seq=#{options[:to]} WHERE name='#{self.table_name}';"`. Something to do with `%Q` maybe? – David Mar 26 '11 at 10:48
Also, the PostgreSQL line should be `... SEQUENCE #{self.table_name}_id_seq ...` – David Mar 26 '11 at 11:23
Excellent, thanks for the corrections; I've added them into the post. – 20man Mar 26 '11 at 16:46

Change Autoincrement values in migration (PostgreSQL and SQLite3)

1 Answers1

Linked