Maybe `insert-one!` ought to query entity inserted?
- Dominant language
- Racket
- Stars
- 65
- Forks
- 11
- PR merge metrics
- No merged PRs in 30d
Description
Hi Bogdan.
I wonder if [this logic](https://github.com/Bogdanp/deta/blob/master/deta-lib/query.rkt#L99-L101) ought to be reconsidered. What happens when you insert an entity into SQLite? You simply query for the last inserted row-id and update the entity struct that was past in to `insert-one!` and then simply return this entity to the user. I imagine there's an implicit assumption that what you insert is what you have in the database. Unfortunately, this isn't true when you have triggers that may e.g. default some values in the inserted row. I learnt this the hard way.
Here's an example:
```sql
create table if not exists test (
id integer primary key autoincrement,
ts timestamp default (strftime('%Y-%m-%dT%H:%M:%S', CURRENT_TIMESTAMP)),
gt timestamp,
gf text
);
create trigger if not exists test_default_ts_compute_gt after insert on test
BEGIN
update test set ts = strftime('%Y-%m-%dT%H:%M:%S', ifnull(ts, CURRENT_TIMESTAMP)) where id = new.id;
update test set gt = strftime('%Y-%m-%dT%H:%M:%S', ifnull(gt, datetime(ts, new.gf))) where id = new.id;
END;
```
This will render the entity returned by `insert-one!` stale.
What I think would be a less dangerous and more intuitive approach is to do the moral equivalent of:
```sql
BEGIN TRANSACTION;
insert into test (ts, gt, gf) values (NULL, NULL, '24 hours');
SELECT * FROM test where id = (SELECT last_insert_rowid());
END TRANSACTION;
```
This probably applies to other DBs not just SQLite.
Thank you
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.