Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
1 contributor

Users who have contributed to this file

105 lines (71 sloc) 3.19 KB

SQL

As sqflite does not do any parsing of SQL commands, its usage is similar to the usage on the native iOS and Android platform so you can refer to their respective documentation as well as the generic sqlite documentation:

The API is relatively close to the Android one. For performance and compatibility reason, cursors are not supported at this time.

It is impossible here to make a full documentation of SQL. Only basic information is given and common pitfalls.

Basic usage

execute

execute is for commands without return values.

// Create a table
await db.execute('CREATE TABLE my_table (id INTEGER PRIMARY KEY AUTO INCREMENT, name TEXT, type TEXT)');

insert

insert is for inserting data into a table. It returns the internal id of the record (an integer).

int recordId = await db.insert('my_table', {'name': 'my_name', 'type': 'my_type'});

See Conflict algorithm for conflict handling.

query

query is for reading a table content. It returns a list of map.

var list = await db.query('my_table', columns: ['name', 'type']);

delete

delete is for deleting content in a table. It returns the number of rows deleted.

var count = await db.delete('my_table', where: 'name = ?', whereArgs: ['cat']);

update

update is for updating content in a table. It returns the number of rows updated.

var count = await db.update('my_table', {'name': 'new cat name'}, where: 'name = ?', whereArgs: ['cat']);

See Conflict algorithm for conflict handling.

transaction

transaction handle the 'all or nothing' scenario. If one command fails, all other commands are reverted.

await db.transaction((txn) async {
  await db.insert('my_table', {'name': 'my_name'});
  await db.delete('my_table', where: 'name = ?', whereArgs: ['cat']);
});

Parameters

When providing a raw SQL statement, you should not attempt to "sanitize" any values. Instead, you should use the standard SQLite binding syntax:

// good
int recordId = await db.rawInsert('INSERT INTO my_table(name, year) VALUES (?, ?)', ['my_name', 2019]);
// bad
int recordId = await db.rawInsert("INSERT INTO my_table(name, year) VALUES ('my_name', 2019)");

The ? character is recognized by SQLite as a placeholder for a value to be inserted.

The number of ? characters must match the number of arguments. Arguments types must be in the list of supported types.

Particulary, lists (expect for blob content) are not supported. A common mistake is to expect to use IN (?) and give a list of values. This does not work. Instead you should list each argument one by one:

var list = await db.rawQuery('SELECT * FROM my_table WHERE name IN (?, ?, ?)', ['cat', 'dog', 'fish']);

NULL value

NULL is a special value. When testing for null in a query you should not do 'WHERE my_col = ?', [null] but use instead WHERE my_col IS NULL.

var list = await db.query('my_table', columns: ['name'], where: 'type IS NULL');
You can’t perform that action at this time.