The background,

When we develop an application, we may need to use a local database instead of using a remote database through restful interfaces all the time. So we need to add a database to our application, which is basically the Electron collocation database. So, which database to choose?

This article was first published on the public account “Full stack big guy’s Cultivation road”, welcome to follow.

Second, database selection

(一) LocalStorage

Data stored in LocalStorage is stored in the browser. The storage capacity is small, perhaps less than 10 MB. It stores data in the form of key-value pairs, and there is no mechanism for associative query or conditional query.

(二) SessionStorage

SessionStorage is similar to LocalStorage except that each time a session is closed, its contents are cleared. Opening a page in a window copies the context of the top-level browsing session as the context for the new session. Different tabs pages of the same URL, where the values are different. With expiration time Settings, if you want to persist data, it can’t.

(三) WebSQL

The Web SQL database API is not part of the HTML5 specification, but it is a separate specification. WebSQL is to simulate the database on the browser, using JS to operate SQL to complete the data read and write.

(4) the Cookies

Cookie storage capacity is too small, can only store 4KB content, and every time with the server, Cookies in the same domain will be carried to the server, there is no associated query, conditional query mechanism.

The data is stored in a local file in Json format, and it’s perfectly fine to store some user configuration information this way. But to store a lot of structured data in this way, it doesn’t make sense. The main reason: manipulating data in this scheme requires loading all the data in the file into the memory of the client computer. Because there is no index mechanism, operations such as associated query and conditional query are inefficient. After a certain data is updated, the update operation needs to be persisted and the whole file needs to be rewritten.

(5) with IndexedDB

IndexedDB is an underlying API for storing large amounts of structured data on the client side. The API uses indexes for high-performance searches of data.

IndexedDB is a transactional database system similar to an SQL-based RDBMS. However, unlike RDBMSS that use fixed lists, IndexedDB is an object-oriented jS-BASED database. IndexedDB stores and retrieves objects indexed by keys. You simply specify the database schema, open the connection to the database, and then retrieve and update a series of transactions.

(6) the SQLite

Official website: sqlite.org/index.html.

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a zero-configuration database, which means that unlike other databases, we don’t need to configure it in the system.

Like other databases, the SQLite engine is not a standalone process and can be connected statically or dynamically as required by the application. SQLite accesses its stored files directly.

Features:

  1. A system that does not require a separate server process or operation;
  2. A completeSQLiteThe database is stored in a single cross-platform disk file;
  3. SQLiteIt’s self-sufficient, which means it doesn’t need any external dependencies;
  4. SQLiteTransactions are fully compatibleACIDAllows safe access from multiple processes or threads.

By comparison, we choose SQLite3 as the database of ELECTRON collocation. The use of SQLite3 and API can learn through the novice tutorial www.runoob.com/sqlite/sqli… .

Install SQLite3 and configure the environment

Download dependency packages from application terminals:

NPM install sqlite3 - save - devCopy the code

Starting the app prompts me to install AWS – SDK:

npm install --save aws-sdk
Copy the code

Start the application and report the following error, really many disasters:

It seems that installing SQLite is not that simple, and it still requires some preparation. For example, you need a Python environment (PYTHon2.7) and vsBuildTools build tools (Visual Studio 15 BuildTools 2017).

(1) Install python

My version of Python:

Download it at www.python.org/downloads. Download and install Python, check the “Add system variable path” option that is not checked by default, configure the environment variable, and test it on the terminal.

(2) Download of construction tools

I installed the Visual Studio 15 build Tool 2017

You also need to implement

NPM install Windows - build - tools - gCopy the code

Install Windows – build – tools.

(3) Start installation

1. One way

CNPM install sqlite3@latest --build-from-source --runtime=electron --target=1.7.9 --dist-url=https://atom.io/download/electron --saveCopy the code

Do not use NPM. Otherwise, an error is reported as follows:

Use CNPM to install it.

