MENU

Create database sqlite encrypted with node.js

Hi, first sorry for the following text, I can not speak English and I’m using google translator to write this. But I think you can understand.

To create the database files encrypted with node.js I’m using sqlite-cipher package, developed by me using sql.js and crypto-js. The usage of this package is very simple, with it you can encrypt and decrypt and create new databases encrypted. See:

Install

To install this package open your prompt or terminal and run:
$ npm install sqlite-cipher

Usage

You can open encrypted database file or create a new also encrypted. And you can encrypt or decrypt an existing file and save in another file. See how:

connect(database, password)

Opening a encrypted database file or creating a new.

1
2
3
4
5
6
7
8
9
10
11
var sqlite = require('sqlite-cipher');
//Oppening your encrypted file, you can use any extension.
sqlite.connect('myEncrypedFile.myextension','MyPassword');
//Creating a table    
sqlite.run("CREATE TABLE COMPANYS(ID  INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL);");
//Inserting the data   
sqlite.insert("COMPANYS",{NAME:"My COMPANY"}, function(inserid){
    console.log(inserid);
});
//Selecting data
console.log(sqlite.run("SELECT name FROM COMPANYS;"));

encrypt(from, to, password)

Encrypting an existing database. In the output file you can use any extension.

1
2
3
var sqlite = require('sqlite-cipher');
//Encrypting
sqlite.encrypt('myDataBase.db', 'outputFile.extension', 'MyPassword');

decrypt(from, to, password)

Decrypting an database.

1
2
3
var sqlite = require('sqlite-cipher');
//Decrypting 
sqlite.decrypt('outputFile.extension', 'myDecrypted.db', 'MyPassword');

Using SQL

Select

To use the SELECT command there are two different ways, synchronous and asynchronous.

Synchronous

This way will be returning an array of JSON objects with the records returned from the database:

1
2
3
 
var rows = sqlite.run("SELECT * FROM myTable");
conosole.log(rows);

You can also pass the clauses where per parameter in an array, and the references in SQL will be replaced in the wake of the contents of the array, as follows:

1
2
3
var rows = sqlite.run("SELECT * FROM myTable WHERE id = ? AND name = '?' ",[3, 'name']);
//SELECT * FROM myTable WHERE id = 3 AND name = 'name'
conosole.log(rows);

Asynchronous

Unlike the asynchronous method is that you must pass a callback function (callback) as the last parameter and must use the function runAsync(sql,[options], callback). See:

1
2
3
4
 
sqlite.runAsync("SELECT * FROM myTable", function(rows){
    conosole.log(rows);
});

or…

1
2
3
sqlite.runAsync("SELECT * FROM myTable WHERE id = ? AND name = '?' ",[1,'name'] ,function(rows){
    conosole.log(rows);
});

Insert

To run an INSERT there are four different ways, both synchronous and asynchronous two. In two of them you only need to inform the authority and the data to be entered in the form of JSON object.

Common SQL and synchronous method

This first form is the most basic of all, what happens synchronously returning the primary key of the inserted item last_insert_rowid(). Look:

1
2
 
var id = sqlite.run("INSERT INTO myTable (name) VALUES ('Jayr')");

Or. You can also pass the values to be inserted per parameter in an array

1
2
 
var id = sqlite.run("INSERT INTO myTable (name) VALUES (?)",['Jayr']);

Common SQL and asynchronous method

The only difference is asynchronously using runAsync function and passing the callback function in the parameters, and this function will return the ID of the last insert. Look:

1
2
3
4
 
sqlite.runAsync("INSERT INTO myTable (name) VALUES ('Jayr')", function(id){
   console.log(id);
});

Or…

1
2
3
sqlite.runAsync("INSERT INTO myTable (name) VALUES (?)",['Jayr'],function(id){
   console.log(id)
});

Only entity and data and synchronous method

You only informs the entity and the data in a JSON object, and the object key must match the column headings in your table in the database see an example:

1
2
3
 
var id = sqlite.insert('COMPANYS',{ID: 1, NAME: "My Company"});
console.log(id)//Last insert id

Only entity and data and asynchronous method

1
2
3
sqlite.insert('COMPANYS',{ID: 1, NAME: "My Company"},function(id){
   console.log(id) //last insert id
});

Update

You only informs the entity and the data in a JSON object, and the object key must match the column headings in your table in the database, as the third parameter you should pass the where clause. see an example:

1
2
3
4
5
6
7
var res = sqlite.update('COMPANYS',{NAME: "My Company"},{ID: 1});
// = UPDATE COMPANYS SET NAME = 'My Company' WHERE ID = 1
 
// OR...
sqlite.update('COMPANYS',{NAME: "My Company"},{ID: 1},function(res){
   console.log(res) //last insert id
});

Other commands

More information see the WIKI

Thank you.

Leave a Comment!

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *