Cordova SQLite Tutorial

Hello friend,

Tired of searching the Cordova SQLite storage example? No worries, I have written a step by step tutorial for integrating SQLite with your Cordova (PhoneGap) apps.

What is SQLite?

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.

What we’ll learn

• Create a new SQLite Database
• Create a New Table
• Insert Data
• Read Data
• Update Data
• Delete Data
• Drop table
• Drop Database

Step by Step Guide for PhoneGap SQLite tutorial

Step 1: Create a new PhoneGap / Apache Cordova Project

Cordova create SQLiteExample com.codesundar.sqlite SQLiteExample
cd SQLiteExample

Step 2: Let’s add platform and plugin to your project

cordova platform add android
cordova plugin add cordova-sqlite-storage

Dependancies : I used Ionic CSS Components (ionic.css) & Jquery (jquery.js) with this example

Now we’re going to perform CRUD operation with Apache Cordova / PhoneGap SQLite.

Database Name : mySQLite.db

Table Name : codesundar

Table Fields : id, title, desc

Functions / Methods for database Operations

openDatabase – Create database connection

transaction – create transactions

executeSql – executes Query with database

SYNTAX

myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> </span>{
    <span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"QUERY"</span>;
    transaction.executeSql(executeQuery, [],
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> </span>{
            <span class="hljs-comment">//Success</span>
        },
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> </span>{
            <span class="hljs-comment">// Error</span>
        });
})

Creating New Database using PhoneGap with SQLite Plugin

First, we need to create / open a database.

<span class="hljs-keyword">var</span> myDB = <span class="hljs-built_in">window</span>.sqlitePlugin.openDatabase({name: <span class="hljs-string">"mySQLite.db"</span>, location: <span class="hljs-string">'default'</span>});

Creating New Table using PhoneGap with SQLite Plugin

Using the myDB variable we need to execute the command.use CREATE a query for creating a table

myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> {</span>
    transaction.executeSql('CREATE TABLE IF NOT EXISTS codesundar (id integer primary key, title <span class="hljs-built_in">text</span>, desc <span class="hljs-built_in">text</span>)', [],
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> {</span>
            alert(<span class="hljs-string">"Table created successfully"</span>);
        },
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> {</span>
            alert(<span class="hljs-string">"Error occurred while creating the table."</span>);
        });
});

Insert data into codesundar table

Inserting data to the table. Here I used static data, instead of that you can pass the data in dynamic. Use INSERT query for inserting data to SQLite database

<span class="hljs-keyword">var</span> title = <span class="hljs-string">"sundaravel"</span>;
<span class="hljs-keyword">var</span> desc = <span class="hljs-string">"phonegap freelancer"</span>;
myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> </span>{
    <span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"INSERT INTO codesundar (title, desc) VALUES (?, ?)"</span>;
    transaction.executeSql(executeQuery, [title, desc], <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> </span>{
            alert(<span class="hljs-string">'Inserted'</span>);
        },
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> </span>{
            alert(<span class="hljs-string">'Error occurred'</span>);
        });
});

View All Data

for reading all data from table execute SELECT the query

myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">transaction</span>) </span>{
    transaction.executeSql(<span class="hljs-string">'SELECT * FROM codesundar'</span>, [], <span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">tx, results</span>) </span>{
        <span class="hljs-keyword">var</span> len = results.rows.length,
            i;
        $(<span class="hljs-string">"#rowCount"</span>).append(len);
        <span class="hljs-keyword">for</span> (i = <span class="hljs-number">0</span>; i < len; i++) {
            $(<span class="hljs-string">"#TableData"</span>).append(<span class="hljs-string">"<tr><td>"</span> + results.rows.item(i).id + <span class="hljs-string">"</td><td>"</span> + results.rows.item(i).title + <span class="hljs-string">"</td><td>"</span> + results.rows.item(i).desc + <span class="hljs-string">"</td></tr>"</span>);
        }
    }, <span class="hljs-literal">null</span>);
});

Update Data to SQLite Database Table

use UPDATE Query for updating the database with id

$(<span class="hljs-string">"#update"</span>).click(<span class="hljs-function"><span class="hljs-keyword">function</span>() </span>{
    <span class="hljs-keyword">var</span> id = $(<span class="hljs-string">"#id"</span>).text();
    <span class="hljs-keyword">var</span> title = $(<span class="hljs-string">"#title"</span>).val();
    <span class="hljs-keyword">var</span> desc = $(<span class="hljs-string">"#desc"</span>).val()
    myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">transaction</span>) </span>{
        <span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"UPDATE codesundar SET title=?, desc=? WHERE id=?"</span>;
        transaction.executeSql(executeQuery, [title, desc, id],
            <span class="hljs-comment">//On Success</span>
            <span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">tx, result</span>) </span>{
                alert(<span class="hljs-string">'Updated successfully'</span>);
            },
            <span class="hljs-comment">//On Error</span>
            <span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">error</span>) </span>{
                alert(<span class="hljs-string">'Something went Wrong'</span>);
            });
    });
});

Delete Data from table

use DELETE Query for updating the database with id

myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> </span>{
    <span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"DELETE FROM codesundar where id=?"</span>;
    transaction.executeSql(executeQuery, [id],
        <span class="hljs-comment">//On Success</span>
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> </span>{
            alert(<span class="hljs-string">'Delete successfully'</span>);
        },
        <span class="hljs-comment">//On Error</span>
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> </span>{
            alert(<span class="hljs-string">'Something went Wrong'</span>);
        });
});

Delete entire table

use DROP Query for updating the database with id

myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> </span>{
    <span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"DROP TABLE IF EXISTS codesundar"</span>;
    transaction.executeSql(executeQuery, [],
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> </span>{
            alert(<span class="hljs-string">'Table deleted successfully.'</span>);
        },
        <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> </span>{
            alert(<span class="hljs-string">'Error occurred while droping the table.'</span>);
        }
    );
});

Download