Database

API Reference: Database

Database is query friendly persisted data interface for performing database operations using SQLite. You can open database from assets or create/open database from file system, but you can't create database inside assets.

Creating Database

Let's create a database inside application:

const Database = require('sf-core/data').Database;
const Path = require('sf-core/io/path');
const File = require('sf-core/io/file');
var database = new File({ path: Path.DataDirectory +'/database.sqlite' });

Or we can read database that exists in our assets like:

JavaScript
JavaScript
const Database = require('sf-core/data').Database;
const Path = require('sf-core/io/path');
const File = require('sf-core/io/file');
var database = new File({ path: 'assets://database.sqlite' });

Creating Table

We created database inside data directory of our application, now we can add table inside of this database.

We should use 'Database.execute' for non-select queries like table creating/dropping, data inserting/updating to table etc.

The person table has column called 'id' as a primary key, 'name' as string, 'age' as integer, 'rate' as real and 'isWorker' as integer. We can use Integer type for make boolean: 0 for false; 1 for true.

SQLite Data Types

You can check this for understand SQLite data types.

Let's create table called 'person':

JavaScript
JavaScript
database.execute("CREATE TABLE 'person' ( 'id' INTEGER, 'name' TEXT DEFAULT 'Smartface', 'age' INTEGER DEFAULT 5, 'isWorker' INTEGER DEFAULT 0, 'rate' REAL DEFAULT 2.5, PRIMARY KEY('id') )");

Inserting Data to Table

After creating person table, we can add data to this table.

JavaScript
JavaScript
database.execute("INSERT INTO person (name, age, isWorker, rate) VALUES ('George', 47, 0, 1.2)");
database.execute("INSERT INTO person (name, age, isWorker, rate) VALUES ('James', 40, 1, 3.4)");
database.execute("INSERT INTO person (name, age, isWorker, rate) VALUES ('Alex', 25, 1, 1.7)");

Getting Data from Database

Now, we created table called 'person' on data directory of application and inserted 3 new data. Let's make query for this data and alert worker count.

We should use 'Database.query' for select queries.

JavaScript
JavaScript
var queryResult = database.query("SELECT * FROM person WHERE(isWorker = 1)");
alert("Worker count is: " + queryResult.count());

Creating Database Queries

We recommend to use Squel.js library for creating SQL Queries.

In-Memory Database

In-memory database is temporary, memory-based database. It will be more faster than storage-based database due to speed differences between memory and storage. In case you downloaded big data from your API. Sometimes there is no need to write the data on disk while computing the final result.

Check this out

You can check SQLite's official documentation about in-memory databases.

You can create in-memory database like:

JavaScript
JavaScript
const Database = require('sf-core/data').Database;
var inMemoryDatabase = new Database({
inMemory: true,
});

Now, you can use in-memory database like normal database. You can create/update table, insert/update rows just like normal database.

Please Note

In-memory databases are memory-based databases. This makes in-memory databases temporary database. That means when you close application all data stored inside in-memory database will lost.

Performance Tips

One the major issues a mobile application developer encounters when using the SQLite in his applications is its performance issue if you not using them correctly.

  • Multiple insertion without BEGIN...END block may cause transaction takes too long. You should use BEGIN...END block if you want to do multiple insertion as SQLite officialy said.

  • SQLite parameters are oriented not to high performance by default, but to maximum data safety and integrity. You can use configurations called PRAGMA, modification of these parameters may increase performance, however, note, that the data corruption risks increase too.

  • If you have big transaction, you should do this transaction in background, you can use Timer. This will prevent ui from freeze.

  • You can use in-memory database if you have big and temporary data.

For testing performance and performance difference between normal and in-memory databases, we created sample person table and we insert randomized rows.

JavaScript
JavaScript
// Sample Table
CREATE TABLE 'person' ( 'id' INTEGER, 'name' TEXT DEFAULT 'Smartface', 'age' INTEGER DEFAULT 5, 'isWorker' INTEGER DEFAULT 0, 'rate' REAL DEFAULT 2.5, PRIMARY KEY('id') )
// Sample Insert Command, executed count times.
INSERT INTO person (name, age, isWorker, rate) VALUES ('F', 47, 0, 1.2)

