Skip to main content
Version: Next

IaSQL on SQL

In this tutorial, we will run SQL queries on an IaSQL database to deploy a Node.js HTTP server within a docker container on your AWS account using Fargate ECS, CodeBuild, IAM, ECR, and ELB. The container image will be built in CodeBuild, hosted within a private repository in ECR, and deployed to ECS using Fargate.

Start managing an AWS account with a hosted IaSQL db

First, make sure you have an IAM user in AWS or create one with Programmatic access through the console/UI or CLI. Ensure that the IAM role has sufficient permissions to deploy and manage all your infrastructure resources.

There are two parts to each access key, which you’ll see in the IAM console/CLI after you create it, an id and a secret. Input these in the connect account modal:

If you use the AWS CLI, you can look at the credentials configured locally. In macOS and Linux this is as simple as:

$ cat ~/.aws/credentials
[default]
aws_access_key_id = <YOUR_ACCESS_KEY_ID>
aws_secret_access_key = <YOUR_SECRET_ACCESS_KEY>

You will be able to see your PostgreSQL connection information when you press Connect.

If you want to connect to the PostgreSQL database outside of the IaSQL dashboard SQL editor, make sure to copy the PostgreSQL connection string as you will not see it again.

Add the necessary cloud services to the hosted database

Use the iasql_install SQL function to install modules into the hosted database.

SELECT * from iasql_install(
'aws_ecs_simplified',
'aws_codebuild'
);

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 | log_group | 0
aws_iam | role | 0
aws_ecr | public_repository | 0
aws_ecr | repository | 1
aws_ecr | repository_policy | 0
aws_security_group | security_group | 2
aws_security_group | security_group_rule | 0
aws_vpc | vpc | 1
aws_vpc | subnet | 3
aws_elb | load_balancer | 0
aws_elb | target_group | 0
aws_elb | listener | 0
aws_elb | load_balancer_security_groups | 0
aws_ecs_fargate | cluster | 0
aws_ecs_fargate | service | 0
aws_ecs_fargate | task_definition | 0
aws_ecs_fargate | container_definition | 0
aws_ecs_fargate | service_security_groups | 0
aws_ecs_simplified | ecs_simplified | 0
aws_codebuild | codebuild_build_list | 0
aws_codebuild | codebuild_build_import | 0
aws_codebuild | codebuild_project | 0
aws_codebuild | source_credentials_list | 0
aws_codebuild | source_credentials_import | 0
(17 rows)

Provision cloud resources in your AWS account, and deploy your app

The two SELECT * from iasql_apply(); queries will apply the changes described in the hosted db to your cloud account which can take a few minutes waiting for AWS. It will then print a virtual table with the cloud resources that have been created, deleted, or updated.

INSERT INTO ecs_simplified (app_name, app_port, public_ip, image_tag)
VALUES ('quickstart', 8088, true, 'latest');

SELECT * from iasql_apply();

INSERT INTO source_credentials_import (token, source_type, auth_type)
VALUES ('gh_XXXXXXXXX', 'GITHUB', 'PERSONAL_ACCESS_TOKEN');

INSERT INTO role (role_name, assume_role_policy_document, attached_policies_arns)
VALUES ('quickstart', '{
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "codebuild.amazonaws.com"
},
"Action": "sts:AssumeRole"
},
],
"Version": "2012-10-17"
}', array['arn:aws:iam::aws:policy/AWSCodeBuildAdminAccess', 'arn:aws:iam::aws:policy/CloudWatchLogsFullAccess', 'arn:aws:iam::aws:policy/EC2InstanceProfileForImageBuilderECRContainerBuilds']);

INSERT INTO codebuild_project (project_name, source_type, service_role_name, source_location, build_spec)
VALUES ('quickstart', 'GITHUB', 'quickstart', 'https://github.com/iasql/iasql-engine', SELECT generate_put_ecr_image_build_spec('us-west-1', 'latest', 'quickstart-repository', SELECT repository_uri FROM ecs_simplified WHERE app_name = 'quickstart', 'examples/ecs-fargate/prisma/app'));

INSERT INTO codebuild_build_import (project_name)
VALUES ('quickstart');

SELECT * from iasql_apply();

If the function calls are successful, they will return a virtual table with a record for each cloud resource type that has been created, deleted, or updated.

Grab your load balancer DNS and access your service!

SELECT load_balancer_dns
FROM ecs_simplified
WHERE app_name = 'quickstart';
curl ${QUICKSTART_LB_DNS}:8088/health

Delete managed cloud resources

danger

If you did not create a new account this section will delete all records managed by IaSQL, including the ones that previously existed in the account under any of the used modules. Run SELECT * FROM iasql_plan_apply() after SELECT delete_all_records(); and before SELECT iasql_apply(); to get a preview of what would get deleted. To undo SELECT delete_all_records();, simply run SELECT iasql_sync(); which will synchronize the database with the cloud's state.

Delete all IaSQL records invoking the void delete_all_records function and apply the changes described in the hosted db to your cloud account

SELECT delete_all_records();
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