Close

Databases

Overview

Databases resources allow you to deploy managed SQL database with multiple familiar database engines to choose from, including Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server. Stacktape makes it easy to set up, operate, and scale a relational database in the cloud. Thanks to underlying AWS RDS platform it provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups.

Usage

➡️ Basic usage

resources:
databases:
myPgSql:
allowConnectionsFrom: 'vpc'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
diskSizeGB: 20
dbInstanceSize: 'db.t3.micro'


➡️ Restricting access to database

Access to database is controlled by property allowConnectionsFrom. The allowConnectionsFrom property can be set to one of the following values:

  • "internet" - least restrictive setting. When using this option the database can be accessed from any IP address on the internet.

  • "vpc" - database can be accessed only from resources in your vpc. This means any function(provided it has joinVpc set to true), batchJob or containerWorkload within your stack can access the database. (You can additionally use property additionalWhitelistedIps to whitelist specific ip addresses from the outer internet.)

  • "scoping-workloads-in-vpc" - similar to vpc setting but even more restrictive. In addition to resource being in your vpc, it also has to scope the database in its allowAccessTo list. See example below. (You can additionally use property additionalWhitelistedIps to whitelist specific ip addresses from the outer internet.)

  • "whitelisted-ips-only" - database can only be accessed from ip addresses and cidr blocks listed in the additionalWhitelistedIps list. See example.

Example "scoping-workloads-in-vpc"

resources:
functions:
# functionOne does NOT have access to database eventhough it is joined in vpc
functionOne:
filePath: 'path/to/my-lambda.ts'
joinVpc: true
# functionTwo does have access to database, because it is scoping the database in allowAccessTo list
functionTwo:
filePath: 'path/to/my-lambda-2.ts'
joinVpc: true
allowAccessTo:
- 'myPgSql'
databases:
myPgSql:
allowConnectionsFrom: 'scoping-workloads-in-vpc'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
diskSizeGB: 20
dbInstanceSize: 'db.t3.micro'

Example "whitelisted-ips-only"

resources:
databases:
myPgSql:
allowConnectionsFrom: 'whitelisted-ips-only'
# connection to database will be only possible from the ips in following list
additionalWhitelistedIps:
- '91.127.199.254'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
diskSizeGB: 20
dbInstanceSize: 'db.t3.micro'

Disabling public ip address

To furthermore restrict access to the database you can choose to disable its public ip address using disablePublicIp property. This way the database will NOT be reachable from the internet even when you set allowConnectionsFrom to "internet". This is usually done to increase the level of database isolation and to ensure no connections are coming from outer internet.

You can only disable public ip when creating a database (first deploy), not during its update (subsequent deploys). Conversly, if you deploy a database with public ip, you CANNOT disable its public ip in subsequent deploys. We are working on overcoming this limitation.

resources:
databases:
myPgSql:
allowConnectionsFrom: 'vpc'
# cannot be changed in between multiple deployments of same stack
disablePublicIp: true
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
diskSizeGB: 20
dbInstanceSize: 'db.t3.micro'


➡️ Choosing database engine

Engine property object allows you to choose from multiple engine types for your SQL database. Additionally it is possible to define a version of engine you wish to use. However, for most of the cases specifying engine type is sufficient.

If you wish to see how to setup serverless database for engine aurora-mysql or aurora-postgresql see section about serveless databases.

You can choose from following engine types:

  • aurora-mysql - default version used is 5.7.mysql_aurora.2.07.1
  • aurora-postgresql - default version used is 10.12
  • mysql - default version used is 8.0.23
  • postgres - default version used is 13.2
  • mariadb - default version used is 10.5.8
  • oracle-ee - default version used is 19.0.0.0.ru-2021-01.rur-2021-01.r2
  • oracle-se1 - default version used is 11.2.0.4.v24
  • oracle-se2 - default version used is 19.0.0.0.ru-2021-01.rur-2021-01.r2
  • sqlserver-ee - default version used is 15.00.4073.23.v1
  • sqlserver-ex - default version used is 15.00.4073.23.v1
  • sqlserver-se - default version used is 15.00.4073.23.v1
  • sqlserver-web - default version used is 15.00.4073.23.v1

resources:
databases:
myPgSql:
allowConnectionsFrom: 'vpc'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
# OPTIONAL - specify version for your engine
version: '13.2'
diskSizeGB: 20
dbInstanceSize: 'db.t3.micro'


➡️ Choosing instance size

DbInstanceSize property object allows you to choose database instance size from one of the instance type families offered by AWS. Each instance size offers different combinations of CPU, memory, storage, and networking capacity which gives you the flexibility to choose the appropriate mix of resources for your database.


Full list of instance sizes can be found in AWS docs.

When using serverless mode databases dbInstanceSize parameter is ignored.
Not every instance size is supported for every database engine, version or region. Please see the database engine-specific pricing pages for details.

resources:
databases:
myPgSql:
allowConnectionsFrom: 'vpc'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
version: '13.2'
dbInstanceSize: 'db.t3.micro'
diskSizeGB: 20


➡️ MultiAZ backup

