This is a quick tutorial about coding a node.js script, so it requires you have prerequisites of knowing how to use node.js, npm and sql. Lets get started.

Install sqlite3 module using npm

> npm install sqlite3

Connect and create a new database

const sqlite3 = require('sqlite3').verbose()

// Simple connection
let db = new sqlite3.Database('dir/to/db/newdb.db')
// Advanced connection
let db = new sqlite3.Database('dir/to/db/newdb.db', OPENMODE*, (err)=>{
	CALLBACK
})

// Operations START
...
// Operations END

/* Dont forget to close the db connection at the end */
db.close() 
  • If newdb.db does not exist, it will automatically create one for you, or you can rename an empty txt file to name.db
  • OPENMODE
    • sqlite3.OPEN_READONLY
    • sqlite3.OPEN_READWRITE
    • sqlite3.OPEN_CREATE

Create a table

db.run('CREATE TABLE TBName(
	variable type,
	variable type,
	...
)')

Insert values with passing parameters

db.run(
	'INSERT INTO TBName(var1, var2, ...) VALUES(?, ?, ...)',
	['FirstCol', 'SecondCol', ...],
	(err)=>{
		CALLBACK
	}
)

Insert multiple rows at the same time

let names = ['John', 'Tom', 'Roth']
let placeholders = names.map((name) => '(?)').join(',') //"(?),(?),(?)"
db.run(
	'INSERT INTO TBName(name) VALUES ' + placeholders,
	names,
	(err)=>{
		CALLBACK
	}
)

Query data using all

# Get all rows at one time. Callback of all is a collection of rows
let query_select_project_1 = 'SELECT * FROM Project'
db.all(query_select_project_1, [], (err, rows)=>{
    if(err) throw err
    rows.forEach((row)=>{
        console.log(row)
    })
    console.log('{ END }\n')
})

Query data using each

# Get a single row at one time. Callback of each is a single row
let query_select_project_1 = 'SELECT * FROM Project'
db.each(query_select_project_1, [], (err, row)=>{
    if(err) throw err
    console.log(row)
})

Query data using get

# Get a single row at one time. Callback of get is the first row in the query result
let query_select_project_2 = 'SELECT * FROM Project WHERE name = ?'
db.get(query_select_project_2, ['Calculator'], (err, row) =>{
    if(err) throw err
    row 
        ? console.log(row)
        : console.log('Not Found')
    console.log('{ END }\n')
})

Update values

db.run(
	'UPDATE TBName
		SET var2 = ?
		WHERE var1 = ?
	', 
	['SecondCol', 'FirstCol'], 
	(err)=> {
		CALLBACK
	}
)