Deploy a Static Website on AWS using SQL
Tags: webdev, beginners, tutorial, aws
Published: 2023-03-06 20:37:34


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](https://github.com/iasql/iasql) 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 a S3 Bucket

To be able to work with S3, we should first install the corresponding IaSQL module.

```sql
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.

```sql
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](/docs/transaction/).

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:

```sql
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](https://github.com/iasql/iasql/blob/c70f068c7520baf00cea9ddd3a76b8c6dbd2b23b/src/modules/aws_s3/rpcs/s3_upload_object.ts#L27-L33) as well as all other IaSQL modules and RPCs in our GitHub [repository](https://github.com/iasql/iasql/) 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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6gs1uxohdyj4uoinmfdl.png)

## 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.

<!-- TODO replace with aws_s3 doc url -->

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](https://iasql.com/docs/reference/sql/#aws_s3) to get a list of tables and RPCs available for `aws_s3` module.

```sql
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`](https://www.postgresql.org/docs/current/sql-insert.html#SQL-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
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wbuga1wuf2gwak0ep8b6.png)

## 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/WebsiteHosting.html) for our bucket to be able to deploy a React codebase. So let's enable it.

```sql
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.

```sql
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 the 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:

```sql
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.

```sql
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.

```sql
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](https://aws.amazon.com/s3/pricing/) aren't cheap and can grow out of control. Also, the speed in which the users can access to 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.

```sql
SELECT
  iasql_install ('aws_cloudfront');
```

Then create the distribution:

```sql
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:

```sql
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.