DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Dominik Weber
Dominik Weber

Posted on • Originally published at domysee.com on

Implementing Soft-Delete in pure MySQL

Soft-delete, setting a deleted flag instead of actually deleting, is a great way to ensure the ability to recover accidentally deleted data by users for many applications.

Some technologies, like Ruby on Rails, have soft-delete baked in, so the developer doesn’t have to do anything to use it.

Unfortunately, others don’t have that, and for some it is quite difficult to integrate. I’ve tried it with Entity Framework 6. It worked, but wasn’t nearly as clean as I hoped it would be.

So for the cases where its too much work to add soft-delete to the application, I’ve developed a MySQL script that adds it to the database, without having to change the application at all.

The Script

The script is a stored procedure that loops through all tables and creates the SQL statements needed to add soft-delete to the database.

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddSoftDelete`()
BEGIN
    DECLARE q, result TEXT;
    DECLARE tableName TEXT;
    DECLARE columnList TEXT;

    DECLARE done1 INT DEFAULT FALSE;
    DECLARE tableNamesCursor CURSOR FOR 
    SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = "your_schema";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;

    SET result = "";
    OPEN tableNamesCursor;
    tableLoop: LOOP
        FETCH tableNamesCursor INTO tableName;
        IF done1 THEN 
            LEAVE tableLoop;
        END IF;

        SET q = CONCAT("CREATE TABLE ", tableName, "_deleted SELECT * FROM ", tableName, " WHERE 1 = 0;");  #don't copy data and don't copy indexes
        SET result = CONCAT(result, q, "\n");

        SET q = CONCAT("ALTER TABLE ", tableName, "_deleted ADD Deleted DATETIME;");
        SET result = CONCAT(result, q, "\n");

        #Create Triggers to move removed Data
        SET result = CONCAT(result, "DELIMITER |", "\n");

        SET columnList = "";
        BLOCK2: BEGIN
            DECLARE columnName, columnTableName TEXT;
            DECLARE done2 INT DEFAULT FALSE;
            DECLARE columnNamesCursor CURSOR FOR 
            SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = "schema_name";
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;

            OPEN columnNamesCursor;
            columnLoop: LOOP
                FETCH columnNamesCursor INTO columnName, columnTableName;
                IF done2 THEN 
                    LEAVE columnLoop;
                END IF;

                IF columnTableName = tableName THEN
                    IF columnList = "" THEN
                        SET columnList = CONCAT("OLD.", columnName);
                    ELSE
                        SET columnList = CONCAT(columnList, ", OLD.", columnName);
                    END IF;
                END IF;
            END LOOP columnLoop;
        END BLOCK2;

        SET q = CONCAT("CREATE TRIGGER ", tableName, "_delete AFTER DELETE ON ", tableName, " FOR EACH ROW BEGIN INSERT INTO ", tableName, "_deleted VALUES(", columnList, ", NOW()); END |"); #NOW() for the Deleted Column
        SET result = CONCAT(result, q, "\n");

        SET result = CONCAT(result, "DELIMITER ;", "\n");

        SET result = CONCAT(result, "\n");
    END LOOP tableLoop;

    SELECT result;
END

After adding that stored procedure, you’d call it with

call AddSoftDelete();

Then you’d take the output and execute it. For each table in your database, it creates a table with the added suffix β€œ_deleted” with the same structure as the original table, adds a β€œDeleted” column and a trigger on the original table that inserts deleted columns into the β€œ_deleted” table. The created tables won’t have any indices on them. This is to ensure that no errors occurr due to duplicate entries. Since the rows in the original table are really deleted, it is possible to have multiple rows with the same id, just not at the same time. But all these rows will end up in the β€œ_deleted” table some day, so it should not check for duplicate data.

Here’s an example output for my β€œpeople” table (there will be one block of these statements for each table you have):

CREATE TABLE people_deleted SELECT * FROM people WHERE 1 = 0;
ALTER TABLE people_deleted ADD Deleted DATETIME;
DELIMITER |
CREATE TRIGGER people_delete AFTER DELETE ON people FOR EACH ROW BEGIN INSERT INTO people_deleted VALUES(OLD.Id, OLD.Firstname, OLD.Lastname, OLD.Sex, OLD.SocialSecurityNumber, OLD.DateOfBirth, NOW()); END |
DELIMITER ;

So that’s all there is to it. Hope it helps you!

β€œBut what if I wanted to get rid of it again” you ask? β€œSurely you wrote a script for that too”. The answer is yes, indeed I did. And I will of course provide it for you.

Here it is:

CREATE PROCEDURE `RemoveSoftDelete`()
BEGIN
    DECLARE q, result TEXT;
    DECLARE tableName TEXT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableNamesCursor CURSOR FOR 
    SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = "your_schema";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET result = "";
    OPEN tableNamesCursor;
    tableLoop: LOOP
        FETCH tableNamesCursor INTO tableName;
        IF done THEN 
            LEAVE tableLoop;
        END IF;

        IF REPLACE(tableName, "_deleted", "") != tableName THEN
            SET q = CONCAT("DROP TABLE ", tableName, ";");
            SET result = CONCAT(result, q, "\n");
        END IF;

        IF REPLACE(tableName, "_deleted", "") = tableName THEN            
            SET q = CONCAT("DROP TRIGGER ", tableName, "_delete;");
            SET result = CONCAT(result, q, "\n");
        END IF;
    END LOOP;

    SELECT result;
END

It creates SQL statements to remove the added tables and triggers. Here’s an example output again for my β€œpeople” table:

DROP TRIGGER people_delete;
DROP TABLE people_deleted;

Now that’s really it. Happy coding :)


Follow me on Twitter for more of my thoughts, articles, projects and work.

Top comments (0)

πŸ€” Did you know?

Β 
🌚 Dark mode is available in Settings.