In this tutorial, we will use a script that uses Prisma to introspect the schema of an IaSQL database and deploy a Node.js HTTP server within a docker container on your AWS account using Fargate ECS, IAM, ECR, and ELB. 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. The container image will be hosted as a private repository in ECR and deployed to ECS using Fargate.
The code for this tutorial lives in this part of the repository.
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:
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.
Make sure to copy the PostgreSQL connection string as you will not see it again.
Add the necessary cloud services to the PostgreSQL database
Use the iasql_install SQL function to install modules into the PostgreSQL database.
SELECT * from iasql_install(
'aws_ecs_simplified'
);
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 | 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
(17 rows)
Connect to the PostgreSQL db and provision cloud resources in your AWS account
Get a local copy of the ECS Fargate examples code
Install the Node.js project dependencies under the
prisma/infrafolder
cd infra
npm i
- Modify the
.env filethat Prisma expects with the connection parameters provided on db creation. You'll need to add your Github personal access token for theecr_buildSQL function to be able to do the pull. Also, if you're going to deploy a codebase other than the default one, set theREPO_URIvariable. In this case:
DATABASE_URL="postgres://d0va6ywg:nfdDh#EP4CyzveFr@localhost:5432/_4b2bb09a59a411e4"
GH_PAT=ghp_XXX
REPO_URI="https://github.com/alantech/iasql"
- (Optional) Set the desired project name that your resources will be named after by changing the
namein themy_project/infra/package.json. If the name is not changed,quickstartwill be used.
The project-name can only contain alphanumeric characters and hyphens(-) because it will be used to name the load balancer
- Per the Prisma quickstart to add an existing project, create a basic
schema.prismafile.
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
- Pull, or introspect, the schema from your database which will auto-populate the rest of the
schema.prismafile
npx prisma db pull
- Now install and generate the Prisma client by the introspected
schema.prisma
npx prisma generate
If you install or uninstall IaSQL modules the database schema will change and you will need to run steps 5 through 7 to introspect the correct schema once again.
- Run the existing script using the Prisma entities
node index.js
This will run the following code
async function main() {
await prisma.$queryRaw`SELECT * FROM iasql_begin();`;
const data = {
app_name: APP_NAME,
public_ip: true,
app_port: PORT,
image_tag: 'latest',
};
await prisma.ecs_simplified.upsert({
where: { app_name: APP_NAME },
create: data,
update: data,
});
const commit = await prisma.$queryRaw`SELECT *
from iasql_commit();`;
console.dir(commit);
console.log('Using ecr_build to build the docker image and push it to ECR...');
const repoId = (await prisma.repository.findFirst({
where: { repository_name: `${APP_NAME}-repository` },
select: { id: true },
})).id.toString();
let repoUri;
if (REPO_URI) // manual
repoUri = REPO_URI;
else if (GITHUB_SERVER_URL && GITHUB_REPOSITORY) // CI
repoUri = `${GITHUB_SERVER_URL}/${GITHUB_REPOSITORY}`;
else
repoUri = 'https://github.com/alantech/iasql'
const image = await prisma.$queryRaw`SELECT ecr_build(
${repoUri},
${repoId},
'./examples/ecs-fargate/prisma/app',
${GITHUB_REF},
${GH_PAT}
);`;
console.log(image);
}
It'll use the ecs_simplified module to create all the necessary AWS resources needed for you app to run (load balancer, ECR repository, IAM role, etc). 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
create | role | | ecsTaskExecRole
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:
SELECT ecr_build(
'https://github.com/alantech/iasql/', -- replace with your own Github repo if you want to use your own codebase
(SELECT id
FROM repository
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
);
That command is already being run in the infra/index.js script. So no need for extra steps if you're using it.
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 "
SELECT dns_name
FROM load_balancer
WHERE load_balancer_name = '<project-name>-load-balancer';")
And then connect to your service!
curl ${QUICKSTART_LB_DNS}:8088/health
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';