SQL support

Introduction

This extension supports some libraries offering access to SQL databases. The SQL frameworks analysis is based on the evaluation of the first argument of the “query()” and “execute()” method calls. The first argument is evaluated and if it corresponds to an SQL query, a ‘TypeScript SQL Query’ object is created. In the case where the first argument does not correspond to a SQL query, we evaluate the second argument if it exists. Text only and parameterized SQL queries are supported. This heuristic allows us to support a large number of SQL database frameworks.

The following libraries are supported:

PostgreSQL
MySQL
MySQL2
Microsoft SQL Server (mssql)
Oracle Server
SQLite3
Tedious

PostgreSQL

Take the following code:

var pg = require("pg");
var conString = "pg://operator:CastAIP@localhost:2280/postgres";
var client = null;

function getTables(schema, cbTables) {
    "use strict";
    client = new pg.Client(conString);
    client.connect();
    var queryTables = client.query("SELECT table_name FROM information_schema.tables WHERE table_schema='" +
                      schema + "' ORDER BY    table_name");

    queryTables.on("row", function (row, result) {
        result.addRow(row);
    });
    queryTables.on("end", function (result) {
        var tables = [];
        for (i = 0; i < result.rows.length; i += 1) {
            tables.push(result.rows[i].table_name);
        }
        client.end();
        cbTables(tables);
    });
};

In this example, a ‘TypeScript SQL Query’ object is created and a callLink between the getTables method and that query is added. The SQL Analyzer can then link that query with the corresponding table if the table exists. In the present case, this extension creates a useSelect link to the table ’tables’:

MySQL

Take the following code:

import {createConnection, QueryError, RowDataPacket} from 'mysql';

const connection = createConnection(process.env['DB']);

connection.query("UPDATE posts SET title = ? WHERE id = ?", ['Hello World', 1], (err: mysql.QueryError, result: mysql.OkPacket) => {
    console.log(result.affectedRows);
});

In this example, a ‘TypeScript SQL Query’ object is created and a ‘callLink’ between the ‘mysql.ts’ module and that query is added. The SQL Analyzer or Missing tables and procedures for Node.js can then link that query with the corresponding table if the table exists. In the present case, this extension creates a ‘useUpdate’ link to the table ‘posts’:

MySQL2

Take the following code:

const mysql = require('mysql2');

const connection = mysql.createConnection(
  'mysql://root:password@localhost:3306/test'
);

connection.addListener('error', (err) => {
  console.log(err);
});

const sql = 'SELECT * FROM users WHERE name = "Page" AND age > 45';

connection.query(sql, (err, rows, fields) => {
  if (err instanceof Error) {
    console.log(err);
    return;
  }
});

In this example, a ‘TypeScript SQL Query’ object is created and a ‘callLink’ between the ‘mysql2.ts’ module and that query is added. The SQL Analyzer or Missing tables and procedures for Node.js can then link that query with the corresponding table if the table exists. In the present case, this extension creates a ‘useSelect’ link to the table ‘users’:

Microsoft SQL Server

Take the following code:

var sql = require('mssql');

app.delete('/rest/todos/:todo_id', function doDelete(req, res) {
    'use strict';
    var id = req.params.todo_id,
        query = 'DELETE FROM TODO WHERE ID=\'' + id + '\'';
    new sql.Request().query(query, function (error) {
        if (error) {
            console.log('delete', error);
            res.status(404).send('Error when clearing completed TODOs.');
            return;
        }
        res.end();
    });
});

In this example, a ‘TypeScript SQL Query’ object is created and a callLink between the anonymous function and that query is added. The sql analyzer can then link that query with the corresponding table if the table exists. In the present case, this extension creates a useDelete link to the table ‘TODO’:

Oracle Server

Take the following code:

var oracledb = require('oracledb');

oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  },
  function doSelect(err, connection)
  {
    if (err) { console.error(err); return; }
    connection.execute(
      "SELECT department_id, department_name "
    + "FROM titles "
    + "WHERE department_id < 70 "
    + "ORDER BY department_id",
      function(err, result)
      {
        if (err) { console.error(err); return; }
        console.log(result.rows);
      });
  });
};

In this example, a ‘TypeScript SQL Query’ object is created and a callLink between the anonymous function and that query is added. The SQL Analyzer can then link that query with the corresponding table if the table exists. In the present case, this extension creates a useSelect link to the table ’titles’:

SQLite3

Take the following code:

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database("data.sqlite");

db.serialize(() => {
  db.run("CREATE TABLE lorem (info TEXT)");

  const stmt = db.prepare("INSERT INTO lorem VALUES (?)");
  for (let i = 0; i < 10; i++) {
    stmt.run("Ipsum " + i);
  }
  stmt.finalize();

  db.each("SELECT rowid AS id, info FROM lorem");
});

In this example, three ‘TypeScript SQL Query’ objects are created and three callLink between the anonymous function and these query are added. The SQL Analyzer can then link that query with the corresponding table if the table exists:

Tedious

Take the following code:

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

const connection = new Connection(config);

connection.connect((err) => {
  const sql = 'CREATE TABLE ${table} ([c1] [int]  DEFAULT 58, [c2] [varchar](30))';
  const request = new Request(sql, (err) => {
    if (err) {
      throw err;
    }
  });
  connection.execSql(request);
});