trying to reset id from console

I’ve got the program in chapter 2.

is there anything i can type in the console to reset the id? I can do e.g. User.delete_all and delete all users, but if a new user is then created it doesn’t have an id of 1 'cos the id doesn’t reset.

this User.reset_primary_key doesn’t work either.

Notice how I removed all users I reset the primary key, I added a user, and they got quite a high id.
  irb(main):064:0> User.first
User Load (1.0ms) SELECT "users".* FROM "users" ORDER BY "user
=> nil
irb(main):065:0> User.primary_key
=> "id"
irb(main):066:0> User.reset_primary_key
=> "id"
irb(main):067:0> User.first
User Load (0.0ms) SELECT "users".* FROM "users" ORDER BY "user
=> #<User id: 13, name: "f", email: "f", created_at: "2017-12-23 0

I get this error

C:\rubyarud\hartl\ch2\toy_app>rails db:drop
Permission denied @ unlink_internal - C:/rubyarud/h
Couldn't drop database 'db/development.sqlite3'
rails aborted!
Errno::EACCES: Permission denied @ unlink_internal
bin/rails:4:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:drop:_unsafe
(See full trace by running task with --trace)

I notice the error mentions sqlite. I'm using the correct gem file, I think. I know the gemfile in the book that we are to use moves sqlite into the development and test groups. I have that and am using the gemfile that the book gives.

Is there anything I can do from the console to reset it?


The id is coming from an auto-increment column in your database. There are two options if you want to reset to start at 1:

* You can roll back your migrations until you drop the table,
* or your can use SQL: truncate table [your_table_name] in a SQL console.

Really, though, the numerical ID is utterly meaningless, so it's not clear why you would care that the ID start back at 1 or not. It matters to the database, because it's the primary key, so there's a real need for it to never repeat ever. But unless you're worried about a few digits in a 64-bit numberspace... You have larger issues to worry about if you ever hit the end of that possible id value.


As Walter already pointed out, you shouldn't care about the content
of the id field, but ...

I get this error

C:\rubyarud\hartl\ch2\toy_app>rails db:drop
Permission denied @ unlink_internal - C:/rubyarud/h
Couldn't drop database 'db/development.sqlite3'
rails aborted!
Errno::EACCES: Permission denied @ unlink_internal

I would be concerned about not being able to drop and recreate a
database -- sometimes when you're experimenting you *will* get to
a place where you want a clean slate.

That said, I have no idea about Windows permissions; my guess
would be you have the DB open in some other process, but beyond
that -- no idea.

Good luck!

To add to what others have said, if you want an id you can reference
that you have full control over then add another field for that, the
standard id field is not even guaranteed to start at 1 and increment
uniformly, different databases may generate ids in different ways.


Regarding if I wanted to drop a table.

I think one thing I can do is use DB Browser for SQLite, and delete the table e.g. a table called blahabcs through that.

Then I could do rails destroy model blahabc

Then could do rails db:migrate

Would that be equivalent or the same as dropping the table?

Ideally, you would do all of this with migrations. You can run the migration that created the table down and back up again (look at the Rails Guide for Migrations for the exact syntax, but it's something like rake db:migrate:down VERSION=123456, where 123456 is the numerical part of the migration's filename). Then you can simply run rake db:migrate, and since that migration is down, it will be brought back up. The table, regardless which database engine is hosting it, will be dropped entirely and re-created.

That's an over-simplification of what you would do, because you may have made changes to the table after its initial creation in other migrations. So what you would need to do is run all migrations related to this table down in reverse order (same directions as above), going from newest (highest number) to the oldest (the creation of the table). Then run rake db:migrate and you'll be back to an empty table.

Note that if you had foreign keys to this table in other tables, they will all be wrong (orphan) at this point.

If you are doing this in SQLite, then I also imagine you are working in development, so there really is no point to this surgical removal and re-creation of one table. Just delete the entire database (rm db/development.sqlite3), and run rake db:migrate. You'll be back to a new and empty database.


You can't successfully run a bog-standard Rails command in your
app, and you want to "work around" that?

Honestly, that's a terrible idea that has "future regret" stamped all
over it...