Liquibase

https://docs.liquibase.com/
Admin
Liquibase is a database DevOps platform that helps manage, track, and deploy database changes with...
Tokens:28,628
Snippets:512
Trust Score:9.9
Update:4 months ago
Show doc for...
Tokens:
Raw
### SQL Example for createTable

Source: https://docs.liquibase.com/createtable

A basic SQL example demonstrating the creation of a table with a VARCHAR column and a comment.

```sql
--liquibase formatted sql

--changeset liquibase-docs:createTable-example
CREATE  TABLE  department.person  (address  VARCHAR(255)  NULL)  COMMENT='A String';
ALTER  TABLE  department.person  COMMENT  =  'A String';
```

--------------------------------

### Create Table with Schema, Catalog, and Remarks

Source: https://docs.liquibase.com/createtable

This example shows how to create a table with specified schema, catalog, and remarks, using property substitution for the tablespace.

```xml
<changeSet id="2" author="liquibase">     <createTable catalogName="department2"             remarks="A String"             schemaName="public"             tableName="person"             tablespace="${tablespace}">         <column name="address" type="varchar(255)"/>     </createTable> </changeSet>
```

--------------------------------

### Create Table in YAML

Source: https://docs.liquibase.com/createtable

Example of creating a table using Liquibase's YAML format. This defines the table schema, columns, and remarks.

```yaml
databaseChangeLog:
-  changeSet:
    id:  createTable-example
    author:  liquibase-docs
    changes:
    -  createTable:
        catalogName:  department
        columns:
        -  column:
            name:  address
            type:  varchar(255)
        remarks:  A String
        schemaName:  public
        tableName:  person
        tablespace:  A String
```

--------------------------------

### Create Table with Dynamic Column Type

Source: https://docs.liquibase.com/createtable

This example demonstrates creating a table with a dynamically sized VARCHAR column. The column size is determined by a property substitution.

```xml
<changeSet id="1" author="liquibase">     <createTable tableName="department">         <column name="id" type="int">             <constraints primaryKey="true"/>         </column>         <column name="dept" type="varchar(${dep.size})">             <constraints nullable="false"/>         </column>         <column name="emp_id" type="int">             <constraints nullable="false"/>         </column>     </createTable> </changeSet>
```

--------------------------------

### Create Table in XML

Source: https://docs.liquibase.com/createtable

Example of creating a table using Liquibase's XML format. This includes the necessary schema definitions and the createTable changeset.

```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="liquibase-docs"  id="createTable-example">
        <createTable catalogName="department"
              remarks="A String"
              schemaName="public"
              tableName="person"
              tablespace="A String">
            <column  name="address"  type="varchar(255)"/>
        </createTable>
  </changeSet>

</databaseChangeLog>
```

--------------------------------

### Maven ChangelogSync Log Example

Source: https://docs.liquibase.com/maven-changelogsync

This snippet shows a typical log output from Maven's changelogSync, including Liquibase startup, database lock acquisition, change entry insertion, and build success messages.

```log
Starting Liquibase at 10:19:36 (version 4.2.1 #35 built at 2020-12-04 16:07+0000)UPDATE testdb1.DATABASECHANGELOGLOCK SET `LOCKED` = 1, LOCKEDBY = 'gemfire-PC (172.30.128.1)', LOCKGRANTED = '2020-12-08 10:19:36.857' WHERE ID = 1 AND `LOCKED` = 0; [INFO] INSERT INTO testdb1.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1607441908742-1', 'gemfire', 'gclaws938.mariadb.sql', NOW(), 10, '8:b6aed7af6f4ad92675fd07f6ff6db46d', 'sql', '', 'EXECUTED', NULL, NULL, '4.2.1', '7444377065'); [INFO] [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time:  16.226 s [INFO] Finished at: 2020-11-24T05:58:55-06:00 [INFO] ------------------------------------------------------------------------
```

--------------------------------

### Create Table in JSON

Source: https://docs.liquibase.com/createtable

Example of creating a table using Liquibase's JSON format. This structure mirrors the YAML definition for table creation.

```json
{
    "databaseChangeLog": [
        {
            "changeSet": {
                "id": "createTable-example",
                "author": "liquibase-docs",
                "changes": [
                    {
                        "createTable": {
                            "catalogName": "department",
                            "columns": [
                                {
                                    "column": {
                                        "name": "address",
                                        "type": "varchar(255)"
                                    }
                                }
                            ],
                            "remarks": "A String",
                            "schemaName": "public",
                            "tableName": "person",
                            "tablespace": "A String"
                        }
                    }
                ]
            }
        }
    ]
}
```

--------------------------------

### Create a Liquibase Changelog with a Changeset

Source: https://docs.liquibase.com/start/tutorials/mssql.html

Define a changelog file (e.g., .sql, .yaml, .json, or .xml) and add a changeset for database schema modifications. This example creates a 'salesTableZ'.

```sql
--liquibase formatted sql --changeset TsviZ:createTable_salesTableZ-1221 CREATE TABLE salesTableZ ( ID int NOT NULL, NAME varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, REGION varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, MARKET varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
--rollback DROP TABLE salesTableZ
```

--------------------------------

### Alter Table Properties (YAML)

Source: https://docs.liquibase.com/alterTableProperties

Example of using alterTableProperties in YAML format to set and unset extended table properties. Includes rollback configuration.

