DEV Community: Luis Fernando De Pombo

Quickly compare cost and results of different LLMs on the same prompt

Luis Fernando De Pombo — Mon, 03 Mar 2025 18:29:09 +0000

I often want a quick comparison of different LLMs to see the result+price+performance across different tasks or prompts.

So I put together LLMcomp—a straightforward site to compare (some) popular LLMs on cost, latency, and other details in one place. It’s still a work in progress, so any suggestions or ideas are welcome. I can add more LLMs if there is interest. It currently has Claude Sonnet, Deep Seek and 4o which are the ones I compare and contrast the most.

I built it using a port of AgentOps' tokencost for the web to estimate LLM usage costs on the web and the code for the website is open source and roughly 400 LOC

3 common mistakes when integrating the OpenAI API with your web or mobile app

Luis Fernando De Pombo — Fri, 14 Feb 2025 19:02:14 +0000

A key concern when building a web or mobile application that uses OpenAI’s language models is protecting your private API key. If you want to keep complexity low and release features quickly, setting up a fully fledged backend just to safeguard an API key can be time consuming. In this article, you’ll learn how to avoid the most common security mistakes when integrating the OpenAI API into a client-side app—without sacrificing simplicity or safety. Overlooking these pitfalls can leave the door open to OpenAI API misuse, which can:

Lead to unexpected or excessive charges on your OpenAI account
Violate OpenAI’s Acceptable Use Policy and Terms of Service
Potentially expose your project to spam or abusive behavior

Mistake # 1: Storing the OpenAI Key in Frontend Code

Putting your API key directly in client-side code (using any type of web or mobile framework) as shown in the sample frontend code below is tempting, but also very risky.

import OpenAI from "openai";

const client = new OpenAI({
  dangerouslyAllowBrowser: true,
  apiKey: 'sk-proj-YOURSECRETKEY',
});

const completion = await OpenAI.instance.chat(...)

Anyone can inspect your code (even if it is minified or obfuscated somehow), extract your key, and abuse it. Don't let this happen to you. The OpenAI SDK requires the dangerouslyAllowBrowser flag to try to prevent this problem and make sure you add a layer of protection that keeps the secret key away from the end user’s browser or device.

Mistake # 2: Using an Unauthenticated Proxy

Putting your OpenAI API key as a secret in a hosted server or cloud function and calling that endpoint from your client is much better than exposing it directly, but it is still unsafe. Let's take the following client-side application code:


const opair = await fetch("https://functionroute.yourcloud.com/v1/chat/completions", {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({...})
});

Alongside the cloud function OpenAI API proxy it is calling:

import route from "yourcloud";

route.get('/', req, res => {
  const opair = await fetch(`https://api.openai.com/${req.path}`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Authentication': `Bearer ${env.OPENAI_SECRET_KEY}`

    },
    body: req.body.clone(),
  });
  return res.send(opair);
})

Since this proxy is publicly accessible, an attacker could bypass your app and access your OpenAI API even without your private key at https://functionroute.yourcloud.com/.

Mistake # 3: Using an Authenticated Proxy without Permissions

Putting your OpenAI API key as a secret in a hosted server or cloud function and adding authentication so only your users can call the proxy endpoint is quite common and still much better than the setups laid out thus far. However, it is still unsafe. Let's take the following frontend code:

import supabase from "supabase-js";

const jwt = supabase.auth.session().access_token;

const opair = await fetch("https://functionroute.yourcloud.com/v1/chat/completion", {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'Authentication': `Bearer ${jwt}`
  },
  body: JSON.stringify({...}),
});

Alongside the cloud function OpenAI API authenticated proxy it is calling:

import route from "yourcloud";
import { createClient } from '@supabase/supabase-js'

const supabase = createSupabaseClient(...);

route.get('/', req, res => {
    const authHeader = req.headers['authorization'];
  try {
    // 1. Get the token from the Authorization header
    const authHeader = req.headers['authorization'] || ''
    const jwt = authHeader.split(' ')[1] // Bearer <token>

    if (!jwt) {
      return res.status(401).json({ error: 'No token provided' })
    }

    // 2. Verify the token using the JWKS
        const { data: { user }, error } = await supabase.auth.getUser(jwt);
        if (error || !user) {
            return createResponse('Unauthorized', { status: 401 });
        }
    // 3. The token is valid
    const opair = await fetch(`https://api.openai.com/${req.path}`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'Authentication': `Bearer ${env.OPENAI_SECRET_KEY}`

      },
      body: req.body.clone(),
    });
    return res.send(opair);

  } catch (error) {
    console.error(error)
    return res.status(401).json({ error: 'Invalid token' })
  }
})

This backend integration for the OpenAI API looks fine at first glance and provides some safety. However, there are three problems with it:

There are no limits to how many times a given user can call your OpenAI API proxy. Any user can call this API proxy up to the default account rate limits for the Open AI API associated with your private API key by simply getting their JWT and calling the proxy at https://functionroute.yourcloud.com/v1/. The JWTs, or authentication tokens, typically expire within an hour. That means that someone can generate a token from your client-side code and (mis)use it until it expires. Then rinse and repeat.
OpenAI, and other LLM APIs, have endpoints that allow uploading files for augmented analysis and creating threads with chat history for autonomous assistants. This backend does not provide any permissions or access control over which users uploaded or created which private resources in the OpenAI API. This means that any user with just their JWT can exploit this security vulnerability and see all the files and conversations ever created with your OpenAI API key. This will cause infringements on the data and privacy of your end users. The following frontend code will call the OpenAI API list files end point which will return all the files ever uploaded regardless of the user that uploaded it:

import supabase from "supabase-js";

const jwt = supabase.auth.session().access_token;

const opair = await fetch("https://functionroute.yourcloud.com/v1/files", {
  method: 'GET',
  headers: {
    'Content-Type': 'application/json',
    'Authentication': `Bearer ${jwt}`
  },
  body: JSON.stringify({...}),
});

Building a safe integration

To prevent the vulnerabilities laid out thus far, you should implement the following safeguards in your backend:

Implement Authentication: Require valid credentials (e.g., token-based auth) so you know exactly who’s accessing your services.
Track Usage and Limits per User: Prevent users from racking up high API costs by restricting the number of requests per minute per user.
Enforce Secure Permissions: Ensure only the user who created a resource can view or modify it to protect against unauthorized access or data leaks.

Building a simple integration

Implementing proper integration with the OpenAI API in a backend can be time-consuming. A Backend-as-a-Service (BaaS) like Backmesh abstracts this complexity handling authentication, rate limits and permission enforcement for you—without requiring you to build or maintain a backend. The idea is straightforward:

Store your private OpenAI API key securely in a protected configuration that users cannot see.
1. Process requests with permissions, rate limiting and authentication
2. Your front end sends a request to the Backmesh endpoint on behalf of your user.
3. The endpoint verifies this request belongs to one of your users, the user hasn't gone past their limits (e.g. 5 requests per min) and the user is not requesting resources it did not previously create. Only if all of this is true, Backmesh attaches your secret API key from the secure store to the request.
4. The request goes to OpenAI, then Backmesh returns the OpenAI API response to your front end with streaming support.

The resulting client-side code is described below and is taken from one of our tutorials:

import OpenAI from "openai";
import supabase from "supabase-js";

const BACKMESH_PROXY_URL =
  "https://edge.backmesh.com/v1/proxy/gbBbHCDBxqb8zwMk6dCio63jhOP2/wjlwRswvSXp4FBXwYLZ1/v1";

const jwt = supabase.auth.session().access_token;

const client = new OpenAI({
  httpAgent: new HttpsProxyAgent(BACKMESH_PROXY_URL),
  dangerouslyAllowBrowser: true,
  apiKey: jwt,
});

From your perspective, there’s “no backend” to manage—no servers, no DevOps, no complicated configurations. But under the hood, Backmesh is a secure middleman between your front end and OpenAI API. Read more about Backmesh's security considerations here or check out the open source code on Github.

Built a ChatGPT-like app on Firestore with no backend/functions at ~1k LOC

Luis Fernando De Pombo — Thu, 30 Jan 2025 19:09:22 +0000

Source Code: https://github.com/backmesh/flutter-chatgpt

Live Demo: https://flutter-chatgpt.pages.dev

npm i anthropic-react-native

Luis Fernando De Pombo — Tue, 10 Sep 2024 15:42:29 +0000

I recently published a new package on npm that brings the Anthropic APIs to React Native without polyfills.

https://github.com/backmesh/anthropic-react-native

