Sqldelight: Support Grouping Multiple SQL Statements

[jchensc]

Background
I would like bring up the conversation of supporting grouping multiple SQL statements together to make it possible to use a single model in Java/Kotlin.

Usage
Following is a simple use case.

Let's say we have two tables:

+-----+-----------------+--------+
| _id | title           | salary |
+-----+-----------------+--------+
| 1   | Sales Associate | 1000   |
+-----+-----------------+--------+
| 2   | Manager         | 1500   |
+-----+-----------------+--------+

salaryChangeNotes
+-----+----------+------------------------------------------+
| _id | time     | updateNote                               |
+-----+----------+------------------------------------------+
| 1   | 20170918 | Increase manager's salary to 1500        |
+-----+----------+------------------------------------------+
| 2   | 20180417 | Increase sale associate's salary to 1000 |
+-----+----------+------------------------------------------+

Every time we update the salary table, we would like to insert a note into salaryChangeNotes table.

One possible implementation probably would be like this:

salary.sq

// ... create table, other logic, etc

GROUP_STATEMENTS updateSalaryRecord {

UPDATE salary
SET salary=?
WHERE title=?;

INSERT INTO salaryChangeNotes(time, updateNote)
VALUES(?, ?);

}

So in business logic, we only need to call

updateSalaryModel.bind(2000, "Manager", "20180418", "Increase manager's salary to 2000");
// Wrap return values from multiple sql statements is something we need to take care of
int updated = updateNumber.executeGroupStatement();

With more statements being grouped together, the implementation of this feature will be cleaner compared with the current approach.

[AlecStrong]

yup have thought about this before and totally agree we could make this easier. In my mind we do this by using BEGIN and END which also creates a transaction for the unit of work to be completed in:

updateSalaryRecord:
BEGIN;

UPDATE salary
SET salary=?
WHERE title=?;

INSERT INTO salaryChangeNotes(time, updateNote)
VALUES(?, ?);

END;

it becomes especially useful for reusing args across multiple statements:

upsertRow:
BEGIN;

INSERT INTO salary
VALUES (:id, :salary, :title);

UPDATE salary
SET salary=:salary
    title = :title
WHERE _id = :id AND changes() = 0

END;

so the calling code is upsertRow(id = "id", salary = 1000, title = "some title")

[jchensc]

Yay, glad you guys have already thought about this. As I mentioned, defining or wrapping the return value of different statements might be a painpoint in implementation.

[AlecStrong]

after talking with jake we're thinking we might go this direction:

upsert {
  UPDATE ...
  ;
  INSERT ...
  ;
}

[North101]

While not the cleanest, the following works:

upsertTeam:
WITH new(id, name, founded, coach, captain, won_cup) AS (
    SELECT * FROM team WHERE NULL -- gets type hints without returning rows
    UNION ALL
    VALUES(?, ?, ?, ?, ?, ?)
)
REPLACE INTO team(id, name, founded, coach, captain, won_cup)  
SELECT  
    new.id,  
    new.name,  
    CASE WHEN old.id IS NULL THEN new.founded ELSE old.founded END, -- example of a way to only insert a value and not update
    new.coach,  
    new.captain,  
    new.won_cup  
FROM new  
LEFT JOIN team AS old  
    ON old.id = new.id;

If you remove

SELECT * FROM team WHERE NULL -- gets type hints without returning rows

it will have a compile error as its not able to find type hints.
Maybe something like

WITH new(id, name) AS (
    VALUES(? AS Integer, ? AS String)
)

[eygraber]

Any update on this?

[manueldidonna]

Any update? This feature would be really useful

[oldergod]

👁️ 👁️

[saket]

Dear maintainer(s), any news?

[AlecStrong]

yes it is done, i just have to write the code

[Egorand]

Any updates yet?