PhoneGap SQLite: Access Data on the Go

In today’s mobile world, apps need to be able to store and access data on users’ devices. Whether you’re building a simple to-do list, chat app, or fully-featured mobile platform, some database is likely required. One popular option for cross-platform mobile development is PhoneGap, which allows you to build native apps using web technologies like HTML, CSS, and JavaScript. PhoneGap has excellent support for storing data locally on devices for SQLite databases.

SQLite is a self-contained, serverless, zero-configuration database that is perfect for apps that need to persist data on mobile devices. It is lightweight, requires no setup, and runs locally on the user’s device. Combined with PhoneGap for building apps with web tools, SQLite provides a compelling database option.

In this comprehensive guide, you’ll learn:

• How to install and set up SQLite in a PhoneGap project
• SQLite statements to create tables, insert, query, and manipulate data
• Examples of using SQLite and PhoneGap to build real-world mobile apps
• Comparison of SQLite to other PhoneGap storage options like LocalStorage
• Best practices for structuring and optimizing SQLite databases for mobile

So if you’re looking to enable mobile storage for your PhoneGap app, read on!

Installing SQLite Plugin in PhoneGap

You need to install a SQLite plugin to use SQLite in a PhoneGap project. This wraps native platform implementations of SQLite, allowing access from JavaScript. Here are the steps to add it in:

Install from NPM:

npm install cordova-sqlite-storage --save

Add the plugin to your PhoneGap config:

<plugin name="cordova-sqlite-storage" spec="~5.0.0" />

Install platforms to apply the plugin:

phonegap prepare

And that’s it! The plugin will now be available across all platforms.

Opening a Database

To open or create a database, call sqlitePlugin.openDatabase() and pass in properties like name and version:

var db = window.sqlitePlugin.openDatabase({
  name: 'my.db',  
  version: '1.0', 
  location: 'default', // iOS only
  createFromLocation: 1 // iOS only
});

This will open a database called my.db in the default data storage location.

SQLite Create Table and Insert Statements

Since SQLite supports full SQL, you can run standard CREATE TABLE and INSERT statements.

Here is an example table to store todo list items:

CREATE TABLE IF NOT EXISTS todos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title VARCHAR(100) NOT NULL, 
  completed BOOLEAN NOT NULL DEFAULT 0
);

INSERT INTO todos (title, completed) 
  VALUES ('Pick up groceries', 0);

This creates a table to store todo records with id, title, and completed properties. We insert one initial record.

To run SQL statements, execute them in a transaction:

db.transaction(tx => {

  tx.executeSql('CREATE TABLE...', [], 
  tx.executeSql('INSERT INTO...', [], 
});

The transaction ensures statements are run atomically.

Selecting Data with SQLite SELECT

To query data from a SQLite database, use the SELECT statement:

SELECT * FROM todos;

In PhoneGap, execute SELECT inside a transaction and pass in success/error callbacks:

db.transaction(tx => {

  tx.executeSql('SELECT * FROM todos', [], (tx, results) => {
    
    console.log(results.rows); // array of row objects

  }, errorCB);

});

The success callback receives the transaction and result set containing arrays of the rows.

You can add WHERE clauses to filter specific records:

SELECT * FROM todos WHERE completed = 1;

And ORDER BY to sort:

SELECT * FROM todos ORDER BY title;

SQLite supports all common SQL clauses like joins, aggregates with GROUP BY, aliases, etc that you would expect in a fully fledged database.

Query Example with Placeholder Params

For dynamic queries, use placeholder params instead of concatenating values:

db.transaction(tx => {

  tx.executeSql('SELECT * FROM todos WHERE id = ?', [todoId], (tx, results) => {

    // results for matching record

  });

});

This helps avoid SQL injection vulnerabilities by safely inserting the todoId.

Updating and Deleting Records

To modify records, use the UPDATE statement:

UPDATE todos SET completed = 1 WHERE id = 1;

And to delete records:

DELETE FROM todos WHERE id = 1;

You can wrap these SQL statements in PhoneGap transaction blocks to run them.

This covers the basics of running CRUD operations in SQLite from a PhoneGap mobile app!

Example: Building a Mobile Chat App

To demonstrate a real-world usage, let’s model a basic chat messaging database with users, contacts and messages.

First define tables:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE contacts (
  user_id INTEGER NOT NULL,
  contact_id INTEGER NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id),
  FOREIGN KEY(contact_id) REFERENCES users(id),
  PRIMARY KEY(user_id, contact_id)
);

CREATE TABLE messages (
  id INTEGER PRIMARY KEY, 
  from_id INTEGER NOT NULL,
  to_id INTEGER NOT NULL,  
  content TEXT NOT NULL,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(from_id) REFERENCES users(id),
  FOREIGN KEY(to_id) REFERENCES users(id)
);

This structures data into normalized tables with foreign key relationships linking users to contacts and messages.

Then when inserting new users, contacts or messages, run SQLite INSERT statements to persist them to SQLite. And SELECT joins across tables when querying to show user contacts and messages.

The simple yet powerful SQLite data access empowers the mobile chat application as a fully functional, offline-capable messaging platform!

LocalStorage vs SQLite Comparison

Aside from SQLite, LocalStorage in web apps provides another option to store persistent data in PhoneGap apps. But there are some key differences:

As this illustrates, SQLite supports more robust data access patterns suited for large structured datasets, while remaining lightweight and embedded directly on devices

SQLite Best Practices

Here are some top tips for optimal SQLite usage in mobile apps:

• Structure normalized tables for efficiency
• Use transactions to group statements
• Parameterize queries to prevent injection
• Create needed indexes for faster lookups
• Keep large media blobs external if possible

Following best practices helps ensure performant and scalable mobile database access even as data grows.

Conclusion

SQLite is the ideal lightweight, embedded database for PhoneGap mobile development. With full SQL support, developers can build rich apps leveraging normalized data models, complex querying/indexing, and efficient offline storage on devices.

Combined with PhoneGap’s cross-platform abilities, the SQLite database access empowers app developers to focus on creating great mobile user experiences powered by data.

So, try out SQLite within your PhoneGap workflows to enable persistence and unlock the possibilities of data-driven mobile innovation!