Version: Next

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.

danger

It is strongly discouraged to use database for offline data storage. It can cause great performance issues on Android. For such purpose, please refer to offline service call util.

Creating Database

Let's create a database inside application:

note

The components in the example are added from the code for better showcase purposes. To learn more about the subject you can refer to:

Adding Component From Code

As a best practice, Smartface recommends using the WYSIWYG editor in order to add components and styles to your page or library. To learn how to use UI Editor better, please refer to this documentation

UI Editor Basics

import Path from "@smartface/native/io/path";
import File from "@smartface/native/io/file";

let database: File = new File({
  path: Path.DataDirectory + "/database.sqlite",
});

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

TypeScript
import Path from "@smartface/native/io/path";
import File from "@smartface/native/io/file";

let database: File = 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.

caution

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':

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.

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.

caution

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

let queryResult: Database.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:

import Database from "@smartface/native/global/data/database";
let inMemoryDatabase: Database = 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.

// 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.

TypeScript
import PageSampleDesign from "generated/pages/page3";
import { Route, Router } from "@smartface/router";
import Application from "@smartface/native/application";
import ImageView from "@smartface/native/ui/imageview";
import FlexLayout from "@smartface/native/ui/flexlayout";
import Button from "@smartface/native/ui/button";
import Database from "@smartface/native/global/data/database";
import { styleableComponentMixin, styleableContainerComponentMixin } from "@smartface/styling-context";
import File from "@smartface/native/io/file";
import Path from "@smartface/native/io/path";
import Image from "@smartface/native/ui/image";


class StyleableImageView extends styleableComponentMixin(ImageView) {}
class StyleableButton extends styleableComponentMixin(Button) {}
class StyleableFlexLayout extends styleableContainerComponentMixin(FlexLayout) {}

//You should create new Page from UI-Editor and extend with it.
export default class Sample extends PageSampleDesign {
    btnFont: Object = {
        font: {
          size: 17,
          bold: true,
          italic: false,
          family: "SFProText",
          style: "Semibold",
        },
      };
      database: Database;
      imgSmartface: StyleableImageView;
      flexlayout: StyleableFlexLayout;
      btnCreateTable: StyleableButton;
      btnDropTable: StyleableButton;
      btnInsertRow: StyleableButton;
      btnPeopleCount: StyleableButton;
      
  constructor(private router?: Router, private route?: Route) {
    super({});
  }

  // The page design has been made from the code for better
  // showcase purposes. As a best practice, remove this and
  // use WYSIWYG editor to style your pages.
  centerizeTheChildrenLayout() {
    this.dispatch({
        type: "updateUserStyle",
        userStyle: {
            flexProps: {
              flexDirection: 'ROW',
              justifyContent: 'CENTER',
              alignItems: 'CENTER'
            }
        }
    })
  }


  createRandomPersonInsert(): string {
    // Gettin random char for name
    let charCode = Math.floor(Math.random() * 25 + 65);
    let name = String.fromCharCode(charCode);
    let isWorker = Math.round(Math.random());
    let age = Math.floor(Math.random() * 62 + 18);
    let 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 +
      ")"
    );
  }


  onShow() {
    super.onShow();
    const { headerBar } = this;
    Application.statusBar.visible = false;
    headerBar.visible = false;

  }

  onLoad() {
    super.onLoad();
    this.centerizeTheChildrenLayout();
    this.database = new Database({
        file: new File({ path: Path.DataDirectory + "/database.sqlite" }),
      });
    
      this.imgSmartface = new ImageView({
        image: Image.createFromFile("images://smartface.png"),
      });
      this.addChild(this.imgSmartface, "imgSmartface", ".sf-imageView", {
        width: 170,
        height: 80,
        imageFillType: "ASPECTFIT",
        margin: 20,
        flexGrow: 3,
        flexShrink: 1,
      });
    
      this.flexlayout = new StyleableFlexLayout();
    
      this.addChild(this.flexlayout, "flexlayout", ".sf-flexLayout", {
        margin: 20,
        padding: 10,
        flexGrow: 7,
        visible: true,
        flexProps: {
          flexDirection: "COLUMN",
        },
        backgroundColor: "#FFFFFF",
      });
    
      this.btnCreateTable = new Button({
        text: "Create Table Person",
        onPress: (): void => {
          this.database.query(
            "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') )"
          );
        },
      });
    
      this.flexlayout.addChild(
        this.btnCreateTable,
        "btnCreateTable",
        ".sf-button",
        {
          backgroundColor: "rgba(255, 0, 161, 241)",
          textColor: "#FFFFFF",
          font: this.btnFont,
          flexGrow: 1,
        }
      );
    
      this.btnDropTable = new Button({
        text: "Drop Table Person",
        onPress: (): void => {
          this.database.query("DROP TABLE 'person'");
        },
      });
    
      this.flexlayout.addChild(this.btnDropTable, "btnDropTable", ".sf-button", {
        backgroundColor: "rgba(255, 0, 161, 241)",
        textColor: "#FFFFFF",
        font: this.btnFont,
        flexGrow: 1,
      });
    
      this.btnInsertRow = new Button({
        text: "Insert Row to Person Table",
        onPress: (): void => {
          this.database.query(this.createRandomPersonInsert());
        },
      });
    
      this.flexlayout.addChild(this.btnInsertRow, "btnInsertRow", ".sf-button", {
        backgroundColor: "rgba(255, 0, 161, 241)",
        textColor: "#FFFFFF",
        font: this.btnFont,
        flexGrow: 1,
      });
    
      this.btnPeopleCount = new Button({
        text: "Get People Count",
        onPress: (): void => {
          let queryResult = this.database.query("SELECT * FROM person");
          alert("People Count is: " + queryResult.count());
        },
      });
    
      this.flexlayout.addChild(
        this.btnPeopleCount,
        "btnPeopleCount",
        ".sf-button",
        {
          backgroundColor: "rgba(255, 0, 161, 241)",
          textColor: "#FFFFFF",
          font: this.btnFont,
          flexGrow: 1,
        }
      );             
  }
}

Database

Creating Database​

Creating Table​

Inserting Data to Table​

Getting Data from Database​

In-Memory Database​

Performance Tips​

Sample Page Code with Database​