Overview and basic concepts

• Relational databases allow you to deploy a fully managed relational (SQL) database to your stack. You can choose from multiple database types, such as PostgreSQL, MySQL, MariaDB, Oracle Database or SQL Server.

• They are easy to set up, operate and scale. They automate capacity scaling, hardware & VM provisioning, database setup, patching, logging, backups and more.

• Stacktape supports 3 underlying engine categories, with different topology and scaling behavior:

  • Rds engine: default, cheapest, single-node database with optional read replicas for higher performance and Multi AZ standby instances for increased resilience and fault tolerance.
  • Aurora engine: mutli-node, highly available database cluster with increased durability and performance.
  • Aurora serverless engine: similar to Aurora engine, but with support for automatic usage-based scaling.

When to use

Advantages

Disdvantages

Basic usage

• Relational databases require an active connection. To establish a connection, you typically need host (database endpoint), port, database_name, database_user_name and database_user_password.
• You can pass these properties as environment variables to your workloads.

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('database.password')
      engine:
        type: postgres
        properties:
          dbName: mydatabase
          port: 5432
          storage:
            diskSizeGB: 20
          instance:
            dbInstanceSize: db.t3.micro

  apiServer:
    type: container-workload
    properties:
      resources:
        cpu: 1
        memory: 1024
      containers:
        - name: api-container
          imageConfig:
            filePath: src/main.ts
          environment:
            - name: DB_USER
              value: $This().resources.myDatabase.properties.credentials.masterUserName
            - name: DB_PASSWORD
              value: $Secret('database.password')
            - name: DB_HOST
              value: $Param('myDatabase', 'DbInstance::Endpoint.Address')
            - name: DB_PORT
              value: $Param('myDatabase', 'DbInstance::Endpoint.Port')
            - name: DB_NAME
              value: $This().resources.myDatabase.properties.engine.properties.dbName

import express from 'express';
import { Pool } from 'pg';

const pgPool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT
});

const app = express();

app.get('/time', async (req, res) => {
  const result = await pgPool.query('SELECT NOW()');
  const time = result.rows[0];

  res.send(time);
});

app.listen(3000, () => {
  console.info('Server running on port 3000');
});

Database credentials

• Configures credentials for the database master user.
• You should not input these directly. The recommended way is using a .env file together with File directive or using secrets.

resources:
  myRelationalDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: $File('.env').DB_USER_NAME
        masterUserPassword: $Secret('dbCredentials.password')
      engine:
        type: postgres
        properties:
          dbName: my-database
          port: 5432
          storage:
            diskSizeGB: 40
          instance:
            dbInstanceSize: db.t3.micro

Engine

• Database engine determines multiple important properties of your database:
  • Database type (PostgreSQL, MySQL, MariaDB, Oracle Database or SQL Server)
  • Performance
  • High availability and fault tolerance
  • Scaling behavior
  • Pricing

Rds Engine

• To use the RDS engine, set the engine.type property to postgres, mysql, mariadb, oracle-ee, oracle-se2, sqlserver-ee, sqlserver-ex, sqlserver-se or sqlserver-web.
• RDS engine is the default, cheapest, single-node, fully managed database engine.
• RdsEngine is not highly available or fault-tolerant by default. However, Stacktape allows you to configure a standby instance in a different AZ (Availability zone) to increase resilience or add read replicas to increase read performance.

Instance size

• Instance size can be configured for both primary instance and read replicas.

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbPassword')
      engine:
        type: postgres
        properties:
          dbName: app-db
          port: 5432
          storage:
            diskSizeGB: 20
          primaryInstance:
            instanceSize: db.t3.micro

MultiAz mode

• Multi AZ (Availability zone) mode can be configured for both primary instances and read replicas.

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbPassword')
      engine:
        type: postgres
        properties:
          dbName: mydatabase
          port: 5432
          storage:
            diskSizeGB: 40
          primaryInstance:
            dbInstanceSize: db.t3.micro
            multiAz: true

Read replicas

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbPassword')
      engine:
        type: postgres
        properties:
          dbName: app-db
          port: 5432
          storage:
            diskSizeGB: 20
          primaryInstance:
            instanceSize: db.t3.micro
          readReplicas:
            - instanceSize: db.t3.micro
            - instanceSize: db.t3.micro

Storage

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbPassword')
      engine:
        type: postgres
        properties:
          dbName: mydatabase
          port: 5432
          primaryInstance:
            dbInstanceSize: db.t3.micro
          storage:
            initialSize: 40
            maxSize: 400

Aurora Engine

