Implement front-end SQLite database operations using

Preface

In web application development, the importance of front-end data processing is increasing. In order to achieve more efficient front-end data management, especially when processing structured data, an excellent solution is provided. It is a library that compiles the SQLite database into JavaScript, allowing developers to directly operate the SQLite database in the browser environment. This tutorial will introduce in detail how to implement the associated operations of multiple tables to improve the flexibility and efficiency of front-end data processing.

What is

It is a SQLite database implemented in JavaScript. SQLite is a self-contained, serverless, zero configuration, transactional SQL database engine. In short, it provides a way to easily manipulate structured data in a browser.

Preparation

Before we start, we need to make sure to download it first. You can get it in the following ways:

Installed via NPM

npm install

Introduced via CDN

<script src="/ajax/libs//1.6.1/"></script>

User Guide

Next, let's look at how to use it in the code.

1. Initialize the database

First, we need to create a database instance. You can do this using a simple piece of JavaScript code:

const initSqlJs = ;

initSqlJs().then(function (SQL) {
  // Create a new database  const db = new ();
  
  // Output database object  (db);
});

2. Create a table

Once we have the database instance, we can start creating tables. We will create a simple user information table, including two fields: id and name:

initSqlJs().then(function (SQL) {
  const db = new ();
  
  // Create a user table  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  
  ("Table created successfully!");
});

3. Insert data

After the table is created, we can insert some data:

initSqlJs().then(function (SQL) {
  const db = new ();
  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");

  // Insert some data  ("INSERT INTO users (id, name) VALUES (?, ?)", [1, 'Alice']);
  ("INSERT INTO users (id, name) VALUES (?, ?)", [2, 'Bob']);

  ("Data inserted successfully!");
});

4. Query data

After inserting the data, we can query the data. The following code shows how to query data from a database:

initSqlJs().then(function (SQL) {
  const db = new ();
  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  ("INSERT INTO users (id, name) VALUES (?, ?)", [1, 'Alice']);
  ("INSERT INTO users (id, name) VALUES (?, ?)", [2, 'Bob']);
  
  // Query data  const res = ("SELECT * FROM users");

  // Output query results  (res);
});

5. Update data

Sometimes we need to update the data that already exists. The following code shows how to update data in the user table:

initSqlJs().then(function (SQL) {
  const db = new ();
  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  ("INSERT INTO users (id, name) VALUES (?, ?)", [1, 'Alice']);
  ("INSERT INTO users (id, name) VALUES (?, ?)", [2, 'Bob']);

  // Update data  ("UPDATE users SET name = ? WHERE id = ?", ['Charlie', 1]);

  // Query updated data  const res = ("SELECT * FROM users");

  (res);
});

6. Delete data

Finally, let's look at how to delete the data:

initSqlJs().then(function (SQL) {
  const db = new ();
  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  ("INSERT INTO users (id, name) VALUES (?, ?)", [1, 'Alice']);
  ("INSERT INTO users (id, name) VALUES (?, ?)", [2, 'Bob']);

  // Delete data  ("DELETE FROM users WHERE id = ?", [1]);

  // Query the deleted data  const res = ("SELECT * FROM users");

  (res);
});

Advanced operations: Multiple table associations

In actual application scenarios, multiple tables are usually needed to complete more complex data queries and operations.

1. Create multiple tables

Next, we create two tables: users and orders. The users table stores user information, and the orders table stores user order information, and is associated to the users table through user_id.

initSqlJs().then(function (SQL) {
  const db = new ();
  
  // Create user table  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  
  // Create an order table  ("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, product TEXT)");
  
  ("Tables created successfully!");
});

2. Insert data

After creating the table, we insert some data into the table:

initSqlJs().then(function (SQL) {
  const db = new ();
  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  ("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, product TEXT)");
  
  // Insert user data  ("INSERT INTO users (id, name) VALUES (?, ?)", [1, 'Alice']);
  ("INSERT INTO users (id, name) VALUES (?, ?)", [2, 'Bob']);
  
  // Insert order data  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [1, 1, 'Laptop']);
  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [2, 1, 'Phone']);
  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [3, 2, 'Tablet']);
  
  ("Data inserted successfully!");
});

3. Query associated data

We can obtain the data of the associated table through SQL joint query. For example, get all users and their corresponding order information:

initSqlJs().then(function (SQL) {
  const db = new ();
  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  ("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, product TEXT)");
  ("INSERT INTO users (id, name) VALUES (?, ?)", [1, 'Alice']);
  ("INSERT INTO users (id, name) VALUES (?, ?)", [2, 'Bob']);
  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [1, 1, 'Laptop']);
  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [2, 1, 'Phone']);
  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [3, 2, 'Tablet']);
  
  // Jointly query users and their order data  const res = (`
    SELECT  AS user_name,  AS product
    FROM users
    JOIN orders ON  = orders.user_id
  `);
  
  (res);
});

4. Process the query results

The result returned is an array, each element in the array is an object, representing a table of query results. You need to parse this result to show the data:

initSqlJs().then(function (SQL) {
  const db = new ();
  ("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  ("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, product TEXT)");
  ("INSERT INTO users (id, name) VALUES (?, ?)", [1, 'Alice']);
  ("INSERT INTO users (id, name) VALUES (?, ?)", [2, 'Bob']);
  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [1, 1, 'Laptop']);
  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [2, 1, 'Phone']);
  ("INSERT INTO orders (id, user_id, product) VALUES (?, ?, ?)", [3, 2, 'Tablet']);
  
  const res = (`
    SELECT  AS user_name,  AS product
    FROM users
    JOIN orders ON  = orders.user_id
  `);
  
  // parse the query results  if ( &gt; 0) {
    const columns = res[0].columns;
    const values = res[0].values;
    
    (row =&gt; {
      const record = {};
      ((value, index) =&gt; {
        record[columns[index]] = value;
      });
      (record);  // Output every record    });
  }
});

Summarize

With , we can easily manipulate SQLite databases in our browser. It provides a powerful tool that enables us to perform complex data operations on the front end without relying on the backend server. This means we can build more responsive and flexible web applications.

This is the end of this article about using front-end SQLite database operations. For more related content to implement SQLite database operations, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!