> For the complete documentation index, see [llms.txt](https://developer.harness.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://developer.harness.io/database-devops/3.0/use-db-devops/deployment-pipeline-configuration/rollback-and-failure-strategies/automatic-and-custom-rollback.md).

# Automatic and Custom Rollbacks

There are two types of rollback functionality:

* **Automatic rollbacks**: Generated automatically based on the change type
* **Custom rollbacks**: Explicitly specified by you in your changelog

{% hint style="warning" %}
SQL changelogs, do not support auto rollback, regardless of the Change Type. You must write custom rollback statements for all formatted SQL changelogs, can also write your own rollbacks if you want to override the default rollback statement for a Change Type that supports auto rollback.
{% endhint %}

### Automatic rollback statements <a href="#automatic-rollback-statements" id="automatic-rollback-statements"></a>

For many Change Types, such as `createTable`, `addColumn`, and `renameColumn`, Liquibase **automatically generates** the SQL statements necessary to complete the rollback, so you can simply run a command like rollback on your desired changeset(s) and be done.

When you run the update command on a createTable changeset, Liquibase executes the SQL statement CREATE TABLE myTable. When you run the rollback command to revert that changeset, Liquibase recognizes that the inverse of the original change is DROP TABLE myTable and executes that statement. In this case, you are not required to add anything special to your changelog.

```
Liquibase command 'rollback' was executed successfully.
Liquibase: Rollback has been successful.
```

However, if you try to run rollback on dropTable, Liquibase cannot automatically generate the rollback SQL:

```
Unexpected error running Liquibase: 
```

To roll back your `dropTable` statement, you have to specify custom logic in your changelog so that Liquibase knows how to correctly restore your database.

{% hint style="info" %}
Go to [Automated rollback for database schemas](/database-devops/3.0/use-db-devops/deployment-pipeline-configuration/rollback-and-failure-strategies/rollback-for-database-schemas.md) to learn how to configure automatic rollback in your pipeline.
{% endhint %}

### Custom rollback statements <a href="#custom-rollback-statements" id="custom-rollback-statements"></a>

Liquibase cannot automatically generate rollback SQL for Change Types like `dropTable` and `insert`. To roll back database updates that include these changesets, you must write custom rollback statements in your desired changeset(s) and then run the rollback command.

In these cases, you must specify custom rollback syntax in your changelog for every changeset that you might want to roll back. This way, when you run the rollback command, Liquibase knows what to do.

The following examples show custom rollback syntax for each supported changelog format:

{% tabs %}
{% tab title="SQL" %}

```sql
--changeset john-doe:1
create table testTable ( id int primary key, name varchar(255) );
--rollback drop table testTable;

--changeset john-doe:2
insert into testTable values ('1','The First', 'Country')
insert into testTable values ('2','The Second', 'Country2')
--rollback delete from testTable where id='1'
--rollback delete from testTable where id='2'

```

{% endtab %}

{% tab title="YAML" %}

```bash
databaseChangeLog:
- changeSet:
    id: 2
    author: john-doe
    changes:
    - dropTable:
        tableName: person
    rollback:
      createTable:
        catalogName: cat
        columns:
        - column:
          name: address
          type: varchar(255)
        remarks: A String
        schemaName: public
        tableName: person
```

{% endtab %}

{% tab title="XML" %}

```
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet author="john-doe" id="2">
        <dropTable tableName="person"/>

        <rollback>
            <createTable catalogName="department"
                  remarks="A String"
                  schemaName="public"
                  tableName="person"
                <column name="address" type="varchar(255)"/>
            </createTable>
        </rollback>
  </changeSet>
</databaseChangeLog>
```

{% endtab %}

{% tab title="JSON" %}

```json
{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "2",
        "author": "john-doe",
        "changes": [
          {
            "dropTable": {
              "tableName": "person"
            }
          }
        ],
        "rollback": [
          {
            "createTable": {
              "catalogName": "cat",
              "columns": [
                {
                  "column": {
                    "name": "address",
                    "type": "varchar(255)"
                  }
                }
              ],
              "remarks": "A String",
              "schemaName": "public",
              "tableName": "person"
            }
          }
        ]
      }
    }
  ]
}
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
When rolling back stored logic, Liquibase does not restore the previously stored version. Instead, Liquibase rolls back to the exact file/code specified in the custom rollback.
{% endhint %}

#### Multiple rollbacks <a href="#multiple-rollbacks" id="multiple-rollbacks"></a>

You can also specify multiple Change Types within a single `rollback` statement or across multiple `rollback` statements:

```yaml
- changeSet:
    id: multiRollbackTest
    author: john-doe
    changes:
      - createTable:
          tableName: multiRollback1
          columns:
            - column:
                name: id
      - createTable:
          tableName: multiRollback2
          columns:
            - column:
                name: id
      - createTable:
          tableName: multiRollback3
          columns:
            - column:
                name: id
    rollback:
      - dropTable:
          tableName: multiRollback1
      - dropTable:
          tableName: multiRollback2
    rollback:
      - dropTable:
          tableName: multiRollback3
```

#### Empty rollback statements <a href="#empty-rollback-statements" id="empty-rollback-statements"></a>

If you do not want to revert a change in a rollback mode, use either the keyword empty or the keyword not required inside the rollback tag. In XML, YAML, and JSON changelogs, you can also use an empty string inside the rollback tag.

{% tabs %}
{% tab title="SQL" %}

```sql
--changeset john-doe:1
create table testTable ( id int primary key, name varchar(255) );
--rollback empty
```

{% endtab %}

{% tab title="YAML" %}

```yaml
- changeSet:
    id: 1
    author: john-doe
    changes:
      - createTable:
          tableName: testTable
          columns:
            - column:
                name: id
                type: int
    rollback: empty
```

{% endtab %}

{% tab title="XML" %}

```xml
<changeSet id="3" author="john-doe">
    <createTable tableName="testTable">
        <column name="id" type="int"/>
    </createTable>
    <rollback>empty</rollback>
</changeSet>
```

{% endtab %}

{% tab title="JSON" %}

```json
{
  "changeSet": {
    "id": 1,
    "author": "john-doe",
    "changes": [
      {
        "createTable": {
          "tableName": "testTable",
          "columns": [
            {
              "column": {
                "name": "id",
                "type": "int"
              }
            }
          ]
        }
      }
    ],
    "rollback": "empty"
  }
}
```

{% endtab %}
{% endtabs %}

### Next steps <a href="#next-steps" id="next-steps"></a>

* Go to [Automated rollback for database schemas](/database-devops/3.0/use-db-devops/deployment-pipeline-configuration/rollback-and-failure-strategies/rollback-for-database-schemas.md) to configure rollback by tag or count in your Harness pipeline.
* Go to [Failure strategies in Database DevOps](/database-devops/3.0/use-db-devops/deployment-pipeline-configuration/rollback-and-failure-strategies/failure-strategies.md) to define how your pipeline handles changeset failures automatically.
* Go to [Tag database changeset](https://github.com/iKettles/harness-gitbook/tree/main/docs/database-devops/features/tag-database-changeset/README.md) to create rollback anchor points in your changelog.