Here is the performance differences between normal and in-memory databases:

Device

Row Count

Normal Database Execution Time (ms)

In-Memory Database Execution Time (ms)

Huawei - Nexus 6P

5000

47942

409

Huawei - Nexus 6P

10000

93597

881

iPhone 7

5000

25636

92

iPhone 7

10000

55781

180

Sample Page Code with Database

Here is the example code for running Database inside Smartface Native Framework.

JavaScript
JavaScript
const Page = require('sf-core/ui/page');
const Font = require("sf-core/ui/font");
const File = require("sf-core/io/file");
const Path = require("sf-core/io/path");
const Image = require("sf-core/ui/image");
const Color = require('sf-core/ui/color');
const extend = require("js-base/core/extend");
const Button = require('sf-core/ui/button');
const Database = require('sf-core/data').Database;
const ImageView = require("sf-core/ui/imageview");
const FlexLayout = require("sf-core/ui/flexlayout");
const ImageFillType = require("sf-core/ui/imagefilltype");
var pageDatabase = extend(Page)(
function(_super) {
_super(this, {
onShow: function() {
this.statusBar.visible = false;
this.headerBar.visible = false;
},
});
var btnFont = Font.create("Arial", 16, Font.NORMAL);
var database = new Database({
file: new File({path: Path.DataDirectory + '/database.sqlite'})
});
var imgSmartface = new ImageView({
image: Image.createFromFile("images://smartface.png"),
imageFillType: ImageFillType.ASPECTFIT,
margin: 20,
flexGrow: 3
});
var flexlayout = new FlexLayout({
visible: true,
flexDirection: FlexLayout.FlexDirection.COLUMN,
margin: 20,
padding: 10,
flexGrow: 7
});
var btnCreateTable = new Button({
text: "Create Table Person",
backgroundColor: Color.create(255, 0, 161, 241),
textColor: Color.WHITE,
font: btnFont,
flexGrow: 1,
onPress: function(){
database.execute("CREATE TABLE 'person' ( 'id' INTEGER, 'name' TEXT DEFAULT 'Smartface', 'age' INTEGER DEFAULT 5, 'isWorker' INTEGER DEFAULT 0, 'rate' REAL DEFAULT 2.5, PRIMARY KEY('id') )");
}
});
var btnDropTable = new Button({
text: "Drop Table Person",
backgroundColor: Color.create(255, 0, 161, 241),
textColor: Color.WHITE,
font: btnFont,
flexGrow: 1,
onPress: function(){
database.execute("DROP TABLE 'person'");
}
});
var btnInsertRow = new Button({
text: "Insert Row to Person Table",
backgroundColor: Color.create(255, 0, 161, 241),
textColor: Color.WHITE,
font: btnFont,
flexGrow: 1,
onPress: function(){
database.execute(createRandomPersonInsert());
}
});
var btnPeopleCount = new Button({
text: "Get People Count",
backgroundColor: Color.create(255, 0, 161, 241),
textColor: Color.WHITE,
font: btnFont,
flexGrow: 1,
onPress: function(){
var queryResult = database.query('SELECT * FROM person');
alert("People Count is: " + queryResult.count());
}
});
this.layout.addChild(imgSmartface);
flexlayout.addChild(btnCreateTable);
flexlayout.addChild(btnDropTable);
flexlayout.addChild(btnInsertRow);
flexlayout.addChild(btnPeopleCount);
this.layout.addChild(flexlayout);
}
);
function createRandomPersonInsert(){
// Gettin random char for name
var charCode = Math.floor((Math.random() * 25) + 65);
var name = String.fromCharCode(charCode);
var isWorker = Math.round(Math.random());
var age = Math.floor((Math.random() * 62) + 18);
var rate = Math.round((Math.random() * 5 + 1) *10 )/10;
// Query will be like: INSERT INTO person (name, age, isWorker, rate) VALUES ('B', 66, 1, 2.1)
return "INSERT INTO person (name, age, isWorker, rate) VALUES ('" + name +"', " + age + ", " + isWorker + ", " + rate + ")";
}
module.exports = pageDatabase;