This is the first in a series of blog posts on deploying different services to AWS using IaSQL. IaSQL covers a wide range of AWS services, so there are lots of different services with different architectures to explore. Deploying containers to ECS, launching servers using EC2, and use AWS Codepipeline to automate the building and deploying your applications are just a few possibilities.

In this post we'll explore how to deploy IaSQL on EC2, using IaSQL!

Start IaSQL​

To manage the cloud resources for your deployment of IaSQL, we'll need to use a separate instance of IaSQL. You may use our SaaS offering at app.iasql.com, or locally via Docker:

docker run -p 9876:9876 -p 5432:5432 --name iasql iasql/iasql

This will download the latest IaSQL image from dockerhub and run it. After it's ready, you can access the dashboard at the following URL:

[http://localhost:9876/](http://localhost:9876)

Now you can connect your AWS account in the dashboard. This will create a new database that can be connected using any Postgres client you prefer, or you may run queries directly through the dashboard.

Deploy IaSQL on EC2 with IaSQL​

Overview​

To deploy IaSQL on EC2, we need to:

• Launch an EC2 instance that will host IaSQL
• Set the instance user data so that Docker will be installed upon instance launch, and the IaSQL container will be started
• Import an SSH key pair into AWS to be able to access the EC2 instance afterwards
• Set a security group for the instance to allow SSH, while disallowing unauthorized access to the dashboard

Install IaSQL Modules​

Let's start by installing the needed IaSQL modules. IaSQL has a modular design, and therefore to manage EC2 instances, we need to use aws_ec2 module. Also, aws_ec2_metadata allows us to get more info on our EC2 instance, like its public IP.

SELECT iasql_install('aws_ec2', 'aws_ec2_metadata');

Import SSH Key Pair Into AWS​

Having SSH access to the instance is important, since if you don't have access to the instance you won't be able to upgrade the running IaSQL container. To enable SSH access, you can import your public key into AWS by executing the following IaSQL RPC:

SELECT * FROM key_pair_import('iasql', '<your-public-key-content>', default_aws_region());

This will direct IaSQL to create an AWS key pair with the name iasql in the us-east-1 region, and with your public key contents.

Create And Configure Security Group​

Now it's time to launch our EC2 instance. Before we go for the instance itself, let's first define the security group that's going to be attached to it. For this purpose, we'll need aws_security_group module. But since aws_ec2 module depends on it, it's already installed because IaSQL automatically installs the dependencies for modules upon an iasql_install call.

WITH sg as (INSERT INTO security_group (description, group_name)
            VALUES ('IaSQL security group', 'iasql_sg')
            RETURNING id)
INSERT
INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, description, security_group_id)
VALUES (FALSE, 'tcp', 22, 22, '0.0.0.0/0', 'ssh port is accessible from all ips', (SELECT id FROM sg)),
       (FALSE, 'tcp', 9879, 9879, '<your-ip>/0', 'open dashboard to just me', (SELECT id FROM sg)),
       (FALSE, 'tcp', 5432, 5432, '0.0.0.0/0', 'postgres port is accessible from all ips', (SELECT id FROM sg)),
       (TRUE, 'tcp', 1, 65535, '0.0.0.0/0', 'allow outgoing traffic to anywhere', (SELECT id FROM sg));

This query uses a WITH Postgres query to insert a security group, and then select the id of the resulting security group to be re-used in the second INSERT query. The WITH query can be thought of as a temporary table that exists just for one query. By use of WITH we can simplify our query and do the insert, and preloading of the resulting id in a single query. We could've written the query using subqueries as follows, but that way we should've repeated the SELECT id FROM security_group WHERE ... part for each set of values (and of course, doing the insert in a separate query):

INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, description, security_group_id)
VALUES (FALSE, 'tcp', 22, 22, '0.0.0.0/0', 'ssh port is accessible from all ips',
        (SELECT id FROM security_group WHERE group_name = 'iasql_sg')),
       (FALSE, 'tcp', 9879, 9879, '<your-ip>/0', 'open dashboard to just me',
        (SELECT id FROM security_group WHERE group_name = 'iasql_sg')),
       (FALSE, 'tcp', 5432, 5432, '0.0.0.0/0', 'postgres port is accessible from all ips',
        (SELECT id FROM security_group WHERE group_name = 'iasql_sg')),
       (TRUE, 'tcp', 1, 65535, '0.0.0.0/0', 'allow outgoing traffic to anywhere',
        (SELECT id FROM security_group WHERE group_name = 'iasql_sg'));

The security_group_id column has an FK set to the security_group table's id column. Therefore, we need to insert the correct id for the security group we just created.

Launch EC2 Instance​

It's now time to actually create the EC2 instance. We'll issue a query as follows to do so:

INSERT INTO instance (ami, instance_type, subnet_id, tags, user_data, key_pair_name)
VALUES (...);

Before we write the query, let's investigate each column in the instance table. As you've already noticed, we create AWS resources by INSERTing rows into corresponding tables. For example, we created a security group by executing the INESRT INTO security_group query. With that in mind, let's look into the columns of the instance table (which is in charge of managing EC2 servers) that will be used in our query:

• ami: this column is the Amazon Machine Image ID that is used to launch the EC2 server. We can either directly insert the AMI ID (like ami-0f1a5f5ada0e7da53), or use a parameter reference to the AWS Systems Manager Parameter Store (like resolve:ssm:/aws/service/canonical/ubuntu/server/22.04/stable/current/amd64/hvm/ebs-gp2/ami-id). We'll go with the parameter reference choice.

resolve:<service-name>:<reference-key>

