Abul Hasan Lakhani

Posted on Jun 24, 2020 • Edited on Mar 5, 2021

Connecting To SQL Server from Electron & React

#electronjs #react #tediousjs #javascript

Introduction

Hello everyone,

Recently, I was looking for some examples of database applications written in Electron and React, for one of my project I am working on these days. Almost all the good examples that I found were node apps using the NoSQL database as a back-end, like MongoDB.

I wanted something that shows how to integrate Microsoft SQL Server database, NodeJS, ElectronJS, and ReactJS, end-to-end. So I decided to write a sample app that does exactly that...

So in this post, I am gonna show you how we can use Tedious, to connect and query MS SQL Server database from our Electron app's renderer process, using React and Typescript for front-end.

Let's get started first by setting up the prerequisites, and then installing and configuring project dependencies.

(NOTE: If you already have a SQL Server Installation with any Database and also a working Electron/React App then skip over to Installing TediousJS section)

Installation & Configuration of Prerequisites:

Setting up the project

As setting up Electron, React and TypeScript with all the bells & whistles can take quite a bit of time and a daunting task for a beginner, to make things easy I have already set up a sample GIT repo so you all can easily clone and follow along by just doing npm install and npm start.

If anyone interested to know more details on how and why I have configured things in a certain way, then take a look at ALEX DEVERO BLOG's Part 2 of 5, where He explains the tech stack and all the dependencies in detail. Of course, I have bumped up all the dependencies to the latest versions (fairly latest 😃)

Clone the repo:

git clone https://github.com/abulhasanlakhani/NodeElectronReactSample NERS
Install Dependencies:

npm i
Run the App:

npm start

You should see a barebone Electron, React, TypeScript App like this:

Setting Up Other Dependencies:

For this post, we will need a SQL Server installation. Any version would work but I have got SQL Server 2019 Express Edition on my machine.

You can use any database you like to follow along but I have got AdventureWorks setup on my installation. You can download full backup here:

AdventureWorks2019

NOTE: Setting up SQL Server is out of scope for this article but you can download the express edition HERE and install it.

Also a note on SQL Server Authentication. You will need to configure either Windows or SQL Server Authentication to be able to connect successfully to your server. I am using Windows Authentication and you can follow either this article or Microsoft's article to create a new login. Then assign the newly created login to your database

Installing TediousJS NPM Package

TediousJS is a Node package that provides an implementation of the TDS protocol, which is used to interact with instances of Microsoft's SQL Server. I chose this package because it's been actively maintained and supported. The documentation is not very intuitive but it's not bad either.

npm install tedious

We are now ready to write our service or module to query SQL server database

Writing the Service Module:

Start by creating a new sub-folder in the src folder of your electron app and adding a new javascript file under it. You can name it anything you want, I call it sqlservice.js:

Then require the following packages from tedious at the top like this:

sqlservice.js



const Connection = require("tedious").Connection
const Request = require("tedious").Request
const async = require('async');

A Connection instance represents a single connection to a database server.

Only one request at a time may be executed on a connection.

A Request instance represents a request that can be executed on a connection.

Preparing The Configuration Object

Before we can connect to our database through Tedious, we need to prepare a configuration object, so we can later pass it on to Connection object and get authenticated by SQL Server instance.

The documentation is not very clear on some of the configuration settings and that made me pull my hair off and had to do a lot of googling and hit and try. But eventually, following settings worked perfectly for my scenario:

sqlservice.js



const connectToServer = () => {
    return new Promise((resolve, reject) => {
        const config = {
            // Host or Machine name in this instance
            // Might try using FQDN or IP of SQL Server on your network
            // Can either be 'LAPTOP01' or 'localhost' if SQLEXPRESS is installed on your own machine
            // If on ABULHASANLAKHANI domain, use SERVER1 or SQLSERVER1 or whatever the network SQL Server name is
            server: 'localhost',

            authentication: {
                // Use Windows Authentication
                // Set to 'default' to use SQL Server Authentication
                type: 'ntlm',

                options: {
                    // Make sure to set this when you set 'type' as 'ntlm' or Windows Authentication
                    domain: 'ABULHASANLAKHANI',

                    // username along with the domain will make up the complete login for SQL Server like
                    // domain\username e.g. ABULHASANLAKHANI\USER1 in our case
                    userName: 'USER1',
                    password: 'robot'
                }
            },

            options: {
                database: 'AdventureWorks',

                // This option is only required if you're using SQL Server Express 
                // with named instance, which is the default setting
                // Together with the 'server' option this will make up to either 'localhost\SQLEXPRESS' or 'LAPTOP01\SQLEXPRESS'
                instanceName: 'SQLEXPRESS',

                // This setting is really important to make successfull connection
                encrypt: false,

                // This is not required but tedious API throws deprecated warning if we don't
                trustServerCertificate: false,

                // This will allow you to access the rows returned. 
                // See 'doneInProc' event below
                rowCollectionOnDone: true
            }
        }
    })
}

