Skip to content

Database

TinyStacks supports creating a Postgres database as part of your stack. You can also choose to pass in a database

Changing your stack's database settings

Go to your Stacks page and, for the stack you want to modify, click the gear icon in the lower right corner. This will open the Settings page. In the left hand column, under Stage, select the stage to which you want to add a database. Then, from the same column, select Database.

You will see two different screens here depending on whether your stage currently has a database or not. If it has a database, you will see a screen that enables you to change the instance type for your database as well as the amount of disk storage used.

TinyStacks - change database settings for a stack

If your stage currently has no database, you will see the following screen. You can either add a new Postgres database, or add another existing AWS database to your stack.

TinyStacks - change database settings for a stack

Note: Saving your changes will trigger a rebuild of your stage. Your application may not be accessible on this stage until the rebuild and redeploy has completed.

Connecting to your Postgres database from your application

When you have TinyStacks create a Postgres database for you, we push all information about the database - including username and password - into a set of runtime variables. These are exposed to your application as environment variables in your Docker container instances.

The variables pushed include:

  • PG_HOST: The IP address
  • PG_PORT: The port on which the Postgres database is running
  • PG_CREDENTIALS_SECRET: The password for connecting to your Postgres database
  • PG_DATABASE: The name of the database on the database host server

Your application code should be able to access these values the same as it would any other environment variable (e.g., process.env in Node.js or os.environ.get() in Python).

Using a bastion host

By default, your Postgres database is only accessible from the same VPC in which your application runs. However, many teams will want to connect directly to their databases to run queries with tools like MySQL Workbench.

To enable these scenarios, you need to create a bastion host. The bastion host is an Amazon EC2 instance that sits in your VPC and creates a secure SSH tunnel so you can connect to your database. The SSH connection requires using public key cryptography, which prevents unauthorized users from connecting to your bastion and attempting to access your database.

Creating a bastion host

You can create a bastion host when you initially launch your stack and configure your Postgres database. If you didn't add a database to your stack yet, you can navigate to Stack Settings for your stack and add it under Stage settings -> Database.

Connecting to your bastion host

To connect to your database, navigate to your stack's Stack settings page. Under Stage settings -> Database, you'll see a box labeled Bastion connection information. Here, you can download the private key you'll need to connect to your database.

TinyStacks - see secrets for Postgres

Once downloaded, place your private key somewhere on your computer where you can reference it easily. Make sure you secure the machine on which the private key exists; anyone with access to this key and your bastion's connection information could use this to attempt to gain access to your database.

The command displayed in this box will enable you to connect to your bastion via the command line, where you can use command-line Postgres tools to query your database. If you prefer to use a visual tool, like MySQL Workbench, you can configure the tool to connect to your database via an SSH tunnel using your connection information and your SSH private key. Consult your tool's documentation for detailed instructions.

Accessing your database's credentials (username and password)

You will, of course, still need your database's username and password to connect to it!

Your Postgres database username and password are stored securely in your AWS account using AWS Secrets Manager. You can see these secrets by navigating to AWS Secrets Manager in your AWS account.

TinyStacks - see secrets for Postgres

You can identify the correct secret for your application from the AWS Console in two ways:

  • The Secret description of the stack, which will contain the name you gave your stack at stack creation time.
  • The tag aws:cloudformation:stack-name, which will also contain the name of your secret.

If you have multiple stages, you will have multiple secrets. You can distinguish them by the name of the secret, which will have the name of the stage pre-pended. In this example, since this is our Postgres database for our dev stage, it starts with the prefix dev.

In the AWS Console, you can see the information stored in this secret by expanding the Secret value dropdown. There, you can see all of the information required to connect to your Postgres database.

TinyStacks - expand secret values for Postgres

If you need to retrieve these values programmatically, you can do so using the AWS Command Line Interface (CLI) or any of the APIs available for programmatic access. For example, using the AWS CLI, you can retrieve the secret you need by searching for all secrets where the tag aws:cloudformation:stack-name matches your stack name. You can then use the command jq to filter out the return values to get the correct database for your stage.

aws secretsmanager list-secrets --region us-east-1 --filters Key=tag-key Values=aws:cloudformation:stack-name Key=tag-value,Values=test-django2 | jq '.[][] | select(.Name|test("^dev"))