Node.js and ORMs? TypeORM at your service

As I’ve mentioned in other posts, I’m working with more Node.js projects than ever and my experience with .NET applications is reducing (not complaining, if you think that). However, most of those projects have been with no-sql databases like DynamoDB and I haven’t had the need to use any ORM for it, even though there are options.

Recently, I was assigned a project that needed a big rescue. It’s an internal application of the company, rewritten many times but none of those times it has been completed, and the design itself wasn’t as good as you would like it to be. My assignment, design the architecture, define the technologies to be used, estimate it and assign tasks to a group of developers to work on it. So far, so good.

I decided to use React in the front end, and one main REST service in ExpressJS. Both solutions are going to be written in typescript. But what about the database? well, mysql was my choice and if you ask me the reason, it’s because the business logic makes more sense in a relational environment, but also because I want that my team (and myself) use an ORM to connect to a relational database like MySQL.

A quick search in Google will give you many results about ORMs but I found an interesting article that compares many of them and gives them a rank, take a look at it here. The only comment I would make there is that mongoose is limited to only one database, whereas TypeORM support multiple ones so in my mind, they should switch positions.

Ok, enough chit-chat, let’s start with the tech-y comments. First, I’m starting with a simple definition on an ORM.

What’s an ORM?

ORM stands for Object Relational Mapping, and it’s a mechanism that enables developers to manipulate data directly from the source without the hassle that it normally would take. They map the data sources to objects in code that can be queried, and the ORM transforms the actions over those objects to the specific commands in the specific data source. In other words, they abstract the data access layer from the developers and serves a “virtual object database” to be used within the programming language.

There are many ORM tools in the community, here are some of the most famous ones per programming language:

  1. Hibernate -> Java

  2. Entity Framework -> C#

  3. CakePHP -> PHP

  4. Django -> Python

  5. ActiveRecord -> Ruby

What’s TypeORM?

As the name suggests, and we have mentioned many times over the post, it is an ORM that runs in NodeJS; however it supports other environments like PhoneGap, React Native, Nativescript, etc.

It’s built to be used with Typescript or the latest versions of Javascript (from 5 to 8). Currently, version number is 0.2.9, but do not get fooled by this, it has over 3000 commits in Github! more than 40 thousand downloads per week! and finally, but not least, over 9000 stars!

The first version was deployed in December 6th of 2016, and had 36 releases since then. Being a young tool, it has been influenced by other ones like Hibernate and Entity Framework, so if you noticed stuff that feel familiar, it’s because they are.

From their website, here are some of the main features it provides:

  1. Both DataMapper and ActiveRecord

  2. Eager and Lazy relations

  3. Multiple inheritance patterns

  4. Transactions

  5. Cross-database queries

  6. Query caching

  7. Support for 8 different databases

  8. And many more here

Is there a model generator?

If you have worked with Entity Framework, you can do reverse engineering to create all the POCO classes from an existing database. Well, for TypeORM there is something similar.

Konnonable’s typeorm-model-generator package solves all of this for you. It can create all the object classes that you need to use in your application and it supports 6 databases, leaving Mongo and sql.js outside of the equation.

This package is even younger than typeorm, its first release was in July 2017 and it had 24 releases since then. It’s far less known in the community since there are not many downloads per week according to npmjs (around 300 per week).

Still, this package works like a charm and the configuration is as simple as you can imagine. Take a look at the next line:

typeorm-model-generator -e mysql -h [HOSTNAME] -d [DATABASE] -u [USER] -x [PASSWORD] -p 3306 --noConfig -o . --cf camel --ce pascal --cp camel --lazy

From it you can specify all the necessary parameters to establish a connection to a database, but also some configurations regarding the classes generated like the naming conventions and the “lazyness” of the relationships between them. If you want to take a look at all the available options for configuration, click here to view them.

Using TypeORM

After creating the classes with the typeorm-model-generator package, I ended up having classes that look something like this one:

import { Index, Entity, Column, OneToOne, OneToMany, ManyToOne, JoinColumn } from "typeorm";
import { UserStatus } from "./userStatus";
import { ProjectMember } from "./projectMember";
import { StatusReport } from "./statusReport";
import { TimeOff } from "./timeOff";