• instance_type: this column contains the instance type of the EC2 server. We'll use a t2.small instance for our deployment.
• subnet_id: this is a foreign key to subnet table based on its id column. Good thing about IaSQL is that it'll automatically import every subnet in your cloud environment when you install the iasql_vpc module. This is not just for the subnets, but all the resources will be imported when installing the module that manages them. You can also manage the infrastructure outside IaSQL afterwards, having IaSQL also import your subsequent changes to the database. We'll use a SELECT query from the subnet table to fill this column.
• tags: this column is a JSON column. It'll contain all the tags for this instance. We'll use {"name":"iasql"} as the value for this column since AWS treats the instance name as a tag.
• user_data: when you launch an instance in Amazon EC2, you have the option of passing user data to the instance that can be used to perform common automated configuration tasks and even run scripts after the instance starts. You can pass two types of user data to Amazon EC2: shell scripts and cloud-init directives [1]. We'll write the deployment scripts in bash, but it's also possible to pass cloud-init directives.
• key_pair_name: AWS accepts a key pair for the instance. It can be used to log into the instance through SSH. We'll use the iasql key pair we initially created using key_pair_import RPC.
• region: this column is omitted and therefore will be replaced with the result of default_aws_region() Postgres function. we can choose the AWS region in which the instance will be launched. IaSQL is already multi-region, which means it'll manage your resources in all the regions. So this additional region column can be used to determine which region the instance is in, and while INSERTing used to direct IaSQL to create the instance in that region.

Bash Startup Script​

Alright, we can now write our query to deploy IaSQL on EC2. Let's write the query with EC2 startup script being a shell script:

INSERT INTO instance (ami, instance_type, subnet_id, tags, user_data, key_pair_name)
SELECT 'resolve:ssm:/aws/service/canonical/ubuntu/server/22.04/stable/current/amd64/hvm/ebs-gp2/ami-id',
       't2.small',
       subnet.id,
       '{"name":"iasql"}',
       '#!/bin/bash
     sudo apt-get upgrade -y
     sudo apt-get install -y docker.io
     docker run -p 9876:9876 -p 5432:5432 --name iasql iasql/iasql',
       'iasql'
FROM subnet
         INNER JOIN vpc ON vpc.id = subnet.vpc_id
    AND vpc.is_default
WHERE vpc.region = default_aws_region()
LIMIT 1;

There's just one last step needed. Remember the security group we created earlier? We never assigned our instance to it (we couldn't because it didn't exist, yet). So let's do it and let IaSQL do the rest:

INSERT INTO instance_security_groups (instance_id, security_group_id)
SELECT instance.id, security_group.id
FROM instance, security_group
WHERE instance.tags ->> 'name' = 'iasql'
      AND security_group.group_name = 'iasql_sg'
LIMIT 1;

The instance_security_groups table is a Many-to-Many join table that connects instances to their security_groups. Now we need to wait for IaSQL to start syncing the state of the cloud with our local changes. This will normally happen every 2 minutes. There is a way to make the cloud apply/sync happen immediately using IaSQL transactions, but for the purpose of this post we'll wait for the next cronjob tick to apply our changes.

After waiting long enough for the server to start and for the startup script to finish, we can use instance's public IP to access our self-hosted IaSQL dashboard:

SELECT public_ip_address
FROM instance_metadata
WHERE id = (SELECT id
            FROM instance
            WHERE tags ->> 'name' = 'iasql'
            LIMIT 1);

Now we can go to http://<instance-ip>:9876/ URL, and we'll see the beautiful IaSQL dashboard deployed on our EC2 server.

Are you using S3 buckets as part of your cloud deployments? How are you accessing them?

When running applications behind VPCs without public access, there may be the need to access S3 buckets from the private subnet over the public internet. One simple but costly way to do so is to rely on NAT gateways.

However, creating gateway or interface VPC endpoints for each region where your buckets are exposed is a more optimal solution.

When the VPC endpoints are enabled you can access your S3 buckets using this endpoint. In this post, we will walk you through how to control your buckets from an internal network with the desired security, and without the extra costs of a NAT gateway using a VPC endpoint and IaSQL. 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.

Do you have some database instances on RDS and wonder what's the most secure way to reach them? In this post, we will walk you through how to securely connect to an AWS RDS instance using Private Link and IaSQL. 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.

Did you know you can deploy a static website using a SQL REPL? In this post, we'll show you how to use IaSQL to deploy a static website from your GitHub repository to AWS S3 + Cloudfront services using only SQL queries. 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.

We will create and configure an S3 bucket to serve our static website. To enable support for HTTPS, we'll also add a CloudFront distribution. We will also leverage CodeBuild to automatically build the files for our project and copy them to the S3 bucket created already.

IaSQL lets developers manage their cloud infrastructure as data in PostgreSQL as an alternative to the AWS console and infrastructure as code (IaC) tools like Pulumi and Terraform. We open-sourced IaSQL's Alpha version (v0.0.x) in April 2022. We still have a long way to go, but we feel ready for what is next. Today, we’re moving IaSQL to Beta (v0.x)!

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. In this post, we are going to learn how untagged ECR images can rack up your AWS bill unnecessarily and how to get rid of unused repository images with a single query in IaSQL: DELETE FROM repository_images WHERE tag = '<untagged>';

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. In this post, we're going to discover an IaSQL module that's built to make deploying to ECS, simplified. Most of the details for deploying a container to AWS ECS are the same (load balancers, security groups, IAM roles, etc), and we have created the aws_ecs_simplified module for you so that you can give it any Github repo with a Dockerfile and get your app deployed to ECS in the fastest time possible, with scalability available! All the needed resources are going to be created automatically in your AWS account, and you'll have full access to the details while you're gaining the benefit of a higher-level simple deployment.

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

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.

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.