# DAL Refactor
**Author**: _Denise Li_
<!-- Status of the document - draft, in-review, etc. - is conveyed via HackMD labels -->

## Description (what)

Split the controller DAL monolith by domain. Example domains: cronjobs, leases

## Goals

- Queries are split up into different dirs based on their domain, along with their sqlc codegen
- Support atomic operations (i.e. Tx) for queries spanning multiple domains. Despite depending on multiple other domains, these functions are housed under _their_ domain.
- Continue having a single DB

## Design (how)

Currently, we only use one type of transaction: `Tx`. We'll need to use two types of transactions going forward:
1. Qtx (type `*sql.Queries`): DAL-specific, sqlc-gen’d txn
1. Tx (type `pgx.Tx`): Wrapper guaranteeing atomic behavior across all Qtxs

Each DAL calls only its own domain’s queries. DALs may bundle multiple queries into one Tx invisibly under the hood, as long as all those queries belong to its own domain.

TxL = Tx Layer, similar to a DAL but it can talk to the DAL/queries of several domains in order to bundle cross-domain queries under a single Tx. TxLs are associated with a domain but aren't restricted to only running queries within that domain.

### Before
<img src="https://hackmd.io/_uploads/SJV7u6KDC.png" alt="drawing" style="height:300px;"/>

### After
<img src="https://hackmd.io/_uploads/Hy1U_pFPA.png" alt="drawing" style="height:400px;"/>

### Directory Structure

Before:

backend/
  controller/
    sql/
    dal/

After:

backend/
  controller/
    cronjobs/ -- no need for txl/
      dal/
      sql/
    deployment/
      dal/
      txl/
      sql/
    leases/
      dal/
      txl/
      sql/
    ...

It's a little annoying that each domain's `txl/` and `dal/` need to be separate packages, but that prevents any circular dependencies.

### Required changes (how)

For each domain:
- Make dir `backend/controller/{domain}`
- Move queries from `b/c/sql/queries.sql` to `b/c/{domain}/sql/queries.sql` and update `Justfile` + `sqlc.yaml` accordingly (e.g. [PR #1860](https://github.com/TBD54566975/ftl/pull/1860))
- Run `just build-sqlc`
- Move DAL methods from `b/c/dal/dal.go` to `b/c/{domain}/dal/dal.go`. At the top of the file, copy this boilerplate:

type DAL struct {
	db sql.DBI
}

func New(pool *pgxpool.Pool) *DAL {
	return &DAL{db: sql.NewDB(pool)}
}

func NewQTx(pool sql.ConnI, tx pgx.Tx) *sql.Queries {
	return sql.New(pool).WithTx(tx)
}

- Create the boilerplate file `b/c/{domain}/sql/conn.go`, which should be identical to `b/c/cronjobs/sql/conn.go`
- If there are any methods in `b/c/dal/dal.go` that pertain to this domain but query at least one domain that is not the one we are migrating, then move those to `b/c/{domain}/txl/txl.go`. For each method that bundles queries into a `Tx`, use the layered `Tx` and `QTx` pattern. Example in `dal.CreateDeployment`. The key lines:

tx, err := db.Begin(ctx)
if err != nil {
	return ...
}
defer tx.CommitOrRollback(ctx, &err)

// Create QTx for this domain's own queries
qtx := dal.NewQTx(db.Conn(), tx.Tx())

// Call queries like CreateEntity via the QTx
_, err = qtx.CreateEntity(...)

// Call helper DAL functions with the QTx
err = dal.Helper(ctx, qtx, ...)

// Create QTx for another domain
otherQtx := otherdal.NewQTx(db.Conn(), tx.Tx())

// Call other domain's helper DAL functions with the QTx
err = otherdal.Helper(ctx, otherQtx, ...)

- Update all callers of all the relocated functions. If any callers are in the parent DAL and execute the relocated query in a `Tx`, then update that caller to likewise use the `Tx` + `QTx` pattern.
- If there is any other logic in `backend/controller/` that should be bundled under this domain, then move that as well.

At the end of this migration, `backend/controller/dal` and `backend/controller/sql` will no longer exist.