@Entity("User", { schema: "statusone" })
@Index("StatusTxt", ["statusTxt",])
export class User {
    @Column("varchar", {
        nullable: false,
        primary: true,
        length: 50,
        name: "UserNm"
    userNm: string;

    @Column("varchar", {
        nullable: false,
        length: 150,
        name: "Email"
    email: string;

    @Column("varchar", {
        nullable: false,
        length: 150,
        name: "FullNm"
    fullNm: string;

    @ManyToOne(() => UserStatus, UserStatus => UserStatus.users, { nullable: false, onDelete: 'RESTRICT', onUpdate: 'RESTRICT' })
    @JoinColumn({ name: 'StatusTxt' })
    userStatus: Promise<UserStatus | null>;

    @OneToOne(() => ProjectMember, ProjectMember => ProjectMember.userNm, { onDelete: 'RESTRICT', onUpdate: 'RESTRICT' })
    projectMember: Promise<ProjectMember | null>;

    @OneToMany(() => StatusReport, StatusReport => StatusReport.userNm, { onDelete: 'RESTRICT', onUpdate: 'RESTRICT' })
    statusReports: Promise<StatusReport[]>;

    @OneToMany(() => TimeOff, TimeOff => TimeOff.userNm, { onDelete: 'RESTRICT', onUpdate: 'RESTRICT' })
    timeOffs: Promise<TimeOff[]>;

This one is an easy example of a User table in our database with its relationships like the status of the user, all the reports and more.

TypeORM uses heavily decorators, which requires some options to be enabled in the tsconfig file, however don’t worry about them since it’s explained in their installation instructions here. But if you want the easy route, here is mine.

  "compileOnSave": false,
  "compilerOptions": {
    "target": "es6",
    "module": "commonjs",
    "esModuleInterop": true,
    "sourceMap": true,
    "moduleResolution": "node",
    "outDir": "dist",
    "typeRoots": ["node_modules/@types"],
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true
  "include": ["typings.d.ts", "server/**/*.ts", "src/**/*.ts"],
  "exclude": ["node_modules"]

Ok, so our project have the classes from the database, and the typescript compiler recognizes all the decorators that TypeORM uses, so how do I use it??

I’m not going to expose the architecture I’m planning on using in the application, mostly because I haven’t completed it. But here is an example of a basic query I’ve done with TypeORM.

import {createConnection} from "typeorm";
import {User} from "../data-access/entity/User";

createConnection().then(async connection => {
      try {
        const users = await connection.manager.find(User);
        console.log("Loaded users: ", users); 
      } catch (error) {
    }).catch(error => console.log(error));

Just as easy as it looks, you can obtain all the users from the database by creating a connection, and then using the connection manager to find all the objects from the class passed as parameter.

Another more complex query can be the following:

import {createConnection} from "typeorm";
import {User} from "../data-access/entity/User";

createConnection().then(async connection => {
      try {
        let projectMembers = await connection
            .innerJoinAndSelect("user.projectMember", "projectMember")
        console.log("Loaded members: ", projectMembers); 
      } catch (error) {
    }).catch(error => console.log(error));

In this example, we are creating a query that retrieves the relationship to ProjectMember for the users using a syntax called QueryBuilder which reminds me a lot of EntityFramework.

As far as query examples goes, I will stop here and suggest you to read more documentation like here or here.

Is there anything bad?

Nothing is perfect in this world, and I didn’t have to investigate a lot to find something that surprised me. Believe it or not, but apparently TypeORM still doesn’t support bit types for mysql (Check line 81 in this file).

Even though that this shocked me at first, I had the possibility of changing the design to overlook those bit types and use something different, however it’s still something that annoys me and they should add that support soon.


TypeORM is one more tool to our toolkit, one that will make your life a lot easier and have the support of a big community. It allows you to abstract a lot of the complex code that can come from connecting to a database, and helps you focus on your business instead of figuring out how to do a select in a table.

If you have any comment, don't hesitate in contacting me or leaving a comment below. And remember to follow me on twitter to get updated on every new post.

Use Athena To Process Data From DynamoDB

Have you ever thought that it would be so good to have SQL-like queries in DynamoDB?

I worked in companies that have always used Microsoft's SQL Server as database, and it's so easy for me to do things like COUNT, AVG, among others to calculate metrics and other indicators. However, now that I've been using Dynamo for some projects, I miss that.

This week, I found Athena (not the Greek Goddess), a tool provided by Amazon to query big data information stored in S3 using standard SQL queries. To use it, you don't need to configure any infrastructure, it runs serverless and executes the queries directly into the datasource in S3. It supports a variety of data formats like CSV, JSON, among others (today, we will be doing some examples using JSON).

But then, how do I use it to query from Dynamo? well, Athena still does not support queries directly into Dynamo, but we can mimic this functionality by using AWS Glue.

AWS Glue, is another tool that allows developers to create ETL jobs that can perform many tasks, and it's completely integrated with Athena. AWS Glue uses something called Crawlers that create schemas from the datasources that are analyzed, so for example, creating a crawler from a dynamo table, will enumerate all the columns that the table can have and a possible type of the data it contains.

So combining everything, we can do the following:

  1. Create a crawler that reads the Dynamo table
  2. Create a Glue job that reads the information and stores it in an S3 bucket
  3. Create a new crawler that reads the S3 bucket
  4. Use Athena to query information using the crawler created in the previous step.
  5. Expose information using API Gateway and Lambda functions

Here you have a visual representation of those steps:

And here is an example of the queries that you can create:

SELECT a.openCount, b.openWACount, c.solvedYTD
   SELECT COUNT(*) AS openCount
   FROM caseview_metrics
   WHERE status='open'
) a
   SELECT COUNT(*) AS openWACount
   FROM caseview_metrics
   WHERE status='open' AND CARDINALITY(abductorsids) >= 1
) b
   SELECT COUNT(*) as solvedYTD
   FROM caseview_metrics
   WHERE status='close' AND closeddate BETWEEN 1514764800000 AND 1546300799000
) c

In this example, you can see functions as COUNT or CARDINALITY, expressions like BETWEEN or comparisons, and finally you can also apply JOIN clauses like the CROSS JOIN.


AWS Athena is a tool that you can add to your toolkit, and if you are using Dynamo, it can enhance greatly the experience for the users, as well as facilitate development. 

If you have any comment, don't hesitate in contacting me or leaving a comment below. And remember to follow me on twitter to get updated on every new post.

Tech Review: NativeScript

Mobile development is a world that, for me, is different than what I'm used to work with. I come from a .NET background and moved recently to a Full Stack Javascript developer, but I wanted to dive a little bit in creating mobile applications. After reading a lot, I saw that there is a bunch of technologies that abstract the native development into a different stack. Maybe, if you have reached this post, you have seen options like:

  1. Apache Cordova
  2. PhoneGap
  3. Ionic
  4. React Native
  5. Xamarin

However, this post, as the title suggest, is about NativeScript. A smaller contender that is trying to grow and compete on equal terms with the others. 

As we can see, comparing searches using Google Trends between NativeScript, Apache Cordova and PhoneGap demonstrates that there is a steady growth for our contender. But, if we do the same comparison against Xamarin or React Native, we will see that there is a big gap that need to be closed.

NativeScript is an open-source framework that builds native applications using other frameworks such as Vue.js or Angular and languages like Typescript or Javascript. It became publicly available in March 2015, so it's a relatively young technology (you might differ, in this world, two years is enough for something to be called old, haha). Since then, there has been 41 releases, with the current version being 4.1.1.

As I stated before, NativeScript is a framework that uses web technologies to create native applications, so this is a big advantage for web developers; having the ability to use the skills possessed to create mobile applications, is a really good selling point.

On Github, it possess more than 14.000 stars, 114 contributors and a really active community, you can follow the repository and see the activity in the issues feed. According to npm, it's licensed under Apache2.0, and it has more than 35.000 downloads per week. 

Even with those numbers, you might still question yourself if it's worthy to work with, well, there are companies that decided to trust on it; big companies like Bosch, Samsung, Qualcomm and others. 

How does it work?

Ok, enough with the introduction, let's move to more interesting topics. To explain how it works, here is a really good extensive post explaining that, but I'm going to explain it briefly now.

In short words, NativeScript uses Javascript virtual machines that are used to execute commands (V8 for Android and JavascriptCore for iOS). These machines execute the code compiled in JIT format that is then transformed to the native counterparts for each operative system. Also, if you are familiar with Reflection, NativeScript uses it to inject the available APIs to match with the Javascript versions of them. 

Using Nativescript

In this post, I'm not going to cover how to install NativeScript, for that, follow the quick setup installation guide from their official site. Also, one of the reasons I used NativeScript is the fact that it integrates with Angular easily, hence, I will not only focus on NativeScript, but also in how to use it with Angular. So, assuming you have a working environment and you are interested in using Angular, let's continue.

The main aspects I will cover for now are: typescript code to bind events and layouts examples, in a later post, I will do a more complete tutorial.

Layout examples

Even though that we are using web technologies like Javascript, the layouts change drastically. The HTML tags we know are no longer usable, instead, there are defined tags that we need to use, they are:

  1. AbsoluteLayout: it uses specific coordinates to allocate items inside of it. 
  2. DockLayout: it allocates items to the edges of the layout (top, bottom, left or right).
  3. GridLayout: it is useful to accommodate items in a table structure where you define the number of rows and columns.
  4. StackLayout: as the name suggests, this layout stacks its children based on the orientation defined.
  5. WrapLayout: this layout works similar to the StackLayout, however, when the items don't have any room left, it continues stacking them in a new row.
  6. FlexboxLayout: this layout mimics the CSS box layout.

Here is an example using some of these layouts:

<StackLayout class="menu-bg" orientation="vertical">
  <StackLayout orientation="horizontal" horizontalAlignment="center" height="100%">
    <DockLayout class="home-menu-container" stretchLastChild="true" verticalAlignment="middle">
      <Label dock="top" class="h1" [text]="title"></Label>
      <StackLayout dock="bottom" class="menu-options" orientation="vertical" verticalAlignment="middle">
        <Button text="Start New Game" class="button new-game" (tap)="onNewGameTap()"></Button>
        <Button text="Options" class="button options" (tap)="onOptionsTap()"></Button>

We can still use classes as in HTML to assign styles using CSS or SCSS files. Other tags like Labels and Buttons also changed a little bit from their counterparts in HTML, labels for example use a text attribute to assign the value and buttons' click events are replaced with tap events.

Event and property binding

Like using Angular in a web page, we create components that will be rendered in the application. These components will handle some UI logic and call other providers or services that will handle the business logic. From these components, we will assign events or properties that will be bound by the two-way binding feature from Angular by using the proper wrappers. In the example we had before, we can see the following bindings:

  1. The label tag has a text property wrapped in square brackets that maps to the title property on the component.
  2. The button tag has a tap event wrapped in parenthesis that calls the onNewGame event on the component.

Here is the matching code example:

import { Component } from '@angular/core';
import { RouterExtensions } from 'nativescript-angular/router';
import { NEWMAZE, CONTINUEMAZE } from '~/shared/constants';

    selector: 'menu',
    templateUrl: './menu/menu.component.html',
    styleUrls: ['./menu/menu.component.css']
export class MenuComponent {    
    title: string = "New Game"
    constructor(private router: RouterExtensions) { }
        this.router.navigate([`maze/${NEWMAZE}`], { clearHistory: true });

        this.router.navigate(["options"], { clearHistory: true });


NativeScript is a great framework that is growing in the community, hopefully, it will match others like Xamarin and React Native, but so far, the experience to develop on it has helped me to create mobile applications fast and easily. Even though that I've found some things that weren't easy to fix, there are always workarounds available. Hopefully, I will create a new post that will go deeper in details and maybe a tutorial, stay tuned.

If you have any comment, don't hesitate in contacting me or leaving a comment below. And remember to follow me on twitter to get updated on every new post.

Tech Review: Angular Stack (2/many)

The second entry of this series is going to talk about two things that are common problems in Javascript. If you have ever experience problems with Dates, then one of the libraries I'm reviewing here will facilitate your development experience a lot; the second library is about putting a loading indicator when there is an async request being executed. So let's dive in:


Let's start with the date management library. According with npmjs, at the time of writing this post, there has been more than 953 559 downloads in average per week, it's MIT licensed and currently on version 1.29.0. Also, according to github, this repository has over 11.000 stars, 98 contributors, and a pretty active community (I hope that with all of those stats, it gives enough background about how big it is). Bottom line, I present to you, if you didn't know about it, date-fns.

According to their home page, date-fns is like lodash but for dates. With over 140 functions to process dates, it doesn't fall short and pretty sure it will cover all the use cases that you can think of. 

So, why and how do I use it? I'm answering both questions of my review from the start, because they are tightly related. Some of the main functions that I look for when handling dates are: formatting a date to specific customer requirements and calculating dates in future. 

Formatting a date is really simple, for example you can have some code like this one:

import { format } from 'date-fns';

export class Utils {
  getDateFormatted(date: Date) {
    return format(date, "MM/DD/YYYY hh:mm:ss")

Date calculation becomes really simple too, with the 140 functions or more that you can use, they export some useful like the ones below:

import { addDays, setSeconds } from 'date-fns';

export class Utils {
  getEmailTriggerDate(date: Date){
    return setSeconds(addDays(date, 7), 0);

This example only mentions the functions addDays and setSeconds, but there are much more and I invite you to read the documentation over here.

Loading Indicator

Our second library has around 1.500 downloads per week, MIT licensed also and is on version 2.3.0. According to github, this repository has 170 stars and 2 contributors. I present to you mpalourdio's ng-http-loader.

Depending on the version of Angular that you are using, a different version of the library must be included when installing the package. This is the description from their home page:

"The latest compatible version with angular 4 is 0.3.4. If you want to use Angular 5, use versions 0.4.0 and above. The latest compatible version with angular 5 is version 0.9.1. Versions 1.0.0+ and 2.0.0+ are angular 6 / RxJS 6 compatible only."

First question, why do I use it? because I don't have to do anything basically! other than configuring the loader icon following the simple steps from the instructions they provide. This library was built with the condition to intercept all http requests performed using the HttpClientModule from Angular, in other words, as long as you use this module, the loader will appear automatically. 

Also, for the cases where you might use a third party tool that does not use that module, for example the AWS-SDK, then you can manually trigger the loader to appear using an Angular service they provide.

So, how do I use it? first let's discuss a little bit about the configuration. The most important thing is to include the main module from their library to our app module, so that we can use the library from our application.

import { NgHttpLoaderModule } from 'ng-http-loader/ng-http-loader.module';
import { AppComponent } from './app.component';

  declarations: [
  imports: [
    /** OTHER IMPORTS */
  bootstrap: [AppComponent]
export class AppModule { }

You can do some customization that is provided out of the box, like different spinners using the following configuration in the app.component.ts file.

import { Spinkit } from 'ng-http-loader/spinkits';

  selector: 'app-root',
  templateUrl: './app.component.html'
export class AppComponent {
  private spinkit = Spinkit;
  spinnerType = this.spinkit.skWanderingCubes;

And a simple add in the app.component.html

<spinner [spinner]="spinnerType"></spinner>

With this default configuration, the spinner will appear every time a request is made, however, I also mentioned that there is a way to manually show the spinner. Here is how:

import { SpinnerVisibilityService } from 'ng-http-loader/services/spinner-visibility.service';

    selector: 'my-component',
    templateUrl: 'my-component.component.html'
export class MyComponent extends OnInit{
  constructor(private spinner: SpinnerVisibilityService) {
  someAction() {;
    //Some more logic


I've nothing else to say except that I love this components and I'll keep using them as long as I can. Both of them simplify a lot my work and I definitely like using things that make my life easier.

If you have any comment, don't hesitate in contacting me or leaving a comment below. And remember to follow me on twitter to get updated on every new post.

How to provide temporary access to S3 Buckets?

There are times when we store assets like images or videos in S3 buckets to be displayed in our websites. But what happens when we want to secure those assets so that only authenticated users can see them?

Well, there are many ways to provide security, one of the most common is used the "Referer" header but this can be spoofed, so we lose the security we wanted before. Another one is using Cloudfront and create signed URLs but that requires a lot of development work, and the last option was to use API Gateway to return binary data. After analyzing all this options, I determined that none of them provided the security we needed nor satisfied all of our use cases. Finally, I came up with another solution using a little bit of all the approaches mentioned before.

In order to provide security to the S3 folder, we are going to use signed URLs to provide temporary access to the bucket where the assets are hosted. To create the signed URLs we are using 2 lambda functions, the first one will be running under a IAM role that will create the signed URLs and the second one will be an authorizer function for the first one that will verify if the user making the request have the proper credentials. Here is a diagram of how the security flow for the S3 bucket works:

S3 Security Architecture

S3 Security Architecture

The first step to accomplish this is to remove the public policy that the bucket has, we want the bucket to be as closed as possible.

The second step will be to create a lambda function that will generate the signed URLs. For that, we need to create a lambda function called resolver and type the code provided below:

const AWS = require('aws-sdk');
exports.handler = (event, context, callback) => {
        region: "us-east-2"
    const s3 = new AWS.S3({signatureVersion: 'v4', signatureCache: false});
    var key = event["queryStringParameters"]["key"];
    s3.getSignedUrl('getObject', {
        Bucket: "owi-trainer-assets",
        Key: key,
        Expires: 7200
    }, function(error, data){
        if(error) {
            var response = {
                statusCode: 301,
                headers: {
                    "Location" : data
                body: null
            callback(null, response);

The getSignedUrl function from the SDK receives 3 parameters, the name of the operation that will be allowed from the URL created, an object containing the configuration (bucket, key of the object in the bucket and the expiration time in seconds), and lastly, the callback that will be executed once the URL is generated. As you can see, we are returning a code 301 in the response to force the client to redirect the request to the generated URL.

The third step is create an API Gateway endpoint that works as a proxy to the lambda function. The only important aspect here is to grab the ID of the API endpoint because we will need it for the next step. The ID can be obtained from the UI when the endpoint is created, in the next image, the text highlighted in yellow is the ID we need.

Gateway ID

Gateway ID

The fourth step is to create the validator lambda function that will verify that the client requesting an asset is a valid client. For that, we will follow the following steps.

  1. The validator function requires 2 NPM packages that not provided by default in the lambda ecosystem. So we will need to upload a zip file that contains all the necessary libraries.
  2. To accomplish that, create a folder named validator and navigate to it in a command window. In there, type "npm init" to create a package.json file and install these two components:
    1. aws-auth-policy: contains the AuthPolicy class that is required for a Gateway authorizer to perform actions.
    2. jsonwebtoken: this library is going to be used to validate the JWT tokens sent in the query string from the client.
  3. Inside of the validator folder created before, add an index.js file that will contain the logic to validate the tokens. The code will be provided below.
  4. Finally, create a lambda function named validator and upload the folder in a zip file.
var jwt = require('jsonwebtoken');
var AuthPolicy = require("aws-auth-policy");
exports.handler = (event, context) => {
    jwt.verify(event.queryStringParameters.token, "<SECRET TOKEN TO AUTHENTICATE JWT>",
    function(err, decoded){
        if(err) {
  "Unable to load encryption key");
            console.log("Decoded: " + JSON.stringify(decoded));
            var policy = new AuthPolicy(decoded.sub, "<AWS-ACCOUNT-ID>", {
                region: "<REGION>",
                restApiId: "<API GATEWAY ID>",
                stage: "<STAGE>"
            policy.allowMethod(AuthPolicy.HttpVerb.GET, "*");

Finally, the fifth and last step is to add the authorizer in the API Gateway, for that, go to the Authorizers section in the Gateway you created and click on  "Create New Authorizer". Follow the details as follows:

Authorizer Configuration

Authorizer Configuration

As you can see, the token will be sent as part of the query string, other options are to send the token as a header or a stage variable.

If you have any comment, don't hesitate in contacting me or leaving a comment below. And remember to follow me on @cannyengineer to get updated on every new post.