Today the library supports chat streaming, normal chat completions and file upload with more endpoints coming soon. The goal of the library is to follow the Node SDK wherever possible while taking advantage of React Native SSE for streaming where the Anthropic Node SDK does not work. Lmk what you think or if this will be useful to you!

npm i openai-react-native

Luis Fernando De Pombo — Mon, 26 Aug 2024 14:48:04 +0000

We just published this package. It is a React Native OpenAI API client without polyfills since the official OpenAI Node SDK does not support React Native. See this issue.

Today the library supports chat streaming, normal chat completions and expo file upload with more endpoints coming soon. The goal of the library is to follow the OpenAI Node SDK wherever possible without using any polyfills by taking advantage of React Native SSE and Expo FileSystem implementations to support calling the OpenAI API through a proxy from React Native with streaming and file upload support. Lmk what you think or what you would like to see!

https://github.com/backmesh/openai-react-native

Securely call private key APIs like openAI from flutter without a backend

Luis Fernando De Pombo — Wed, 14 Aug 2024 19:22:23 +0000

As of August 2024, none of the major Backend-as-a-service platforms for developing Flutter apps like Supabase, Cloudflare, Vercel, or Firebase support Dart cloud functions. Additionally, many Flutter packages can't be utilized in Dart backends due to the absence of UI dependencies. As a result, developers using Flutter often have to rewrite model and controller logic to handle database operations a second time in Dart, Javascript or Python. This leads to duplicated effort and a slower development process which is the entire purpose of these services.

Introducing Backmesh

I got tired of having to write a cloud function in Javascript or Python for every Flutter project that called some private key API so I built Backmesh. Backmesh is a minimal SaaS for Flutter developers to safely call private key APIs, such as OpenAI in the example below, without writing any backend code in a server or cloud function.

// Auth Provider: Firebase
// App Type: Flutter Dart
// Private Key API: OpenAI

import 'package:firebase_auth/firebase_auth.dart';
import 'package:dart_openai/dart_openai.dart';


OpenAI.baseUrl =
  "https://edge.backmesh.com/proxyname"; // "https://api.openai.com" is the default one.
// set api secret key to jwt
OpenAI.apiKey = await FirebaseAuth.instance.currentUser.getIdToken();
await OpenAI.instance.chat(...)

There are many legitimate use cases for backends, but the primary use case for many Flutter apps that require a backend is just being able to securely call an external, private key API. Backmesh allows Flutter apps, or any client app written in any language, to do this without a backend by storing the private API key and providing a JWT protected proxy with user-scoped access using the app's authentication provider.

Security Considerations

Let's quickly go over authorization and the two different types of authentication:

Authentication verifies someone is who they say they are.

User authentication means we can identify which user is making a request and whether the user in question is valid.
Client app authentication means we can verify that the request is originating from a valid and untampered app client.

Authorization verifies that someone has access to something specific and grants access if applicable. Authorization requires authentication to happen first.

For example, Firebase provides user authentication, but only properly configured Firestore security rules provide authorization to your database. Furthermore, only adding Firebase AppCheck can provide client app authentication. More about that here.

In the case of a public API proxy, Backmesh performs user authentication to ensure that requests to the proxy securing your private key API come from one of your users. This does not provide authorization about which specific users are allowed to make which requests to the proxy, or how many requests a specific user can make. However, with Backmesh you can set rate limits across all your users to reasonably protect your API resources e.g. no user should be calling a given API more than X times per hour. It is possible to integrate with Stripe to be able to set authorization access rules for users based on their payment plan. If you are interested in this email hello at backmesh dot com.

cloud infrastructure that proposes verifiable bugfixes

Luis Fernando De Pombo — Tue, 05 Mar 2024 19:24:59 +0000

LLMs are thus far the fastest adopted technology in history after OpenAI reported that ChatGPT achieved 100 million users within 2 months of its release. Chief among the uses cases for LLMs is code generation. Today, we mostly generate code with LLMs using chat-like interfaces within a terminal, website, or code editor. However as impressive as LLMs are at coding today, any developer that has used LLMs for code generation knows that even with the right context the vast majority of the time LLMs don't generate code that works as-is. Instead the generated code either needs to be throughly massaged, or it serves as inspiration for the code we ultimately end up writing. In some cases, the LLM-generated code seems to come close to the desired solution and we verify it by perusing it and/or manually running it within our codebases. However, this rarely works. One Princeton study that tasked two different LLMs to solve 2,294 real GitHub issues found that Claude 2 and GPT-4 solved only 4.8% and 1.7% of the issues respectively.

Let's look at the most common types of LLM code generation and for debugging let's explore a workflow to automatically verify LLM-generated bugfixes outside of a chat/visual interface where we can't see the many mistakes made by LLMs. These types of workflows to programmatically verify LLM-generated code might not be the norm today, but they will become more attractive as the price of invoking LLMs goes down and the generative coding abilities of LLMs improve.

The different types of LLM code generation

Let's take a closer look at the different scenarios in which developers use LLMs for code generation:

1) "I know how to write this code but it's repetitive or cumbersome to write"

Code editor AI copilots shine here. This is one of the most common scenarios and we can easily verify the accuracy of the code as there are often many code samples that can be given to the LLM. Some examples include implementing a function with a well defined purpose, calling an API and then performing some sort of data transformation on the response, or generating self-contained components in React or some other reactive frontend framework.

2) "I don't know how to write this code. Help"

This use case is tricky because the LLM can often lead us astray unless we reason from first principles and try to understand the underlying technology, API and/or framework. However, we often don't have many other alternatives as searching Google and Stackoverflow probably didn't help much either. This often ends up being a conversation with a lot of back and forth where code-generated is interleaved with explanations and ideas for how approach the problem in ways that may or may not work.

3) "Write tests for this code"

This one is straightforward for the LLM to understand, but there is still no easy way to know if the tests generated do a sufficiently good enough job at testing the provided code even if the tests pass. However, it is up to the developer to be exhaustive enough in describing the many different ways in which the provided code should be tested and what type of input should be provided for unit tests. I worked on a syntax that combined English and functional notation into a constrained format that was compiled by LLMs into tested Python programs. We tried to make sure the LLM had enough information to generate valid tests by failing to compile the syntax if examples were not provided by the user, but there was still no way of verifying the LLM-generated unit tests themselves.

4) "Make this error go away"

This one is straightforward for the LLM to understand. The developer often just provides the stacktrace and can sometimes add more context by including the code referenced in the stacktrace. LLMs tend to do better when the error generated belongs to a commonly used programming language and/or framework as training datasets from Reddit and Stackoverflow are likely richer for these cases. The LLM can often output instructions or sample code that the developer can implement to quickly test if the bugfix works. Like the 2nd use case on this list, this use case can also end up being a conversation with a lot of back and forth where code generated by the LLM is interleaved with suggestions on ways to resolve the error that may or may not work.

I'm particularly interested in automating the verification of the LLM code generated for the last use case because the functionality of LLM-generated code when debugging is binary and always verifiable- did the error go away and did any new errors arise?

Automatically verifying LLM-generated bugfixes

Automatically debugging exceptions within a containerized production application is possible with an opinionated cloud infrastructure and error monitoring setup that collects the necessary data to verify bugfixes and behind the scenes tasks an LLM to generate a bugfix for each of the recorded production errors. A pull request to the application's codebase is submitted if and only if a bugfix verifiably fixes a given error. Pull requests ideally run with unit tests that ensure there are no breaking changes or regressions. Finally, one of the maintainers can take a closer look at the LLM's proposal for a verified bugfix to a particular error and decide to discard it, further edit it or merge it.

In order for the cloud infrastructure hosting the production application in question to verify the LLM-generated bugfixes, the platform needs to be able to parse and collect incoming requests and outgoing network traffic using eBPF (more on that later). The infrastructure offering can then create branches out of the main git branch of the application's codebase with potential bugfixes and deploy them to sandboxed development environments behind the scenes. Finally, the cloud offering replays the incoming requests for the sandboxed environments while simultaneously mocking the outgoing network traffic using previously recorded values from the time the error took place. If no error is thrown, the cloud offering opens a pull request for the maintainers to review. This is a rough sketch of what I am describing:

A cloud infrastructure platform that is able to generate and verify LLM bugfixes while also providing the best UX requires two important features:

1) git-based deployment process for containerized apps

