🚀 Feature Request: D1 Transaction support

[codehz]

Describe the solution

Has the procedures feature mentioned in this article been shipped?
This feature does not seem to be available in the current version sdk, either locally or remotely. Is there another plan for this? I really want this feature.

One of my use cases is to cascade insert data into two (or more) tables, where the generated id obtained by the first insert statement is used as a parameter in the second insert statement. These two inserts need to be done in the same transaction, otherwise the inconsistent state may cause some problem. (In other database engines, such as postgresql, we can achieve this through subqueries or cte with returning clauses in one statement. Unfortunately, this method does not work in sqlite)

[rozenmd]

Hey @codehz - it hasn't been shipped yet, though @geelen will be working on it soon

[geelen]

Yeah afaik there's no way to do an insert in a subquery, but you can use last_insert_rowid() inside a batch call to get a bit of what you're after. I just confirmed this works in both native SQLite and D1:

PRAGMA foreign_keys=on;

drop table if exists b;
drop table if exists a;

create table a (
   id INTEGER PRIMARY KEY,
   value TEXT
);

create table b (
   id INTEGER PRIMARY KEY,
   a_id INTEGER,
   value TEXT,
   FOREIGN KEY (a_id) REFERENCES a(id)
);

INSERT INTO a (value) VALUES ('foo');
INSERT INTO b (a_id, value) VALUES (last_insert_rowid(), 'bar');

INSERT INTO a (value) VALUES ('baz');
INSERT INTO b (a_id, value) VALUES (last_insert_rowid(), 'boo');

Transactions are coming but hopefully this can unblock you in the meantime?

[codehz]

It does work in this scenario, but in fact what I'm creating a generic framework that doesn't assume that users use rowid(or its alias) as a constraint for foreign key references.
Indeed, there is still another workaround to use the previous rowid in the subquery to query the referenced key. This is exactly what I'm trying to do now.
it will looks like this:

create table A(id default (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))), value);
create table B(id default (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))), aid, value);
insert into A(value) values (42);
insert into B(aid, value) values ((select id from A where A.rowid = last_insert_rowid()), 8);

But this method is still not robust enough, because users may create rowid-less tables for some optimization purposes.
Or for more complex scenarios, create elements in more tables and then reference them in another table(This function only allows to get the previous rowid, and there is no way to store variables in a transaction).

[geelen]

Yeah, it's definitely not a complete workaround, just a stopgap.

I'm creating a generic framework that doesn't assume that users use rowid(or its alias) as a constraint for foreign key references.

I think you're going to have to disallow rowid-less tables for the moment, then. Then when true JS/SQL procedures land, you can make things more generic.

[codehz]

I think you're going to have to disallow rowid-less tables for the moment, then. Then when true JS/SQL procedures land, you can make things more generic.

I'm going to disable the whole feature (Insert an object along with its related objects through relationships) right now, since it require more complex solution if I need insert more than one related objects (last_insert_rowid can only get last one. This means that it may be necessary to create a temporary table to store the rowids of these related objects. The benefits it brings are not worth compared to the cost of increased complexity, not to mention that it will soon be replaced by a new mechanism), and it's not a very useful feature either, just for completeness(since hasura do).

[geelen]

No worries. Hopefully this issue will help someone googling this problem in the short term. I'm going leave it open for now and update & close it once Transactions/Procedures lands.

[codehz]

BTW, in this article, it is not clear which types we can pass to the Procedures 'worklet' (and return back). only primitive types? object which can be serialized to json (or structuredClone)? ArrayBuffer?

[geelen]

Can't be sure at this stage, but I'm aiming for full structuredClone support. Worst case JSON-serialisable only.

If JSON is good enough though it might be easier to build so we might start there, ship that, then expand it later? Happy to hear suggestions on that front...

[codehz]

JSON's hidden problem is, it cannot contains cyclic reference(and this might bring some surprises for user since it is transparent), other than that, the json looks good enough

[OhSoooLucky]

Any ETA on this feature?