# FTL-aware Data Mapper
**Author**: Lizzy Worstell
**Status**: ==in-review== / accepted / implemented / rejected

## Motivation

Providing readily accesible data operators is in keeping with FTL's goal of simplifying software development for its users. Doing so also serves the practical function of injecting FTL into the data layer. This gives FTL more control over persistance logic, which will enable us to honor FTL-defined type annotations all the way down to the DB; for example, we can manage DB encryption on fields declared in FTL as PII/sensitive.

## Goals

- Users can easily declare and query databases in their FTL systems
- FTL provides built-in, ergonomic data operators that negate the need for ORMs or other alternatives
- FTL can leverage its data layer to align the DB with FTL, e.g. a field declared to FTL as sensitive will be encrypted in the DB

## Non-Goals

- Design DB provisioning

## Design

The user will define a database as follows:

var BoutiqueDB = ftlsql.NewDatabase(
    "boutique", 
    ftlsql.DatabaseConfig{
        /* TBD: Information needed to connect to an existing DB instance */
    }
).Create()

Until we've implemented database provisioning via FTL, the object returned will provide a connection to an instance already provisioned by the user.

We will use code generation to provide objects for each table in the database instance, where each object will represent an entry for its underlying table. Suppose there exists a `users` table; an object like the following would be generated correspondingly:

type User struct {
	ID        *ftlsql.Id `db:"id"`
	Username  string     `db:"username"`
	Email     string     `db:"email"`
	Password  string     `db:"password"`
	CreatedAt time.Time  `db:"created_at"`
	UpdatedAt time.Time  `db:"updated_at"`
}

func (u *User) TableName() string {
	return "users"
}

The `BoutiqueDB` object returned by `ftlsql.NewDatabase` will provide simple CRUD APIs for querying the database. Developers can use a combination of this and instances of the generated types to interact with their database.

func create() error {
	userId, err := BoutiqueDB.CreateRow(User{
		Username: "hello",
		Email:    "[e-mail]",
		Password: "password",
	})
	if err != nil {
		return err
	}
}

// column name -> value
type QueryArgs map[string]interface{}

func read() error {
	var results []User
	_, err = BoutiqueDB.QueryRow(&results, QueryArgs{
		"id": userId,
	})
	if err != nil {
		return err
	}
}

func update(user *User) error {
	user.Email = "[e-mail]"
	BoutiqueDB.UpdateRow(user)
}

func delete(id ftlsql.Id) error {
	BoutiqueDB.DeleteRow(id)
}

**provide a mechanism for falling back to raw SQL in the APIs

The data mapper itself, which is the implementation behind these CRUD APIs, will be built as a feature on the FTL runner. Calls to the above APIs will perform GRPC calls to endpoints hosted on the same runner that is hosting the application code. Thus the data mapper need only be defined once to support all language runtimes.

Under the hood, the mapper will simply construct raw SQL statements using the provided input and operation type. We can extend this implementation to manage special-case data attributes, such as sensitive/encrypted fields (see below for more).

### Extensions

#### DB Provisioning

We will eventually support DB provisioning directly though FTL, rather than leveraging a connection to an existing DB. This is yet to be designed, but in keeping with the design of the mapper, user code may look something like the following:

var BoutiqueDB = ftlsql.NewDatabase("boutique", ftlsql.DatabaseConfig{
	Migrations: "path/to/migrations/directory",
}).Create()

The directory passed to `Migrations` should contain standard SQL/Postgres* migration files, e.g. something like the following to create a table:

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  email varchar(255) NOT NULL,
  username varchar(255) NOT NULL,
  password varchar(255) NOT NULL,
  PRIMARY KEY (id),
)

We will use code generation similarly to above, providing objects for each table created by the invocation of `ftlsql.NewDatabase`. Each object will represent an entry for its underlying table, determined by aggregating all migration files and examining the resultant tables.

*Note: Whether FTL will support MySQL or Postgres as its preferred DB technology is yet to be determined.*

#### Custom data attributes

Once we support DB provisioning through FTL, we anticipate extending support for declaring data fields with special attributes. The following explores this in the case of sensitive data, which should be encrypted in the DB.

To flag fields for encyption, FTL will generate their database classes with special `ftlsql.Sensitive` types:

type User struct {
	ID        *ftlsql.Id        `db:"id"`
	Username  string            `db:"username"`
	Email     *ftlsql.Sensitive `db:"email"`
	Password  *ftlsql.Sensitive `db:"password"`
	CreatedAt time.Time         `db:"created_at"`
	UpdatedAt time.Time         `db:"updated_at"`
}

Note that compared with the prior example, fields `email` and `password`, formerly of type `string`, are now of type `*ftlsql.Sensitive`.

When a `User` type is passed through the FTL data mapper via the aforementioned CRUD APIs, `ftlsql.Sensitive` fields will be encrypted before persistance. Below is a discussion of ways we might identify these fields for the code generator, in both Postgres and MySQL.

##### Postgres

In Postgres, we can leverage custom types to identify special attributes directly in migration files:

CREATE TABLE users (
  id INTEGER GENERATED always AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL,
  username SENSITIVE_TEXT,
  password SENSITIVE_TEXT,
  PRIMARY KEY (id),
)

##### MySQL

In the longer term, a preferred MySQL approach would involve using SQL parsers to enable something similar to the above Postgres solution, where users could mark special attributes directly in their migration files. To avoid the complexity of such a solution in the short term, the following describes a more lightweight approach:

var BoutiqueDB = ftlsql.NewDatabase("boutique", ftlsql.DatabaseConfig{
	Migrations: "path/to/migrations/directory",
}).WithSensitiveRows(/* string tableName */ "users", /* ...string columns */ "email", "password").Create()

Note the `WithSensitiveRows` builder invocation, which allows the developer to specify rows in the given table that should be marked as sensitive. Underlying this operation, we will maintain a separate table that persists data attributes.

CREATE TABLE `ftl_data_attributes` (
    `table_name` varchar(255) NOT NULL,
    `attributes` varchar(255) NOT NULL,
    PRIMARY KEY (`table_name`),
)

Invoking `WithSensitiveRows` will effectively translate to a separate insert statement during DB provisioning:

INSERT INTO `ftl_data_attributes` (`table_name`, `attributes`) VALUES
('users', '{"senstive": ["email", "password"]}');

Where the `attibutes` value is JSON providing a mapping of attribute name to the affected columns. This information will be queried during the code generation step.