A git-centered deployment experience allows us to deploy by pushing a git branch to a remote repository without having to configure a CI/CD system. This workflow has been around since 2009 thanks to Heroku, the grandfather of all PaaS. This DevOps abstraction tucks away a fair amount of complexity by making some assumptions about our app. For most cases, it really should be as simple as some form of pushing to a branch with a Dockerfile or docker-compose.yml in it (or nothing in particular if using Nixpacks) without having to deal with the fairly standard DevOps process of:

building the docker image from a Dockerfile
pushing the image to a repository
updating the process in a VM or K8 pod with the new code

I do wish more container cloud offerings today where as easy to use as the git-based, webapp offerings of Netlify or Vercel. Fly.io is one good example of a provider with a seamless, git-based deployment process for containerized apps. Cloud infrastructure that abstracts away the repetitive parts of the deployment process not only has superior UX, but it can easily provide features that can save us a lot of development time like preview deployments. Most importantly, a git-based deployment process also makes it possible to programmatically replicate deployments behind the scenes. This is imperative to be able to propose verifiable bugfixes as we can test if an LLM can generate code that fixes a given bug, and do that repeatedly for all bugs, without an overly complicated CI/CD workflow, or some sort of spaghetti of yaml.

2) replay of incoming traffic and mocking of outgoing traffic via eBPF-based, SDK-less error monitoring

Error monitoring tools are used primordialy to triage applicaiton errors. However, some offerings can also collect networking traffic to later replicate production conditions in sandboxed staging environments to improve reliability of upcoming software releases. Most error monitoring tools offer SDKs to explicitly instrument errors and networking calls within an app which can be tedious. However, some providers like DataDog have SDK-less error monitoring offerings built on eBPF.

eBPF is a Linux technology that can run sandboxed programs in the kernel without changing kernel source. This technology opens the door for powerful error monitoring tools by hooking directly into kernel OS calls without any additional syntax required within a containerized codebase. However, fully SDK-less error monitoring via eBPF programs requires privileged, or sudo, access to hook into the kernel to then span the containerized app as a child process. This can be a complicated setup as most containerized deployment systems provide a sandboxed, or unprivileged, environment to run apps on. As such, SDK-less error monitoring is less common and typically reserved for business critical high performance needs. However, an SDK-less setup provides both a better UX, and the ability to replay incoming traffic and mock outgoing traffic behind the scenes which is necessary to verify the LLM's proposed bugfixes programmatically. Mocking all outgoing network traffic is particulally tricky as any communication to downstream databases and services needs to be properly parsed, recorded and stubbed which would not be possible without eBPF hooks.

Devtools that can automatically verify LLM-generated code

Certain devtools within domains where the functionality of LLM-generated code can be verified have been on the rise as they can save developers countless of hours. This trend will continue as the price of invoking LLMs goes down and the generative coding abilities of LLMs improve. Figma to code plugins are now abundant and significantly better than only a couple of years ago thanks to the newer generative AI models that can understand design assets and use them to verify the correctness of LLM-generated frontend code. Similarly, we can verify code generated by an LLM that was tasked to fix application errors because the expected output is simply that the error goes away and no new errors arise.

Drop me a line at lfdepombo at gmail dot com if you want to learn more about a cloud infrastructure offering I have been working on that proposes verifiable bugfixes. I would also love to hear your thoughts on any type of programmatic verification for LLM-generated code, or on building abstractions with LLMs without directly exposing a chat, or visual, interface to end-users.

LLMs as compilers

Luis Fernando De Pombo — Mon, 31 Jul 2023 18:51:06 +0000

a brief history of our programming abstractions

It is hard to fathom, but in a not-so-distant past we considered assembly languages a high-level programming language when compared to its predecessor, machine code. In 1947, assembly languages introduced an abstraction layer to simplify programming computers by letting us use numbers, symbols, and abbreviations to talk to computers instead of just 0s and 1s as had been the case with machine code. Assembly languages gave birth to computer science as a field in the digital realm if we exclude analog technologies from the 1800s and early 1900s like mechanical calculators, abaci, tabulators, etc. However, assembly languages were still very low-level and required a lot of effort to write and maintain.

; A sample “hello world” program that writes to stdout using `write` then exits using `exit` on Linux x86-64.
; courtesy of Jim Fisher https://jameshfisher.com/2018/03/10/linux-assembly-hello-world/

global _start

section .text

_start:
  mov rax, 1        ; write(
  mov rdi, 1        ;   STDOUT_FILENO,
  mov rsi, msg      ;   "Hello, world!\n",
  mov rdx, msglen   ;   sizeof("Hello, world!\n")
  syscall           ; );

  mov rax, 60       ; exit(
  mov rdi, 0        ;   EXIT_SUCCESS
  syscall           ; );