When running critical workloads in production, it is recommended to use MultiAZ (Multi availability zone) deployments which provide enhanced availability and durability.

  • By setting database instance "multiAz" property to true, a standby instance is created in a different Availability Zone (AZ).
  • The primary instance synchronously replicates the data to the standby instance at all times.
  • In case of an infrastructure failure, AWS performs an automatic failover to the standby instance, so that you can resume database operations as soon as the failover is complete. Since the endpoint for your DB Instance remains the same after a failover, your application can resume database operation without the need for manual administrative intervention.

resources:
databases:
myPgSql:
allowConnectionsFrom: 'vpc'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
diskSizeGB: 20
dbInstanceSize: 'db.t3.micro'
# multiAz can be enabled by setting single parameter
multiAz: true


➡️ Read replicas

Read replicas provide enhanced performance and durability for database instances. They make it easy to elastically scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads.

  • You can setup one or more replicas of the database by defining replicas section of databse configuration.
  • Creating read replicas helps you serve high-volume application by allowing reads from multiple copies of your data, thereby increasing aggregate read throughput.
  • Replication uses database's native asynchronous replication to update the read replica whenever there is a change to the primary instance.

resources:
databases:
myPgSql:
allowConnectionsFrom: 'vpc'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
diskSizeGB: 20
dbInstanceSize: 'db.t3.micro'
replicas:
# number of replicas you wish to have
count: 1
# OPTIONAL dbReplicaInstanceSize is optional. By default primary dbInstanceSize is used
dbReplicaInstanceSize: 'db.t3.micro'

Example usage

Typical example of utilizing read replicas is using them for running BI/reporting queries. This way your primary database's performance (used for reads and writes made by your main application) is not affected.

In the following example we see following infrastructure:

  • containerWorkload myApp representing main component of your application which reads and writes to the database continuously,
  • batchJob biJob which is scheduled to run daily and runs advanced analytics query to get insights from data in database.

resources:
batchJobs:
biJob:
container:
imageConfig:
dockerfilePath: 'bi-job/Dockerfile'
command: ['python', 'bijob-script.py']
environment:
# we are injecting replica database address into environment variables
DB_URL: "$GetParam('myPgSql', 'DbReplica.0::Endpoint.Address')"
resources:
cpu: 4
memory: 7800
events:
- schedule:
scheduleRate: 'cron(0 4 * * ? *)' # every day at 04:00 UTC
containerWorkloads:
myApp:
container:
imageConfig:
filePath: '_example-configs/containers/my-app.ts'
environment:
# we are injecting primary dbInstance database address into environment variables
# primary instance can be used for both read and write connections
DB_URL: "$GetParam('myPgSql', 'DbInstance::Endpoint.Address')"
resources:
cpu: 0.5
memory: 512
events:
- httpApi:
httpApiGatewayName: myApiGw
targetContainerPort: 80
path: /my-path
method: GET
databases:
myPgSql:
allowConnectionsFrom: 'vpc'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'postgres'
diskSizeGB: 20
dbInstanceSize: 'db.t3.medium'
replicas:
count: 1


➡️ Serverless database

Engine types 'aurora-mysql' and 'aurora-postgresql' support serverless mode. In this mode, database compute capacity scales proportionally to your needs.

  • database can scale-out during peak hours and scale back in during periods of low traffic,
  • database can completely pause after some time of inactivity to save costs (perfect for development environments),
  • aurora engines are compliant with standard mysql/postgresql connectors and clients,
  • Aurora serverless uses ACUs (or Aurora Capacity Units) to measure database capacity. Each ACU has approximately 2 GB of memory with corresponding CPU and networking resources.

resources:
databases:
myAuroraPgSql:
allowConnectionsFrom: 'vpc'
masterUserName: 'admin'
masterUserPassword: "$GetSecret('dbPassword')"
dbName: 'app'
port: 5432
engine:
type: 'aurora-postgresql'
auroraDbSettings:
# dbMode must be set to 'serverless'
dbMode: 'serverless'
# OPTIONAL minimum capacity in ACU (default 2)
minCapacity: 2
# OPTIONAL maximum capacity in ACU (default 4) - database will not scale above this threshold
maxCapacity: 4
# OPTIONAL pauseAfterSeconds property completely pauses the database after "pauseAfterSeconds" seconds
pauseAfterSeconds: 900 # pause after 15 minutes of inactivity


API Reference

Property in Stacktape configAllowed types
resources.databases.{name}.additionalWhitelistedIps[]string
resources.databases.{name}.allowConnectionsFromenumRequired
resources.databases.{name}.dbInstanceSizestring
resources.databases.{name}.dbNamestringRequired
resources.databases.{name}.disablePublicIpboolean
resources.databases.{name}.diskSizeGBnumber
resources.databases.{name}.engine.auroraDbSettingsAuroraDbSettings
resources.databases.{name}.engine.typeenumRequired
resources.databases.{name}.engine.versionstringnumber
resources.databases.{name}.masterUserNamestringRequired
resources.databases.{name}.masterUserPasswordstringRequired
resources.databases.{name}.multiAzboolean
resources.databases.{name}.portnumberRequired
resources.databases.{name}.replicasReplicas
🗄️ Resources — Previous
Batch Jobs
Next — 🗄️ Resources
Atlas Mongo Clusters