ObjectSQLite

SQLite objects are built-in modulesdbA member of the SQLite database, mainly responsible for the connection and operation of the SQLite database, and can be used for the creation, query, insertion, update and other operations of the SQLite database. At the same time, the SQLite object also provides some advanced operations such as backup and formatting SQL. SQLite connection objects also support transaction operations

In practical applications, we usually create SQLite-type data tables based on business needs, and then perform operations such as adding, deleting, modifying, and querying data, for example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
var db = require('db')

// open a SQLite database
var sqlite = db.openSQLite('test.db')

// use execute method to create a table
sqlite.execute('CREATE TABLE test (id INT PRIMARY KEY NOT NULL, name TEXT NOT NULL, age INT NOT NULL)')

// use execute method to insert data
sqlite.execute('INSERT INTO test (id, name, age) VALUES (?, ?, ?)', 1, 'Alice', 18)
sqlite.execute('INSERT INTO test (id, name, age) VALUES (?, ?, ?)', 2, 'Bob', 20)
sqlite.execute('INSERT INTO test (id, name, age) VALUES (?, ?, ?)', 3, 'Charlie', 22)

// use execute method to query data
var rs = sqlite.execute('SELECT * FROM test')
console.log(rs)

// use execute method to update data
sqlite.execute('UPDATE test SET name=?, age=? WHERE id=?', 'Marry', 19, 1)

// use execute method to delete data
sqlite.execute('DELETE FROM test WHERE id=?', 2)

SQLite also has a built-in vec_index module. We can create an index on the vector field on the SQLite database, search based on the vector field, and obtain the vector set that is most similar to the target vector. Supports using numerical type arrays to represent vectors, such as [1, 2, 3], and supports vector dimensions. Additionally, vec_index supports batch operations within a transaction.

Here's a simple example:

1
2
3
4
5
6
7
8
var db = require('db');
var path = require('path');

var conn = db.openSQLite(path.join(__dirname, 'vec_test.db'));

conn.execute('create virtual table vindex using vec_index(title(3), description(3))');

conn.execute(`insert into vindex(title, description, rowid) values("[1,2,3]", "[3,4,5]", 3)`);

Vector retrieval can be performed using the vec_search function, for example:

1
2
3
4
var key = [1, 2, 5.1234];
var limit = 1;

var res = conn.execute(`select rowid, distance from vindex where vec_search(title, "${JSON.stringify(key)}")`);

vec_search returns a collection of closest vectors and an array of distances, where distances are ordered from small to large. If you need to return multiple closest vector sets, you can use the :limit parameter, for example:

1
2
3
4
var key = [1, 2, 5.1234];
var limit = 1;

var res = conn.execute(`select rowid, distance from vindex where vec_search(title, "${JSON.stringify(key)}:10")`);

inheritance relationship

member properties

fileName

String, current database file name

1
readonly String SQLite.fileName;

timeout

Integer, query and set database timeout, in milliseconds

1Integer SQLite.timeout;

type

String, query the current connection database type

1
readonly String SQLite.type;

member function

backup

Back up the current database to a new file

1
SQLite.backup(String fileName) async;

Call parameters:

fileName: String, specify the backup database file name

close

Close current database connection

1
SQLite.close() async;

use

Select the default database for the current database connection

1
SQLite.use(String dbName) async;

Call parameters:

dbName: String, specify the database name

begin

Start a transaction on the current database connection

1
SQLite.begin(String point = "") async;

Call parameters:

point: String, specifies the name of the transaction, not specified by default

commit

Commit the transaction on the current database connection

1
SQLite.commit(String point = "") async;

Call parameters:

point: String, specifies the name of the transaction, not specified by default

rollback

Rollback a transaction on the current database connection

1
SQLite.rollback(String point = "") async;

Call parameters:

point: String, specifies the name of the transaction, not specified by default

trans

Enter a transaction to execute a function, and commit or rollback based on the execution of the function.

1
Boolean SQLite.trans(Function func);

Call parameters:

func: Function, a function executed in a transactional manner

Return results:

Boolean, returns whether the transaction is committed, returns true for normal commit, returns false for rollback, and throws an error if the transaction goes wrong.

