# Design: SQL Verbs
**Author**: @worstell
<!-- Status of the document - draft, in-review, etc. - is conveyed via HackMD labels -->

## Description

Convert database schemas and queries into FTL types (queries as `Verb` and models as `Data`), integrating SQL into the FTL type system.

## Motivation

Ensuring type safety, reducing boilerplate, and simplifying development against DBs. This feature also lays the groundwork for enforcing redaction and encryption policies throughout the FTL ecosystem.

## Goals

* Provide ergonomic, type-safe interaction with DBs
* Integrate DB operations with the FTL type system

### Non-Goals

* Implementation of redaction, encryption, and other policies is out-of-scope

## Design

### 1. User Configuration
SQL schema and query files are supplied in known directories. Users can optionally specify their root paths, relative to the module root, in `ftl.toml`:

module = "echo"
language = "go"
db-schema-dir = "sql/schema"
db-query-dir = "sql/query"

If not specified, the default relative paths `sql/schema` and `sql/query` are used.

### 2. Build Engine
The build engine identifies when directories are present at the provided SQL paths and, if they are, invokes `sqlc generate ...` using our custom plugin.

Prior to executing the plugin, we'll scaffold a per-build sqlc config into the user's build directory:

version: '2'
plugins:
- name: sqlc-gen-ftl
  wasm:
    url: https://github.com/TBD54566975/ftl/releases/download/ **<release-version>** /sqlc-gen-ftl.wasm
    sha256: <calculated-sha256>
sql:
- schema: schema.sql
  queries: queries.sql
  engine: postgresql
  codegen:
  - out: **<build-directory>**
    plugin: sqlc-gen-ftl
    options:
      module: **<module-name>**

*(more on the WASM plugin below)*

The plugin converts SQL to FTL schema protos and writes the result to the module build directory. The schema is then read and validated before being sent to a new `GenerateQueries` API on the language plugin.

The language plugin forwards this request to the runtime service.

### 3. Runtime Service

The runtime service generates language-specific types (e.g. Go structs, Python classes) from the schema proto and provides them in `queries.ftl.go` in the root module directory.

---

### sqlc-gen-ftl Plugin Implementation

sqlc supports [custom plugins](https://docs.sqlc.dev/en/stable/guides/plugins.html#) that can process its intermediate representation (IR) to generate files. Plugins can be:

**Process-based**: Standalone executables that interact with sqlc via proto over stdin/stdout.

**WASM-based**: Sandboxed plugins written in languages that compile to WebAssembly.

We can host the plugin as WASM using GH Releases. WASM plugins are more portable compared with process-based plugins, which require managing and distributing platform-specific binaries.

Suggested type mappings:

| **SQL** | **FTL** |
|------------------|--------------|
| `INTEGER` | `Int` |
| `BIGINT` | `Int` |
| `SMALLINT` | `Int` |
| `SERIAL` | `Int` |
| `BIGSERIAL` | `Int` |
| `REAL` | `Float` |
| `FLOAT` | `Float` |
| `DOUBLE` | `Float` |
| `NUMERIC` | `Float` |
| `DECIMAL` | `Float` |
| `TEXT` | `String` |
| `VARCHAR` | `String` |
| `CHAR` | `String` |
| `UUID` | `String` |
| `BOOLEAN` | `Bool` |
| `TIMESTAMP` | `Time` |
| `DATE` | `Time` |
| `TIME` | `Time` |
| `JSON` | `Any` |
| `JSONB` | `Any` |
| `BYTEA` | `Bytes` |
| `BLOB` | `Bytes` |

*-should JSON be bytes or Any?*