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

# RDS (Postgres & MySQL)

> Provision Amazon RDS databases - MySQL/Postgres

Your service(s) may want to store/access data in Postgres/MySQL database. This guide will show you steps to create and
access Amazon RDS databases that offer fully managed Postgres/MySQL databases.

## Declarative & Automatic using `ops.json`:

For your service, if you need one or more RDS instances, you can add `ops.json` in the root directory of Github repo
you've connected for the service.

<Tip>Learn more about configuring dependencies using `ops.json` [here](/environment/services/ops-json).</Tip>

And add RDS instances as a dependency, like below.

```json theme={null}
{
  "dependencies": {
    "rds": {
      "instances": [
        {
          "id": "test123",
          "prefix": "testdep123",
          "engine": "postgres",
          "version": "17.5",
          "storage_gb": 10,
          "instance_type": "db.t4g.small",
          "publicly_accessible": false,
          "managed_password": true,
          "parameters": [
            {
              "name": "shared_preload_libraries",
              "value": "pg_stat_statements,pg_cron",
              "apply_method": "pending-reboot"
            }
          ],
          "exports": {
            "MY_RDS_INSTANCE_NAME": "$name",
            "MY_RDS_INSTANCE_ARN": "$arn",
            "MY_RDS_INSTANCE_ENDPOINT": "$endpoint",
            "MY_RDS_INSTANCE_ADDRESS": "$address",
            "MY_RDS_INSTANCE_USERNAME": "$username",
            "MY_RDS_INSTANCE_PASSWORD_ARN": "$passwordArn",
            "MY_RDS_INSTANCE_PASSWORD": "$password",
            "MY_RDS_INSTANCE_DB_NAME": "$dbName",
            "MY_RDS_INSTANCE_DSN": "$dsn"
          }
        }
      ]
    }
  }
}
```

You can add as many RDS instances as you want.

Arguments you can add in each instance object above:

1. `id` - Alphanumeric string. Must be unique amongst the instances you've declared above. Changing this string will
   replace the original instance with new one.
2. `prefix` - Alphanumeric string. Will be used as a prefix in the name of your instance. Changing this string will
   replace the original instance with new instance.
3. `engine` - Provide either `postgres` or `mysql`. Default: `postgres`
4. `version` - Pick a version for your postgres or mysql database. Ensure RDS supports it, by referring to AWS docs -
   [Postgres](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-versions.html) /
   [MySQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Concepts.VersionMgmt.html). Default: `""` - so
   it will provision latest version as decided by AWS.
5. `storage_gb` - Specify the amount of storage you need in GB. You can set one value and yet change this later.
   Default: `10`