Start installing the latest version of SQLite3.

In fact, this process will try to download SQLite3 and the prebuild version of ELECTRON I specified, if not found. A proper version will be rebuilt.

2. 2

Execute the following three commands:

npm install sqlite3 --save
npm install electron-rebuild --save
.\node_modules\.bin\electron-rebuild.cmd
Copy the code

Execute either of the two solutions and the installation will be successful. Once installed, SQLite is ready to use.

SQLite visual management tool

The generated database is not a double-click open to preview data, so a visual management tool is needed. SQLite visual management tools, is to do SQLite related development necessary software. Unlike mysql, SQLite has a limited range of options for visual management tools.

(一) SQLite studio

Sqlitestudio.pl /

SQLiteStudio is a tool that helps users manage SQLite databases. The SQLiteStudio database management tool has full-featured SQLite2 and SQLite3 tools, utF8 support for view encoding, and the ability to open multiple database files simultaneously.

Features:

  1. Full-featured SQLite2 and SQLite3 tools;
  2. View encoding supports UTF8;
  3. Data can be exported in CSV, HTML, plain, SQL, and XML formats.
  4. Can open multiple database files at the same time;
  5. Support to view and edit binary fields;
  6. Lightweight, independent, without dependencies;
  7. Isolation, all in one folder system;
  8. Cross-platform, supporting many operating systems;
  9. Multilingual interfaces support many programming languages;
  10. Security, through exclusive and shared locks to achieve the processing of independent transactions, multiple processes can read data from the same database at the same time, but only one can write data;
  11. Supports database size up to 2TB.

(二) Sqlitespy

Download: www.yunqa.de/delphi/prod… .

SQLiteSpy is a visual management tool for THE SQLite3 database. It does not need to be installed, just need to lift the software to any directory and execution files to start work immediately, the software is a standalone management tool, he does not conflict with the database files and programs, very easy to operate. Users can easily execute SQL queries, views, copy and delete tables, edit cells, and view indexes, triggers, and collations with basic SQL statements.

Navicat for SQLite

Web site address: www.navicat.com.cn/products/na… .

Navicat for SQLite is a powerful and comprehensive SQLite GRAPHICAL user interface that provides a complete set of database management and development capabilities and enables optimized SQLite workflows and productivity to quickly and securely create, organize, access, and share information.

Features:

  1. Data transfer and data synchronization to easily and quickly migrate data at low cost;
  2. Can transfer different formats of data to data. The data of tables, views, and query results can be exported in Excel, Access, or CSV formats.
  3. The visual SQL creation tool helps you create, edit, and run SQL statements without worrying about syntax and the use of correct commands. Code completion and custom snippet capabilities make coding faster by suggesting keywords and reducing the need to type the same code repeatedly.

5. Operate SQLite3 database

Create the sqlite3db.js file in the background folder of your project, introduce SQLite3 and initialize the database, then create a new table, and finally insert the data.

import sq3 from "sqlite3";
const sqlite3 = sq3.verbose()
const db = new sqlite3.Database('sq3.db')

db.serialize(() = > {
    db.run("create table test(name varchar(20))".() = > {
        db.run("insert into test values('nihao')".() = > {
            db.all("select * from test".(err, res) = > {
                if (err) throw err
                console.log(JSON.stringify(res))
            })
        })
    })
})
export default db
Copy the code

Import and use in main.js

import sqlite3db from './sqlite3db'
/ / database
app.sqlite3db = sqlite3db
Copy the code

Start the application and generate the database in the root directory.

Open with SQLite Studio, add the database, and view the imported data.

Select the file path and add the database.

Select the Test table, switch to data, and view the inserted values.

The import succeeded.

Vi. References

Newsn.net/say/electro…

www.cnblogs.com/liulun/p/13…

This article was first published on the public account “Full stack big guy’s Cultivation road”, welcome to follow.

Finally, I hope you must point to like three times.

More articles are in my blog address