Skip to main content
Version: 0.0.4

IaSQL on TypeORM (SQL ORM)

In this tutorial we will run TypeORM SQL migrations on top of IaSQL to deploy a Node.js HTTP server within a docker container on your AWS account using Fargate ECS, IAM, ECR and ELB. The container image will be hosted as a private repository in ECR and deployed to ECS using Fargate.

The code for this tutorial can be found in this part of the repository

Start managing an AWS account with a hosted IaSQL db

Connecting an AWS account directly will redirect you to the AWS console to create a new IAM user for IaSQL with its respective credentials. Please do not modify the fields in the cloud formation template or the account will be misconfigured.

You will be able to see your PostgreSQL connection information when the cloud formation stack creation completes.

Make sure to copy the PostgreSQL connection string as you will not see it again.

Add the necessary cloud services to the hosted database

  1. Many different clients can be used to connect to a PostgreSQL database. For this tutorial, we'll use the standard psql CLI client. If you need to install psql, follow the instructions for your corresponding OS here.

  2. The up part of the first migration calls the iasql_install SQL function to install modules into the hosted database.

my_project/infra/src/migration/1646683871211-Install.js
SELECT * from iasql_install(
'aws_iam',
'aws_cloudwatch',
'aws_ecr',
'aws_ecs_fargate',
'aws_elb',
'aws_security_group',
'aws_vpc'
);

If the function call is successful, it will return a virtual table with a record for each new table in your database under created_table_name and the number of existing resources or records imported from the account under record_count.

       module_name        |      created_table_name       | record_count
--------------------------+-------------------------------+--------------
aws_cloudwatch@0.0.1 | log_group | 0
aws_iam@0.0.1 | role | 0
aws_ecr@0.0.1 | public_repository | 0
aws_ecr@0.0.1 | repository | 1
aws_ecr@0.0.1 | repository_policy | 0
aws_security_group@0.0.1 | security_group | 2
aws_security_group@0.0.1 | security_group_rule | 0
aws_vpc@0.0.1 | vpc | 1
aws_vpc@0.0.1 | subnet | 3
aws_elb@0.0.1 | load_balancer | 0
aws_elb@0.0.1 | target_group | 0
aws_elb@0.0.1 | listener | 0
aws_elb@0.0.1 | load_balancer_security_groups | 0
aws_ecs_fargate@0.0.1 | cluster | 0
aws_ecs_fargate@0.0.1 | service | 0
aws_ecs_fargate@0.0.1 | task_definition | 0
aws_ecs_fargate@0.0.1 | container_definition | 0
aws_ecs_fargate@0.0.1 | service_security_groups | 0
(17 rows)

Connect to the hosted db and provision cloud resources in your AWS account

  1. Get a local copy of the ECS Fargate examples repository
git clone git@github.com:iasql/ecs-fargate-examples.git my_project
cd my_project
git filter-branch --subdirectory-filter typeorm
  1. Install the Node.js project dependencies under the my_project/infra folder
cd infra
npm i
  1. (Optional) Set the desired project name that your resources will be named after by changing the name in the my_project/infra/package.json. If the name is not changed, quickstart will be used.
note

The project-name can only contain alphanumeric characters and hyphens(-) because it will be used to name the load balancer

  1. Create a ormconfig.json with the connection parameters provided on db creation. In this case:
my_project/infra/ormconfig.json
{
"type": "postgres",
"host": "db.iasql.com",
"username": "d0va6ywg",
"password": "nfdDh#EP4CyzveFr",
"database": "_4b2bb09a59a411e4",
"ssl": true,
"extra": {
"ssl": {
"rejectUnauthorized": false
}
},
"logging": false,
"migrations": [
"src/migration/**/*.js"
],
"cli": {
"migrationsDir": "src/migration"
}
}
  1. Run the existing TypeORM migrations on the hosted IaSQL db by invoking typeorm CLI
npx typeorm migration:run
  1. The up part of the second, and last, migration will run the following code:
