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, IAM, ECR, and ELB. The container image will be built locally, hosted within a private repository in ECR, and deployed to ECS using Fargate.
IaSQL is an open-source software tool that creates a two-way connection between an unmodified PostgreSQL database and an AWS account so you can manage your infrastructure from a database.
Start managing an AWS account with a PostgreSQL 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:
$ cat ~/.aws/credentials
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 PostgreSQL database
iasql_install SQL function to install modules into the PostgreSQL database.
SELECT * from iasql_install(
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
module_name | created_table_name | record_count
aws_cloudwatch | log_group | 0
aws_iam | 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
Provision cloud resources in your AWS account
Insert a row into the
ecs_simplified table within an IaSQL
transaction the changes described in the PostgreSQL db to your cloud account which will take a few minutes waiting for AWS
SELECT * from iasql_begin();
INSERT INTO ecs_simplified (app_name, app_port, public_ip, image_tag)
VALUES ('quickstart', 8088, true, 'latest');
SELECT * from iasql_commit();
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. Login, build and push your code to the container registry
Previously, you needed to manually build and push your image to the ECR. But recently we've added the high-level
ecr_build SQL function which does all those steps automatically. It will do the following:
- Pull the code from your Github repository
- Build the Docker image in the directory you've specified
- Push the image to the ECR repository you've provided
All of these steps will be done in a CodeBuild project in your AWS account. To use the
ecr_build function, you can run:
'https://github.com/alantech/iasql/', -- replace with your own Github repo if you want to use your own codebase
WHERE repository_name = 'quickstart-repository')::varchar(255), -- replace quickstart if you've changed the project name
'./examples/ecs-fargate/prisma/app', -- the sub directory in the Github repo that the image should be built in
'main', -- the Github repo branch name
'' -- replace your github personal access token here if the repo is private
After running the above SQL command to completion, you can check the running app using the load balancer DNS name. To grab the name, run:
QUICKSTART_LB_DNS=$(psql -At 'postgres://d0va6ywg:nfdDh#EP4CyzveFr@localhost:5432/_4b2bb09a59a411e4' -c "
WHERE load_balancer_name = '<project-name>-load-balancer';")
And then connect to your service!
Delete Managed Cloud Resources
Delete the resources created by this tutorial using the following SQL code:
DELETE FROM repository_image WHERE private_repository_id = (SELECT id FROM repository WHERE repository_name = 'quickstart-repository');
DELETE FROM ecs_simplified WHERE app_name = 'quickstart';