Rails Way to Reset Seed on Id Field

Rails way to reset seed on id field

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])

How to reset auto increment field in a ActiveRecord migration?

You have 2 separate issues. One is that you are trying to specify the id with mass assignment, rails won't allow you to do that. See Overriding id on create in ActiveRecord for a way to do that.

The other issue is that the auto-increment isn't resetting. Each DBMS has a unique way of setting an increment counter, and rails doesn't give you a generic way to access them, although it is implemented for some of them (not MySQL), see Rails way to reset seed on id field

So you'll need to run the MySQL specific SQL for this, which is something like:

ALTER TABLE my_models AUTO_INCREMENT = 1;

This should reset to the number after the largest id in your table (1 if there aren't any)

How to restart id counting on a table in PostgreSQL after deleting some previous data?

You can do it directly in PostgreSQL using "alter sequence": http://www.postgresql.org/docs/current/static/sql-altersequence.html

Particularly "restart with"

I don't know how you would do it via the rails abstraction.

Model.delete_all and make the ids start back at 1?

Most databases have a concept of a sequence which auto increments as it's used. ActiveRecord uses the underlying database sequence to create primary keys. This gem (https://github.com/splendeo/activerecord-reset-pk-sequence) may help reset it for you depending on your database.

Reset the database (purge all), then seed a database

I use rake db:reset which drops and then recreates the database and includes your seeds.rb file.
http://guides.rubyonrails.org/migrations.html#resetting-the-database

reseting primary key id in rails

First, you shouldn't do it! If you want to change record, then just update rows in your table. Don't delete it and reinsert it! If you still want to do it, then you should delete all associated rows in other tables and insert both computers and apples.

I don't know Rails way of reseting auto increment primary key, but if you use mysql then you can run custom query:

ActiveRecord::Base.connection.execute('ALTER TABLE tablename AUTO_INCREMENT = 1')

it should reset auto increment value to next available number.

EDIT:

How to load data with id.
I'm not sure if loading with seeds can do it, but for sure using fixtures can help. What you need is to prepare computers.yml file with data:

First_computer:
  id: 1
  name: computer

Second_computer:
  id: 2
  name: dell

etc.

Then you can use this to load it to db:

require 'active_record/fixtures'

Fixtures.create_fixtures("/path/to/directory/where/your/yml/file/is/", "computers")

But it will reset whole table (all rows will be deleted and reinserted - but you can keep your ids).

Again I want to warn you that it is bad idea to load data this way. Fixtures/seeds should be used only to seed your db with initial (needed to start your application) data.

Why do you want to reset this table?

Is there a way to reset only a part of the DB in Rails?

Just delete the records first. Then you have to look for the DBspecific command that resets the autoincrement counter. This post shows how to do that (they do it in a migration although I think the rails console would work as well:

How to reset auto increment field in a ActiveRecord migration?

Seeds file - adding id column to existing data

What I think is happening is you have existing data in your database ... let's say

[{id:1 , code: 'ABC'},
{id:2 , code: 'DEF'}]

Now you run your seed file which has {id: 3, 'DEF'} for example.

Because you are using find_or_initialize_by with id you are running into errors. Since you can potentially insert duplicates.

I recon you should just clear your data, but you can try doing find_or_initialize_by using code instead of id. That way you wont ever have a problem of trying to create a duplicate country code.

Country.find_or_initialize_by(code: value[:code])

I think you might run into problems with your ids, but you will have to test that. It's generally bad practice to do what you are doing. Whether they ids change or now should be irrelevant. Your seed file should reference the objects that are being created not ids.

Also make sure you aren't using any default_scopes ... this would affect how find_or_initialize_by works.

How to reset the primary key id in ruby on rails 6?

The method does exist (as of Rails 6.0.0) for PostgreSQL:

ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements#reset_pk_sequence!

https://apidock.com/rails/v6.0.0/ActiveRecord/ConnectionAdapters/PostgreSQL/SchemaStatements/reset_pk_sequence%21

Note that you are using SQLite:

#<ActiveRecord::ConnectionAdapters::SQLite3Adapter:0x000000000af92360>

So your available methods are here:
https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SQLite3Adapter.html

More importantly, YOU SHOULDN'T RESET YOUR TABLE IDS JUST BECAUSE YOU DELETED A USER.
The database can handle incrementing the IDs no problem, even after you delete a user.

I'm having a hard time coming up with a use-case that would require resetting the Db Ids after deleting a record.

If you REALLY want to do this:
Reset the id field in a table in a sqlite db in Rails

---

Related Topics