Connecting To SQL Server from Electron & React
Tags: electronjs, react, tediousjs, javascript
Published: 2020-06-24 12:06:11

## 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](http://tediousjs.github.io/tedious/index.html)__, 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](https://github.com/abulhasanlakhani/NodeElectronReactSample)** 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](https://blog.alexdevero.com/electron-app-pt2/)**, 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 :smiley:)


1. Clone the repo:
   
    `git clone https://github.com/abulhasanlakhani/NodeElectronReactSample NERS`
   
    ![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ifp765jje5wztahdrdsy.png)

2. Install Dependencies:
    
    `npm i`
    
3. Run the App:
    
    `npm start`
    
    You should see a barebone Electron, React, TypeScript App like this: 

    ![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/pto2qqgfxvdlvxx6q8wx.png)


#### 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](https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks)**

***
*NOTE: Setting up SQL Server is out of scope for this article but you can download the express edition __[HERE](https://go.microsoft.com/fwlink/?linkid=866658)__ 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](https://www.ibm.com/support/knowledgecenter/en/SSHLNR_8.1.4/com.ibm.pm.doc/install/sql_config_agent_grant_permission_sqlserver.html) article or [Microsoft's](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-2017) article to create a new login. Then assign the newly created login to your database*
***


#### Installing TediousJS NPM Package

__[TediousJS](http://tediousjs.github.io/tedious/index.html)__ is a Node package that provides an implementation of the __[TDS](https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/b46a581a-39de-4745-b076-ec4dbb7d13ec?redirectedfrom=MSDN)__ 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`:


![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/zvhb2tvdkzusicjlinyh.png)

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

__sqlservice.js__
```javascript
const Connection = require("tedious").Connection
const Request = require("tedious").Request
const async = require('async');
```


A __[Connection](http://tediousjs.github.io/tedious/api-connection.html)__ instance represents a single connection to a database server.

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


A [Request](http://tediousjs.github.io/tedious/api-request.html) 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
```javascript
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:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/5tbdqm3sawvkkv5sgl2h.png) 

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


> ### A Word on [Promise](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise) Usage

> So as per Electron's documentation, we can either use [Remote](https://www.electronjs.org/docs/api/remote) module or [IpcMain](https://www.electronjs.org/docs/api/ipc-main)/[IpcRenderer](https://www.electronjs.org/docs/api/ipc-renderer) 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](https://www.electronjs.org/docs/api/remote#passing-callbacks-to-the-main-process) and also a very good post [Here](https://medium.com/@nornagon/electrons-remote-module-considered-harmful-70d69500f31).
> 
> 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](https://blog.bitsrc.io/understanding-asynchronous-javascript-the-event-loop-74cd408419ff) 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
```javascript
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
```javascript
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! :smiley:

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

##### sqlservice.js
```javascript
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.

```javascript
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:

```javascript
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:**

```javascript
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:

```javascript
// 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__
```javascript
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:

```javascript
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](https://github.com/abulhasanlakhani/NodeElectronReactSample/tree/Finished)


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! :smiley: