`partiqlFile`

partiqlFile is a Change Type in the Liquibase DynamoDB Pro extension that allows you to specify some PartiQL statements from an external file in your changesets. In DynamoDB changesets, use partiqlFile instead of sqlFile. For more information, see PartiQL - a SQL-compatible query language for Amazon DynamoDB.

Uses

partiqlFile is useful for complex changes that are not supported through Liquibase automated Change Types such as stored procedures. The PartiQL contained in the file can be multi-line.

Note: PartiQL files must use the .sql file extension to guarantee that your operating system can recognize them.

Run `partiqlFile`

To run this Change Type, follow these steps:

Add the Change Type to your changeset, as shown in the examples on this page.
Specify any required attributes. Use the table on this page to see which ones your database requires.
Deploy your changeset by running the update command:

liquibase update

Now, Liquibase deploys your change on your DynamoDB database. By default, read operations on DynamoDB are eventually consistent. When you look at your database immediately after running liquibase update, DynamoDB may display the status of an object as CREATING, UPDATING, or DELETING. When it finishes, it displays the status as ACTIVE.

Note: If your deployment fails because the DynamoDB waiter times out or reaches a retry limit, you can modify the waiter settings using Liquibase Parameters for Amazon DynamoDB Pro.

Available attributes

Name	Type	Description	Requirement
`path`	String	Specifies the file path of the PartiQL file to load.	Required
`dbms`	String	Specifies which database type(s) a changeset is to be used for. See valid database type names on `dbms`. Separate multiple databases with commas. Specify that a changeset is not applicable to a particular database type by prefixing with `!`. The keywords `all` and `none` are also available. Note: Liquibase currently only supports PartiQL on DynamoDB. If you use PartiQL on other databases, your changes may result in unexpected behavior.	Optional
`encoding`	String	Encoding used in the file specified in the `path` attribute. Default: `UTF-8`.	Optional
`endDelimiter`	String	Specifies delimiter to apply to the end of the statement. Your delimiter string can be a combination of one or more letters, symbols, and/or numbers, or the empty string (`""`). Default: "`;`". See also: `--pro-global-end-delimiter` and `--pro-global-end-delimiter-prioritized`. Tip: It is a best practice not to use `endDelimiter` on changesets you are running with a native executor. Native executors handle delimiters natively.	Optional
`relativeToChangelogFile`	Boolean	Specifies whether the file path is relative to the changelog file rather than looked up in the search path. Default: `false`.	Optional
`splitStatements`	Boolean	If `true`, Liquibase splits SQL statements using the delimiters `;` and `GO`. If `false`, Liquibase does not split SQL statements. Default: `true`. Tip: It is a best practice not to use `splitStatements=true` on changesets you are running with a native executor. Native executors handle statement splitting natively.	Optional
`stripComments`	Boolean	When `true`, removes any comments in the statement before executing. Otherwise, set it to `false`. Default: `true`. See also: `--pro-global-strip-comments` and `--pro-global-strip-comments-prioritized`.	Optional

Examples

databaseChangeLog:
  - changeSet:
      id: 2
      author: your.name
      changes:
        - partiqlFile:
            path: "partiql-test.sql"
            splitStatements: true
            endDelimiter: "@@"
        - rollback:
            partiqlFile:
              path: "partiql-test-rollback.sql"
              splitStatements: true
              endDelimiter: "@@"

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "2",
        "author": "your.name",
        "changes": [
          {
            "partiqlFile": {
              "path": "partiql-test.sql",
              "splitStatements": true,
              "endDelimiter":"@@"
            }
          }
        ],
        "rollback": [
          {
            "partiqlFile": {
              "path": "partiql-test-rollback.sql",
              "splitStatements": true,
              "endDelimiter":"@@"
            }
          }
        ]
      }
    }
  ]
}

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:dynamodb="http://www.liquibase.org/xml/ns/pro-dynamodb"
    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/pro-dynamodb
        http://www.liquibase.org/xml/ns/pro-dynamodb/liquibase-pro-dynamodb-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">
        <dynamodb-pro:partiqlFile path="partiql-test.sql" splitStatements="true" endDelimiter="@@"/>

        <rollback>
            <dynamodb-pro:partiqlFile path="partiql-test-rollback.sql" splitStatements="true" endDelimiter="@@"/>
        </rollback>
    </changeSet>

</databaseChangeLog>

PartiQL file contents

In this example, you can use the following PartiQL for partiql-test.sql:

INSERT INTO MusicPartiqlFileTest value {'Artist' : 'Taras Chubay','SongTitle' : 'Вона'} @@ INSERT INTO MusicPartiqlFileTestXML value {'Artist' : 'Alan Walker','SongTitle' : 'Faded'}

You can use the following PartiQL for partiql-test-rollback.sql:

DELETE FROM MusicPartiqlFileTest WHERE Artist='Taras Chubay' AND SongTitle='Вона' @@ DELETE FROM MusicPartiqlFileTestXML WHERE Artist='Alan Walker' AND SongTitle='Faded'

Database support

This Change Type is only supported for Amazon DynamoDB. It does not support auto rollback.