> ## Documentation Index
> Fetch the complete documentation index at: https://astronomer.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Use a MySQL or PostgreSQL database for metadata or storage

You can create Astro Private Cloud Deployments with the [Houston API](/docs/astro-private-cloud/v-1-x/houston-api) that use pre-created databases, external to the Airflow Deployment, as both a metadata storage and result storage backend.

## Prerequisites

* Workspace Admin [user privileges](/docs/astro-private-cloud/v-1-x/role-permission-reference#default-role-permissions-tables) and a Workspace ID
* (Optional) A MySQL or PostgreSQL database
* (Optional) An existing Deployment

<Note>If you create a new connection to an external database from a Deployment with existing Dag data, you must migrate that historic data to the new database. Information about your historic Deployment activity, such as task instances and Dag runs, won't be displayed as the database where you stored that information has changed.</Note>

## Step 1: Enable manual connection strings

In Astro Private Cloud 1.x, `manualConnectionStrings.enabled` is a `deployments.*` setting. Cluster configuration is the final layer for `deployments.*` keys, so the cluster value wins when both `values.yaml` and a cluster override are set. See [Configure Astro Private Cloud](/docs/astro-private-cloud/v-1-x/configure-astro-private-cloud) for the precedence rules.

Choose one of the following options based on the scope you need:

* To enable manual connection strings for one data plane cluster, update the cluster's **Configuration Override**.
* To enable manual connection strings as the platform default for clusters that don't have their own saved value for this key, update `values.yaml` and run a Helm upgrade.

### Option A: Update the cluster configuration override (recommended)

1. In the Astro UI, open **Clusters**, select your data plane cluster, then click **Edit** on **Configuration Override**. To automate this with the Houston API, use the `updateCluster` mutation with `deploymentsConfigOverride`. See [Update data plane cluster configurations](/docs/astro-private-cloud/v-1-x/override-data-plane-cluster).

2. Add the following to **Configuration Override**:

   ```json wrap theme={null}
   {
     "manualConnectionStrings": {
       "enabled": true
     }
   }
   ```

3. Click **Update cluster** to apply the change. The override is deep-merged with the cluster's existing configuration.

### Option B: Update `values.yaml` (platform default)

Use this option only when no cluster has saved an override for `deployments.manualConnectionStrings.enabled`. If a cluster already has a saved value for this key, the cluster value wins and you must update the cluster configuration as in Option A.

1. Open your `values.yaml` file.

2. Add the following under `astronomer.houston.config`:

   ```yaml wrap theme={null}
   astronomer:
     houston:
       config:
         deployments:
           manualConnectionStrings:
             enabled: true
   ```

3. Push the configuration change. See [Apply a config change](/docs/astro-private-cloud/v-1-x/apply-platform-config). If the `astronomer-houston` pods don't roll automatically after the Helm upgrade, restart them manually so they pick up the new configuration.

<Note>
  **Already-installed clusters**

  The `values.yaml` settings in this step only take effect during the initial cluster installation. For clusters that are already registered, Houston resolves Deployment configuration directly from the cluster's database record (`Cluster.config.deployments`) and ignores the Helm-derived ConfigMap. To enable manual connection strings on an existing cluster, apply the change through **System Admin → Clusters → Edit → Cluster Deployment Configuration** in Astronomer instead.
</Note>

## Step 2: (Optional) Create your database

Substitute `astro-db-name` with your own database name, if you need to create a new database.

```sql wrap theme={null}
CREATE DATABASE astro-db-name;
```

## Step 3: Add a user account to your database for the connection

Substitute `astro-user-name` and `astro-user-password` with your information. You can use an existing database for this step.

<Warning>PostgreSQL usernames must be lowercase. </Warning>

<Tabs>
  <Tab title="PostgreSQL">
    1. Create a user with a password for Astro Private Cloud to use to access the database.

    ```sql wrap theme={null}
    CREATE USER astro-user-name WITH PASSWORD 'astro-user-password';
    ```

    2. Grant all privileges on the database to the user.

    ```sql wrap theme={null}
    GRANT ALL PRIVILEGES ON DATABASE postgreSQL_linked_DB TO astro-user-name;
    ```

    3. Grant `USAGE` and `CREATE` privileges on the `public` schema to `astro-user-name`:

    ```sql wrap theme={null}
    GRANT USAGE, CREATE ON SCHEMA public TO astro-user-name;
    ```

    Now, go into the database you created, which is `astro-db-name` in this example, and run the following queries

    4. Grant all privileges on all tables, sequences, and functions to the user.

    ```sql wrap theme={null}
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO astro-user-name;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO astro-user-name;
    GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO astro-user-name;
    ```

    5. Set default privileges for the user, so any new tables, sequences, or functions automatically have the user's access.

    ```sql wrap theme={null}
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO astro-user-name;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO astro-user-name;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO astro-user-name;
    GRANT USAGE, CREATE ON SCHEMA public TO astro-user-name;
    ```
  </Tab>

  <Tab title="MySQL">
    1. Create a new user and password.

    ```sql wrap theme={null}
    CREATE USER 'astro-user-name'@'%' IDENTIFIED BY 'astro-user-password';
    ```

    2. Assign privileges.

    ```sql wrap theme={null}
    GRANT ALL PRIVILEGES ON astro-db-name.* TO 'astro-user-name'@'%';
    ```
  </Tab>
</Tabs>

## Step 4: Retrieve database host information

Retrieve the connection information for your external database. For example, with AWS, you can retrieve your endpoint information by [Finding the connection information for an RDS for MySQL DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToInstance.EndpointAndPort.html).

## Step 5: Compose a connection string for your database

You need connection strings that define how Astro Private Cloud configures the connection to your external databases from your Airflow Deployment. The values of these strings are used when you define your `metadataConnection` or `resultBackendConnection` when you create, update, or upsert your Deployment.

Use the values for your `astro-user-name`, `astro-user-password`, `astro-db-name`, and the host information you retrieved to compose the connection strings in the following format, depending on whether you want to define a result backend connection or a metadata database connection.

<Warning>
  **PgBouncer is enabled by default**

  For PostgreSQL Deployments, PgBouncer is enabled by default in Astro Private Cloud. When PgBouncer is enabled, URI-style connection strings (`postgresql://...`) are rejected during upsert and you must use the JSON format (`metadataConnectionJson` and `resultBackendConnectionJson`). Use the URI tabs below only if you have explicitly disabled PgBouncer for your Deployment.
</Warning>

<Tabs>
  <Tab title="PostgreSQL">
    #### With PGBouncer disabled

    * `metadataConnection`:

      ```text wrap theme={null}
      postgresql://astro-user-name:astro-user-password@host:5432/astro-db-name
      ```

    * `resultBackendConnection`:

      ```text wrap theme={null}
      db+postgresql://astro-user-name:astro-user-password@host:5432/astro-db-name
      ```

    <Warning>
      **Celery Executor**

      The connection string format validation regex don't cover the `resultbackend` connection string format, which includes `db+`. This is specifically required for the Celery executor worker. If the connection string doesn't include `db+`, then Celery worker pod fails. The regex validation is not implemented because it adds the complications on format validation logic in different scenarios.
    </Warning>

    #### With PGBouncer enabled

    If you have PGBouncer enabled, and are using Postgres, you must configure `metadataConnectionJson` and `resultBackendConnectionJson` instead. PgBouncer is enabled by default in Astro Private Cloud, so this is the typical path.

    Use the values for your `astro-user-name`, `astro-user-password`, `astro-db-name`, and the host information you retrieved to compose the connection strings in the following format, depending on whether you want to define a result backend connection or a metadata database connection.

    * `metadataConnectionJson`:

      ```json wrap theme={null}

      "metadataConnectionJson": {
          "user": "astro-user-name",
          "pass": "astro-user-password",
          "protocol": "postgresql",
          "host": "host",
          "port": 5432,
          "db": "astro-db-name"
          },
      ```

    * `resultBackendConnectionJson`:

      ```json wrap theme={null}
      "resultBackendConnectionJson": {
          "user": "astro-user-name",
          "pass": "astro-user-password",
          "protocol": "postgresql",
          "host": "host",
          "port": 5432,
          "db": "astro-db-name"
          },

      ```
  </Tab>

  <Tab title="MySQL">
    * `metadataConnection`:

      ```text wrap theme={null}
      mysql+mysqldb://astro-user-name:astro-user-password@host:3306/astro-db-name
      ```
    * `resultBackendConnection`:

      ```text wrap theme={null}
      db+mysql+mysqldb://astro-user-name:astro-user-password@host:3306/astro-db-name
      ```

    <Warning>
      **Celery Executor**

      The connection string format validation regex don't cover the `resultbackend` connection string format, which includes `db+`. This is specifically required for the Celery executor worker. If the connection string doesn't include `db+`, then Celery worker pod fails. The regex validation is not implemented because it adds the complications on format validation logic in different scenarios.
    </Warning>
  </Tab>
</Tabs>

## Step 6: Add to Deployment configuration

Use the [Houston API](/docs/astro-private-cloud/v-1-x/houston-api) to create your Deployment configuration.

<Warning>
  **Required: skipAirflowDatabaseProvisioning**

  When you point a Deployment at an external database, you must set `skipAirflowDatabaseProvisioning: true` in the `upsertDeployment` mutation. Without this flag, Commander overwrites the `host` value in your `metadataConnection` or `metadataConnectionJson` with the data plane database URL before running `helm install`, regardless of the host you submitted. The Deployment then uses the platform database instead of your external database without reporting an error. Setting `skipAirflowDatabaseProvisioning: true` skips automatic database provisioning and preserves the host you provided.
</Warning>

The following example shows the mutation and queries for using `upsertDeployment`. See [Houston API code examples](/docs/astro-private-cloud/v-1-x/houston-api-example-queries) for examples on how to use the `update` and `upsert` options for configuring your Deployment.

### Create a new Deployment

```graphQL expandable wrap theme={null}
mutation upsertDeployment(
  $workspaceUuid: Uuid!
  $releaseName: String
  $namespace: String!
  $label: String!
  $description: String
  $version: String
  $airflowVersion: String
  $runtimeVersion: String
  $executor: ExecutorType
  $workers: Workers
  $webserver: Webserver
  $scheduler: Scheduler
  $triggerer: Triggerer
  $properties: JSON
  $dagDeployment: DagDeployment
  $rollbackEnabled: Boolean
  $metadataConnection: String
  $resultBackendConnection: String
  $metadataConnectionJson: JSON
  $resultBackendConnectionJson: JSON
  $skipAirflowDatabaseProvisioning: Boolean
) {
  upsertDeployment(
    workspaceUuid: $workspaceUuid
    releaseName: $releaseName
    namespace: $namespace
    label: $label
    airflowVersion: $airflowVersion
    description: $description
    version: $version
    executor: $executor
    workers: $workers
    webserver: $webserver
    scheduler: $scheduler
    triggerer: $triggerer
    properties: $properties
    runtimeVersion: $runtimeVersion
    dagDeployment: $dagDeployment
    rollbackEnabled: $rollbackEnabled
    metadataConnection: $metadataConnection
    resultBackendConnection: $resultBackendConnection
    metadataConnectionJson: $metadataConnectionJson
    resultBackendConnectionJson: $resultBackendConnectionJson
    skipAirflowDatabaseProvisioning: $skipAirflowDatabaseProvisioning
  ) {
    id
    config
    urls {
      type
      url
      __typename
    }
    properties
    description
    label
    releaseName
    namespace
    status
    type
    version
    workspace {
      id
      label
      __typename
    }
    airflowVersion
    runtimeVersion
    dagDeployment {
      type
      nfsLocation
      repositoryUrl
      branchName
      syncInterval
      syncTimeout
      ephemeralStorage
      dagDirectoryLocation
      rev
      sshKey
      knownHosts
      __typename
    }
    createdAt
    updatedAt
    __typename
  }
}
```

### JSON Query example

```json expandable wrap theme={null}
{
  "workspaceUuid": "cm3g0cjd2000008l74jigb54y",
  "skipAirflowDatabaseProvisioning": true,
 "metadataConnectionJson": {
    "user": "astro-user-name",
    "pass": "astro-password",
    "protocol": "postgresql",
    "host": "host",
    "port": 5432,
    "db": "astro-db-name"
    },
  "resultBackendConnectionJson": {
    "user": "astro-user-name",
    "pass": "astro-password",
    "protocol": "postgresql",
    "host": "postgres-db-lb.external-postgres.svc.cluster.local",
    "port": 5432,
    "db": "astro-db-name"
    },
  "namespace": "",
  "executor": "CeleryExecutor",
  "workers": {},
  "webserver": {},
  "scheduler": {
    "replicas": 1
  },
  "triggerer": {},
  "label": "Rt1160-Celery-Pgbouncer-Enabled-Json-5",
  "description": "",
  "runtimeVersion": "11.6.0",
  "properties": {
    "extra_capacity": {
      "cpu": 1000,
      "memory": 3840
    }
  },
  "rollbackEnabled": true,
  "dagDeployment": {
    "type": "dag_deploy",
    "nfsLocation": "",
    "repositoryUrl": "",
    "branchName": "",
    "syncInterval": 1,
    "syncTimeout": 120,
    "ephemeralStorage": 2,
    "dagDirectoryLocation": "",
    "rev": "",
    "sshKey": "",
    "knownHosts": ""
  }
}
```

### Example query string variables

```json expandable wrap theme={null}
{
  "workspaceUuid": "cm3g0cjd2000008l74jigb54y",
  "skipAirflowDatabaseProvisioning": true,
"metadataConnection": "postgresql://astro-user-name:astro-user-password@host:5432/astro-db-name"
"resultBackendConnection": "db+postgresql://astro-user-name:astro-user-password@host:5432/astro-db-name"
  "namespace": "",
  "executor": "CeleryExecutor",
  "workers": {},
  "webserver": {},
  "scheduler": {
    "replicas": 1
  },
  "triggerer": {},
  "label": "Rt1160-Celery-Pgbouncer-Enabled-Json-5",
  "description": "",
  "runtimeVersion": "11.6.0",
  "properties": {
    "extra_capacity": {
      "cpu": 1000,
      "memory": 3840
    }
  },
  "rollbackEnabled": true,
  "dagDeployment": {
    "type": "dag_deploy",
    "nfsLocation": "",
    "repositoryUrl": "",
    "branchName": "",
    "syncInterval": 1,
    "syncTimeout": 120,
    "ephemeralStorage": 2,
    "dagDirectoryLocation": "",
    "rev": "",
    "sshKey": "",
    "knownHosts": ""
  }
}
```