How to use Microsoft SQL Server with JavaScript

This is a memo on how to use Microsoft SQL Server (MSSQL) with JavaScript.

The library is tedious, which is also recommended by Microsoft.

tedios is a callback style library, but it is written in async, await, and TypeScript in a modern style.

install

npm install tedious
npm install @types/tedious

sample

import {
    Connection,
    ConnectionConfig,
    Request,
    TYPES,
    TediousType,
} from 'tedious'

interface inputParam {
    name: string;
    type: TediousType;
    value: any;
};

async function createConnection(): Promise<Connection> {

    return new Promise((resolve, reject) => {

        const config: ConnectionConfig = {
            server: '<server_url>',
            authentication: {
                type: 'default',
                options: {
                    userName: '<user_name>',
                    password: '<password>',
                },
            },
            options: {
                database: '<db_name>',
                encrypt: true,
                debug: {
                    // debug output
                    token: true,
                },
            }
        };

        const con = new Connection(config);

        con.on('debug', (msg) => {
            console.log(msg);
        });

        con.on('connect', (err) => {
            if (!err) {
                resolve(con);
            } else {
                reject(err);
            }
        });

        con.connect();
    });
}

function execSql(con: Connection, sql: string, params: Array<inputParam>): Promise<Array<any>> {

    return new Promise((resolve, reject) => {

        const rows: Array<any> = [];

        const request = new Request(sql, (err, rowCount) => {
            if (err) {
                reject(err);
            } else {
                console.log(`done : rowCount->${rowCount}`);
                resolve(rows);
            }
        });

        for (const param of params) {
            request.addParameter(param.name, param.type, param.value);
        }

        request.on('row', (cols) => {
            const row: any = {};
            cols.forEach(col => {
                row[col.metadata.colName] = col.value;
            });
            rows.push(row);
        });

        con.execSql(request);
    });
}

(async () => {

    let con: Connection;

    try {

        con = await createConnection();

        const sql = `
            SELECT
             *
            FROM [dbo].[tbl_user]
            WHERE [user_name] LIKE @param_user_name
        `;

        const params: Array<inputParam> = [
            {
                name: 'param_user_name',
                type: TYPES.NVarChar,
                value: 'a%',
            }
        ];

        const rows = await execSql(con, sql, params);

        for (const row of rows) {
            console.log(row);
        }

        await con.close();

    } catch (err) {
        console.error(err);
        await con.close();
    }
})();

Description.

query execution

When the query is executed, the row event is invoked for each line, and when the query ends, the callback function registered when Request was generated is invoked.

Executing execSql() returns an array of results.

query parameter

You can embed query parameters in your query with @<param_name>. It is absolutely necessary to use this method when you want to pass values to the query, since it performs escaping.

When no parameters are used, an empty array is passed to execSql().

Parameters require a parameter name name, a data type type, and a parameter value value, and a type inputParam is defined to prevent input errors.

The data type is defined as TYPES. If the type is incorrect, an error will occur, but the error statement will indicate the correct type so you know how to correct it.

debug

For debugging, change the options.debug.token option of Connection to true.

Then the debug event will be fired, so register the debug event handler to display the message.

Welcome to new things

[Technical] [Electronic work] [Gadget] [Game] memo writing

How to use Microsoft SQL Server with JavaScript

install

sample