insert/delete/update with returning

[germsb]

Hello,
I'm a little confused about which method to use for this type of query

sql: INSERT INTO "person" ("age", "first_name") VALUES (?, ?), (?, ?) RETURNING *
params: [20, test, 23, test1]
=> [{id: 1, age: 20, first_name: test}, {...}]

The only method whose return type seems to match is query(statement: string, values?: any[] | undefined) => Promise<DBSQLiteValues> but it seems to me that it is only suitable for SELECT statement

Any help is welcome :)

[germsb]

Ideally the run or execute method should return changes: {changes: number , lastId: number, rows?: any[]}

[germsb]

The RETURNING syntax has been supported by SQLite since version 3.35.0 (2021-03-12).

Supported from android 14 (3.39.2)
Supported from IOS 15 (3.36.0)
Supported on sql.js current version

[jepiqueau]

@germsb you should look at the applications provided.
Any way

• the execute command is to execute a bench of statements

const createSchemaContacts: string = `
CREATE TABLE IF NOT EXISTS contacts (
  id INTEGER PRIMARY KEY NOT NULL,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  FirstName TEXT,
  company TEXT,
  size REAL,
  age INTEGER,
  MobileNumber TEXT
);
CREATE INDEX IF NOT EXISTS contacts_index_name ON contacts (name);
CREATE INDEX IF NOT EXISTS contacts_index_email ON contacts (email);
PRAGMA user_version = 1;
INSERT INTO contacts (name,FirstName,email,company,age,MobileNumber) VALUES ('Simpson','Tom','Simpson@example.com','X',69,'4405060708');
`;
const ret = await db.execute(createSchemaContacts);

ret.changes.changes will give you the number of changes.

• the run command allows you to execute one statement
  1)

  const stmt = "INSERT INTO contacts (name,FirstName,email,company,age,MobileNumber) VALUES ('Simpson','Tom','Simpson@example.com','X',69,'4405060708');"
  const ret = await db.run(stmt);
  

ret.changes.changes will give you the number of changes.
ret.changes.lastId will give you the last id.

```
const stmt = "INSERT INTO contacts (name,FirstName,email,company,age,MobileNumber) VALUES (?,?,?,?,?,?);"
const values = ['Simpson','Tom','Simpson@example.com','X',69,'4405060708']
const ret = await db.run(stmt,values);
```

ret.changes.changes will give you the number of changes.
ret.changes.lastId will give you the last id.

• the executeSet command is to run a statement for a set of values

const setContacts: Array<any>  = [
  { statement:"INSERT INTO contacts (name,FirstName,email,company,age,MobileNumber) VALUES (?,?,?,?,?,?);",
    values:["Simpson","Tom","Simpson@example.com",,69,"4405060708"]
  },
  { statement:"INSERT INTO contacts (name,FirstName,email,company,age,MobileNumber) VALUES (?,?,?,?,?,?);",
    values:[
      ["Jones","David","Jones@example.com",,42.1,"4404030201"],
      ["Whiteley","Dave","Whiteley@example.com",,45.3,"4405162732"],
      ["Brown","John","Brown@example.com",,35,"4405243853"]
    ]
  },
  { statement:"UPDATE contacts SET age = ? , MobileNumber = ? WHERE id = ?;",
    values:[51.4,"4404030202",2]
  }
];

      ret = await db.executeSet(setContacts);

ret.changes.changes will give you the number of changes.
ret.changes.lastId will give you the last id.

• the select method is used to query the database
  1)

  ret = await db.query("SELECT * FROM contacts;");
  

  ret.values will returned an array of rows

  const stmt = "SELECT * FROM contacts where age > ? ;"
  const values = [35];
   ret = await db.query(stmt, values);
  

  ret.values will returned an array of rows

Hope this clarify

[germsb]

Thank you for the answer! This confirms my research. The API doc would deserves these details

[beligatclement]

I agree the API doc would deserves these level of detail. I finally find my answers here thx.

[jepiqueau]

@germsb by the way RETURNING is not supported

[germsb]

Do you plan to implement it soon? It is a very interesting function. If you're considering it, a getter that returns whether the version of sqlite supports this feature would also be great. eg 'capdbConnection.isReturningSupported'
I am working on a driver to connect Kysely to your plugin which precisely offers an option to activate or not the returning. Kysely seems to be a very promising fully typed queryBuilder!

