[RUBY] [Rails6] devise + paranoia + both user logic deletion and unique constraints realized in MySQL8 series

Introduction

In the title environment, I struggled to balance the logical deletion of the user and the unique constraint. I will briefly summarize the solution. There was a way to override the unique constraint of devise and give it its own constraint. All of them are the result of wondering if they are bothersome and easier to do.

Thing you want to do

In the previous environment (especially MySQL), do a logical delete instead of the devise default user physical delete, And I want to secure a unique constraint so that a withdrawn user can re-register with the same address and ID.

PostgreSQL, SQLite It seems that these two DBs can be easily implemented by utilizing the partial index. ・ Implementation article ・ Add_index Specification 1 ・ Add_index specification 2

It seems that this partial index is not adopted by MySQL and cannot be handled easily. (Since the information is only 5.7, is it adopted in 8 series? Either way, it seems impossible from Rails migrate) It might be lightly anti-MySQL ...

Conclusion (How to implement with MySQL)

I think you can read this article. https://vanhuyz.com/how-to-apply-unique-restriction-with-soft-delete-in-rails/

1. After adding the deleted_at column,
2. By giving deleted_at a specific value for active users instead of Null Achieve UNIQUE with 2 columns (not realized if dollarted_at is NULL)
3. Simple logical deletion and unique constraint

Thank you very much for the best article! !!

Implementation

[environment]

• Ruby：2.6.6p146
• Rails：6.0.3.3
• MySQL：8.0.21
• mysql2：0.5.3
• devise：4.7.2
• paranoia：2.4.2

Preparation

There are many other articles on the implementation of basic parts such as environment construction, devise, and paranoia introduction, so I will omit them. It is a prerequisite that the devise standard membership registration / withdrawal function etc. works.

1. Create a configuration file paranoia.rb

config/initializers/paranoia.rb

#Processing to achieve both logical deletion and unique constraints after user withdrawal
#Non-deleted records are deleted_at = '0000-01-01 00:00:00'
#Deleted record is deleted_at != '0000-01-01 00:00:00'
Paranoia.default_sentinel_value = DateTime.new(0)

2. Index change in migration

Create a migration file that changes the index of the default Users table

rails g Change_Index_To_Users

Change the created migration file as follows

Delete the email index once created by default Create a new unique index with [email, deleted_at]

db/migrate/202009220000000.rb

class ChangeIndexUniuqueToUsers < ActiveRecord::Migration[6.0]
  def change
    remove_index :users, :email
    add_index :users, [:email,:deleted_at], unique: true
  end
end

migration

rails db:migrate

3. Add Validation

Added application-level validation

app/models/user.rb

class User < ActiveRecord::Base
  acts_as_paranoid
  validates :email, uniqueness: { scope: :deleted_at }	

4. Operation check

Start the rails console or rails server and check the DB by actually creating a user, unsubscribing, and re-registering with the same address.

You have successfully re-registered with the same address! !! Please let me know if there is any other good way!