section .rodata
  msg: db "Hello, world!", 10
  msglen: equ $ - msg
        `

Then came Fortran I in 1953, with a new I/O interface abstraction that allowed us to more eloquently ask computers to perform and display numerical calculations. Fortran I was the first general-purpose programming language of its kind, but it would later become the common ancestor for many more of these syntaxes including C in 1972. However as we started to write more complex software with Fortran I, we realized that manual memory management became more and more cumbersome. John McCarthy came up with Lisp in 1959. Lisp was the second general-purpose programming language to exist after Fortran I and provided a higher-level abstraction that automatically managed memory for us using a new concept at the time called garbage collection. Automatic memory management of a computer's memory wasn't (and still isn't) as performant or efficient as the manual allocation and deallocation of memory, but it provided us with a superior software development experience.

The evolution of programming languages has been a constant march towards abstractions that make programming easier and more accessible. Newer versions of Fortran and the majority of the most used programming languages today use some descendent of the original garbage collector invented by John McCarthy to automatically manage computer memory. Programming abstractions build on top of each other over time and the old ones continue to live underneath the newer ones that we use today. And so the high-level languages of yesterday become the build artifacts and implementation details of today.

how deterministic does a compiler need to be?

LLMs are already changing the way we interact with code as witnessed with the advent of GitHub's Copilot. However, thus far LLMs have only served as programming companions that we can't fully rely on as they are not deterministic and can periodically hallucinate or produce errors. Historically, compilers are deterministic. Given the same input, the same compiler will always give you the same output. This has ruled out LLMs as compilers in the traditional sense. However, how much determinism do we really need to reliably produce software that does what we want it to do? If we think of an LLM as a software engineer, as opposed to a companion, the same software engineer can produce different software given the same spec or set of requirements. All the outputs across different implementation attempts can be valid unless the requirements provided are excessively stringent. If we can get to a point where LLMs can reliably produce correct code that meets the requirements given, then we can start to think of LLMs as compilers even if they are not deterministic. The question is not really if it will happen, but when it will happen. The field of generative AI is unfolding rapidly and working towards both the correctness and determinism of the code LLMs produce. As we make progress on both fronts, LLMs will sooner or later add English as another root node of the genealogical tree of programming languages, and from it, a whole new set of syntaxes will span that will use the high-level programming languages of today, like Javascript and Python, as foundational and low-level implementation details akin to what assembly represents today.

compillmers

There is already a lot of hay to mow with the current state of affairs in generative AI. LLMs as proper compilers, compiLLMers if you will, can produce correct code reliably enough today given enough guidance. Getting an LLM to generate correct code requires providing various examples and descriptive instructions. The UX of a chat interface to an LLM inherently leads people to write prompts that do not meet these criteria. We need to make it easy for people to give LLMs precise descriptions and numerous examples as concisely as possible via syntaxes that are similar to English so they remain easy to learn and use. Coq is a great example of a functional programming syntax that is verbose and distant from English, but example-driven via assertions. David Ellis, Alejandro Guillen and I recently introduced Marsha as a proposal for what a syntax that meets the requirements outlined can look like. It is still early, but LLMs will increasingly give us the power to create more accessible representations of computer programs that look close to English. These representations will be distilled by LLMs into the complexities of the current high-level languages. Knowing Java or Python will become a rare skill, akin to individuals specializing in low-level optimizations using C or assembly language these days. Instead, the focus of developer experience will shift to the higher-level abstractions that are built on top of LLMs and composing these abstractions for different tasks. Compillmers will make programming more accessible in the near future such that writing software becomes part of the resume of most knowledge workers.

Why SQL is right for Infrastructure Management

Luis Fernando De Pombo — Thu, 06 Apr 2023 21:15:23 +0000

Infrastructure is the heart of your company. Without it, nothing can actually be done and there'd be no reason for customers to pay you. For software companies that infrastructure is the software that its engineers directly write, and usually the cloud infrastructure and services it runs on top of and integrates with.

In this post, we will geek out on various software abstractions and data structures, tools used by professionals of various sorts, and dig into the pros and cons of these with respect to cloud infrastructure management in particular. We'll see that while SQL has its own warts, it is the "least worst" of all of the options out there.

Following these instructions is imperative

The simplest way to define how to set something up is to write up a list of instructions to follow, in order, to build whatever infrastructure you're dealing with, whether its the instructions on how to build a restaurant or an AWS Fargate cluster. This list of steps to process (with a LISt Processor, or LISP, if you will 😉), which can include instructions to repeat or skip over steps based on conditions you have run into, is called imperative programming in the software world.

Imperative Restaurant Instructions

Design restaurant.
Flatten the ground.
Prop up pieces of wood next to each other.
Bang nails into wood.
Is restaurant finished? No, go to 3.

For your cloud infrastructure, this is similar to using the AWS SDK and directly calling the various methods, checking their results and eventually arriving at the desired infrastructure. It is also like following a step-by-step guide clicking through the various AWS console UI elements to set up your infrastructure, like in this guide. The latter may not be automated, to you, but to your company executives it is as they don't care how the infrastructure building was accomplished as long as it was done quickly, cheaply, and reliably, and you know they always want all three. 😉

Imperative infrastructure management works well for initial setup of new infrastructure. There's nothing there to interfere with it, so you can just write the "happy path" and get it working. And if that fails, you can just tear it all down and start all over again if that's cheap, which it is for cloud infrastructure (though not for building a restaurant), so the cyclomatic complexity of the code you write is low and everyone can follow along with it. For example, one can use the aws cli application inside of a bash script to create a new ssh keypair in AWS, a security group enabling ssh access, a new EC2 instance associated with both, and then access that instance (to demonstrate that it works).

#!/bin/bash

aws ec2 create-key-pair \
  --region us-west-2 \
  --key-name login \
  --key-type ed25519 | \
  jq -r .KeyMaterial > login.pem
chmod 600 login.pem
SG_ID=$(aws ec2 create-security-group \
  --group-name login-sg \
  --description "Login security group" \
  --region us-west-2 \
  --vpc-id $(aws ec2 describe-vpcs \
    --region us-west-2 | \
    jq -r '.Vpcs[] | select(.IsDefault = true) | .VpcId') | \
  jq -r .GroupId)
aws ec2 authorize-security-group-ingress \
  --group-name login-sg \
  --region us-west-2 \
  --ip-permissions 'IpProtocol=tcp,FromPort=22,ToPort=22,IpRanges=[{CidrIp=0.0.0.0/0}]' > /dev/null
INSTANCE_ID=$(aws ec2 run-instances \
  --region us-west-2 \
  --subnet-id $(aws ec2 describe-subnets \
    --region us-west-2 | \
    jq -r .Subnets[0].SubnetId) \
  --instance-type t3.small \
  --image-id resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id \
  --security-group-ids ${SG_ID} \
  --key-name login \
  --associate-public-ip-address \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=name,Value=login-inst}]' \
  --output json | \
  jq -r .Instances[0].InstanceId)
echo EC2 instance ${INSTANCE_ID} starting 
aws ec2 wait instance-status-ok \
  --region us-west-2 \
  --instance-ids ${INSTANCE_ID}
PUBLIC_IP=$(aws ec2 describe-instances \
  --region us-west-2 \
  --filters Name=tag:name,Values=[login-inst] \
  --filters Name=instance-state-name,Values=[running] | \
  jq -r .Reservations[0].Instances[0].PublicIpAddress)
echo Server accessible at ${PUBLIC_IP}
ssh -i login.pem -o "StrictHostKeyChecking no" ubuntu@${PUBLIC_IP} uname -a

Keep in mind that this is a simple script that does not handle failure of any of the commands called, creating one EC2 instance with two supporting elements (a security group to allow access to the SSH port and an SSH keypair to log into the instance). If you intend to actually use it multiple times, several of the arguments currently hardwired should be turned into shell arguments themselves, and all of the error paths should be tackled.

However, the vast majority of the time you are not creating clones of the same infrastructure over and over again, instead you need to make changes to your existing infrastructure, and that original code is more than likely useless to you. To get from the current state of your infrastructure to your desired state, you need to call different APIs than you did before, and its much riskier to make a mistake because this infrastructure is already in use.

Declare your intentions at once

When the desired state is relatively simple to define and the mechanism to reach that state is not that important, writing up a declaration of what is needed and letting something/someone else deal with it is the most logical abstraction. This would be like drafting up the architectural draft for your new restaurant and paying a contracting company to actually build it, or writing HTML and letting a web browser render it, or writing a Terraform HCL file and letting the Terraform CLI tool apply it. This is called declarative programming in the software world, and has many advantages (and a few disadvantages!) for cloud infrastructure management.

Declarative Restaurant Instructions

By decree of the king, a restaurant shall be built!

In declarative programming you have some initial state and in comparatively dense and high-level declarative code define the desired state. In many use-cases (like web browsers and sometimes for restaurant-building contractors) the initial state is "blank" and the engine that transforms the declarative code into imperative operations can often be a relatively straightforward interpreter walking the AST of the declarative code.

Infrastructure management generally is not that straightforward. Changes to infrastructure require in-place mutations of the current state or a full blue/green deployment of the entirety of your production infrastructure is very expensive, requiring all resource costs to be doubled, and deployments to require some amount of downtime for users to swap over and state to be resynchronized. Particularly for databases this approach is essentially impossible as there is too much state to swap, which is what makes database migrations tricky when dropping data.

So declarative programming for infrastructure, also known as Infrastructure as Code, must take the existing state and generate the imperative operations to perform based on the differences between the existing state and the declared desired state. This is like a contracting company being given an architecture draft for a new restaurant and being told to remodel an existing commercial space (maybe it was also a restaurant, maybe it was a clothing store, etc) and the contracting company figuring out what needs to be torn down first, what can be re-used, and what needs to be built new.

terraform {
  required_providers {
    aws = {
      source = "hashicorp/aws"
      version = "4.60.0"
    }
  }
}

data "external" "example" {
  program = [
    "bash",
    "-c",
    <<-EOT
      if [ ! -f ./login.pem ]; then
        # Terraform doesn't support creating the PEM file via AWS. It has to be
        # created locally and then imported into AWS. Inside of a conditional so
        # we don't accidentally re-create the file between 'plan' and 'apply'
        yes '' | ssh-keygen -t ed25519 -m PEM -f login.pem
      fi
      chmod 600 login.pem
      # The output of this script needs to be JSON formatted. This is a bit wonky
      # but building it over time via 'jq' operators is actually harder to follow
      echo {
      echo   '"access_key_id": "'$(aws configure get aws_access_key_id)'",'
      echo   '"secret_access_key": "'$(aws configure get aws_secret_access_key)'",'
      echo   '"public_key": "'$(cat login.pem.pub)'"'
      echo }
    EOT
  ]
}

provider "aws" {
  region = "us-west-2"
  access_key = data.external.example.result["access_key_id"]
  secret_key = data.external.example.result["secret_access_key"]
}

resource "aws_key_pair" "login" {
  key_name = "login"
  public_key = data.external.example.result["public_key"]
}

resource "aws_security_group" "login_sg" {
  name = "login-sg"
  description = "Login security group"
  ingress {
    from_port = 22
    to_port = 22
    protocol = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

resource "aws_instance" "login" {
  ami = "resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id"
  instance_type = "t3.small"
  associate_public_ip_address = true
  key_name = aws_key_pair.login.key_name
  vpc_security_group_ids = [aws_security_group.login_sg.id]
  tags = {
    name = "login-inst"
  }
}

This example still requires the following lines from the imperative example to get the public IP address of the new EC2 instance and log into it:

PUBLIC_IP=$(aws ec2 describe-instances \
  --region us-west-2 \
  --filters Name=tag:name,Values=[login-inst] \
  --filters Name=instance-state-name,Values=[running] | \
  jq -r .Reservations[0].Instances[0].PublicIpAddress)
echo Server accessible at ${PUBLIC_IP}
ssh -i login.pem -o "StrictHostKeyChecking no" ubuntu@${PUBLIC_IP} uname -a

This diffing of original and desired state can be resolved several ways. The contracting company could spend a lot of time inspecting the existing commercial space to find absolutely every piece that can be re-used, removing them and setting them aside with the materials that need to be purchased, then rework the interior walls, and build everything from scratch, or it could decide to tear everything out back into an empty space and rebuild from scratch (like what your web browser does between web pages), or perhaps you need to keep the space mostly usable for customers, minimizing disruption to the current operations while things are changed. This last case is what infrastructure management tools need to tackle your infrastructure with the least amount of downtime or no downtime at all, if you're careful with it.

How can you "careful" with declarative programming tools, if you don't have direct control over what they do? Terraform does this with a plan command, which performs the diffing of current and desired state and reports back to you the operations it expects to execute to do so. Terraform will tell you when a change it intends to make is going to provision new resources, alter existing resources, drop resources, and replace resources. That last one is particularly annoying when it shows up because it means the change you want to perform can only be done by dropping it and recreating it with the new configuration.

$ terraform plan

data.external.example: Reading...
data.external.example: Read complete after 1s [id=-]

Terraform used the selected providers to generate the following execution
plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # aws_instance.login will be created
  + resource "aws_instance" "login" {
      + ami                                  = "resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id"
      + arn                                  = (known after apply)
      + associate_public_ip_address          = true
      + availability_zone                    = (known after apply)
      + cpu_core_count                       = (known after apply)
      + cpu_threads_per_core                 = (known after apply)
      + disable_api_stop                     = (known after apply)
      + disable_api_termination              = (known after apply)
      + ebs_optimized                        = (known after apply)
      + get_password_data                    = false
      + host_id                              = (known after apply)
      + host_resource_group_arn              = (known after apply)
      + iam_instance_profile                 = (known after apply)
      + id                                   = (known after apply)
      + instance_initiated_shutdown_behavior = (known after apply)
      + instance_state                       = (known after apply)
      + instance_type                        = "t3.small"
      + ipv6_address_count                   = (known after apply)
      + ipv6_addresses                       = (known after apply)
      + key_name                             = "login"
      + monitoring                           = (known after apply)
      + outpost_arn                          = (known after apply)
      + password_data                        = (known after apply)
      + placement_group                      = (known after apply)
      + placement_partition_number           = (known after apply)
      + primary_network_interface_id         = (known after apply)
      + private_dns                          = (known after apply)
      + private_ip                           = (known after apply)
      + public_dns                           = (known after apply)
      + public_ip                            = (known after apply)
      + secondary_private_ips                = (known after apply)
      + security_groups                      = (known after apply)
      + source_dest_check                    = true
      + subnet_id                            = (known after apply)
      + tags                                 = {
          + "name" = "login-inst"
        }
      + tags_all                             = {
          + "name" = "login-inst"
        }
      + tenancy                              = (known after apply)
      + user_data                            = (known after apply)
      + user_data_base64                     = (known after apply)
      + user_data_replace_on_change          = false
      + vpc_security_group_ids               = (known after apply)
    }

  # aws_key_pair.login will be created
  + resource "aws_key_pair" "login" {
      + arn             = (known after apply)
      + fingerprint     = (known after apply)
      + id              = (known after apply)
      + key_name        = "login"
      + key_name_prefix = (known after apply)
      + key_pair_id     = (known after apply)
      + key_type        = (known after apply)
      + public_key      = "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIF4yiy4lef2WFQ7zdtuOZUHz9onyADTJ16l0OX8a211y damocles@zelbinion"
      + tags_all        = (known after apply)
    }

  # aws_security_group.login_sg will be created
  + resource "aws_security_group" "login_sg" {
      + arn                    = (known after apply)
      + description            = "Login security group"
      + egress                 = (known after apply)
      + id                     = (known after apply)
      + ingress                = [
          + {
              + cidr_blocks      = [
                  + "0.0.0.0/0",
                ]
              + description      = ""
              + from_port        = 22
              + ipv6_cidr_blocks = []
              + prefix_list_ids  = []
              + protocol         = "tcp"
              + security_groups  = []
              + self             = false
              + to_port          = 22
            },
        ]
      + name                   = "login-sg"
      + name_prefix            = (known after apply)
      + owner_id               = (known after apply)
      + revoke_rules_on_delete = false
      + tags_all               = (known after apply)
      + vpc_id                 = (known after apply)
    }

Plan: 3 to add, 0 to change, 0 to destroy.

─────────────────────────────────────────────────────────────────────────────

Note: You didn't use the -out option to save this plan, so Terraform can't
guarantee to take exactly these actions if you run "terraform apply" now.

When you see steps where Terraform plans to drop or replace resources, you can decide if that's actually alright, or if it will negatively impact your business during the deployment, and then you can abort and define an intermediate state to first transition to, usually creating a new resource without dropping the old one, then do whatever operation is necessary to move internal state to it, then continue by deleting the old resource. This is like checking up on your contracting company before they start doing their work to make sure they don't do anything strange you don't want, and sometimes needing to handhold them through details of your business to come up with the new plan of action.

Until cloud resource management is as quick to execute as a webpage render where state changes can be done faster than a human can respond, this sort of hand-holding is necessary for live infrastructure with uptime guarantees. Declarative programming, at least for cloud infrastructure management, is a leaky abstraction over the imperative operations that need to be executed.

The Terraform HCL files don't have the prior state encoded into them, nor does it depend on being stored within a git repository to provide that prior state, so how does Terraform get that initial state to operate on? Terraform maintains an internal statefile to perform the diffing against. It has a mechanism to refresh that state, but being a JSON blob with poor typing, can become corrupted, requiring manual intervention.

Why does Terraform need this JSON-based statefile when it has the much cleaner HCL format that you use as a developer? This is because HCL, as a language that provides affordances to the developer's own intuition on how to structure their representation of their infrastructure, does not have a canonical form to make these comparisons with. Terraform must first pre-process the HCL into an internal data structure which it can then generate a diff that can then be used to determine the operations to perform. The JSON-based statefile is likely a very close representation of this internal data structure, and manual editing of it is highly discouraged because the details of it likely vary from release to release with an internal migration mechanism performed on upgrade of the Terraform CLI.

Needing to learn Terraform's HCL language to make infrastructure changes has been cited as a weakness that can be mitigated by wrapping it in the syntaxes more familiar to the user like Terraform CDK, Pulumi, or AWS CDK. This is a weakness, but we believe that the HCL-to-Statefile transformation being surjective instead of bijective is actually the bigger issue.

Surjective Function f Diagram

From Wikipedia: A function f is surjective if all of its inputs X map to all outputs Y, but overlaps can occur.

This is like multiple Terraform HCL representations all mapping to the exact same cloud infrastructure.

The Objective is Bi- hmmmm....

Bijective Function f Diagram

From Wikipedia: A function f is bijective if all of its inputs X map to all outputs Y exactly once.

A bijective function has a one-to-one mapping of all state in one set with all state in another set. Both representations are equivalent (given the appropriate transform functions), both sets (of possible cloud states) are the same size (conceptually), and for any one particular state in one set there is exactly one state in the other set. Programming Languages don't fit the bill here, as was demonstrated earlier, but what does? What, exactly, defines your cloud resources in the most minimal way?

The best answer in most cases is ~~water vapor~~ Entities. Each cloud resource has some unique identifier and a set of properties that configure it. Some of those properties can be relations or dependencies on other cloud resources. The ordering of the entities does not matter, so long as the dependencies are explicitly defined to make determination of the order of operations possible. You may have noticed that this sounds a lot like Objects in Object-Oriented Programming. It is very much the same, with the only difference being that the unique identifier must be unique, so if we considered an array of pointers to these objects as the entity IDs, multiple pointers to the same object wouldn't be allowed.

This means the representation of your cloud resources should be data, not code. Particularly, it should be relational data to allow the references/relations with other entities that actually exist within your cloud. Modeling each entity as a wholly-siloed object without those relations explicitly tracked might work for read-only cases, but puts the burden of properly joining the entities on the user and could lead to unexpected errors during mutation if the disjointed state is not carefully kept in sync. This mutation of that data would be the code, and fits very well into a classic REST architecture (in the original sense, though it could also work in the Web-oriented RESTful meaning.

Since your infrastructure is actually data, just about any data format could be used to store it, as demonstrated by Terraform's usage of JSON under-the-hood. Some will require more application-level guarantees on top of the data format to achieve all of the necessary components, however. For instance JSON does not have any native way to represent references to other JSON objects that are not wholly contained within the object in question, so you would need to define a way to represent them and enforce it within your application. YAML does support them via tags but being a markup language has multiple equivalent representations and is therefore not suitable for this purpose (without a strict linter to reduce it back down to a canonical form). There are also binary formats that can encode references with SQLite's file format arguably being the most prolific.

SQL immediately stands out here because it was designed for making relational algebra, the other side of the Entity-Relationship model, accessible. There are likely more people who know SQL than any programming language (for IaC) or data format you could choose to represent your cloud infrastructure. Many non-programmers know it, as well, such as data scientists, business analysts, accountants, etc, and there is an entire ecosystem of useful functionality you gain "for free" by using a SQL representation than just about any format out there. Furthermore, a powerful SQL engine like the open source PostgreSQL provides tools capable of elevating the infrastructure management experience beyond what IaC can do today.

The Advantages of SQL Databases for Infrastructure Management

First, by defining foreign key relations between tables, the database can immediately inform you if you have dependencies that must be created before you can create the resource you desire. It will simply return an error message to you if you have inserted an incomplete record missing any required fields.

"But," you say, "programming languages with solid type systems, like Rust, can also do that for you." That is true, and this was not an attempt to disparage them, merely to show that you aren't losing anything by using SQL as the representation. There are, however, ways SQL goes above and beyond. By defining unique, not-null, and check constraints on top of a rich type system the database can prevent you from inserting faulty data to a greater degree than even Rust, as the constraints can be against the live state of your infrastructure itself. Instead of code needing to deal with unexpected values further down the line, the faulty data can be prevented from ever being inserted. Rust's compiler making sure that you handle every possible branch is an awesome thing, but SQL has the power to make more of these branches impossible in the first place.

By being in a database, you can answer questions about your infrastructure via queries, and in the case of security vulnerabilities or expensive misconfigurations you can update that state across the board at the same time. The shared, live nature of the SQL database more closely matches the reality of the cloud infrastructure, while the more malleable nature of the querying in the Structured Query Language lets you cross-check in many dimensions the AWS console does not.

There's a further advantage by having a bijective relationship with your cloud: you can re-synchronize the database with your cloud at any time, avoiding statefile issues by never getting too out-of-date, and being almost impossible to corrupt. Software bugs are unavoidable, but since populating the database is trivial (compared to the cloud account itself), it could simply be dropped and recreated at any time without an impact on your own usage.

Most SQL databases have the concept of trigger functions that execute based on changes to the database itself. With a collection of automatically-created trigger functions, an audit log can be maintained, which can keep track of who made what cloud changes and when they did, going a step further than even a git repository of IaC changes, as changes done manually through the AWS console will also show up (though these would not have a database user to tie them to).

Trigger functions have other uses that don't even have an analogous concept in IaC tooling. For instance, trigger functions can be added to automatically re-insert deleted records of sensitive cloud resources for automated Chaos Engineering recovery. Either a mutation that was attempted by a developer that shouldn't be allowed (without also going through the much larger change of removing said trigger function first) so the resource is never deleted in the first place, as well as automatic recreation of the desired resource in the cloud if deleted via the AWS console or dropped during an outage.

SQL Database Snapshotting could be used to quickly revert all infrastructure changes for infrastructure without hidden state (eg, the snapshot of your load balancers' prior state would successfully restore the load balancer configuration, but a dropped database restored via a snapshot would not restore the data within, only that you had such a database in the first place and you would still need to manually re-load a backup snapshot of those, as well).

Database engines also represent a standardized protocol to interface with your data. This standard opens up a world of possibilities that can encompass the imperative and declarative infrastructure management styles and go beyond them. You can:

Perform operations on your database via scripts and the psql CLI tool, using whatever mix of declarative and imperative styles you prefer, mutating various tables declaratively and calling AWS SDK functions directly where and when you see fit.
Integrate the database into your application itself with a postgres client library allowing your applications to make infrastructure changes (like provisioning sharded resources for a client that wants isolation, or using a more accurate forecasting model to pre-allocate more resources before the storm hits).
Connect with a SQL IDE for a variety of reasons, such as outage mitigation or inspection of your infrastructure in a tabular (Excel-like) view.
Connect the database to a graphical dashboard for live visualization of your infrastructure's state and any other metadata transferred through.
Connect it to a low code tool for rapid response dashboards for your infrastructure with buttons you can tackle known outage vectors near-instantly, or just to make self-service provisioning of new microservices in the infrastructure team's preferred way.

There are negatives to representing your infrastructure as SQL, of course, as there is no perfect solution for all situations, but we believe IaSQL is a superset versus IaC in all but one category.

SQL is an old, irregular language to work with, but it is better known than HCL and SQL already has it's own Pulumi/CDK in the form of every ORM with introspection (like Javascript's Prisma, Python's Django, Go's XO etc) and QueryBuilder (LINQ, Knex, etc) in whatever programming language you prefer. You probably already know it.

Peer review of changes is different versus IaC. Instead of writing HCL or other IaC code, having it reviewed, and then applying it, you can enter an IaSQL transaction, make the desired changes with Postgres acting as your IDE, automatically blocking many impossible changes via types and relations, and use iasql_create_review to create a review artifact for your team after IaSQL has already vetted it for technical correctness. This vetting makes the result superior to the traditional peer review system in IaC.

Database schemas can be overwhelming, and a schema accurately representing all of AWS would be dizzying. HCL and other IaC tools let you elide parts of AWS via a module system, so we have implemented our own module system on top of Postgres inspired equally by programming language module systems and Linux distro package managers. This module system takes the place the usual database schema migration system for the management of the tables in the database, which may feel unusual at first, but we believe it is superior (for this use-case).

Even with IaC tools letting you ignore details you don't specifically need, AWS and other clouds expose many details in multiple layers for you to control when often you just need the same subset of functionality over-and-over, so these IaC tools have higher-level modules for these cookie-cutter use-cases. For IaSQL we did the same, though in a way that coexists with the lower-level modules so you can still query those details later, if you like. Here is an example of a high-level module that greatly simplifies ECS fargate.

IaSQL is declarative like IaC, so the leaky abstraction problem of declarative programming can still impact things. Like IaC's apply concept IaSQL has its own transaction system (separate from the actual database transaction system) that will let you enqueue changes and preview what actions the engine will take before commiting to it (or rolling it back). Going beyond that and acknowledging that sometimes you need to directly work in the lower abstraction layer, IaSQL exposes optional raw SDK access within the database to handle those tougher situations, and can then re-synchronize the declarative state afterwards.

As IaSQL is able to both push changes from the database to the cloud and pull changes from the cloud to the database versus the one-way push from HCL that Terraform provides, you can make changes directly in the AWS console, then inspect the audit log directly or call a special formatting function to see what SQL statements were the equivalent of those console changes. This reduces the learning curve of IaSQL and also lowers the barrier to entry in comparison to IaC. You can continue using other cloud management tooling in conjunction with IaSQL (including to a degree IaC) and IaSQL will show you "it's way" of doing the same when it syncs.

Finally, IaSQL makes your infrastructure accessible to a much large portion of your company than IaC tools can. Data Scientists who know SQL could help with the provisioning of their own batch processing code, EngSec can query all company infrastructure for security vulnerabilities and propose changes to improve things, Business Analysts in Growth teams can query actual traffic information from the live infrastructure (with read-only AWS credentials, of course), Finance auditors can query your infrastructure joined on pricing data and figure out accurate expense reporting per division, and DevOps can run queries joined on CloudWatch utilization stats to automatically identify overprovisioned services and create recommended auto-load scaling configuration changes. Tons of "not-developer" and "developer-adjacent" roles that can take over a lot of the ancillary burden of maintaining a production system.

But IaSQL is much newer than the IaC tools. We have coverage at the time of writing for 25 different AWS services, but that is admittedly far from complete. IaC tools also have cloud resources they cannot represent, but it is a much smaller percentage. This disadvantage will diminish over time, but creating a bijective entity representation of every cloud service requires more developer firepower than the approach IaC tools have taken, so we expect to always lag temporally on this front. At least, as long as AWS and friends don't follow a resource-oriented standard like Swagger.

Cloud APIs on Swagger?

Me too, Craig, me too...

With that said, what does our EC2 instance example look like in IaSQL, uh, SQL?

-- Assuming that the AWS credentials have already been inserted as done during the setup wizard
SELECT default_aws_region('us-west-2');

-- Install the necessary modules
SELECT iasql_install('aws_ec2', 'aws_ec2_metadata', 'ssh_accounts');

-- Create the key-pair and put it in our ssh_credentials table
-- We will update this later with the public IP address
INSERT INTO ssh_credentials ("name", hostname, username, private_key)
VALUES ('login', 'tbd', 'ubuntu', (SELECT privateKey FROM key_pair_request('login', 'us-west-2')));

-- Start an IaSQL transaction so these changes don't mutate our infrastructure immediately
SELECT iasql_begin();

-- Define the security group
INSERT INTO security_group (description, group_name)
VALUES ('Login security group', 'login-sg');

-- And it's ingress rule
INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, security_group_id)
SELECT false, 'tcp', 22, 22, '0.0.0.0/0', id
FROM security_group
WHERE group_name = 'login-sg';

-- Define the instance and the key-pair to use
INSERT INTO instance (ami, instance_type, key_pair_name, tags, subnet_id)
  SELECT
    'resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id',
    't3.small',
    'login',
    '{"name":"login"}',
    id
  FROM subnet
  WHERE availability_zone = 'us-west-2a'
  LIMIT 1;

-- Associate the instance with the security group
INSERT INTO instance_security_groups (instance_id, security_group_id) SELECT
  (SELECT id FROM instance WHERE tags ->> 'name' = 'login'),
  (SELECT id FROM security_group WHERE group_name='login-sg' AND region = 'us-west-2');

We can then SELECT * FROM iasql_preview(); to see what this does, like a terraform plan:

`iasql_preview` output

action	table_name	id	description
create	instance	1	1
create	security_group	18	18
create	security_group_rule	1	1

This is a "high level" overview with minimal details. We can get back a listing of all changes, represented in auto-generated SQL with SELECT * FROM iasql_get_sql_for_transaction();

INSERT INTO security_group (description, group_name, region)
VALUES ('Login security group', 'login-sg', (SELECT region FROM aws_regions WHERE region = 'us-west-2'));


INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, region, security_group_id)
VALUES ('f', 'tcp', '22', '22', '0.0.0.0/0', (SELECT region FROM aws_regions WHERE region = 'us-west-2'), (SELECT id FROM security_group WHERE group_id = NULL AND region = (SELECT region FROM aws_regions WHERE region = 'us-west-2')));


INSERT INTO instance (ami, instance_type, key_pair_name, state, tags, hibernation_enabled, region, subnet_id)
VALUES ('resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id', 't3.small', 'login', 'running', '{"name":"login"}'::jsonb, 'f', (SELECT region FROM aws_regions WHERE region = 'us-west-2'), (SELECT id FROM subnet WHERE subnet_id = 'subnet-06140fd708a495450' AND region = (SELECT region FROM aws_regions WHERE region = 'us-west-2')));


INSERT INTO instance_security_groups (instance_id, security_group_id)
VALUES ((SELECT id FROM instance WHERE instance_id = NULL AND region = (SELECT region FROM aws_regions WHERE region = 'us-west-2')), (SELECT id FROM security_group WHERE group_id = NULL AND region = (SELECT region FROM aws_regions WHERE region = 'us-west-2')));

which in this case is very similar to (though a bit more verbose than) our original SQL statements, but if you were going back and forth on what exact changes you're going to make, it can be a good summary.

If we're good with this, we can SELECT iasql_commit() to push this to our cloud account and then update our ssh_credentials record with the public IP address and check the connection:

WITH h AS (
  SELECT host(im.public_ip_address) AS hostname
  FROM instance_metadata im
  INNER JOIN instance i ON im.instance_id = i.instance_id
  WHERE i.tags ->> 'name' = 'login'
  LIMIT 1
)
UPDATE ssh_credentials SET hostname = h.hostname FROM ssh_credentials, h WHERE hostname = 'tbd';

SELECT * FROM ssh_exec('login', 'uname -a');

SQL is the least bad option for Infrastructure Management

Infrastructure Management is a complex beast. The cloud APIs are massive with a rich collection of knobs to tweak, and mistakes can be dangerous because the changes often take a long time and recovery even more so. We have learned about the two main types of infrastructure management: imperative and declarative, where ad-hoc usage of the AWS console fits under the imperative umbrella along with scripts calling an SDK, while IaC has until recently been the only way to declaratively manage your infrastructure.

Both imperative and declarative styles have their downsides. Imperative being more explicit makes it more tedious and more prone to error, and the usual execution frequency of only once for most production changes makes scripting usually no better than clicking around in the AWS console. Declarative is much less tedious, but because it is a leaky abstraction inspecting its execution plan is critical to make sure it doesn't automatically put you into an outage, and failures during application can still require falling back to imperative mode to get things back into shape from time-to-time.

Also until recently, all declarative approaches have suffered from only being a one-way transition, from your IaC codebase into the cloud, but no good or easy way to synchronize that code with the current state of the cloud if they drift from each other. Because these declarative programming approaches do not have a singular canonical form, they can't be bijective, making the transform back from the other side impossible to square up.

Data, on the other hand, is much easier to normalize in such a way, and relational data models, as found in SQL databases, can naturally model the relationships between cloud services, making it possible to produce a bijective representation. IaSQL takes advantage of this to make a declarative model based on tables and foreign keys that can automatically handle changes done outside of this declarative model, either by dropping down into its own imperative mode or by using any other tooling to manage your infrastructure, giving it a flexibility and resiliency that IaC tools don't have.

On top of this foundation, tooling to replicate or substitute for existing IaC tooling has been added, along with all of the past ~50 years of database tooling and expertise that you can take advantage of, providing a superset of functionality that makes existing use-cases easier and new use-cases possible.

IaSQL is currently in beta and can be used locally with docker or via our SaaS. We're proud of what we have built, and can't wait to hear from you on feature requests and bug reports.

Securely connect to an Amazon RDS via PrivateLink using SQL

Luis Fernando De Pombo — Wed, 29 Mar 2023 20:55:59 +0000

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 PrivateLink and IaSQL. AWS PrivateLink provides private connectivity between virtual private clouds (VPCs), supported AWS services, and your on-premises networks without exposing your traffic to the public internet. 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.

When creating a database, AWS provides some specific information about the hostnames and ports used. You can access those to reach your databases by using specific clients for MySQL, Postgres, etc...:

On a public VPC, those endpoints will be public by default. However, consider creating them under a private VPC and accessing them internally, to grant additional security to critical services. AWS PrivateLink offers the possibility to securely access those services without exposing them to the internet, just using Amazon's private network.

Are my RDS instances properly configured?

Please use this query to check it:

 ---- Installing the needed modules
SELECT
  iasql_install ('aws_rds', 'aws_vpc');

-- Perform the query for endpoints
SELECT
  rds.region,
  vpc.is_default,
  vpc.cidr_block,
  (
    SELECT
      COUNT(*) > 0
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = rds.region
      AND service = 'rds'
      AND endpoint_interface.vpc_id = vpc.id
  ) AS has_endpoint_interface
FROM
  rds
  LEFT OUTER JOIN vpc ON vpc.region = rds.region;

Have you found missing endpoints? No problem, IaSQL can generate missing Endpoint Interfaces for you:

SELECT
  *
FROM
  iasql_begin ();

-- Inserts the missing endpoints
INSERT INTO
  endpoint_interface (region, vpc_id, service, private_dns_enabled)
SELECT
  RDS.region,
  vpc.id,
  'rds',
  TRUE
FROM
  rds
  INNER JOIN vpc ON rds.region = vpc.region
WHERE
  NOT EXISTS (
    SELECT
      id
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = rds.region
      AND endpoint_interface.vpc_id = vpc.id
  );

-- Preview the changes
SELECT
  *
FROM
  iasql_preview ();

-- Apply the changes
--select * from iasql_commit();
-- Rollback the changes
select * from iasql_rollback();

Running this query on an IaSQL database will auto-generate the missing endpoint interfaces and will allow you to preview the changes to be applied on your cloud. Once you are OK with the results, you can uncomment the iasql_commit query, comment the iasql_rollback query, and it will create the endpoints for you. If the final results in the cloud are not as expected, you can always roll back your changes by calling the iasql_rollback command.

Testing the result

After running the query, you should have Endpoint Interfaces created for your RDS resources. Those should be on the region and VPC where you had your databases:

To start testing the result, you can start a new EC2 instance on a private VPC in the same region where your RDS and Endpoint interface is configured. You can double-check that there is no internet connectivity. But the RDS endpoint could still be reached, by using the interface that has been created:

Please note that you could only use those endpoints from the same region. You could reach the services in multiple regions with the use of VPC peering

Save $ on public S3 buckets using VPC endpoints via SQL

Luis Fernando De Pombo — Thu, 23 Mar 2023 20:19:30 +0000

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.

Why use VPC Interface Endpoints

AWS VPC Interface Endpoints are a component of AWS's PrivateLink infrastructure. AWS PrivateLink provides private connectivity between virtual private clouds (VPCs), supported AWS services, and your on-premises networks without exposing your traffic to the public internet.

Relying on PrivateLink offers a set of advantages in terms of cost, security and performance.

Cost

When using NAT gateways, you are paying for the NAT gateway itself, and the bandwidth used to access the internet. A NAT gateway is a generic way for private instances to reach the internet, and can be useful for generic usage, such as pulling dependencies, updating repositories, etc... But when used for reaching AWS resources directly, there are more optimal and cheaper solutions such as VPC endpoints - they will not have generic access to internet but only to specified public AWS endpoints. When using VPC endpoints, you are paying for the bandwidth used to access the public endpoints, but not for the VPC endpoint itself.

Costs for NAT gateway depend on each region, but it should be over 0.045 USD/hour per GB processed + 0.045 USD/hour for the instance itself. Costs for VPC endpoints are charged at 0.01 USD/hour per GB processed, being cheaper after the first PB.

Component	Hourly pricing	Per GB pricing
NAT gateway	0.045 USD/hour	0.45 USD/hour per GB
VPC endpoint	--	0.01 USD/hour per GB

Security: no internet traversal

When deploying services in production, the concept of defense-in-depth is essential: have an information assurance strategy that provides multiple, redundant defensive measures in case a security control fails or a vulnerability is exploited.
By using interface endpoints the services will be accessed via Amazon's internal networks. It is an essential security measure, that will prevent attackers from directly reaching the services by keeping them in a private network, reducing the surface area of attack.

Security: policies

AWS services can benefit from IAM policies for fine-grained access control. When using interface endpoints, those policies can be applied to determine the traffic that can pass through the interface.

Performance: latency

Because the traffic stays within Amazon’s networks, the physical distance traveled, the number of hops and the risk of traversing congested networks are all significantly lower.

Performance: bandwidth

PrivateLink supports a sustained bandwidth of 10 Gbps per availability zone with bursts up to 40 Gbps.

Performance: stability

PrivateLink consistently lowers the number of errors and timeouts on high loads. The distance traveled, the number of hops, and the risks associated with traversing congested networks are reduced when the traffic over Private Link stays within Amazon’s networks.

In terms of speed, Private Link supports a sustained bandwidth of 10 Gbps per availability zone with bursts up to 40 Gbps.

Do you want to know if you have it properly configured? The following query will check for active S3 VPC interface endpoints:

This query will find your active S3 buckets for all regions, associated with the existing endpoint gateways or interfaces - if they exist.

 -- Installing the needed modules
SELECT
  iasql_install ('aws_s3', 'aws_vpc');

-- Perform the query for endpoints
SELECT
  bucket.region,
  vpc.is_default,
  vpc.cidr_block,
  (
    SELECT
      COUNT(*) > 0
    FROM
      endpoint_gateway
    WHERE
      endpoint_gateway.region = bucket.region
      AND service = 's3'
      AND endpoint_gateway.vpc_id = vpc.id
  ) AS has_endpoint_gateway,
  (
    SELECT
      COUNT(*) > 0
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = bucket.region
      AND service = 's3'
      AND endpoint_interface.vpc_id = vpc.id
  ) AS has_endpoint_interface
FROM
  bucket
  LEFT OUTER JOIN vpc ON vpc.region = bucket.region;

Have you found missing endpoints? No problem, IaSQL can generate them for you. You can create two different types of endpoints: gateway and interface. We're going to describe their main features and you can create the desired type in an automated way.

Interface endpoints

An interface endpoint is an elastic network interface with a private IP address that serves as an entry point for traffic destined for a supported AWS service. As they use ENIs, they can benefit from security groups to control traffic.

IAM policies can also be
added to control access to those endpoints.

They are regional, meaning they can only be accessed by the same region they are created. However, Multi-region is possible by also using VPC peering, so resources in one region can be accessed from others, though this only supports IPv4 TCP traffic.

An interface endpoint (except S3 interface endpoint) has a corresponding private DNS hostname, that can be used to access the resource.

Interface endpoints cover a wide range of services such as S3, Lambda, API Gateway, etc... Get more detail about interface endpoints

This query will auto-generate the missing interface endpoints and will preview the changes to be applied on your cloud. Simply uncomment the final statement and run it to make the changes to your cloud account.

 -- Inserts the missing endpoints
SELECT
  *
FROM
  iasql_begin ();

INSERT INTO
  endpoint_interface (region, vpc_id, service)
SELECT
  bucket.region,
  vpc.id,
  's3'
FROM
  bucket
  INNER JOIN vpc ON bucket.region = vpc.region
WHERE
  NOT EXISTS (
    SELECT
      id
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = bucket.region
      AND endpoint_interface.vpc_id = vpc.id
  );

-- Preview the changes
SELECT
  *
FROM
  iasql_preview ();

-- Commit the changes
-- SELECT * FROM iasql_commit();
-- Rollback changes
-- SELECT * FROM iasql_rollback();

Endpoint gateways

An endpoint gateway is a gateway that can be configured as a target for a route in your route table, used to access traffic in DynamoDB or S3.

Multiple gateway endpoints can be created in a single VPC, and those can be used on different route tables to enforce different access policies from different subnets to the same service.

Gateway endpoints are supported within the same region only, resources from multiple regions cannot be accessed, even using VPC peering. They also support IPv4 traffic only.

To use them, DNS resolution must be enabled in the VPC.

When a route is added, all instances in the subnets associated with the route table will automatically use the endpoint to access the service.

Gateway endpoints only support S3 and DynamoDB services. Get more detail about gateway endpoints.

This query will auto-generate the missing endpoint gateways and will allow you to preview the changes to be applied on your cloud.

 -- Inserts the missing endpoints
SELECT
  *
FROM
  iasql_begin ();

INSERT INTO
  endpoint_gateway (region, vpc_id, service)
SELECT
  bucket.region,
  vpc.id,
  's3'
FROM
  bucket
  INNER JOIN vpc ON bucket.region = vpc.region
WHERE
  NOT EXISTS (
    SELECT
      id
    FROM
      endpoint_gateway
    WHERE
      endpoint_gateway.region = bucket.region
      AND endpoint_gateway.vpc_id = vpc.id
  )
  -- Preview the changes
SELECT
  *
FROM
  iasql_preview ();

-- Commit the changes
-- SELECT * FROM iasql_commit();
-- Rollback changes
-- SELECT * FROM iasql_rollback();

Testing the result

Once all the relevant endpoints have been created, you can confirm your access to the gateway endpoints from an internal EC2 instance on the same region as the endpoint you want to test:

For interface endpoints, access also can be tested using the named endpoint.

Deploy a Static Website on AWS using SQL

Luis Fernando De Pombo — Mon, 06 Mar 2023 20:37:34 +0000

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 a 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 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:

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

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.

DEV Community: Luis Fernando De Pombo

Quickly compare cost and results of different LLMs on the same prompt

3 common mistakes when integrating the OpenAI API with your web or mobile app

Mistake # 1: Storing the OpenAI Key in Frontend Code

Mistake # 2: Using an Unauthenticated Proxy

Mistake # 3: Using an Authenticated Proxy without Permissions

Building a safe integration

Building a simple integration

Built a ChatGPT-like app on Firestore with no backend/functions at ~1k LOC

npm i anthropic-react-native

npm i openai-react-native

Securely call private key APIs like openAI from flutter without a backend

Introducing Backmesh

Security Considerations

cloud infrastructure that proposes verifiable bugfixes

The different types of LLM code generation

Automatically verifying LLM-generated bugfixes

Devtools that can automatically verify LLM-generated code

LLMs as compilers

a brief history of our programming abstractions

how deterministic does a compiler need to be?

compillmers

Why SQL is right for Infrastructure Management

Following these instructions is imperative

Imperative Restaurant Instructions

Declare your intentions at once

Declarative Restaurant Instructions

Surjective Function f Diagram

The Objective is Bi- hmmmm....

Bijective Function f Diagram

The Advantages of SQL Databases for Infrastructure Management

Cloud APIs on Swagger?

iasql_preview output

SQL is the least bad option for Infrastructure Management

Securely connect to an Amazon RDS via PrivateLink using SQL

Are my RDS instances properly configured?

Testing the result

Save $ on public S3 buckets using VPC endpoints via SQL

Why use VPC Interface Endpoints

Interface endpoints

Endpoint gateways

Testing the result

Deploy a Static Website on AWS using SQL

Create a S3 Bucket

Make The Bucket Public

Use S3 Static Website Hosting

Build The Project And Sync To S3

Create a CloudFront Distribution

`iasql_preview` output