This topic describes how you can use SQL database in the application.
The SQLite database provides a way to store databases that can be queried using various SQL statements. Each Tizen application can store multiple databases and each one of them can store multiple tables. Databases can be accessed using the SQL language, which is a common standard for these kinds of applications.
Note
When working with the SQL database, this API provide Synchronous database API only.
- To start an operation, you must wait for the previous one to be completed. This approach can be used with promise.
const url = `ws://${DB_HOSTNAME}:${DB_PORT}${DB_PATH}`;
const sqlite3 = new SQLite3(url);
const db = await sqlite3.open(DB_SOURCE, (err) => {
if (err) {
return;
}
});
{
const q = `CREATE TABLE user (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name text,
email text UNIQUE,
password text,
CONSTRAINT email_unique UNIQUE (email)
)`;
await db.run(q, [], (err) => {
if (err) {
`run: error: ${JSON.stringify(
err
)}: Table User exists in the current database. To fix, remove ${DB_SOURCE}`
);
return;
}
});
}
Provide native SQLite database binding using JavaScript WebAPI.
Straightforward query and parameter binding interface.
let db = await new sqlite3.Database('filename.db');
Name | Type | Attribute | Description |
---|---|---|---|
filename | string | Valid values are filenames, and ":memory:" for an anonymous in-memory database | |
mode | int | <optional> | One or more of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE and sqlite3.OPEN_CREATE. The default value is OPEN_READWRITE |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing a database object.
Type Promise.<Database>
Name | Type | Attribute | Description |
---|---|---|---|
query | string | The SQL query to run. | |
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing an array of rows. If the result set is empty, it will be an empty array, otherwise it will have an object for each result row which in turn contains the values of that row, like the Database#get function.
Type Promise.<Array.<Object>>
Returns:
Type Promise.<undefined>
Name | Type | Description |
---|---|---|
option | string | A name of an option to set. |
value | int | Time in milliseconds. |
Returns:
Type Promise.<undefined>
There is currently no way to abort execution.
Name | Type | Attribute | Description |
---|---|---|---|
query | string | The SQL query to run. | |
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
callback | function | <optional> | (function(row):void) If the result set succeeds but is empty, the callback is never called. In all other cases, the callback is called once for every retrieved row. The order of calls correspond exactly to the order of rows in the result set. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing the number of retrieved rows.
Type Promise.<Integer>
Note
This function will only execute statements up to the first NULL byte. Comments are not allowed and will lead to runtime errors.
Name | Type | Description |
---|---|---|
query | string | The SQL query to run. |
Throws:
Throw an error object if an error occurred.
Returns:
Type Promise.<undefined>
Name | Type | Attribute | Description |
---|---|---|---|
query | string | The SQL query to run. | |
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing undefined, if the result set is empty, otherwise an object containing the values for the first row. The property names correspond to the column names of the result set. It is impossible to access them by column index; the only supported way is by column name.
Type Promise.<(undefined|Object)>
Event | Callback | Description |
---|---|---|
trace | function(query):void | Invoked when an SQL statement starts to execute, with a rendering of the statement text. |
profile | function(query, time):void | Invoked when an SQL statement finishes execution, with a rendering of the statement text, and its execution time taken |
To enable event handling, call sqlite3.verbose() before registering callbacks.
Name | Type | Description |
---|---|---|
event | string | A name of an event to handle. |
callback | function | A callback for the event. |
Returns:
Type Promise.<undefined>
Name | Type | Description |
---|---|---|
query | string | The SQL query to run. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing a statement object.
Type Promise.<Statement>
Name | Type | Description |
---|---|---|
event | string | A name of an event to handle. |
Returns:
Type Promise.<undefined>
Name | Type | Description |
---|---|---|
event | string | A name of an event to handle. |
callback | function | A callback for the event. |
Returns:
Type Promise.<undefined>
Name | Type | Attribute | Description |
---|---|---|---|
query | string | The SQL query to run. | |
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing an executed statement
Type Promise.<Statement>
Returns:
Type Promise.<undefined>
Name | Type | Attribute | Description |
---|---|---|---|
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing an array, that contains an object for each result row which in turn contains the values of that row.
Type Promise.<Array.<Object>>
Name | Type | Attribute | Description |
---|---|---|---|
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
Throws:
Throw an error object if an error occurred.
Returns:
Type Promise.<null>
If you know that a query only returns a very limited number of rows, it might be more convenient to use Statement#all to retrieve all rows at once. There is currently no way to abort execution!
Name | Type | Attribute | Description |
---|---|---|---|
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
callback | function | <optional> | (function(row):void) If the result set succeeds but is empty, the callback is never called. In all other cases, the callback is called once for every retrieved row. The order of calls correspond exactly to the order of rows in the result set. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing the number of retrieved rows.
Type Promise.<Integer>
Throws:
Throw an error object if an error occurred.
Returns:
Type Promise.<null>
Using this method can leave the database locked, as the database awaits further calls to Statement#get to retrieve subsequent rows. To inform the database that you are finished retrieving rows, you should either finalize (with Statement#finalize) or reset (with Statement#reset) the statement.
Name | Type | Attribute | Description |
---|---|---|---|
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing an object that contains the values for the first row.
Type Promise.<Object>
Throws:
Throw an error object if an error occurred.
Returns:
Type Promise.<null>
The behavior is identical to the Database#run method with the difference that the statement will not be finalized after it is run. This means you can run it multiple times.
Name | Type | Attribute | Description |
---|---|---|---|
param | string | <optional> <repeatable> | When the SQL statement contains placeholders, you can pass them in here. |
Throws:
Throw an error object if an error occurred.
Returns:
A promise containing an executed statement
Type Promise.<Statement>