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.
Create an S3 Bucket
To be able to work with S3, we should first install the corresponding IaSQL module.
SELECT
iasql_install ('aws_s3');
Installing a module gives us the ability to use tables and RPCs provided by it. aws_s3 module gives us the ability to manage an S3 bucket, S3 static website hosting, and other related stuff. So let's create an S3 bucket first.
SELECT
iasql_begin ();
INSERT INTO
bucket (NAME, policy_document, region)
VALUES
(
'<bucket-name>',
'{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "PublicReadGetObject",
"Effect": "Allow",
"Principal": "*",
"Action": [
"s3:GetObject"
],
"Resource": [
"arn:aws:s3:::<bucket-name>/*"
]
}
]
}',
'us-east-1'
);
SELECT
iasql_commit ();
The above query will create a new bucket in the us-east-1 region with the defined name <bucket-name> and the given policy using IaSQL. The iasql_begin and iasql_commit functions are RPCs that will start and finish an IaSQL transaction. Learn more about IaSQL transactions in this part of our documentation.
Now that we have a bucket, we can upload a file to it and see if we're able to view it using our web browser. Let's use IaSQL to upload a file to our newly created bucket:
SELECT
*
FROM
s3_upload_object ('<bucket-name>', 'hello.txt', 'Hello IaSQL!', 'text/plain');
This is going to upload a file named hello.txt in our bucket whose content is Hello IaSQL!.
You can read the code for s3_upload_object RPC as well as all other IaSQL modules and RPCs in our GitHub repository to see how they work.
Let's see if we can access our file using the S3 bucket URL. It should be as follows:
https://<bucket-name>.s3.amazonaws.com/hello.txt
But we're unable to access the file directly because S3 blocks public access by default.
Make The Bucket Public
We need to enable public access to our bucket files to be able to directly access the files. We can use the public_access_block table provided by aws_s3 module to allow for public requests to reach our objects.
If you want to know which resources (via tables) an IaSQL module handles, you can visit our documentation page. It also provides a list and explanation of all the RPCs that are provided by a module. In our case, we can visit this link to get a list of tables and RPCs available for aws_s3 module.
SELECT
iasql_begin ();
INSERT INTO
public_access_block (bucket_name, block_public_acls, ignore_public_acls, block_public_policy, restrict_public_buckets)
VALUES
('<bucket-name>', FALSE, FALSE, FALSE, FALSE) ON CONFLICT (bucket_name)
DO
UPDATE
SET
block_public_acls = excluded.block_public_acls,
ignore_public_acls = excluded.ignore_public_acls,
block_public_policy = excluded.block_public_policy,
restrict_public_buckets = excluded.restrict_public_buckets;
SELECT
iasql_commit ();
There's a 1-1 relationship between the bucket and bucket public access block, therefore we're using Postgres ON CONFLICT syntax so that when there's a record already, we can replace it without hassle.
Now we should be able to directly access our file through the web browser.
https://<bucket-name>.s3.amazonaws.com/hello.txt
Use S3 Static Website Hosting
But simply serving the files doesn't mean we can host a static website. We need to enable static website hosting for our bucket to be able to deploy a React codebase. So let's enable it.
SELECT
iasql_begin ();
INSERT INTO
bucket_website (bucket_name, index_document)
VALUES
('<bucket-name>', 'index.html');
SELECT
iasql_commit ();
We'll use this functionality to route all the requests to index.html file. This way we can deploy a sample React application and serve it through S3. To get the link for our S3 bucket's static website, we can use get_bucket_website_endpoint function.
SELECT
*
FROM
get_bucket_website_endpoint ('<bucket-name>');
Build The Project And Sync To S3
Now that everything is set, we just need to build our React app and deploy it to S3. We have already pushed a sample app to this repository:
https://github.com/iasql/sample-react-app
But you can use whatever codebase you'd like by changing the URLs so that they point to the Github repository hosting your React app.
Now it's time to create a CodeBuild project. CodeBuild is an AWS CI/CD system that is free of cost. The CodeBuild project will do the following:
- Pull the codebase from the GitHub repository
- Build the app
- Copy the resulting files (
build/*) to the S3 bucket
We can do this with the following SQL query:
SELECT
iasql_begin ();
-- create the needed role for codebuild
INSERT INTO
iam_role (role_name, assume_role_policy_document, attached_policies_arns)
VALUES
(
'deploy-static-website-role',
'{
"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/AmazonS3FullAccess'
]
);
-- create the codebuild project
INSERT INTO
codebuild_project (project_name, build_spec, source_type, privileged_mode, service_role_name, region)
VALUES
(
'deploy-static-website',
'version: 0.2
phases:
pre_build:
commands:
- git clone https://github.com/iasql/sample-react-app && cd sample-react-app
build:
commands:
- echo Installing dependencies
- npm install
- echo Building the app
- npm run build
post_build:
commands:
- echo Copying the files to the S3 bucket
- aws s3 sync build/ s3://<bucket-name>',
'NO_SOURCE',
FALSE,
'deploy-static-website-role',
'us-east-1'
);
SELECT
iasql_commit ();
The above SQL command first creates a role that is needed for CodeBuild to operate. Then it'll create the actual CodeBuild project that clones the repo, builds it and finally syncs the resulting files to our S3 bucket. We need to trigger the CodeBuild project to run and then our files will be uploaded to our bucket.
SELECT
*
FROM
start_build ('deploy-static-website', 'us-east-1');
This will trigger the CodeBuild project to start. After a while, we should be able to see the files appearing in our S3 bucket. We can access our React app using the endpoint for the S3 static website hosting. As we already mentioned, to get the endpoint we can use get_bucket_website_endpoint helper function.
SELECT
get_bucket_website_endpoint ('<bucket-name>');
By visiting the link returned by the above function, you can see our sample app has been deployed. The problem is that S3 static website hosting does not support HTTPS, and therefore we need to use a CloudFront distribution in order to have HTTPS connection.
Create a CloudFront Distribution
Serving files in a bucket to the public using pure S3 isn't a good idea. In our case because the S3 static website hosting does not provide an HTTPS endpoint, but in most cases because the data transfer rates for S3 aren't cheap and can grow out of control. Also, the speed at which the users can access the bucket objects will increase if you use a CDN because they'll be delivered to the users from the nearest edge server.
With the above in mind, let's create a CloudFront distribution for our S3 bucket. But first, we need to install the aws_cloudfront module to be able to leverage its abilities.
SELECT
iasql_install ('aws_cloudfront');
Then create the distribution:
SELECT
iasql_begin ();
INSERT INTO
distribution (caller_reference, origins, default_cache_behavior)
VALUES
(
'my-website',
(
'[
{
"DomainName": "' || (
SELECT
get_bucket_website_endpoint ('<bucket-name>')
) || '",
"Id": "my-website-origin",
"CustomOriginConfig": {
"HTTPPort": 80,
"HTTPSPort": 443,
"OriginProtocolPolicy": "http-only"
}
}
]'
)::json,
'{
"TargetOriginId": "my-website-origin",
"ViewerProtocolPolicy": "allow-all",
"CachePolicyId": "658327ea-f89d-4fab-a63d-7e88639e58f6"
}'
);
SELECT
iasql_commit ();
We can access our website through the CloudFront distribution domain name. To get it, we can simply run the following query:
SELECT
domain_name
FROM
distribution
WHERE
caller_reference = 'my-website';
It supports HTTPS, it's fast, it's cheaper than directly serving on S3, and it can be easily connected to a Route53 domain.