Sqlite nodeJS Server and Unity C# Client

Unity+ NodeJS SQL Server & Client

nodeJS & Unity SQL

In this tutorial, we will create a nodeJS server and UnityC# client for storing our app data on the server and we will deploy the server using a docker file.

Server code

First, we will create a folder for our nodeJS server and then open this folder in VSCode

In the terminal (Command Line Editor) we create a package.json by running

npm init -y

This will add package.json, next we need to import some modules that we will be using

npm i express sqlite3 body-parser

Modules we will be using

express minimal web framework

sqllite3 popular DB framework or relational database management system

body-parser parser middleware for parsing the web request body

I will start by creating a util.js script for all of our utility functions

I will implement some basic CRUD operations like:

Create Database, Insert Row, Read rows, Update row and Delete Row

Here is how our util.js will look like.

As you can see above, Database operations are asynchronous that is why we used Promises, we reject the promise if some error happens and resolve the promise if everything worked fine.

Notice that in the deleteUser, updateUser, insertUser, I used db.prepare where we prepare a SQL statement and then run it.

stmt = db.prepare(‘SQL STATMENT’)
stmt.run( function(err)=>{})

According to sqlite3 API stmt.run returns null if no error occurred and the context of the function (this) which contains two pieces of data.
this.lastID and this.changes.

For getUsers I used db.all where I pass a SQL statement and return err and the rows selected.

Also, you can use db.run(‘SQL Statement’) as I did with database creation.

And finally, I exported the functions to be used in the server script.

Now that we have the SQL functions next is the server script.

I will name my server app.js and here is the server code

The code above goes as the following:

First I imported all the required modules including our own util.js

Second, I use bodyParser as the middleware responsible for parsing my JSON and form data.

Lastly, I specify my web requests and their behavior.

C# Unity Client

For creating the Unity C# Client, I will create two classes UserProtcol and Manager.cs.

User Protocol

This class will be responsible for handling the User data structure.

WebRequests Manager

This class will be holding all of our client database requests

Let’s do a small test in our Manager.cs

Example of Inserting a row in DB & Retrieving rows from DB

Output in Unity Console if we press A two times

Deploying the Server using Docker

To proceed with the next step you need to have docker installed on your device, You can download docker from here.

I will create a docker file like this

FROM node:12
WORKDIR /usr/src/app/
COPY package*.json /usr/src/app/
RUN npm install
COPY . /usr/src/app/
CMD node app.js
EXPOSE 3000

If you are interested in Deploying your SQL Server to MobiledgeX Backend as a docker image, please check the video below.