JavaScript SQLite: Client-Side Databases

Local storage and client-side databases allow web applications to store data directly in the browser without needing a server. This improves performance, allows offline functionality, and gives users more control over their data. SQLite is a powerful, open-source database that can be embedded directly into JavaScript web apps to enable these benefits.

Browser data storage has come a long way in recent years. What once required server round trips and complex infrastructure can now be achieved entirely client-side using simple JavaScript APIs. The SQLite database brings full relational database capabilities, including SQL querying and indexing, to the browser.

SQLite offers a simple yet highly functional database that runs directly inside a single application. It doesn’t require setting up and configuring a complex database server. This makes SQLite a convenient and portable choice for client-side storage in web applications.

In this guide, you’ll learn how to:

• Set up SQLite in a JavaScript project
• Create databases and tables
• Insert, query, and manipulate data
• Enable offline persistence

By the end, you’ll have the skills to supercharge your web apps with embedded client-side storage and searching using SQLite.

Installing SQLite with NPM

To get started with SQLite, you need to install a JavaScript library that acts as an interface to the SQLite database. The most full-featured library is sql.js.

To install:

npm install sql.js

This will allow you to create SQLite databases and interact using JavaScript in both Node.js and the browser.

Creating an SQLite Database with Javascript

Here is how to create an SQLite database with sql.js:

// Load sql.js 
const SQL = require('sql.js');

// Create the database
let db = new SQL.Database();

This db object contains our database instance. Next we can create tables inside the database and start working with data.

Creating SQLite Tables with Javascript

SQLite tables store structured data in columns and rows, just like regular SQL databases.

Here is an example query to create a table in SQLite:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT,
  content TEXT,
  author TEXT,
  created_at DATETIME
);

This would create a posts table with columns for id, title, content, author, and created_at timestamp.

To run this in sql.js:

// Create the posts table
db.run("CREATE TABLE posts (...")); // As shown above

You can also define multiple tables and relationships between them.

Inserting Data in an SQLite Table using Javascript

With the tables set up, we can start adding data to the database using INSERT queries:

INSERT INTO posts (title, content, author, created_at) VALUES (
  'Post one',
  'This is the first post!',  
  'Mary',
  '2023-01-15 12:00:00'
);

This would insert a new row into the posts table. To run an insert from JavaScript:

db.run("INSERT INTO posts VALUES (?, ?, ?, ?)", [
  "Post two",
  "The content of post two", 
  "Kyle",
  "2023-01-17 10:00:00"
]);

The ? placeholders allow passing parameters separately for safer queries.

Querying Data on an SQLite Table using Javascript

Once data is inserted into the database tables, you can query it using SELECT statements like regular SQL:

SELECT * FROM posts WHERE author="Mary";

This would get all posts written by Mary.

To run queries:

let result = db.exec("SELECT * FROM posts WHERE author = ?", ["Mary"]);

// Log the results
for (let row of result[0].values) {
  console.log(row); 
}

This iterates over the result rows and logs them to the console.

There are also methods like all() and get() that return query results in a convenient format.

Persisting the Database

A great aspect of SQLite is it keeps the entire database in a single file. This allows easy saving of the data for long-term storage and sharing across environments.

You can export the current in-memory database to a binary .sqlite file like this:

function persist() {

  // Export to an ArrayBuffer
  let buffer = db.export();  

  // Convert to Blob
  let blob = new Blob([buffer]);

  // Create download link
  let url = URL.createObjectURL(blob);
  let a = document.createElement('a');
  a.href = url;
  a.download = 'db.sqlite'; 
  document.body.appendChild(a); 
  a.click();

}

This saves the SQLite database to a file that can later be loaded to pick up where it left off.

There is also a node module called node-local storage that enables a browser-like localStorage API powered by SQLite rather than in-memory storage. This provides automatic persistence that survives restarts and clears old data based on storage limits.

Comparison to IndexedDB

IndexedDB is another client-side storage option. Here is how SQLite compares:

As this shows, SQLite provides richer functionality while IndexedDB is simpler to use. Choose SQLite if you need complex querying, filtering, and relationships.

Conclusion

I hope this article has shown the power of using SQLite for client-side data storage in web applications. Some key takeaways:

• SQLite brings the capabilities of a full relational database to browser storage using JavaScript. This unlocks faster access, offline usage, and complex querying without a server.
• The sql.js library provides a simple interface for creating/querying SQLite databases directly from JavaScript code.
• SQLite databases can be persisted long-term as simple files, shared across environments.
• For apps needing SQL capabilities like joins and indexing, SQLite is a far more capable choice than IndexedDB.

I hope you enjoyed this guide to getting started with SQLite in JavaScript web applications! Let me know if you have any other questions.