[jepiqueau]

@germsb i test it with better-sqlite3 which is now the core of the Electron plugin and it does not seem to be supported, i will check with sql.js. in the SQLite doc it is said that it is part of an extension but it is not said which one. I do not think that there is a chance for implementation soon

[germsb]

It supports it, but its implementation is a bit specific...

import Database from "better-sqlite3";

const db = new Database(':memory:');

db.exec(`
  CREATE TABLE t0(
    a INTEGER PRIMARY KEY,
    b DATE DEFAULT CURRENT_TIMESTAMP,
    c INTEGER
  );
`)

const res = db.prepare("INSERT INTO user(c) VALUES(random()), (random())").all();
// throw  TypeError: This statement does not return data. Use run() instead

const res = db.prepare('INSERT INTO t0(c) VALUES(random()), (random()) RETURNING *').all();
// <= 2 rows
// => [{ a: 1, b: '2023-07-01 08:22:27', c: 1791095673623899400 },
         { a: 2, b: '2023-07-01 08:22:27', c: 191423466968585860 }] 

const res1 = db.prepare('INSERT INTO t0(c) VALUES(random()), (random()) RETURNING b').all();
// <= 2 rows
// => [ { b: '2023-07-01 08:34:44' }, { b: '2023-07-01 08:34:44' } ]

const res2 = db.prepare('INSERT INTO t0(c) VALUES(random()), (random()) RETURNING *').get();
// <= 2 rows
// => { a: 1, b: '2023-07-01 08:22:27', c: 1791095673623899400 }
        
for (const t0 of db.prepare('INSERT INTO t0(c) VALUES(random()), (random()) RETURNING *').iterate()) {
     ...
}

const res3 = db.prepare('INSERT INTO t0(c) VALUES(random()), (random()) RETURNING *').run();
// <= 2 rows
// => { changes: 2, lastInsertRowid: 2 }

[germsb]

it work fine with kysely

import Database from "better-sqlite3";
import { Kysely, SqliteDialect, Generated } from "kysely";

interface DatabaseTablesTypes {
	user: {
		a: Generated<number>;
		b: Generated<string>;
		c: number;
	};
}

const db = new Database(":memory:");

db.exec(`
  CREATE TABLE user(
    a INTEGER PRIMARY KEY,
    b DATE DEFAULT CURRENT_TIMESTAMP,
    c INTEGER
  );
`);

const db1 = new Kysely<DatabaseTablesTypes>({
	dialect: new SqliteDialect({
		database: db,
	}),
});

db1.insertInto("user")
	.values([{ c: 5555 }, { c: 5555 }])
	.returningAll()
	.execute()
	.then((v) => console.log("insert returning", v));
        //insert returning [
        //    { a: 3, b: '2023-07-01 09:49:54', c: 5555 },
        //    { a: 4, b: '2023-07-01 09:49:54, c: 5555 }
        // ]

[germsb]

Work also with Sql.js you can test here
So finally, only your plugin does not support this clause

[jepiqueau]

@germsb i did it for electron and ios but androidx.sqlite does not support it the only way to do it is to compile the statement and to do a select

[germsb]

Great for ios and electron !! What version are you using for testing on android? Only android 14 includes sqlite 3.35.0+ for the moment.

[germsb]

Additionally, there is a third-party library that embeds the desired version of sqlite to maintain consistency across Android versions and devices. Do you think it's possible for me to use this library and point your plugin to it?

Maybe the same library exists for ios

[jepiqueau]

@germsb thanks for all this info i am running Android 13 API 33 which is based on sqlite3.32.2 so this is why there is no support for RETURNING.
I will implemented with a SELECT wating for Android 14

[germsb]

I also thought it was related to the Android version. Good news if you can emulate with a SELECT !!
Maybe it should be implemented as fallback if sqlite < 3.35.0 and use the native clause if higher
This would provide full support regardless of version.
Be carfull on ios, the problem is the same, during your test it was ok because I imagine that your iphone/pad is running on ios 15+
Anyway, thank you very much for considering my suggestions.

[jepiqueau]

@germsb This has been implemented in release 5.0.5

[germsb]

Excellent, I will test this soon. Thank you again for your time.