How does `db.serialize` in` node -sqlite3`

Question

I recently learned to use node and node-sqlite3 to control sqlite3, here is an example.

var sqlite3 = require('sqlite3'); var db = new sqlite3.Database(':memory:'); db.serialize(function() { db.run("CREATE TABLE test(info TEXT)"); db.run("INSERT INTO test (info) VALUES ('info1')"); }) db.close(); 

The documentation says that db.serialized used to ensure that SQL strings were executed in order, but I was confused, why would they not be executed in order without db.serialize , after which they will be pulled from the event queue and executed in order? How does it work here?

And if only one sql is executed, is it safe to run it without db.serialize as follows?

 var sqlite3 = require('sqlite3'); var db = new sqlite3.Database(':memory:'); db.run("CREATE TABLE test(info TEXT)"); db.close(); 

Answer 1

Each command inside the serialize() function is guaranteed to complete execution before the next run.

In your example, CREATE TABLE will complete before INSERT starts. If you did not use serialize() , the CREATE TABLE and INSERT will run in parallel. They will start so fast one after another that the INSERT can end before the table is created, which will give you an error in trying to insert data into a table that does not exist.

This is called a race condition, because every time you run your program, you can get another winner. If CREATE TABLE wins the race, the program will work fine. But if INSERT wins the race, the program will fail with an error. Since you cannot control who wins the race, serialize() will stop the INSERT from the beginning until the CREATE TABLE reaches the end, ensuring that you get the same result every time.

In your second example with only one statement, then serialize() is still required. This is because run() starts the SQL query, but immediately returns , leaving the query in the background. Since your next command is one of the close() databases, you will disable it while the query is still running.

Since serialize() not returned until the last of its internal requests has been completed, its use will hold close() until the request completes.

If you used a different type of request (say, in response to a button click on a web page where the database remains open between calls), you probably won't need serialize() . It depends only on whether the code following each request should request before it completes or not.

When deciding whether to use serialize() or not, it can be helpful to think of any unserialized requests as if they were commented out and then see if the code will work. In the first example above, deleting the CREATE TABLE command will break the next INSERT (because then the table will not be inserted in the table), so they must be serialized. But if you have two CREATE TABLE commands, then deleting one will not affect the other, so these two commands should not be serialized.

(This advice does not apply to close() , however - the rule of thumb is to only call close() when it's over.)