Part 5. Clock-in/out System: Seed Database and migration data
Tags: javascript, typescript, node, nestjs
Published: 2019-03-14 15:49:51

---
title: Part 5. Clock-in/out System: Seed Database and migration data
published: true
tags: javascript, typescript, nodejs, nestjs
description: So, in this posts I’m going to described how I have created a series of scripts (typescript) that allow filling the database from an excel sheet provided by the client from its old software (MS Excel).
series: clock-in/out system
canonical_url: https://www.carloscaballero.io/part-5-clock-in-out-system-seed-database-and-migration-data/
cover_image: https://cdn-images-1.medium.com/max/1000/1*KhCkmbtORw2Ux3n5WCrtzA.png 
---

This post is part of a Series of post which I’m describing a clock-in/out system
if you want to read more you can read the following posts:


* [Part 1. Clock-in/out System: Diagram.](https://dev.to/carlillo/part-1-clock-inout-system---diagram-4kgj)
* [Part 2. Clock-in/out System: Basic backend — AuthModule.](https://dev.to/carlillo/part-2-clock-inout-system-basic-backend-i--authmodule-d2l)
* [Part 3. Clock-in/out System: Basic backend — UsersModule.](https://dev.to/carlillo/part-3-clock-inout-system---basic-backend-ii---usersmodule-5hf5)
* [Part 4. Clock-in/out System: Basic backend- AppModule.](https://dev.to/carlillo/part-4-clock-inout-system---basic-backend-iii---appmodule-5anb)
* [Part 5. Clock-in/out System: Seed Database and migration data](https://dev.to/carlillo/part-5-clock-inout-system-seed-database-and-migration-data-320m)
* [Part 6. Clock-in/out System: Basic frontend.](https://dev.to/carlillo/part-6-clock-inout-system-basic-frontend---angular-28pg)
* [Part 7. Clock-in/out System: Deploy backend (nestJS) using docker/docker-compose.](https://dev.to/carlillo/part-7-deploy-backend-nestjs-dockerdocker-compose-3cmb)
* [Part 8. Clock-in/out System: Deploy frontend (Angular 2+) using environments.](https://dev.to/carlillo/part-8-clock-in-out-system-deploy-frontend-angular-6-using-environments-nog)
* [Part 9. Testing: Backend Testing — Unit Testing - Services](https://dev.to/carlillo/part-9-clock-in-out-system-testing-backend-testing-3imk)
* [Part 10. Testing: Backend Testing — Unit Testing - Controllers](https://dev.to/carlillo/part-10-testing-backend-testing-unit-testing-controllers-573a)
* Part 11. Testing: Backend Testing — E2E Testing
* Part 12. Testing: Frontend Testing — Unit Testing
* Part 13. Testing: Frontend Testing — Integration Testing


In the previous posts the development of the backend of the clock-in system has
been described but there are no data to verify that our backend has been
successfully developed.

So, in this posts I’m going to described how I have created a series of scripts
(typescript) that allow filling the database from an excel sheet provided by the
client from its old software (MS Excel).

The first step is to add a **npm script** in the **package.json** that allows the execution of our script in [TypeScript](http://www.typescriptlang.org/) using **node-ts**. So, the execution of our script will be executing the command **npm run init:db**.

![](https://cdn-images-1.medium.com/max/1000/1*2qO5mLnit_30iTVhvPNU6g.png)

The script **create-data-fixture** is a self-executing function which create a
new object based on **DatabaseTest** and invoke the method **db.reload()**. The
function **DatabaseTest** is created using two parameters:

**A connection of database** (the database which will be used to store the data)
which would be different in each execution if we want to use different databases. This fact is interesting when we want to have different databases in parallel running e2e (End to End) test with mock-data. **DataFixture** which is a object which contains the information which will be insert into the database (**Users** and **Users-schedule**).

![](https://cdn-images-1.medium.com/max/1000/1*w6w75taONOpzCJbJpABqnA.png)

So, the most simple file is **TestFixture** which exports the data once they have been imported and loaded from the XLS file of a data mapping script (which will also be developed by us).

![](https://cdn-images-1.medium.com/max/1000/1*KtcvXxjyuYhY4cbdWH5QZw.png)

On the other hand, the **database.test.ts** file is responsible of load the faked data in the database. That is, the **Users** and **UsersSchedule** will be taken from the **test.fixture** file. Then we will go describing this file, since it is quite important:

* The **DatabaseTest** class received using DI (Dependency Injection) the database
connection and the data mocked from the **test.fixture** file.
* The **modelsCharged** attribute is initialized, which stores the models that
have already been loaded in the database and do not have to reload them.
* The **createConnectionDB** method is static to do the connection to the database
from outside the class. In this way, when the class is injected, the connection has already been established successfully.

![](https://cdn-images-1.medium.com/max/1000/1*OkUgICbYJkkKDiW-zKhS6A.png)

The next step is to build the basic methods to initialize, reload and synchronize the database.

![](https://cdn-images-1.medium.com/max/1000/1*jC9ECyDPMv4rEZkwJKMv5g.png)

Next, the models are loaded, which are recursively performed one by one, taking the information of the existing dependencies between the different models.

![](https://cdn-images-1.medium.com/max/1000/1*YFOd2aNy9rdm4GUNZXfIsQ.png)

To recapitulate, the complete **database.test** file is shown.

![](https://cdn-images-1.medium.com/max/1000/1*3uG0YiznrZXRi73XgXMAGg.png)

### BONUS TRACK

Next we will show the **XLSToJson** method, which simply consists of transforming each row of the excel to the different JSON objects needed to be imported by TypeORM. It is important to note that each developer will have to adapt the **XLSToJson** function from their XLS.

First of all we need to install the **node-xlsx** package which allows us to interact with the XLS files. Below is a first version that makes use of several **forEach** loops to go through and build a data structure similar to the following:

* [schedulers, users] where
* scheduler is an object of the following type:

![](https://cdn-images-1.medium.com/max/1000/1*1rInbpm0n3Irp41KNMuXGg.png)

* user is an object of the following type:


The following code, I would consider that it is not in its cleanest version and I consider a nice refactoring of it when the system is done as a future post. If someone wants to advance the task, the following points are proposed:

1. Extract functions according to the tasks performed, instead of a large, single function.
2. Replace the **forEach** methods that hide the classic data structure for but functionally by more suitable methods such as the **reduce**.

![](https://cdn-images-1.medium.com/max/1000/1*0HhII86DZiJuAzYY89R9nA.png)

### RESUME

‌In this post I’ve explain my **seed database** which is composed of a series of
scripts that allow me to load any data structure in the database. To import from
Excel, a specific script has been created that converts the XLS file into JSON.

The GitHub project is [https://github.com/Caballerog/clock-in-out](https://github.com/Caballerog/clock-in-out).
The GitHub branch of this post is [https://github.com/Caballerog/clock-in-out/tree/part5-seed-database](https://github.com/Caballerog/clock-in-out/tree/part5-seed-database).

*****
Originally published at [www.carloscaballero.io](https://carloscaballero.io/part-5-clock-in-out-system-seed-database-and-migration-data/) on December 21, 2018.