Integrating Sqlite

You need to install the sqlite3 command line tool to create a simple database file. We can then read it from our agent.

Lets create an sqlite database and add some data to it

/tmp$ sqlite3 building-data.db
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> create table IAQData(sensor varchar(255), value float, time datetime);
sqlite> insert into IAQData(sensor,value,time) values('S1',23,'2024-01-01T09:00:00Z');
sqlite> insert into IAQData(sensor,value,time) values('S2',21.2,'2024-01-01T09:00:00Z');
sqlite> insert into IAQData(sensor,value,time) values('S3',22.6,'2024-01-01T09:00:00Z');

So we have a database with some data inside the IAQData table.

Lets now read this from our agent.

We need to install the sqlite3 driver for node.js

Integrating Sqlite into your connector

First install the appropriate npm package

npm install sqlite3

Then use require to reference that module in your index.js file

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

We can now open the database.

Note that we are now hard-coding the database file path. You could change this to also accept a database file as one of the inputs from your connector definition.

Be careful when allowing the connector in Lucy to specify the file name. This could be a security issue. In general, don't let your integration directly reference any file names or network IP addresses etc... Instead, have a way of white-listing database files in your connector in some way and validating that.

Lets open the database:

```javascript
let db = new sqlite3.Database('/tmp/building-data.db', (err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Connected to the database');
});
```

This can go at the top of the file right when the program starts.

Next, lets add an async function to query the database:

```javascript
async function executeQuery(query) {
    return new Promise((resolve,reject)=>{
        db.serialize(() => {
            let results = [];
            db.all(query, (err, rows) => {
                if (err) {
                    reject(err.message);
                }
                resolve(rows);
            });
        });
    });
}
```

Finally, lets modify our processRequest() function:

```javascript
async function processRequest(payload) {
    try {

        let obj = JSON.parse(payload);
        if (obj.function == 'sqlquery') {
            let queryResults = await executeQuery(obj.query);
            console.log('returning', queryResults);
            return { result: queryResults, count: queryResults.length };
        }
        throw 'Unknown function: ' + obj.function;
    } catch (e) {
        return { result: e }
    }

}
```

The full code is here:

```javascript
const process = require('process');
const {LucyConnector} = require('lucy-node-sdk');
const sqlite3 = require('sqlite3').verbose();

const ConnectorName='SqliteConnector';
let db = new sqlite3.Database('/tmp/building-data.db', (err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Connected to the database');
});
async function executeQuery(query) {
    return new Promise((resolve,reject)=>{
        db.serialize(() => {
            let results = [];
            db.all(query, (err, rows) => {
                if (err) {
                    reject(err.message);
                }
                resolve(rows);
            });
        });
    });
}
async function processRequest(payload) {
    try {

        let obj = JSON.parse(payload);
        if (obj.function == 'sqlquery') {
            let queryResults = await executeQuery(obj.query);
            console.log('returning', queryResults);
            return { result: queryResults, count: queryResults.length };
        }
        throw 'Unknown function: ' + obj.function;
    } catch (e) {
        return { result: e }
    }

}
const connector = LucyConnector.fromInstallationKey(process.env.LUCY_CONNECTOR_KEY,ConnectorName,processRequest);
connector.init().then(()=>{

    console.log('Connector initialized');
});
```

Now when you exeute your model action you should see the data from your sqlite3 database showing up in the Lucy model!