How to execute the SQL created by SQL Database Chain?

[almirb]

Hi!

I'm creating an agent that can search in documents store OR return info from database. After struggling a little with the SQL Database Chain, I understood that this agent creates but doesn't execute the query.
The other solution is to build a more complex flow using the SQL Prompt template (I was trying to avoid that).

In the chatflow below, what I need to connect to SQL Database Chain to allow running a Custom JS Function for executing and returning the results in the answer?

Thanks!

[DEMONKINGKAI]

I would also like to know how to do it

[victornain]

Maybe you'd like to take a different approach, and this tutorial might help with that: https://youtu.be/OejuvdyN_U8?si=n7hEzY9cSPovmTxL

[MaskedLlama04]

@almirb hello, I have actually found your question and would love to know if you actually got the answer.
I'm trying a similar thing, but reading from a sqlite database and then generating some sql code with agents. Then this code will be executed. How would that be?

[HenryHengZJ]

https://docs.flowiseai.com/tutorials/sql-agent

[almirb]

That's amazing!! @MaskedLlama04 look at that!!

Thanks @HenryHengZJ ! 👊

[MaskedLlama04]

@HenryHengZJ looking at the tutorial, I have been trying to adapt the example JavaScripts functions from the document you showed us to a SQLite database, currently having this.

`const { DataSource } = require('typeorm');
const path = require('path');

const DATABASE_PATH = path.join(__dirname, 'superheroes.db');

let sqlSchemaPrompt = '';

const AppDataSource = new DataSource({
type: 'sqlite',
database: DATABASE_PATH,
synchronize: false,
logging: false,
});

async function getSQLPrompt() {
try {
await AppDataSource.initialize();
const queryRunner = AppDataSource.createQueryRunner();

// Get all user-defined tables (excluding sqlite internal ones)
const tablesResult = await queryRunner.query(`
  SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'
`);

for (const tableRow of tablesResult) {
  const tableName = tableRow.name;

  const schemaInfo = await queryRunner.query(`PRAGMA table_info(${tableName})`);

  const createColumns = [];
  const columnNames = [];

  for (const column of schemaInfo) {
    const name = column.name;
    const type = column.type.toUpperCase();
    const notNull = column.notnull === 1 ? 'NOT NULL' : '';
    columnNames.push(name);
    createColumns.push(`${name} ${type} ${notNull}`);
  }

  const sqlCreateTableQuery = `CREATE TABLE ${tableName} (${createColumns.join(', ')})`;
  const sqlSelectTableQuery = `SELECT * FROM ${tableName} LIMIT 3`;

  let allValues = [];
  try {
    const rows = await queryRunner.query(sqlSelectTableQuery);
    allValues = rows.map(row =>
      columnNames.map(col => row[col]).join(' ')
    );
  } catch (err) {
    allValues.push('[ERROR FETCHING ROWS]');
  }

  sqlSchemaPrompt +=
    sqlCreateTableQuery + '\n' +
    sqlSelectTableQuery + '\n' +
    columnNames.join(' ') + '\n' +
    allValues.join('\n') + '\n\n';
}

await queryRunner.release();

} catch (err) {
console.error(err);
throw err;
}
}

// Call and return the prompt
await getSQLPrompt();
return sqlSchemaPrompt;
`
However, looks like there are problems using the sqlite connection. Is it because of my code or because its yet not implemented on Flowise? I have not found an example of this kind of connection using sqlite, thus making me doubt. What would it be like then?

[MaskedLlama04]

@HenryHengZJ even tried to do a custom tool with this so the agents can work with it but nothing.

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

class SQLiteTool {
async queryDatabase(query) {
const db = new sqlite3.Database('./superhero.db');

return new Promise((resolve, reject) => {
  db.all(query, [], (err, rows) => {
    db.close();
    if (err) {
      reject(err);
    } else {
      resolve(rows);
    }
  });
});

}

async call(query) {
try {
const result = await this.queryDatabase(query);
return JSON.stringify(result);
} catch (error) {
return Error: ${error.message};
}
}
}

module.exports = { default: SQLiteTool };`

It often hits me with

{
"statusCode": 500,
"success": false,
"message": "Error: nodesService.executeCustomFunction - Error running custom function: Error: VMError: Cannot find module 'sqlite3'",
"stack": {}
}