preface

The last article talked about how to compile the better-SQlite3 plug-in in the electron5.x environment, so this article will share how to use the better-SQlite3 plug-in.

The official documentation for better-SQlite3 is really hard to look at, so LET me summarize it with the simple features I use.

Creating a database

this.db = new Database("foobar.db");
try {
  this.db.exec(
    `CREATE TABLE Photo ( id integer primary key AUTOINCREMENT, photo_path text, is_detect bool, detect_status int, desc text, time timestamp)`
  );
} catch (error) {
  console.log(error);
  if (error.message == "table Photo already exists") {
    console.log("Photo table already exists"); }}Copy the code

Write data

const insert = this.db.prepare(
        "INSERT INTO Photo (photo_path, is_detect, detect_status, time) " +
          "VALUES (@photo_path, @is_detect, @detect_status, @time)"
      );
const insertMany = this.db.transaction((cats) = > {
        for (const cat ofcats) { insert.run(cat); }}); insertMany(arr_photo_path);Copy the code

Of course, the insert statement can be executed one by one, but if it is written this way, it should be sqLite transactions and write data much faster.

Query data

const stmt = this.db.prepare(
        "select * from Photo where is_detect=0 limit 0,1;"
      );
let obj_photo = stmt.get();
Copy the code

Here obj_photo gets the result of the query, converted into an object.

Modify the data

const update = this.db.prepare(
        "update Photo set is_detect=? where id=? ;"
      );
update.run(1.1);
Copy the code

Delete the data

 this.db.exec(
                "delete from Photo; update sqlite_sequence SET seq = 0 where name ='Photo';"
              );
Copy the code

conclusion

Ok, so now we have done the basic database add, delete, change and check function. In addition, I found that the better-sqlite3 functions are executed synchronously, which makes it easier to write some logic. If you want asynchrony, you can do it inside a callback function.