Rails way to reset seed on id field

Question

I have found the "pure SQL" answers to this question. Is there a way, in Rails, to reset the id field for a specific table?
Why do I want to do this? Because I have tables with constantly moving data - rarely more than 100 rows, but always different. It is up to 25k now, and there's just no point in that. I intend on using a scheduler internal to the Rails app (rufus-scheduler) to run the id field reset monthly or so.

Answer 1

You never mentioned what DBMS you're using. If this is postgreSQL, the ActiveRecord postgres adapter has a reset_pk_sequences! method that you could use:

ActiveRecord::Base.connection.reset_pk_sequence!('table_name')

Answer 2

I came out with a solution based on hgimenez's answer and this other one.

Since I usually work with either Sqlite or PostgreSQL, I've only developed for those; but extending it to, say MySQL, shouldn't be too troublesome.

Put this inside lib/ and require it on an initializer:

# lib/active_record/add_reset_pk_sequence_to_base.rb
module ActiveRecord
  class Base
    def self.reset_pk_sequence
      case ActiveRecord::Base.connection.adapter_name
      when 'SQLite'
        new_max = maximum(primary_key) || 0
        update_seq_sql = "update sqlite_sequence set seq = #{new_max} where name = '#{table_name}';"
        ActiveRecord::Base.connection.execute(update_seq_sql)
      when 'PostgreSQL'
        ActiveRecord::Base.connection.reset_pk_sequence!(table_name)
      else
        raise "Task not implemented for this DB adapter"
      end
    end     
  end
end

Usage:

Client.count # 10
Client.destroy_all
Client.reset_pk_sequence
Client.create(:name => 'Peter') # this client will have id=1

EDIT: Since the most usual case in which you will want to do this is after clearing a database table, I recommend giving a look to database_cleaner. It handles the ID resetting automatically. You can tell it to delete just selected tables like this:

DatabaseCleaner.clean_with(:truncation, :only => %w[clients employees])

Answer 3

I assume you don't care about the data:

def self.truncate!
  connection.execute("truncate table #{quoted_table_name}")
end

Or if you do, but not too much (there is a slice of time where the data only exists in memory):

def self.truncate_preserving_data!
  data = all.map(&:clone).each{|r| raise "Record would not be able to be saved" unless r.valid? }
  connection.execute("truncate table #{quoted_table_name}")
  data.each(&:save)
end

This will give new records, with the same attributes, but id's starting at 1.

Anything belongs_toing this table could get screwy.