```yaml
databaseChangeLog:
  - changeSet:
      id: 2
      author: your.name
      changes:
        - alterTableProperties:
            tableName: test_alter_table_properties
            setExtendedTableProperties:
              tblProperties: "'external.location'='s3://mybucket/mytable','this.is.my.key'=12,'this.is.my.key2'=true"
      rollback:
        - alterTableProperties:
            tableName: test_alter_table_properties
            unsetExtendedTablePropeties:
              tblProperties: "'external.location', 'this.is.my.key','this.is.my.key2'"
```

--------------------------------

### Create Table with Partitioned Columns

Source: https://docs.liquibase.com/extendedtableproperties

Use this snippet to create a table with specified partitioned columns, table format, location, and custom properties.

```json
{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "1",
        "author": "your.name",
        "changes": [
          {
            "createTable": {
              "tableName": "test_table_properties",
              "columns": [
                {
                  "column": {
                    "name": "id",
                    "type": "int",
                    "constraints": {
                      "primaryKey": "true",
                      "nullable": "false"
                    }
                  }
                },
                {
                  "column": {
                    "name": "some_column",
                    "type": "int"
                  }
                },
                {
                  "column": {
                    "name": "some_other_column",
                    "type": "int"
                  }
                }
              ],
              "extendedTableProperties": {
                "partitionColumns": "id, some_column",
                "tableFormat": "delta",
                "tableLocation": "s3://databricks-external-folder/test_table_properties",
                "tblProperties": "'this.is.my.key'=12,'this.is.my.key2'=true"
              }
            }
          }
        ],
        "rollback": [
          {
            "dropTable": {
              "tableName": "test_table_properties"
            }
          }
        ]
      }
    }
  ]
}
```

--------------------------------

### Alter Table Properties (JSON)

Source: https://docs.liquibase.com/alterTableProperties

Example of using alterTableProperties in JSON format to set and unset extended table properties. Includes rollback configuration.

```json
{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "2",
        "author": "your.name",
        "changes": [
          {
            "alterTableProperties": {
              "tableName": "test_alter_table_properties",
              "setExtendedTableProperties": {
                "tblProperties": "'external.location'='s3://mybucket/mytable','this.is.my.key'=12,'this.is.my.key2'=true"
              }
            }
          }
        ],
        "rollback": [
          {
            "alterTableProperties": {
              "tableName": "test_alter_table_properties",
              "unsetExtendedTableProperties": {
                "tblProperties": "'external.location', 'this.is.my.key','this.is.my.key2'"
              }
            }
          }
        ]
      }
    }
  ]
}
```

--------------------------------

### Create Table with Partitioned Columns (XML)

Source: https://docs.liquibase.com/extendedtableproperties

Use this XML snippet to create a table with specified partitioned columns, table format, location, and properties. This is useful for optimizing query performance on large datasets.

```xml
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:databricks="http://www.liquibase.org/xml/ns/databricks"
    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/databricks
        http://www.liquibase.org/xml/ns/databricks/liquibase-databricks-latest.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet id="1" author="your.name">
        <createTable tableName="test_table_properties">
            <column name="id" type="int" >
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="some_column" type="int"/>
            <column name="some_other_column" type="int"/>

            <databricks:extendedTableProperties partitionColumns="id, some_column"
                                                tableFormat="delta"
                                                tableLocation="s3://databricks-external-folder/test_table_properties"
                                                tblProperties="'this.is.my.key'=12,'this.is.my.key2'=true"/>
        </createTable>

        <rollback>
            <dropTable tableName="test_table_properties"/>
        </rollback>
    </changeSet>

</databaseChangeLog>
```

--------------------------------

### Alter View Properties with XML

Source: https://docs.liquibase.com/alterviewproperties

Example of using alterViewProperties to set extended table properties in XML format. Includes a rollback section to unset properties.

```xml
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:databricks="http://www.liquibase.org/xml/ns/databricks"
    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/databricks
        http://www.liquibase.org/xml/ns/databricks/liquibase-databricks-latest.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet id="2" author="your.name">
        <databricks:alterViewProperties viewName="test_alter_view_properties">
            <databricks:setExtendedTableProperties tblProperties="'external.location'='s3://mybucket/mytable','this.is.my.key'=12,'this.is.my.key2'=true"/>
        </databricks:alterViewProperties>

        <rollback>
            <databricks:alterViewProperties viewName="test_alter_view_properties">
                <databricks:unsetExtendedTableProperties tblProperties="'external.location', 'this.is.my.key','this.is.my.key2'"/>
            </databricks:alterViewProperties>
        </rollback>
    </changeSet>

</databaseChangeLog>
```

--------------------------------

### Alter View Properties with YAML

Source: https://docs.liquibase.com/alterviewproperties

Example of using alterViewProperties to set extended table properties in YAML format. Includes a rollback section to unset properties.

```yaml
databaseChangeLog:
  - changeSet:
      id: 2
      author: your.name
      changes:
        - alterViewProperties:
            viewName: test_alter_view_properties
            setExtendedTableProperties:
              tblProperties: "'external.location'='s3://mybucket/mytable','this.is.my.key'=12,'this.is.my.key2'=true"
      rollback:
        - alterViewProperties:
            viewName: test_alter_view_properties
            unsetExtendedTablePropeties:
              tblProperties: "'external.location', 'this.is.my.key','this.is.my.key2'"
```
Tokens:
Raw