SQL Processor Ignores Record Fields and Populates Values in Order of SQL Result

[Arpita-Jaiswal]

Description

When using SQL type processors such as sql-query, sql-execute, and sql-batch in fastn, if the return type is a record, fastn does not correctly map the record fields to the corresponding values from SQL according to the key. Instead, it populates the fields based on the order in which the results are returned. This leads to incorrect data mapping and unexpected behaviour.

Example

Consider the following code:

In FASTN.ftd file:

-- fastn.migration:

CREATE TABLE posts (
    postId INTEGER PRIMARY KEY,
    userId INTEGER NOT NULL,
    postContent TEXT NULL,
    mediaUrl TEXT NULL,
    createdon INTEGER NOT NULL
);

In <some-other-file>.ftd:

-- import: fastn/processors as pr

-- post-data list data:
user: some-user
$processor$: pr.sql-query

SELECT * FROM posts;

-- record post-data:
integer postId:
integer userId:
integer createdon:
optional string postContent:
optional string mediaUrl:

This code fails because $processor$: pr.sql-query returns the columns in any order and uses that order to populate the post-data list data values. For instance, if the SELECT * FROM posts; returns the columns as postId, userId, postContent, mediaUrl, createdon, the processor maps values as follows:

• postId: postId
• userId: userId
• createdon: postContent
• postContent: mediaUrl
• mediaUrl: createdon

The processor ignores the keys and maps values based on the order of columns in the SQL result.

Workaround

A possible workaround is to avoid using SELECT* in the SQL statement. Instead, explicitly specify the column names and ensure the record fields are declared in the same order.

-- import: fastn/processors as pr

-- post-data list data:
user: some-user
$processor$: pr.sql-query

SELECT userId, createdon, postContent, mediaUrl FROM posts;

-- record post-data:
integer userId:
integer createdon:
optional string postContent:
optional string mediaUrl:

This approach works but fails if the order of record field declarations changes.

Expected Behavior

The processor should correctly map SQL result columns to record fields based on the keys, not the order of columns in the result.

Steps to Reproduce

1. Create a table and populate it with data.
2. Use sql-query processor to fetch data using SELECT * statement.
3. Define a record with fields in a specific order.
4. Observe that the record fields are populated based on the order of SQL result columns instead of their keys.

Possible Solution

Modify the SQL processors (sql-query, sql-execute, sql-batch) to ensure they map values to record fields based on the field keys rather than the order of columns in the result.

Additional Context

This issue impacts data integrity and requires developers to be cautious about the order of columns in their SQL queries and record field declarations, which can be error-prone and reduces flexibility.