There are three results of func execution:

The function returns normally, including end of operation and active return, at which time the transaction will be automatically committed.
The function returns false and the transaction will be rolled back
Function operation error, transaction automatically rolled back

Enter a transaction to execute a function, and commit or rollback based on the execution of the function.

1
2
Boolean SQLite.trans(String point,
    Function func);

Call parameters:

point: String, specifies the name of the transaction
func: Function, a function executed in a transactional manner

Return results:

Boolean, returns whether the transaction is committed, returns true for normal commit, returns false for rollback, and throws an error if the transaction goes wrong.

There are three results of func execution:

The function returns normally, including end of operation and active return, at which time the transaction will be automatically committed.
The function returns false and the transaction will be rolled back
Function operation error, transaction automatically rolled back

execute

Execute a sql command and return the execution result

1
NArray SQLite.execute(String sql) async;

Call parameters:

sql: String, string

Return results:

NArray, returns an array containing result records. If the request is UPDATE or INSERT, the returned result will also include affected and insertId. mssql does not support insertId.

Execute a sql command and return the execution result. The string can be formatted according to the parameters.

1
2
NArray SQLite.execute(String sql,
    ...args) async;

Call parameters:

sql: String, format string, optional parameters are specified with ?. For example: 'SELECT FROM TEST WHERE [id]=?'
args: ..., optional parameter list

Return results:

NArray, returns an array containing result records. If the request is UPDATE or INSERT, the returned result will also include affected and insertId. mssql does not support insertId.

createTable

Create data table

1
SQLite.createTable(Object opts) async;

Call parameters:

opts: Object, parameter list

dropTable

Delete data table

1
SQLite.dropTable(Object opts) async;

Call parameters:

opts: Object, parameter list

createIndex

Create data table index

1
SQLite.createIndex(Object opts) async;

Call parameters:

opts: Object, parameter list

dropIndex

Delete data table index

1
SQLite.dropIndex(Object opts) async;

Call parameters:

opts: Object, parameter list

insert

Insert new record

1
Number SQLite.insert(Object opts) async;

Call parameters:

opts: Object, parameter list

Return results:

Number, returns the id containing the insertion, or 0 if the engine does not support it.

find

Query data based on specified conditions

1
NArray SQLite.find(Object opts) async;

Call parameters:

opts: Object, parameter list

Return results:

NArray, returns records containing results

count

Count the number of data records based on specified conditions

1
Integer SQLite.count(Object opts) async;

Call parameters:

opts: Object, parameter list

Return results:

Integer, returns the number of records containing the result

update

Update data based on specified conditions

1
Integer SQLite.update(Object opts) async;

Call parameters:

opts: Object, parameter list

Return results:

Integer, returns the number of records containing updates

remove

Delete data based on specified conditions

1
Integer SQLite.remove(Object opts) async;

Call parameters:

opts: Object, optional parameter list

Return results:

Integer, returns the number of records containing updates

format

Format a sql command and return the formatted result

1
2
String SQLite.format(String method,
    Object opts);

Call parameters:

method: String, specifies the requested method
opts: Object, optional parameter list

Return results:

String, returns the formatted sql command

Format a sql command and return the formatted result

1
2
String SQLite.format(String sql,
    ...args);

Call parameters:

sql: String, format string, optional parameters are specified with ?. For example: 'SELECT FROM TEST WHERE [id]=?'
args: ..., optional parameter list

Return results:

String, returns the formatted sql command

toString

Returns the string representation of the object. Generally, "[Native Object]" is returned. The object can be re-implemented according to its own characteristics.

1
String SQLite.toString();

Return results:

String, returns the string representation of the object

toJSON

Returns a JSON format representation of the object, generally returning a collection of readable properties defined by the object.

1
Value SQLite.toJSON(String key = "");

Call parameters:

key: String, not used

Return results:

Value, returns a value containing JSON serializable

Development Guide

Basic module

built-in objects

community module

ObjectSQLite

inheritance relationship

member properties

fileName

timeout

type

member function

backup

close

use

begin

commit

rollback

trans

execute

createTable

dropTable

createIndex

dropIndex

insert

find

count

update

remove

format

toString

toJSON