aws_ecs_fargate

Components

Tables

    cluster

    container_definition

    service

    task_definition

Functions

    deploy_service

Enums

    transport_protocol

    assign_public_ip

    cpu_mem_combination

    task_definition_status

Code examples

Manage ECS

--- installs the ecs module and its dependencies
SELECT * FROM iasql_install('<modules>');
--- adds a new cluster
INSERT INTO cluster (cluster_name)
VALUES('<clusterName>');
--- check cluster insertion
SELECT *
FROM cluster
WHERE cluster_name = '<clusterName>';
--- adds service dependencies
BEGIN;
INSERT INTO security_group
(description, group_name)
VALUES
('<securityGroup>', '<securityGroup>');
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', '<securityGroup>', id
FROM security_group
WHERE group_name = '<securityGroup>';
INSERT INTO target_group
(target_group_name, target_type, protocol, port, vpc, health_check_path)
VALUES
('<serviceTargetGroupName>', 'ip', 'HTTP',<hostPort>, null, '/health');
INSERT INTO load_balancer
(load_balancer_name, scheme, vpc, load_balancer_type, ip_address_type)
VALUES
('<serviceLoadBalancerName>', 'internet-facing', null, 'application', 'ipv4');
INSERT INTO load_balancer_security_groups
(load_balancer_id, security_group_id)
VALUES
((SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>'),
(SELECT id FROM security_group WHERE group_name = '<securityGroup>' LIMIT 1));
INSERT INTO listener
(load_balancer_id, port, protocol, action_type, target_group_id)
VALUES
((SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>'),<hostPort>, 'HTTP', 'forward',
(SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>'));
COMMIT;
--- adds container dependencies
BEGIN;
INSERT INTO log_group (log_group_name)
VALUES ('<logGroupName>');
INSERT INTO iam_role (role_name, assume_role_policy_document, attached_policies_arns)
VALUES ('<taskExecRoleName>', '<taskRolePolicyDoc>', array['<taskPolicyArn>']);
COMMIT;
--- adds a new task definition
INSERT INTO task_definition ("family", task_role_name, execution_role_name, cpu_memory)
VALUES ('<tdFamily>', '<taskExecRoleName>', '<taskExecRoleName>', '<tdCpuMem>');
--- check task_definition insertion
SELECT *
FROM task_definition
WHERE family = '<tdFamily>' AND status IS NULL;
--- adds a new container definition
BEGIN;
INSERT INTO container_definition ("name", image, essential, memory_reservation, host_port, container_port, protocol, env_variables, task_definition_id, log_group_id)
VALUES('<containerName>', '<image>',<containerEssential>,<containerMemoryReservation>,<hostPort>,<containerPort>, '<protocol>', '{ "test": 2}', (select id from task_definition where family = '<tdFamily>' and status is null and region = '<region>' limit 1), (select id from log_group where log_group_name = '<logGroupName>' and region = '<region>'));
INSERT INTO container_definition ("name", image, tag, essential, memory_reservation, host_port, container_port, protocol, env_variables, task_definition_id, log_group_id)
VALUES('<containerNameTag>', '<image>', '<imageTag>', false,<containerMemoryReservation>,<protocol>,<tdFamily>, '<region>', '{ "test": 2}', (select id from task_definition where family = '<logGroupName>' and status is null and region = '<region>' limit 1), (select id from log_group where log_group_name = '<containerNameDigest>' and region = '<image>'));
INSERT INTO container_definition ("name", image, digest, essential, memory_reservation, host_port, container_port, protocol, env_variables, task_definition_id, log_group_id)
VALUES('<imageDigest>', '<containerMemoryReservation>', '<protocol>', false,<tdFamily>,<region>,<logGroupName>, '<region>', '{ "test": 2}', (select id from task_definition where family = '' and status is null and region = '' limit 1), (select id from log_group where log_group_name = '' and region = ''));
COMMIT;
--- check task_definition insertion
SELECT *
FROM task_definition
WHERE family = '<tdFamily>';
--- check container definition insertion
SELECT *
FROM container_definition
WHERE name = '<containerNameDigest>' AND image = '<image>' AND digest = '<imageDigest>';
--- adds a new service
BEGIN;
INSERT INTO service ("name", desired_count, subnets, assign_public_ip, cluster_id, task_definition_id, target_group_id)
VALUES ('<serviceName>',<serviceDesiredCount>, (select array(select subnet_id from subnet inner join vpc on vpc.id = subnet.vpc_id where is_default = true and vpc.region = '<region>' limit 3)), 'ENABLED', (SELECT id FROM cluster WHERE cluster_name = '<clusterName>'), (select id from task_definition where family = '<tdFamily>' and region = '<region>' order by revision desc limit 1), (SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>' and region = '<region>'));

INSERT INTO service_security_groups (service_id, security_group_id)
VALUES ((SELECT id FROM service WHERE name = '<serviceName>'), (select id from security_group where group_name = '<securityGroup>' and region = '<region>' limit 1));
COMMIT;
--- tries to update a task definition
WITH td AS (
SELECT revision
FROM task_definition
WHERE family = '<tdFamily>' AND status = '<tdActive>'
ORDER BY family, revision DESC
LIMIT 1
)
UPDATE task_definition SET revision = 55 WHERE family = '<tdFamily>' AND revision IN (SELECT revision FROM td) ;
--- check service insertion
SELECT *
FROM service
WHERE name = '<serviceName>';
--- tries to update a service (update)
UPDATE service SET desired_count =<serviceName>WHERE name = '';
--- tries to update a service (replace)
UPDATE service SET name = '<newServiceName>' WHERE name = '<serviceName>';
--- deletes service
BEGIN;
delete from service_security_groups
using service
where name = '<newServiceName>';

delete from service
where name = '<newServiceName>';


COMMIT;
--- deletes container definitons
begin;
delete from container_definition
using task_definition
where container_definition.task_definition_id = task_definition.id and task_definition.family = '<tdFamily>';

delete from task_definition
where family = '<tdFamily>';

delete from iam_role
where role_name = '<taskExecRoleName>';

delete from log_group
where log_group_name = '<logGroupName>';
commit;
--- deletes service dependencies
BEGIN;
DELETE FROM listener
WHERE load_balancer_id = (SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>')
and port =<hostPort>and protocol = 'HTTP' and action_type = 'forward'
and target_group_id = (SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>');

DELETE FROM load_balancer_security_groups
WHERE load_balancer_id = (SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>');

DELETE FROM load_balancer
WHERE load_balancer_name = '<serviceLoadBalancerName>';

DELETE FROM target_group
WHERE target_group_name = '<serviceTargetGroupName>';

DELETE FROM security_group_rule
USING security_group
WHERE group_name = '<securityGroup>';

DELETE FROM security_group
WHERE group_name = '<securityGroup>';
COMMIT;
--- deletes the cluster
delete from cluster
where cluster_name = '<newClusterName>';

Integrate with aws_ecr private repos

--- installs the ecs module and its dependencies
SELECT * FROM iasql_install('<modules>');
--- adds a new cluster
INSERT INTO cluster (cluster_name)
VALUES('<clusterName>');
--- check cluster insertion
SELECT *
FROM cluster
WHERE cluster_name = '<clusterName>';
--- adds service dependencies
BEGIN;
INSERT INTO security_group
(description, group_name)
VALUES
('<securityGroup>', '<securityGroup>');
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', '<securityGroup>', id
FROM security_group
WHERE group_name = '<securityGroup>';
INSERT INTO target_group
(target_group_name, target_type, protocol, port, vpc, health_check_path)
VALUES
('<serviceTargetGroupName>', 'ip', 'HTTP',<hostPort>, null, '/health');
INSERT INTO load_balancer
(load_balancer_name, scheme, vpc, load_balancer_type, ip_address_type)
VALUES
('<serviceLoadBalancerName>', 'internet-facing', null, 'application', 'ipv4');
INSERT INTO load_balancer_security_groups
(load_balancer_id, security_group_id)
VALUES
((SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>'),
(SELECT id FROM security_group WHERE group_name = '<securityGroup>' LIMIT 1));
INSERT INTO listener
(load_balancer_id, port, protocol, action_type, target_group_id)
VALUES
((SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>'),<hostPort>, 'HTTP', 'forward',
(SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>'));
COMMIT;
--- check target group insertion
SELECT *
FROM target_group
WHERE target_group_name = '<serviceTargetGroupName>';
--- check load balancer insertion
SELECT *
FROM load_balancer
WHERE load_balancer_name = '<serviceLoadBalancerName>';
--- adds a new ECR
INSERT INTO repository
(repository_name)
VALUES
('<repositoryName>');
--- adds a new role
INSERT INTO iam_role (role_name, assume_role_policy_document, attached_policies_arns)
VALUES ('<taskExecRoleName>', '<taskRolePolicyDoc>', array['<taskPolicyArn>']);
--- adds a new task definition
INSERT INTO task_definition ("family", task_role_name, execution_role_name, cpu_memory)
VALUES ('<tdRepositoryFamily>', '<taskExecRoleName>', '<taskExecRoleName>', '<tdCpuMem>');
--- adds a new container definition
BEGIN;
INSERT INTO container_definition ("name", repository_id, region, tag, essential, memory_reservation, host_port, container_port, protocol, env_variables, task_definition_id)
VALUES('<containerNameRepository>', (select id from repository where repository_name = '<repositoryName>' and region = '<region>'), (select region from repository where repository_name = '<repositoryName>'), '<imageTag>',<containerEssential>,<containerMemoryReservation>,<hostPort>,<containerPort>, '<protocol>', '{ "test": 2}', (select id from task_definition where family = '<tdRepositoryFamily>' and status is null and region = '<region>' limit 1));
INSERT INTO container_definition ("name", repository_id, region, essential, memory_reservation, host_port, container_port, protocol, env_variables, task_definition_id)
VALUES('<containerNameRepository>dgst', (select id from repository where repository_name = '<repositoryName>' and region = '<region>'), (select region from repository where repository_name = '<repositoryName>'), false,<containerMemoryReservation>,<protocol>,<tdRepositoryFamily>, '<region>', '{ "test": 2}', (select id from task_definition where family = '' and status is null and region = '' limit 1));
COMMIT;
--- check task_definition insertion
SELECT *
FROM task_definition
WHERE family = '<tdRepositoryFamily>';
--- check container definition insertion
SELECT *
FROM container_definition
WHERE name = '<containerNameRepository>' AND repository_id = (select id from repository where repository_name = '<repositoryName>' and region = '<region>') AND tag = '<imageTag>';
--- adds a new service
BEGIN;
INSERT INTO service ("name", desired_count, subnets, assign_public_ip, cluster_id, task_definition_id, target_group_id)
VALUES ('<serviceRepositoryName>',<serviceDesiredCount>, (select array(select subnet_id from subnet inner join vpc on vpc.id = subnet.vpc_id where is_default = true and vpc.region = '<region>' limit 3)), 'ENABLED', (SELECT id FROM cluster WHERE cluster_name = '<clusterName>'), (select id from task_definition where family = '<tdRepositoryFamily>' and region = '<region>' order by revision desc limit 1), (SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>' and region = '<region>'));

INSERT INTO service_security_groups (service_id, security_group_id)
VALUES ((SELECT id FROM service WHERE name = '<serviceRepositoryName>'), (select id from security_group where group_name = '<securityGroup>' and region = '<region>' limit 1));
COMMIT;
--- check service insertion
SELECT *
FROM service
WHERE name = '<serviceRepositoryName>';
--- check service_security_groups insertion
SELECT *
FROM service_security_groups
WHERE service_id = (SELECT id FROM service WHERE name = '<serviceRepositoryName>');
--- deletes service
BEGIN;
delete from service_security_groups
using service
where name = '<serviceRepositoryName>';

delete from service
where name = '<serviceRepositoryName>';
COMMIT;
--- deletes container definitons
begin;
delete from container_definition
using task_definition
where container_definition.task_definition_id = task_definition.id and task_definition.family = '<tdRepositoryFamily>';

delete from task_definition
where family = '<tdRepositoryFamily>';

delete from repository
where repository_name = '<repositoryName>';
commit;
--- deletes service dependencies
BEGIN;
DELETE FROM listener
WHERE load_balancer_id = (SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>')
and port =<hostPort>and protocol = 'HTTP' and action_type = 'forward'
and target_group_id = (SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>');

DELETE FROM load_balancer_security_groups
WHERE load_balancer_id = (SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>');

DELETE FROM load_balancer
WHERE load_balancer_name = '<serviceLoadBalancerName>';

DELETE FROM target_group
WHERE target_group_name = '<serviceTargetGroupName>';

DELETE FROM security_group_rule
USING security_group
WHERE group_name = '<securityGroup>';

DELETE FROM security_group
WHERE group_name = '<securityGroup>';
COMMIT;
--- tries to update cluster (replace)
UPDATE cluster SET cluster_name = '<newClusterName>' WHERE cluster_name = '<clusterName>';
--- deletes the cluster
delete from cluster
where cluster_name = '<newClusterName>';

Integrate with aws_ecr public repos

--- installs the ecs module and its dependencies
SELECT * FROM iasql_install('<modules>');
--- adds a new cluster
INSERT INTO cluster (cluster_name)
VALUES('<clusterName>');
--- adds service dependencies
BEGIN;
INSERT INTO security_group
(description, group_name)
VALUES
('<securityGroup>', '<securityGroup>');
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', '<securityGroup>', id
FROM security_group
WHERE group_name = '<securityGroup>';
INSERT INTO target_group
(target_group_name, target_type, protocol, port, vpc, health_check_path)
VALUES
('<serviceTargetGroupName>', 'ip', 'HTTP',<hostPort>, null, '/health');
INSERT INTO load_balancer
(load_balancer_name, scheme, vpc, load_balancer_type, ip_address_type)
VALUES
('<serviceLoadBalancerName>', 'internet-facing', null, 'application', 'ipv4');
INSERT INTO load_balancer_security_groups
(load_balancer_id, security_group_id)
VALUES
((SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>'),
(SELECT id FROM security_group WHERE group_name = '<securityGroup>' LIMIT 1));
INSERT INTO listener
(load_balancer_id, port, protocol, action_type, target_group_id)
VALUES
((SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>'),<hostPort>, 'HTTP', 'forward',
(SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>'));
COMMIT;
--- adds a new public ECR
INSERT INTO public_repository
(repository_name)
VALUES
('<publicRepositoryName>');
--- adds a new task definition
INSERT INTO task_definition ("family", cpu_memory)
VALUES ('<tdPublicRepositoryFamily>', '<tdCpuMem>');
--- adds a new container definition
INSERT INTO container_definition ("name", public_repository_name, tag, essential, memory_reservation, host_port, container_port, protocol, env_variables, task_definition_id)
VALUES('<containerNamePublicRepository>', '<publicRepositoryName>', '<imageTag>',<containerEssential>,<containerMemoryReservation>,<hostPort>,<containerPort>, '<protocol>', '{ "test": 2}', (select id from task_definition where family = '<tdPublicRepositoryFamily>' and status is null and region = '<region>' limit 1));
--- check container definition insertion
SELECT *
FROM container_definition
WHERE name = '<containerNamePublicRepository>' AND public_repository_name = '<publicRepositoryName>' AND tag = '<imageTag>';
--- check task_definition insertion
SELECT *
FROM task_definition
WHERE family = '<tdPublicRepositoryFamily>';
--- check public_repository insertion
SELECT *
FROM public_repository
WHERE repository_name = '<publicRepositoryName>';
--- adds a new service
BEGIN;
INSERT INTO service ("name", desired_count, subnets, assign_public_ip, cluster_id, task_definition_id, target_group_id)
VALUES ('<servicePublicRepositoryName>',<serviceDesiredCount>, (select array(select subnet_id from subnet inner join vpc on vpc.id = subnet.vpc_id where is_default = true and vpc.region = '<region>' limit 3)), 'ENABLED', (SELECT id FROM cluster WHERE cluster_name = '<clusterName>'), (select id from task_definition where family = '<tdPublicRepositoryFamily>' order by revision desc limit 1), (SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>' and region = '<region>'));

INSERT INTO service_security_groups (service_id, security_group_id)
VALUES ((SELECT id FROM service WHERE name = '<servicePublicRepositoryName>'), (select id from security_group where group_name = '<securityGroup>' and region = '<region>' limit 1));
COMMIT;
--- check service insertion
SELECT *
FROM service
WHERE name = '<servicePublicRepositoryName>';
--- deletes service
BEGIN;
delete from service_security_groups
using service
where name = '<servicePublicRepositoryName>';

delete from service
where name = '<servicePublicRepositoryName>';
COMMIT;
--- deletes container definitions
begin;
delete from container_definition
using task_definition
where container_definition.task_definition_id = task_definition.id and task_definition.family = '<tdPublicRepositoryFamily>';

delete from task_definition
where family = '<tdPublicRepositoryFamily>';

delete from public_repository
where repository_name = '<publicRepositoryName>';
commit;
--- deletes service dependencies
BEGIN;
DELETE FROM listener
WHERE load_balancer_id = (SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>')
and port =<hostPort>and protocol = 'HTTP' and action_type = 'forward'
and target_group_id = (SELECT id FROM target_group WHERE target_group_name = '<serviceTargetGroupName>');

DELETE FROM load_balancer_security_groups
WHERE load_balancer_id = (SELECT id FROM load_balancer WHERE load_balancer_name = '<serviceLoadBalancerName>');

DELETE FROM load_balancer
WHERE load_balancer_name = '<serviceLoadBalancerName>';

DELETE FROM target_group
WHERE target_group_name = '<serviceTargetGroupName>';

DELETE FROM security_group_rule
USING security_group
WHERE group_name = '<securityGroup>';

DELETE FROM security_group
WHERE group_name = '<securityGroup>';
COMMIT;
--- deletes the cluster
delete from cluster
where cluster_name = '<newClusterName>';