Please read the comments in the above code snippets as they explain why I had to use certain settings.

You would notice that even though I have chosen Windows Authentication (type: 'ntlm'), I am still providing username and password and must be thinking, why I have done that!

The reason is that Tedious API requires us to provide our windows credential and throw an error if we don't. Something like this:

After some debugging, I found out that the API is expecting a username and password to connect.

A Word on Promise Usage

So as per Electron's documentation, we can either use Remote module or IpcMain/IpcRenderer to communicate between the main and renderer process.

After reading the documentation and some articles on the internet, I decided to use IpcMain and IpcRenderer modules because of the risks that the Remote module poses in terms of Garbage Collection and Remote Object Leaking. Read more about this Here and also a very good post Here.

So the next best option for our use case is ipcMain & ipcRenderer modules. Specifically, we will be using the invoke method on ipcRenderer and handle method on ipcMain. Both of these are asynchronous and rely on JavaScript's Promise API.

Connecting To The Server

Now we are ready to connect to our database. We will do so within the connectToServer method we created above:

sqlservice.js



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

        // Omitting the config details for brevity
        const config = {...
        }

        let connection = new Connection(config)

        connection.connect()

        connection.on('connect', function (err) {
            if (err) {
                console.log('Error: ', err)
                reject(err)
            } else {
                // If no error, then good to go...
                console.log('Connection Successful!')
                resolve(connection)
            }
        })

        connection.on('end', () => { console.log("Connection Closed!") })
    })
}

Please note that we are still in the Promise constructor. After setting up the configuration object, we will first need to instantiate the Connection object and call the connect() method on it. Then, we will create an event handler that will be called once the connection is established or it throws an error. In case of a successful connection, we will resolve the promise by passing on the connection object out.

Querying the Server to Retrieve the Products

sqlservice.js



const readFromDb = (connection, sqlQuery) => {
    return new Promise((resolve, reject) => {
        let products = []

        console.log('Reading rows from the Table...')

        // Read all rows from table
        let request = new Request(sqlQuery, (err, rowCount, rows) => {
            if (err) {
                reject(err)
            } else {
                console.log(rowCount + ' row(s) returned')
                resolve(products)
                connection.close()
            }
        })
    })
}

This new method, readFromDb accepts two parameters. The first one is the connection object and second, for the query, we wish to execute, to retrieve the top two products from the Production.Product table. Again note, we are doing everything in a new Promise constructor so we can resolve it with the data returned from the server asynchronously.

We first need to set up a new Request object, passing in the query and a callback to execute, once the request is successful (see doneInProc event below). Notice that in the else block within the callback function, we are resolving the promise, passing in the products array, we have declared at the top.

let products = []

Don't worry, we will fill this up next! 😃

Once the request is instantiated, it will raise doneInProc event to indicate, it has finished executing the SQL statement.

sqlservice.js



const readFromDb = (connection, sqlQuery) => {
    return new Promise((resolve, reject) => {
        let products = []
        // code omitted for brevity
        .
        .
        request.on('doneInProc', (rowCount, more, rows) => {
            products = []
            rows.map(row => {
                let result = {}
                row.map(child => {
                    result[child.metadata.colName] = child.value
                })
                products.push(result)
            })
        })

        // Execute SQL statement
        connection.execSql(request)
    })
}

If the query is successful, the rows parameter of this event's callback will contain the returned rows from our database table.

Note: rows will only have data if we have set rowCollectionOnDone as true in our config object.

Only after doneInProc event is raised and its callback executed, the callback under Request object instantiation will be executed and the promise will be resolved with the products array.

Finishing sqlservice.js

Now that we have functions to connect to the database and make requests to it, we will now finish off writing this service by creating one last function that will be called by ipcMain's handle method. Here is the definition of getProducts() method.



const getProducts = () => {
    return new Promise((resolve, reject) => {
        connectToServer()
            .then(connection => {
                let sqlStr = 'SELECT TOP(2) [Name], [ProductNumber] FROM Production.Product'

                return readFromDb(connection, sqlStr)
            })
            .then(products => resolve(products))
            .catch(err => reject(err))
    })
}

The promise in this method will wrap the other two method calls, connectToServer and readFromDb, and use the promises returned by them to resolve the current promise (too many promises, I know).

Ok so finish writing this file now by adding one last line, which will be key to access all the logic above:



ipcMain.handle('getproducts', getProducts)

You can read in detail, how the handle method works but in short when the renderer process will call ipcRenderer.Invoke from the front-end, this line will handle that request.

Here is the full code of sqlservice.js:



const Connection = require("tedious").Connection
const Request = require("tedious").Request
const { ipcMain } = require('electron')

