Managing Database Scripts for Deployment

This page provides general guidance and best practices for creating, managing, and deploying database scripts across environments.

Overview

Improving an application often requires modifications to the data or infrastructure backing your processes. You can modify the data sources in your target environment during deployment by deploying database scripts alongside your application objects.

Both direct and external deployments support deploying database scripts alongside application objects. Manual deployments require you to directly execute database scripts against the data source.

Prerequisites

Before deploying database scripts, ensure the following:

• The target environment has the required data source configured and accessible.
• Each package containing database scripts has a data source selected. If a data source is not selected for a package, the deployment will fail.
• Database scripts are of type: .sql or .ddl.
• The Admin Console configurations in the target environment allow database scripts to be executed as part of the deployment.

Add database scripts to a package

We recommend adding database scripts to packages during development to limit additional steps at deployment time. Associating database scripts with your feature work in a package helps with maintainability and collaboration—allowing you to track, add, reorder, and manage scripts as you develop.

For detailed steps, see Add database scripts in the Prepare Deployment Packages guide.

Deploy database scripts

Direct deployment (Compare & Deploy)

When you deploy packages using direct deployment, Appian automatically handles database script execution based on the execution order you set during Compare and Deploy.

The order of script execution is based first on the order the packages were selected, then by the order specified in the package. Order is completely configurable in the Database Scripts tab of Compare and Deploy.

Once deployed, a ZIP file is created with the database scripts. This ZIP contains metadata about the order specified in Compare and Deploy to ensure that this execution order is retained for any future deployments that use the ZIP. This file is only stored for the length of time specified as the Data Retention period in the Admin Console. The default is 30 days but can be configured to meet organizational needs.

External deployment (API)

The deployment API supports database scripts differently depending on the version of the endpoint:

API Version	Database scripts format
v1/v2	Individual script files uploaded as form-data, with a single dataSource specified and databaseScripts array in the JSON body.
v3	A single database scripts ZIP uploaded as databaseScriptsFileName in the JSON body, with the ZIP file attached in form-data.

For full request and response details, see the Deploy Package API.

Deploy database scripts for generated packages

When you simultaneously deploy multiple packages that are associated with a release, packages are automatically generated in the target environment to hold all of the objects in deployed packages associated with each release.

If the generated package on the target environment contains multiple database scripts with different sources, you can only select one database source when you go to deploy the package to the next environment. To address the multiple source issue, you can either deploy one database script at a time or upload the database scripts directly into database for the next environment.

Database scripts ZIP format

During deployment, the database scripts and necessary metadata - such as execution order and target data sources - are collected and persisted in a ZIP containing:

• The individual .sql or .ddl script files.
• A script-order.xml manifest that defines the execution order, data source, and package metadata for each script.

Script Order Manifest

The script-order.xml file preserves and retains all necessary information about the execution order and target data sources needed for subsequent deployments of the ZIP file. For exports from the external APIs, the order of the package uuids in the request is used for the order of the package's scripts. Within a package, the order specified in the UI is preserved for execution order of scripts within the package.

During Compare and Deploy the order of scripts is completely configurable in the Database Script tab.

Download database scripts

You can download the database scripts ZIP in the following ways:

Where	How	What
Deployment summary view	In the database scripts section of the deployment details	Click the Download Database Scripts button.
Deployment API	Retrieve the export response for the deployment.	Access the databaseScriptsZip URL.
In an expression	To retrieve the database scripts ZIP document ID programmatically.	Use a!deployment and access the databaseScriptsPackageId property.

Best practices

Below are best practices for managing your database scripts effectively throughout the development lifecycle. Using these best practices helps ensure smooth deployments across environments.

• Add scripts to packages during development. This ensures scripts are tracked, reviewed, and deployed alongside related object changes.
• Use a consistent naming convention. Alphabetical naming (e.g., 01_Create_Tables.sql, 02_Update_Data.sql) makes ordering easier.
• Select a data source for every package with scripts. Deployment will fail if a data source is not configured.
• Trigger a record type sync when needed. If your scripts affect a data source used by a synced record type, consider triggering a sync via the import customization file.
• Test scripts in a lower environment first. Validate that scripts execute successfully before promoting to production.
• Keep scripts idempotent when possible. Design scripts so they can be safely re-run without causing errors (e.g., use IF NOT EXISTS clauses).

Duplicate file names

If multiple packages contain scripts with the same file name, Appian appends a numeric suffix (e.g., Create Tables (1).sql, Create Tables (2).sql) to ensure uniqueness within the ZIP.

Feedback