my_project/infra/1646683871219-Initial.js
const pkg = require('../../package.json');
// TODO replace with your desired project name
const PROJECT_NAME = pkg.name;

// AWS ELASTIC CONTAINER REPOSITORY (ECR)
const region = !process.env.AWS_REGION ? '' : `-${process.env.AWS_REGION}`;
const REPOSITORY = `${PROJECT_NAME}-repository`;

// AWS IAM
const TASK_ROLE_NAME = `ecsTaskExecRole-${region}`;
const TASK_ASSUME_POLICY = JSON.stringify({
"Version": "2012-10-17",
"Statement": [
{
"Sid": "",
"Effect": "Allow",
"Principal": {
"Service": "ecs-tasks.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
});
const TASK_POLICY_ARN = 'arn:aws:iam::aws:policy/service-role/AmazonECSTaskExecutionRolePolicy';

// AWS CLOUDWATCH
const LOG_GROUP = `${PROJECT_NAME}-log-group`

// AWS FARGATE + ELASTIC CONTAINER SERVICE (ECS)
// https://docs.aws.amazon.com/AmazonECS/latest/developerguide/task_definition_parameters.html
const TASK_DEF_RESOURCES = 'vCPU2-8GB'; // task_definition_cpu_memory enum
const TASK_DEF_FAMILY = `${PROJECT_NAME}-td`;
const SERVICE_DESIRED_COUNT = 1;
const IMAGE_TAG = 'latest';
const CONTAINER = `${PROJECT_NAME}-container`;
const CONTAINER_MEM_RESERVATION = 8192; // in MiB
const PROTOCOL = 'TCP';
const CLUSTER = `${PROJECT_NAME}-cluster`;
const SERVICE = `${PROJECT_NAME}-service`;

// AWS SECURITY GROUP + VPC
const SECURITY_GROUP = `${PROJECT_NAME}-security-group`;
const PORT = 8088;

// AWS ELASTIC LOAD BALANCER
const TARGET_GROUP = `${PROJECT_NAME}-target`;
const LOAD_BALANCER = `${PROJECT_NAME}-load-balancer`;

module.exports = class Initial1646683871219 {

async up(queryRunner) {
// security group
await queryRunner.query(`
BEGIN;
INSERT INTO security_group (description, group_name)
VALUES ('${PROJECT_NAME} security group', '${SECURITY_GROUP}');

INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, description, security_group_id)
SELECT false, 'tcp', ${PORT}, ${PORT}, '0.0.0.0/0', '${SECURITY_GROUP}', id
FROM security_group
WHERE group_name = '${SECURITY_GROUP}';

INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, description, security_group_id)
SELECT true, '-1', -1, -1, '0.0.0.0/0', '${SECURITY_GROUP}', id
FROM security_group
WHERE group_name = '${SECURITY_GROUP}';
COMMIT;
`);

// load balancer
await queryRunner.query(`
BEGIN;
INSERT INTO target_group
(target_group_name, target_type, protocol, port, health_check_path)
VALUES
('${TARGET_GROUP}', 'ip', 'HTTP', ${PORT}, '/health');

INSERT INTO load_balancer
(load_balancer_name, scheme, load_balancer_type, ip_address_type)
VALUES
('${LOAD_BALANCER}', 'internet-facing', 'application', 'ipv4');

INSERT INTO load_balancer_security_groups
(load_balancer_name, security_group_id)
VALUES
('${LOAD_BALANCER}',
(SELECT id FROM security_group WHERE group_name = '${SECURITY_GROUP}' LIMIT 1));

INSERT INTO listener
(load_balancer_name, port, protocol, action_type, target_group_name)
VALUES
('${LOAD_BALANCER}',
${PORT}, 'HTTP', 'forward', '${TARGET_GROUP}');
COMMIT;
`);

// container (ECR + ECS)
await queryRunner.query(`
BEGIN;
INSERT INTO log_group (log_group_name) VALUES ('${LOG_GROUP}');

INSERT INTO repository (repository_name) VALUES ('${REPOSITORY}');

INSERT INTO cluster (cluster_name) VALUES('${CLUSTER}');

INSERT INTO role (role_name, assume_role_policy_document, attached_policies_arns)
VALUES ('${TASK_ROLE_NAME}', '${TASK_ASSUME_POLICY}', array['${TASK_POLICY_ARN}']);

INSERT INTO task_definition ("family", task_role_name, execution_role_name, cpu_memory)
VALUES ('${TASK_DEF_FAMILY}', '${TASK_ROLE_NAME}', '${TASK_ROLE_NAME}', '${TASK_DEF_RESOURCES}');

INSERT INTO container_definition ("name", essential, repository_name, task_definition_id, tag, memory_reservation, host_port, container_port, protocol, log_group_name)
VALUES (
'${CONTAINER}', true,
'${REPOSITORY}',
(select id from task_definition where family = '${TASK_DEF_FAMILY}' and status is null limit 1),
'${IMAGE_TAG}', ${CONTAINER_MEM_RESERVATION}, ${PORT}, ${PORT}, '${PROTOCOL.toLowerCase()}', '${LOG_GROUP}'
);
COMMIT;
`);

// create ECS service and associate it to security group
await queryRunner.query(`
BEGIN;
INSERT INTO service ("name", desired_count, assign_public_ip, subnets, cluster_name, task_definition_id, target_group_name)
VALUES (
'${SERVICE}', ${SERVICE_DESIRED_COUNT}, 'ENABLED',
(select array(select subnet_id from subnet inner join vpc on vpc.id = subnet.vpc_id where is_default = true limit 3)),
'${CLUSTER}',
(select id from task_definition where family = '${TASK_DEF_FAMILY}' order by revision desc limit 1),
'${TARGET_GROUP}'
);

INSERT INTO service_security_groups (service_name, security_group_id)
VALUES (
'${SERVICE}',
(select id from security_group where group_name = '${SECURITY_GROUP}' limit 1)
);
COMMIT;
`);

// apply the changes
await queryRunner.query(`
SELECT * FROM iasql_apply();
`);
}

// order matters
async down(queryRunner) {
// delete ECS service
await queryRunner.query(`
BEGIN;
DELETE FROM service_security_groups
USING service
WHERE name = '${SERVICE}';

DELETE FROM service WHERE name = '${SERVICE}';
COMMIT;
`);

// delete ECS + ECR
await queryRunner.query(`
BEGIN;
DELETE FROM container_definition
USING task_definition
WHERE container_definition.task_definition_id = task_definition.id and task_definition.family = '${TASK_DEF_FAMILY}';

DELETE FROM task_definition WHERE family = '${TASK_DEF_FAMILY}';

DELETE FROM role WHERE role_name = '${TASK_ROLE_NAME}';

DELETE FROM cluster WHERE cluster_name = '${CLUSTER}';

DELETE FROM repository WHERE repository_name = '${REPOSITORY}';

DELETE FROM log_group WHERE log_group_name = '${LOG_GROUP}';
COMMIT;
`);

// delete ELB
await queryRunner.query(`
BEGIN;
DELETE FROM listener
WHERE load_balancer_name = '${LOAD_BALANCER}' AND target_group_name = '${TARGET_GROUP}';

DELETE FROM load_balancer_security_groups
WHERE load_balancer_name = '${LOAD_BALANCER}';

DELETE FROM load_balancer
WHERE load_balancer_name = '${LOAD_BALANCER}';

DELETE FROM target_group
WHERE target_group_name = '${TARGET_GROUP}';
COMMIT;
`);

// delete security groups
await queryRunner.query(`
BEGIN;
DELETE FROM security_group_rule
USING security_group
WHERE security_group.id = security_group_rule.security_group_id AND security_group.group_name = '${SECURITY_GROUP}';

DELETE FROM security_group WHERE group_name = '${SECURITY_GROUP}';
COMMIT;
`);
}
}

The last part of the migration will apply the changes described in the hosted db to your cloud account which will take a few minutes waiting for AWS

my_project/infra/1646683871219-Initial.js
SELECT * from iasql_apply();

If the function call is successful, it will return a virtual table with a record for each cloud resource that has been created, deleted or updated.

 action |    table_name       |   id   |      description      
--------+---------------------+--------+-----------------------
create | public_repository | 2 | quickstart-repository
create | cluster | 2 | 2
create | task_definition | 2 | 2
create | service | 2 | 2
create | listener | 2 | 2
create | load_balancer | 2 | 2
create | target_group | 2 | 2
create | security_group | 5 | 5
create | security_group_rule | 3 | 3
create | security_group_rule | 4 | 4

Login, build and push your code to the container registry

  1. Grab your new ECR URI from the hosted DB
QUICKSTART_ECR_URI=$(psql -At postgres://d0va6ywg:nfdDh#EP4CyzveFr@db.iasql.com/_4b2bb09a59a411e4 -c "
SELECT repository_uri
FROM repository
WHERE repository_name = '<project-name>-repository';")
  1. Login to AWS ECR using the AWS CLI. Run the following command and using the correct <ECR-URI> and AWS <profile>
aws ecr get-login-password --region ${AWS_REGION} --profile <profile> | docker login --username AWS --password-stdin ${QUICKSTART_ECR_URI}
  1. Build your image locally
docker build -t <project-name>-repository app
  1. Tag your image
docker tag <project-name>-repository:latest ${QUICKSTART_ECR_URI}:latest
  1. Push your image
docker push ${QUICKSTART_ECR_URI}:latest
  1. Grab your load balancer DNS and access your service!
QUICKSTART_LB_DNS=$(psql -At postgres://d0va6ywg:nfdDh#EP4CyzveFr@db.iasql.com/_4b2bb09a59a411e4 -c "
SELECT dns_name
FROM load_balancer
WHERE load_balancer_name = '<project-name>-load-balancer';")
  1. Connect to your service!
curl ${QUICKSTART_LB_DNS}:8088/health

Clean up the created cloud resources

  1. Delete all the docker images in the repository
aws ecr batch-delete-image \
--repository-name <project-name>-repository \
--profile <profile> \
--image-ids imageTag=latest
  1. Reverse the latest migration, which in this case only requires invoking the following command once:
npx typeorm migration:revert
  1. The down part of the second, and last, migration is called which reverts the changes and calls the iasql_apply function:
my_project/infra/1646683871219-Initial.js
...
SELECT * from iasql_apply();

If the function call is successful, it will return a virtual table with a record for each cloud resource that has been created, deleted or updated.

 action |     table_name      |   id   |                                                         description                                                         
--------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------------
delete | cluster | [NULL] | arn:aws:ecs:sa-east-1:658401754851:cluster/quickstart-cluster
delete | task_definition | [NULL] | arn:aws:ecs:sa-east-1:658401754851:task-definition/quickstart-td:1
delete | service | [NULL] | arn:aws:ecs:sa-east-1:658401754851:service/quickstart-cluster/quickstart-service
delete | listener | [NULL] | arn:aws:elasticloadbalancing:sa-east-1:658401754851:listener/app/quickstart-load-balancer/3925cdb9acada7c1/7a459d6259dac5c9
delete | load_balancer | [NULL] | arn:aws:elasticloadbalancing:sa-east-1:658401754851:loadbalancer/app/quickstart-load-balancer/3925cdb9acada7c1
delete | target_group | [NULL] | arn:aws:elasticloadbalancing:sa-east-1:658401754851:targetgroup/quickstart-target/826f804f496d0a90
delete | security_group | [NULL] | sg-0015b0e07bd10b7d2
delete | security_group | [NULL] | sg-e0df1095
delete | security_group_rule | [NULL] | sgr-06aa0915b15fd23a9
delete | security_group_rule | [NULL] | sgr-02e2096ac9e77a5bf
delete | role | [NULL] | ecsTaskExecRole