6. `instance_type` - Specify any class of instance in the following groups - "db.t4g.*", "db.t3.*", "db.m5.\*". Refer to
   [AWS docs](https://aws.amazon.com/rds/instance-types/) for available sizes. Default: `db.t4g.small`
7. `publicly_accessible` - Set it to `true` if you need this database to be available publicly on the internet. You
   would want this to be `false` most of the time. Default: `false`
8. `multi_az` - Specifies if the RDS instance is multi-AZ
9. `maintenance_window` - The window to perform maintenance in. Syntax: "ddd:hh24:mi-ddd:hh24:mi". Eg:
   "Mon:00:00-Mon:03:00". See
   [RDS Maintenance Window docs](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Maintenance.html#AdjustingTheMaintenanceWindow)
   for more information.
10. `managed_password` - Controls who creates and stores the RDS master password. Default: `true`.

    * When `true` (recommended), AWS RDS generates the master password and stores it in its own AWS Secrets Manager
      entry, and RDS rotates it automatically over time.
    * When `false`, LocalOps generates a cryptographically unique password string and sets it as the RDS master
      password. The password is not rotated, so the same value keeps being used for the life of the instance.

    Setting this to `false` is not recommended since keeping the same master password forever is less secure than
    letting RDS rotate it. Regardless of which mode you pick, use `$password` and `$dsn` in `exports` to fetch the
    current password and connection string - LocalOps resolves them transparently in both cases.
11. `parameters` - Optional list of DB parameter group settings to apply to the RDS instance. Each entry is an object
    with the following keys:

    * `name` - Name of the parameter (e.g., `shared_preload_libraries`, `max_connections`). Must be a valid parameter
      for the chosen `engine` and `version`. Refer to the
      [Postgres parameter docs](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Parameters.html)
      or
      [MySQL parameter docs](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html)
      for available names.
    * `value` - Value to set for the parameter. Always specified as a string.
    * `apply_method` - When the change should take effect. Required. One of `immediate` or `pending-reboot`. Use
      `pending-reboot` for parameters that require a database restart (such as `shared_preload_libraries`).

    When `parameters` is provided, LocalOps creates a dedicated DB parameter group for the instance and attaches it.
    Adding, updating or removing entries on subsequent deployments updates the parameter group in-place.
12. `exports` - Set of key value pairs. Keys are the ENVIRONMENT VARS we will pass to your code / containers. Values are
    the properties of the RDS instance provisioned. See below for available properties.

<Warning>Changing `id` or `prefix` string will delete & replace the original RDS instance with new one.</Warning>

Available properties of the RDS instance to use in `exports`:

1. `$name` - Name of the RDS instance
2. `$arn` - Amazon resource name of the RDS instance. Eg., `arn:rds:..`
3. `$endpoint` - The connection endpoint in `address:port` format.
4. `$address` - Specifies the DNS address of the DB instance.
5. `$dbName` - Specifies the database name created under the RDS instance. This is auto-created for you so you won't
   specific this name explicitly anywhere in your config.
6. `$username` - Specifics the username of the database user. You will typically use this in your code's Db connection
   string to access the database `$dbName`.
7. `$passwordArn` - Password of the user `$username` is automatically generated, encrypted and maintained by RDS in AWS
   secret manager. Your code has to read the password from the AWS secret manager using this `$passwordArn`.
8. `$password` - The plaintext password of the user `$username`. LocalOps resolves the password from AWS Secrets
   Manager and injects it directly as an environment variable, so your code can use it without calling the Secrets
   Manager API. Treat this value as a secret.
9. `$dsn` - Ready-to-use database connection string for the instance, in the standard URL form for the chosen engine
   (e.g., `postgres://$username:$password@$address:5432/$dbName` for Postgres or
   `mysql://$username:$password@$address:3306/$dbName` for MySQL). Convenient for libraries/ORMs that accept a single
   connection URL. Treat this value as a secret since it embeds the password.

### Lifecycle:

If you have provided `ops.json` at the root of the git repository, it will be processed if a corresponding service in
any of your active environment points at the same repository as source and when a deployment is triggered.

* When the service is spinned up first time or when a new deployment is triggered, `ops.json` is parsed for processing.
  Resources declared in the `dependencies` object will be provisioned before your code starts to run.
* Resources with same `id` are provisioned only once for the life of the service. And updated when there is a change in
  one of the properties above.
* Keys in `exports` object will be passed as enviroment variables to your service.
* When the service is deleted, the provisioned RDS instances are deleted from the cloud account immediately &
  automatically.

### Private only access:

RDS databases can be accessed from your code just as usual using your SQL-compatible DB libraries or ORMs.

All RDS instances are created only in the private subnets of the same VPC where your environment is running. And they
are attached with following security group.

Ingress:

1. From source: `10.0.0.0/16` (Your environment's VPC CIDR IP range)
2. At port: `5432` (for Postgres) or `3306` (for MySQL)
3. Protocol: `TCP`

So only workload/containers/servers from within your environment's VPC can access the RDS instance.

### Reading Database password:

By default (`managed_password: true`), RDS creates and encrypts a unique random password and stores it in your AWS
account's Secrets Manager, and rotates it automatically. You can capture the ARN of this secret under an environment
variable like shown above and fetch the password value using AWS SDK.

If you set `managed_password: false`, LocalOps generates a cryptographically unique string and uses it as the RDS
master password instead. This password is not rotated, which is why `managed_password: true` is the recommended
setting.

In both modes, you can use `$password` to receive the resolved plaintext password as an environment variable, or
`$dsn` for a ready-to-use connection string - LocalOps handles the resolution for you regardless of which mode the
instance was created in.

When `managed_password: true`, RDS rotates the password periodically, so the `$password` and `$dsn` values injected at
container start can go stale between rotations. Also export `$passwordArn` and have your code fall back to reading the
current password from AWS Secrets Manager using that ARN whenever a DB connection fails with a bad-password / auth
error, then retry the connection with the freshly fetched password.

### Pre-configured for production use:

All RDS instances are pre-configured for production use.

1. Daily backup is enabled. With 30-day retention for each backup.
2. Encryption is enabled to safeguard data at rest.
3. Backup is created automatically when the instance is about to get deleted.

For speed and cost savings reasons, all the above are turned off in ephemeral services we create in the case of
[pull request previews](https://localops.co/blog/introducing-pull-request-previews).

## Manually provision new RDS Instance:

To proceed with this guide, you would need access to:

1. AWS console with permissions to create RDS database
2. LocalOps console

Goal is to create a RDS database in AWS and give your environment/service(s) access to it. Please follow the steps below
to proceed:

### 1. Fetch the VPC and private subnet IDs

To give your environment and service(s) access to a RDS database, you need to create the RDS database within the same
VPC and region used by the environment.

Navigate to LocalOps console and the environment you created. In the environment's overview section,

1. Pick/copy the VPC ID and Private Subnet IDs
2. Note down the region

You will use these in the next step while creating the RDS database.

### 2. Create RDS database

Login to the same AWS account and region where you created the environment using LocalOps. And create new RDS database
with appropriate engine - Postgres/MySQL.

You will have to create a new subnet group first before creating the RDS instance. While creating the subnet group, use
the VPC ID and private subnet IDs you copied above.

<Tip>
  For the same app, if you already have a RDS database in a different AWS account, different VPC or in a different
  region, you can very well use the same and skip creating a new RDS database. We can connect your environment/services
  and the database via VPC peering. Learn more about using an existing database below.
</Tip>

For rest of the instructions, you can follow this
[official AWS guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateDBInstance.html) to create your
database.

Record the database endpoint, username and password.

### 3. Add DB endpoint and credentials as secrets

Last step is to give your service(s) access to the RDS database. In LocalOps console, navigate the Service settings
within the corresponding environment.

In secrets section, add a new key value pair like

```
- Key: `DB_HOST`
- Value: `your-db-endpoint`
- Key: `DB_USERNAME`
- Value: `your-db-username`
- Key: `DB_PASS`
- Value: `your-db-password`
```

Learn more about secrets [here](/environment/services/secrets).

You can name the key in any way you want. In your code, you can access `DB_*` environment variables to connect and
access the database.

Repeat the above process for each RDS instance you want to create for each environment - test, staging, production,
etc.,

That's it!

## Use pre-existing RDS Instance

### 1. Setup VPC peering

Your LocalOps environment is created in its own VPC in the chosen AWS account and region. For the same application, if
you already have a RDS database instance running in the same/different AWS account, you can connect/access it with the
corresponding LocalOps environment using Amazon VPC peering.

VPC peering lets you connect two VPCs from same or different AWS account/region so that resources in one VPC can access
the resources in another VPC like they belong to the same network.

Ping us on slack / write to us at [support@localops.co](mailto:support@localops.co) / [book a call](https://go.localops.co/meet-engineer) with our team
to guide you on this.

### 2. Add DB endpoint and credentials as secrets

Last step is to give your service(s) access to the RDS database. In LocalOps console, navigate the Service settings
within the corresponding environment.

In secrets section, add a new key value pair like

```
- Key: `DB_HOST`
- Value: `your-db-endpoint`
- Key: `DB_USERNAME`
- Value: `your-db-username`
- Key: `DB_PASS`
- Value: `your-db-password`
```

Learn more about secrets [here](/environment/services/secrets).

You can name the key in any way you want. In your code, you can access `DB_*` environment variables to connect and
access the database.

That's it. 🎉