• To use the Aurora engine, set the engine.type property to aurora-postgresql or aurora-mysql.
• Fully-managed, AWS-developed engine with clustering support, high-availability, increased durability & performance.
• Compute instances (nodes) run in a single Availability Zones. Storage is automatically replicated 6-ways accross 3 availability zones.
• Automatically load-balances read operations between nodes.
• Automatic failover - if a primary instance fails, one of the read replicas is elected as a new primary instance.
• To learn more about the AuroraEngine, refer to AWS Docs

resources:
  auroraSlsPostgres:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbSecret.password')
      engine:
        type: aurora-postgresql
        properties:
          dbName: mydatabase
          instances:
            - dbInstanceSize: db.t3.medium
          port: 5432

Aurora Serverless Engine

• To use the Aurora Serverless engine, set the engine.type property to aurora-postgresql-serverless or aurora-mysql-serverless.
• Fully-managed AWS-developed engine with clustering support, high-availability, increased durability & performance.
• Similar to AuroraEngine type, but automatically scales based on usage. Scaling is done using ACUs (Aurora Compute units).
• Each ACU has ~2GB of RAM and 1 virtual CPU.
• Can scale to 0 ACUs (database is paused, and you don't pay anything).
• To learn more about AuroraServerlessEngine, refer to AWS Docs

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbSecret.password')
      engine:
        type: aurora-postgresql-serverless
        properties:
          dbName: mydatabase
          minCapacity: 4
          maxCapacity: 8
          pauseAfterSeconds: 600

Backups

resources:
  myPgSql:
    type: 'relational-database'
    properties:
      credentials:
        masterUserName: commander
        masterUserPassword: mySecretPassword
      engine:
        type: postgres
        properties:
          port: 5432
          storage:
            diskSizeGB: 20
          instance:
            dbInstanceSize: db.t3.micro
      automatedBackupRetention: 5

Connection pool size

• Depending on the number of clients connecting to your database, you might want to adjust the connection pool size.
• Note that every new connection consume database resources.
• Depending on the database and client connecting to the database, it can also take 10s to 100s of milliseconds to initialize a database connection.

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbPassword')
      engine:
        type: postgres
        properties:
          dbName: mydatabase
          port: 5432
          primaryInstance:
            dbInstanceSize: db.t3.micro
          databaseParameters:
            connectionPoolSize: 50

Closing zombie connections

• Connections initiated by resources that are no longer running (for example stopped containers) can result in so called "zombie" connections.
• Modern, cloud-native architectures usually include horizontally scalable, ephemeral (short-lived) resources. When using these resources, you should have a strategy of dealing with zombie connections.

From container workloads

• For connections initiated from container workloads, you shouldn't forget to close the connection before your container exits. For example, ou can hook to a sigterm signal.

const connectionPool = createConnectionPool();

// remember to close the connection even on errors
process
  .on('uncaughtException', () => {
    connectionPool.close();
    process.exit(1);
  })
  .on('unhandledRejection', () => {
    connectionPool.close();
    process.exit(1);
  });

process.on('SIGTERM', () => {
  connectionPool.close();
  process.exit(0);
});

From batch jobs

• For connections initiated from batch jobs, you shouldn't forget to close the connection before your batch job finishes its job and your container exits.

const connectionPool = createConnectionPool();

connectionPool.connect();

// remember to close the connection even on errors
process
  .on('uncaughtException', () => {
    connectionPool.close();
    process.exit(1);
  })
  .on('unhandledRejection', () => {
    connectionPool.close();
    process.exit(1);
  });

doSomethingWithYourConnection();

connectionPool.close();

From lambda functions

• For connections initiated from lambda functions, you have 2 options:

Initialize and close the connection INSIDE the function handler.

• This way you can avoid having a zombie connection.
• This approach is not optimal, because creating a database connection can be slow (can take 10s to 100s of milliseconds).

  import { Client } from 'pg';
  
  const handler = async (event, context) => {
    const pgClient = new Client({
      user: process.env.DB_USER,
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      password: process.env.DB_PASSWORD,
      port: process.env.DB_PORT
    });
  
    await pgClient.connect();
  
    const result = await pgClient.query('SELECT NOW()');
    const time = result.rows[0];
  
    await pgClient.end();
  
    return { result: time };
  };
  
  export default handler;

Initialize connections OUTSIDE the function handler

• reuse it in every function invocation.

• This WILL result in zombie connections, when the container running your function stops (you can't hook to a lambda container SIGTERM signal to close it). In this case, you should do 2 things:

  • lower your database connection timeout (using a database parameter based on the database used):
    • for Postgres, set idle_in_transaction_session_timeout to something like 30min, tcp_keepalives_idle to 30min and tcp_keepalives_interval to 1min). This means inactive connections will be closed by the database.
    • for MySQL, @todo
  • if a database request fails because of a closed connection, you should re-create it within your application code.

    import { Client } from 'pg';
    
    const pgClient = new Client({
      user: process.env.DB_USER,
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      password: process.env.DB_PASSWORD,
      port: process.env.DB_PORT
    });
    
    (async () => {
      await pgClient.connect();
    })();
    
    const handler = async (event, context) => {
      const result = await pgClient.query('SELECT NOW()');
      const time = result.rows[0];
    
      return { result: time };
    };
    
    export default handler;

Accessibility

• You can configure which resources and hosts can access your cluster.
• To access your database, you always need to use your database user credentials.
• On top of that, Stacktape allows your to restrict the accessibility of your database to only certain resources or hosts.

Internet mode

• Default mode.
• Least restrictive. The database can be accessed from anywhere on the internet.

VPC mode

• The database can be accessed only from resources within the default VPC.
• Any function (provided it has joinDefaultVpc set to true), batch job or container workload within your stack can access the database.
• Additionally, IP addresses configured in whitelistedIps can also access the database (even from the internet). To disable this behavior, and enforce database isolation ONLY to the VPC, you can set the forceDisablePublicIp property.

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbPassword')
      engine:
        type: aurora-postgresql
        properties:
          instances:
            - dbInstanceSize: db.t3.medium
          port: 5432
      accessibility:
        accessibilityMode: vpc

  myFunction:
    type: function
    properties:
      packageConfig:
        filePath: path/to/my/function.ts
      joinDefaultVpc: true

Scoping workloads in VPC mode

• Similar to vpc mode, but even more restrictive. In addition to resource being in the VPC, any host or resource trying to access your database must also have the sufficient IAM (Identity and Access management) permissions. You can grant these permission to your workloads in 2 ways:
  • configuring allowAccessTo on your workload (functions, container workloads, batch jobs)
  • configuring iamRoleStatements on your workload (functions, container workloads, batch jobs)
• Additionally, IP addresses configured in whitelistedIps can also access the database (even from the internet). To disable this behavior, and enforce database isolation, you can set the forceDisablePublicIp property.

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbPassword')
      engine:
        type: aurora-postgresql
        properties:
          instances:
            - dbInstanceSize: db.t3.medium
          port: 5432
      accessibility:
        accessibilityMode: scoping-workloads-in-vpc
  myFunction:
    type: function
    properties:
      packageConfig:
        filePath: path/to/my/function.ts
      joinDefaultVpc: true
      accessControl:
        allowAccessTo:
          - myDatabase

Whitelisted IPs only mode

• The database can only be accessed from an IP addresses and CIDR blocks listed in the whitelistedIps list.

resources:
  myDatabase:
    type: relational-database
    properties:
      credentials:
        masterUserName: admin
        masterUserPassword: $Secret('dbPassword')
      engine:
        type: aurora-postgresql
        properties:
          instances:
            - dbInstanceSize: db.t3.medium
          port: 5432
      accessibility:
        accessibilityMode: whitelisted-ips-only
        whitelistedIps:
          - '147.25.33.12'

Pricing

Pricing heavily dependes on the engine used.

RDS engines:

• Database server instance
  • Price depends on the instance size and region. Allowed instances depend on the database type. Postgres instancesMySQL instances, Maria db instances, Oracle db, SQL server.
• Storage
  • $0.115 - $0.238 per GB-month
• Backups:
  • For automated backups with default retention setting (where you never store more than 100% of your total database storage), there is no additional charge.
  • Additional backup storage is $0.095 per GB-month.

Aurora engines:

• Database server instance
  • Price depends on the instance sizes and region. To see exact prices, refer to AWS pricing page
  • Price starts at $0.073 / hour.
• Storage
  • $0.10 - $0.19 per GB-month
• I/O Rate
  • $0.20 - $0.28 per 1 million read/write operations
• Backups
  • For automated backups with default retention setting (where you never store more than 100% of your total database storage), there is no additional charge.
  • Additional backup storage is $0.021 - $0.037 per GB-month.

Aurora serverless:

• ACUs (Aurora capacity units)
  • Each capacity unit has ~2GB of memory, ~1 Virtual CPU and corresponding network capabilities
  • 1 ACU costs $0.06 - $0.10 (depending on region)
  • Aurora Serverless databases can scale to 0 ACUs (with no costs)
• Storage, I/O Rate and Backups cost the same amount as Aurora non-serverless.

Data transfer charges (same for all engines).

• IN transfer: free
• OUT to VPC (subnet in the same Availability zone): free
• OUT to VPC (subnet in different Availability zone): $0.01 per GB ($0.02, because you are paying on both sides)
• OUT to Internet: first 1 GB free, then $0.09 -$0.15 per GB

FREE TIER (eligible for first 12 months)

• 750 Hours per month of db.t2.micro database usage (applicable DB engines)
• 20 GB of General Purpose (SSD) database storage \
• 20 GB of storage for database backups and DB Snapshots

API reference