/**
 * Connect to the database
 * @returns 'Promise' A promise object containing an open connection to the database
*/
const connectToServer = () => {
    return new Promise((resolve, reject) => {
        const config = {
            server: process.env.DB_SERVER,
            authentication: {
                type: process.env.DB_AUTHTYPE,
                options: {
                    domain: process.env.DB_DOMAIN,
                    userName: process.env.DB_USERNAME,
                    password: process.env.DB_PASSWORD
                }
            },
            options: {
                database: process.env.DB_DBNAME,
                instanceName: process.env.DB_INSTANCENAME,

                // These two settings are really important to make successfull connection
                encrypt: false,
                trustServerCertificate: false,

                // This will allow you to access the rows returned. 
                // See 'doneInProc' event below
                rowCollectionOnDone: true
            }
        }

        let connection = new Connection(config)

        connection.connect()

        connection.on('connect', function (err) {
            if (err) {
                console.log('Error: ', err)
                reject(err)
            } else {
                // If no error, then good to go...
                console.log('Connection Successful!')
                resolve(connection)
            }
        })

        connection.on('end', () => { console.log("Connection Closed!") })
    })
}

/**
 * Read data from the database
 * @param 'connection' connection object to use to connect to DB
 * @param 'sqlQuery' sqlQuery as a string to be executed against the database
 * @returns 'Promise' A promise object with either collection of data or an error
*/
const readFromDb = (connection, sqlQuery) => {
    return new Promise((resolve, reject) => {
        let products = []

        console.log('Reading rows from the Table...')

        // Read all rows from table
        let request = new Request(sqlQuery, (err, rowCount, rows) => {
            if (err) {
                reject(err)
            } else {
                console.log(rowCount + ' row(s) returned')
                resolve(products)
                connection.close()
            }
        })

        request.on('doneInProc', (rowCount, more, rows) => {
            products = []
            rows.map(row => {
                let result = {}
                row.map(child => {
                    result[child.metadata.colName] = child.value
                })
                products.push(result)
            })
        })

        // Execute SQL statement
        connection.execSql(request)
    })
}

const getProducts = () => {
    return new Promise((resolve, reject) => {
        connectToServer()
            .then(connection => {
                let sqlStr = 'SELECT TOP(2) [Name], [ProductNumber] FROM Production.Product'

                return readFromDb(connection, sqlStr)
            })
            .then(products => resolve(products))
            .catch(err => reject(err))
    })
}

ipcMain.handle('getproducts', getProducts)

Requiring Our Service Into Main Process

Next, we will need to register our service by requiring it into main.js, like this:



// This method will be called when Electron has finished
// initialization and is ready to create browser windows.
// Some APIs can only be used after this event occurs.
app.whenReady().then(() => {
  createWindow()
  require('./src/services/sqlservice')
})

Writing our React Component - ProductList

We can use the main App.tsx component for our demo but let's just create a new component in the src -> components directory, to keep clear separation. The component will look like this:

ProductList.tsx



import React, { useState } from 'react'
import { ipcRenderer as ipc } from 'electron'

interface Product {
    Name: string,
    ProductNumber: string
}
let productsFromDB: Product[] = []

const ProductList = () => {
    const [show, showProducts] = useState(false)

    const getProducts = () => {
        ipc.invoke('getproducts').then((products) => {
            productsFromDB = products
            showProducts(true)
        })
    }

    if (!productsFromDB.length) {
        getProducts()
    }

    return show && (
        <>
            <h1>Products:</h1>
            {
                productsFromDB.map((p, index) =>
                    <div key={index}>{p.Name} - {p.ProductNumber}</div>
                )
            }
        </>
    )
}

export default ProductList

The component is quite simple if you are a react developer. Nothing too fancy really. I would like to draw your attention to the code where we communicate with our service via ipcRenderer's invoke call:



ipc.invoke('getproducts')
    .then((products) => {
        productsFromDB = products
        showProducts(true)
    })


```javascript

Now we can use our new component in `App.tsx`:

```javascript


// Import React library
import React from 'react'
import ProductList from './components/ProductList'

const App = () => {
    return (
        <div>
            <p>Hello,</p>
            <p>This is a sample application to demonstrate the use of <strong><em>TediousJS within Electron/React App</em></strong></p>

            <hr />

            <ProductList />
        </div>
    )
}

// Export the main component
export default App

save all the files, go to the terminal and run npm start

If something bad happens and you are stuck, you can check out the repository at Github.com:

NodeElectronReactSample

I hope you enjoyed reading this article and it's not too dry to follow. I expect that it should help people even if they don't read the full article, by just jumping to the section they want to read.

I would love your feedback and any suggestion related to the content, my writing, or anything else.

Thanks for reading! 😃

Top comments (6)

bondi155 • Mar 4 '21

Hi Abul , thank you for the post.
I have a question.. the first code of sqlservice.js
const connectToServer shows a configuration but then you show other configuration for const connectToServer , i dont urdestand that , which is the right code?

Abul Hasan Lakhani • Mar 5 '21

Hi there,

I have updated the code snippets to make it more clear. And also added the full code of sqlservice.js. Also, I have updated my github repo with some minor refactoring.

Thanks a lot for reading this how to. Hope it helps you.