How do I get ActiveRecord to show the next id (last + 1) in Ruby on Rails?

Question

Is there a compact way with ActiveRecord to query for what id it's going to use next if an object was going to be persisted to the database? In SQL, a query like this would look something like:

SELECT max(id) + 1 FROM some_table;

Answer 1

Here is a slightly modified Taryn East's version:

Model.maximum(:id).next
# or
Model.calculate(:maximum, :id).next

Sources:

#maximum @ ActiveRecord::Calculations

#next @ Integer

Answer 2

While accepting fig's answer I might want to draw your attention to a small thing. If you are getting the next ID to set to a particular record before saving, I think its not a good idea.

because as an example in a web based system

1. you get the last id as 10
2. you set the next id as 11
3. before you save the record someone else has saved the record, now the last id should be 12 likewise..

I'm not sure you want the last id to do what I'm thinking here, But if so this is just to draw your attention.

Answer 3

If your database is Postgres, you can get the next id with this (example for a table called 'users'):

ActiveRecord::Base.connection.execute("select last_value from users_id_seq").first["last_value"]

Unlike the other answers, this value is not affected by the deletion of records.

There's probably a mySQL equivalent, but I don't have one set up to confirm.

If you have imported data into your postgresql database, there's a good chance that the next id value after the import is not set to the next integer greater than the largest one you imported. So you will run into problems trying to save the activerecord model instances.

In this scenario, you will need to set the next id value manually like this:

ActiveRecord::Base.connection.execute("alter sequence users_id_seq restart with 54321;") #or whatever value you need

Answer 4

Slightly better than the accepted answer:

YourModel.maximum(:id) + 1

Still prone to race-conditions etc, but at least it will take note of skipped ids and is slightly more efficient than, say, ordering the table by id then returning the last.

Answer 5

This is an old question, but none of the other answers work if you have deleted the last record:

Model.last.id #=> 10
Model.last.destroy
Model.last.id #=> 9, so (Model.last.id + 1) would be 10... but...
Model.create  #=> 11, your next id was actually 11

I solved the problem using the following approach:

current_value = ActiveRecord::Base.connection.execute("SELECT currval('models_id_seq')").first['currval'].to_i
Model.last.id #=> 10
Model.last.destroy
Model.last.id #=> 9
current_value + 1 #=> 11

Answer 6

Get the largest id on the table

YourModel.maximum(:id)

This will run the following sql

SELECT MAX("your_models"."id") AS max_id FROM "your_models"

Convert the result to an integer by calling to_i. This is important as for an empty table the above max command will return nil. Fortunately nil.to_i returns 0.

Now to get the next available id, just add 1 +1`

The final result:

YourModal.maximum(:id).to_i+1

Answer 7

If no one else is using the table (otherwise you would have to use locking), you could get the autoincrement value from MySQL, the SQL command is

SELECT auto_increment FROM information_schema.tables 
WHERE table_schema = 'db_name' AND table_name = 'table_name';

and the Rails command would be

ActiveRecord::Base.connection.execute("SELECT auto_increment 
     FROM information_schema.tables 
     WHERE table_schema = 'db_name' AND table_name = 'table_name';").first[0]

Answer 8

If you want to be sure no one else can take the 'new' index, you should lock the table. By using something like:

ActiveRecord::Base.connection.execute("LOCK TABLES table_name WRITE")

and

ActiveRecord::Base.connection.execute("UNLOCK TABLES")

But this is specific for each database engine.

The only correct answer for a sequential id column is:

YourModel.maximum(:id)+1

If you sort your model in a default scope, last and first will depend on that order.

Answer 9

I don't think there's a generic answer to this since you may not want to assume one database over another. Oracle, for example, may assign id's by a sequence or, worse, by a trigger.

As far as other databases are concerned, one may set them up with non sequential or random id allocation.

May I ask what the use case is? Why would you need to anticipate the next id? What does 'next' mean? Relative to when? What about race conditions (multiuser environments)?

Answer 10

There seems to be need of a answer here that removes the race conditions, and addresses the actual problem of pre-providing unique identifiers.

To solve the problem you maintain a second model, which serves unique ID's for the primary model. This way you don't have any race condition.

If you need to make these ID's secure you should hash them with SHA256 (or SHA512) and store the hash as an indexed column on identifier model when they are generated.

They can then be associated and validated when used on the primary model. If you don't hash them you still associate them, to provide validation.

I'll post some example code a bit later.

Answer 11

Don't get the intent but you might wanna think of using GUID

Answer 12

You should never assume what the next id will be in the sequence. If you have more than 1 user you run the risk of the id being in use by the time the new object is created.

Instead, the safe approach would be to create the new object and update it. Making 2 hits to your database but with an absolute id for the object you're working with.

This method takes the guess work out of the equation.

Answer 13

I came to this SO question because I wanted to be able to predict the id of a model created in my test suite (the id was then used a REST request to an external service and I needed to predict the exact value to mock the request).

I found that Model.maximum(:id).next, although elegant, doesn't work in a rails testing environment with transactional fixtures since there are usually no records in the db so it will simply return nil.

Transactional fixtures make the issue extra tricky since the auto increment field ascends even when there aren't any records in the db. Furthermore using an ALTER TABLE ***your_table_name*** AUTO_INCREMENT = 100 breaks the transaction your tests are in because it requires its own transaction.

What I did to solve this was to create a new object and add 1 to its id:

let!(:my_model_next_id) { FactoryBot.create(:my_model).id + 1 }

Although somewhat hacky (and slightly inefficient on your db since you create an extra object for the sake of its id), it doesn't do anything goofy to the transaction and works reliably in a testing environment with no records (unless your tests run in parallel with access to the same db...in which case: race conditions...maybe?).

Answer 14

Along with above answers, here is another way to check current auto increment:

query = "show create table features" 
res = ActiveRecord::Base.connection.execute(query)
puts res.to_a 

This can also print current AUTO_INCREMENT property, along with table properties such as create table statement, CHARSET etc..