BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage Articles Using TypeScript with the MySQL Database

Using TypeScript with the MySQL Database

This item in japanese

Bookmarks

Key Takeaways

  • TypeScript is a strict syntactical, typed superset of JavaScript.
  • TypeScript overcomes the limitations of JavaScript in modern, large-scale applications.
  • A Decorator is an experimental feature that is used to annotate a class declaration, method, accessor, property or annotation.
  • TypeScript is ranked in the top 50 on the TIOBE index of programming languages.
  • The TypeORM package provides object-relational mapping to access relational databases such as MySQL database with TypeScript.

JavaScript is a scripting language based on the ECMAScript specification designed for the web. JavaScript has evolved from being a client-side only scripting language to one that runs on both the client- and the server-side. The most notable implementation of server-side JavaScript is Node.js.

Problem

JavaScript lacks several features that are used by large-scale modern web applications; features such as type annotations, compile-time type checking, and type inference.  JavaScript code becomes complex in large-scale applications.

Solution

TypeScript was introduced as a typed superset of JavaScript to ease some of the limitations of JavaScript in large-scale applications.

TypeScript is a strict syntactical superset of JavaScript adding features such as compile-time type checking, type annotations, type inference, type erasure and object-oriented features such as support for interfaces and.  TypeScript is an open source scripting language and transpiles to JavaScript. The transpiled output is clean, idiomatic JS rather than something that only machines can read. 

TypeScript has added an experimental feature called Decorators that provides support for adding additional features to classes and class members using annotations and a meta-programming syntax. Decorators are declarations of the form @expression in which expression evaluates to a function to be called at runtime along with information about the decorated declaration. A decorator may be used to annotate a class declaration, method, accessor, property or annotation. Decorators are used in this article. 

TypeScript, introduced in 2012, has had a recent surge in popularity. A recent JavaScript and Web Development InfoQ Trends Report notes that "TypeScript has had a dramatic rise in popularity, now in the top 10 most popular programming languages on GitHub...". In June 2018 TypeScript made its debut on the TIOBE Index of programming languages top 100 at #93 and the following month was ranked in the top 50. More recently TypeScript is ranked at 44 on the TIOBE index.

TypeScript has emerged as a powerful environment for authoring web applications, providing significant improvements over standard JavaScript while remaining consistent with the language. In this article we'll explore in depth the details necessary to use TypeScript with Node.js, MySQL, and TypeORM to create a powerful solution for managing database access with server-side TypeScript. We'll build an example CRUD application to provide a complete end to end solution. The example application shall model a journal catalog.  We shall assume familiarity with JavaScript. This article has the following sections.

  • Setting the Environment
  • Creating a Project
  • Configuring Project
  • Creating an Entity
  • Creating a Connection to Generate Tables
  • Running Application
  • Exploring Database Tables
  • Using Repository to Add Data
  • Finding Entities with Connection Manager
  • Finding Entities with Repository
  • Updating Data
  • Removing Data
  • Creating a One-to-One Relation
  • Finding Objects with Relations
  • Creating a One-to-Many Relation
  • Creating a Many-to-Many Relation

Setting the Environment

Download and install the following software.

Next, we need to install some Node.js modules (packages). We shall be using the TypeORM package, which provides object-relational mapping for TypeScript to access most relational databases including MySQL database.

Install the typeorm package.

npm install typeorm -g

Install the reflect-metadata library, which is needed when using class decorators. The reflect-metadata library is experimental just as decorators are. 

npm install reflect-metadata -g

Install node typings.

npm install @types/node -g

Install the MySQL Database driver.

npm install mysql -g

Creating a Project

Create a TypeORM project for MySQL database. The project name is arbitrary  (MySQLProject).

typeorm init --name MySQLProject --database mysql

A project directory MySQLProject gets created. Change directory (cd) to the MySQLProject directory and list the project files.

cd MySQLProject
C:\Typescript\MySQLProject>DIR
 Volume in drive C is OS
 Volume Serial Number is BEFC-C04A
 Directory of C:\Typescript\MySQLProject
02/02/2019  05:17 PM  <DIR>        .
02/02/2019  05:17 PM  <DIR>       ..
02/02/2019  05:13 PM              47 .gitignore
02/02/2019  05:17 PM  <DIR>       node_modules
02/02/2019  05:13 PM            473 ormconfig.json
02/02/2019  05:17 PM          46,178 package-lock.json
02/02/2019  05:17 PM            406 package.json
02/02/2019  05:13 PM            172 README.md
02/02/2019  05:13 PM  <DIR>       src
02/02/2019  05:13 PM            298 tsconfig.json
            6 File(s)       47,574 bytes
            4 Dir(s)  25,897,005,056 bytes free
C:\Typescript\MySQLProject>

Install the project dependencies.

npm install

The output from preceding commands is listed:

C:\Typescript>typeorm init --name MySQLProject --database mysql
Project created inside C:\Typescript/MySQLProject directory.
C:\Typescript>cd MySQLProject
 
C:\Typescript\MySQLProject>npm install
npm notice created a lockfile as package-lock.json. You should commit this file.
npm WARN MySQLProject@0.0.1 No repository field.
npm WARN MySQLProject@0.0.1 No license field.
added 153 packages from 447 contributors and audited 231 packages in 70.022s
found 0 vulnerabilities

The MySQLProject configuration and src files are available on GitHub including scripts added in this article.

Configuring Project

The TypeScript  compiler options are configured in the tsconfig.json. Modify tsconfig.json to enable the following settings.

"emitDecoratorMetadata": true,
"experimentalDecorators": true,

The other compiler options such as target compile version are predefined in tsconfig.json. The --experimentalDecorators option enables experimental support for decorators as specified in the ECMAScript (ES)  specification standard for JavaScript.   The --emitDecoratorMetadata setting emits design-type metadata for decorated declarations in source. The reflect-metadata library would need to be imported in a TypeScript program to emit decorator metadata.

Modify the ormconfig.json to configure the database options to connect to MySQL database. While the host and port would be the default if a local MySQL database is used the username and password could be different.

{
   "type": "mysql",
   "host": "localhost",
   "port": 3306,
   "username": "root",
   "password": "mysql",
   "database": "mysql",
   "synchronize": true,
   "logging": false,
   "entities": [
      "src/entity/**/*.ts"
   ],
   "migrations": [
      "src/migration/**/*.ts"
   ],
   "subscribers": [
    "src/subscriber/**/*.ts"
   ]
}

Creating an Entity

In this section, we shall develop an entity to model a journal catalog. An entity is a class that maps to a database table.  What makes a class an entity is the decorator @Entity() from the typeorm library.  Add a file Catalog.ts in the entity directory to define an entity. Add an import declaration to import the Entity, Column, and PrimaryGeneratedColumn functions from the typeorm library.

import {Entity, Column, PrimaryGeneratedColumn} from "typeorm";

Export a class called Catalog and annotate or decorate the class with @Entity().


@Entity()
export class Catalog {
…
}

The complete Catalog.ts script is available on the GitHub. A basic entity consists of columns and every entity must declare at least one primary column.  TypeORM provides several types of primary columns as discussed in Table 1.

Primary Column Type

Description

@PrimaryColumn()

Creates a primary column. The column type could  be optionally specified and if not set is inferred from the property type. The value for the primary column must be user supplied.

@PrimaryGeneratedColumn()

Creates a primary column of type int that is automatically generated with an auto-increment value.    

@PrimaryGeneratedColumn("uuid")

Creates a primary column that is also automatically generated with a uuid value; uuid being a unique string value.     

Table 1. Primary Column Types

Add a primary column called id to the Catalog entity.  MySQL database supports auto-increment of a primary column with which a unique primary key value is assigned by the database automatically for each new row of data.  To avail of the auto increment feature use the auto-generated column @PrimaryGeneratedColumn.

@PrimaryGeneratedColumn()
  id: number;

We are modeling a journal catalog in the example application. Add other columns; journal, publisher, edition, title and author all of type string. Add a column called isPublished of type Boolean to indicate whether the catalog entry is published. The entity property types are mapped to appropriate database column types, which could vary with the database used. The string type is mapped to a varchar (255) or similar database type. The number property type is mapped to an integer or similar database type. The entity-database type mapping may also be user supplied. As an example, map the string type column title to MySQL database type text.

@Column("text")
  title: string;

The default length for the string type is 255 but a custom length may be specified as an example:

@Column({
      length: 25
  })
  edition: string;

Copy and paste the following listing to the entity file.

import {Entity, Column, PrimaryGeneratedColumn} from "typeorm";
@Entity()
export class Catalog {
    @PrimaryGeneratedColumn()
  id: number;
  @Column()
  journal: string;
 
  @Column()
  publisher: string;
 
  @Column({
      length: 25
  })
  edition: string;
 
    @Column("text")
  title: string;
 
  @Column()
  author: string;
 
  @Column()
  isPublished: boolean;
}

Creating a Connection to Generate Tables

As mentioned before we are developing a CRUD (Create, Read, Update and Delete) application. To be able to develop the application we would need to connect with a MySQL database.

In this section we shall develop a TypeScript script to connect to MySQL database and create tables for the entities defined in the entity directory. We have defined only one entity in the entity/Catalog.ts file. Delete any other entity scripts from the entity directory including the project default User.ts. Create an index.js in src directory of the TypeScript project MySQLProject.  Import the reflect-metadata library to be able to use decorators. Import the createConnection function from the typeorm library. Import the Catalog class from the entity directory. We shall use the async/await syntax, which was added in the ES2017.  With the async/await syntax asynchronous functions are prefixed with async keyword. The await keyword suspends the running of the script until an asynchronous function return promise is fulfilled. Create a connection using the createConnection function in which the connection options including database type, host, port, username, password, database name and entities are specified.

createConnection({
  type: "mysql",
  host: "localhost",
  port: 3306,
  username: "root",
  password: "mysql",
  database: "mysql",
  entities: [
     __dirname + "/entity/*.ts"
  ],
  synchronize: true,
  logging: false
}).then(async connection => {
…
…
}).catch(error => console.log(error));

In the then block create an instance of the Catalog entity.

  let catalog = new Catalog();

Set entity properties’ values to create a catalog entry.

    catalog.journal = "Oracle Magazine";
  catalog.publisher = "Oracle Publishing";
  catalog.edition = "March-April 2005";
  catalog.title = "Starting with Oracle ADF";
  catalog.author = "Steve Muench";
  catalog.isPublished = true;

Obtain an instance of EntityManager and save the entity instance using the save method.

await connection.manager.save(catalog);

The save method saves all given entities in the database. The save method first verifies if the entity already exists in the database. If it does the save method updates the entity and if it doesn’t the save method adds a new entity. Similarly, add another entity. The src/index.ts is listed:

import "reflect-metadata";
import {createConnection} from "typeorm";
import {Catalog} from "./entity/Catalog";
 
createConnection({
  type: "mysql",
  host: "localhost",
  port: 3306,
  username: "root",
  password: "mysql",
  database: "mysql",
  entities: [
      __dirname + "/entity/*.ts"
  ],
  synchronize: true,
  logging: false
}).then(async connection => {
 
  let catalog = new Catalog();
  catalog.journal = "Oracle Magazine";
  catalog.publisher = "Oracle Publishing";
  catalog.edition = "March-April 2005";
  catalog.title = "Starting with Oracle ADF";
  catalog.author = "Steve Muench";
  catalog.isPublished = true;
 
  await connection.manager.save(catalog);
  console.log('Catalog has been saved'+'\n');
 
     let catalog2 = new Catalog();
  catalog2.journal = "Oracle Magazine";
  catalog2.publisher = "Oracle Publishing";
  catalog2.edition = "November December 2013";
  catalog2.title = "Engineering as a Service";
  catalog2.author = "David A. Kelly";
  catalog2.isPublished = true;
 
  await connection.manager.save(catalog2);
  console.log('Catalog has been saved'+'\n');
 
}).catch(error => console.log(error));

Running the Application

Having created the application, run the application with the following command.

npm start

The database tables for entities in the project get created as index.js runs to connect to the database. Only one database table Catalog gets created. Table data gets added. The output from the command is as follows:

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
Catalog has been saved
Catalog has been saved

Exploring Database Tables

Next, we shall explore the MySQL database tables generated from the MySQL CLI (command-line interface). Start the MySQL CLI shell to display the mysql command prompt.

C:\mysql-5.7.25-winx64\mysql-5.7.25-winx64\bin>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.25 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>  

Set database to use as mysql.

mysql> use mysql
Database changed

List tables and the catalog table gets listed.

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| catalog                |
…
| user                      |
+---------------------------+
32 rows in set (0.00 sec)

Describe the catalog table and it lists columns corresponding to Catalog entity fields. Because we are using an auto-generated primary key column with auto increment, the id column value is set by the database.

mysql> DESC catalog;
+-------------+--------------+------+-----+---------+----------------+
| Field     | Type      | Null | Key | Default | Extra        |
+-------------+--------------+------+-----+---------+----------------+
| id        | int(11)   | NO   | PRI | NULL | auto_increment |
| journal   | varchar(255) | NO   |   | NULL    |             |
| publisher   | varchar(255) | NO   |   | NULL    |             |
| edition   | varchar(25)  | NO   |     | NULL  |             |
| title     | text      | NO   |  | NULL  |             |
| author    | varchar(255) | NO   |   | NULL    |             |
| isPublished | tinyint(4)   | NO   |   | NULL    |             |
+-------------+--------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

Run an SQL query with a SELECT statement to list data.


mysql> SELECT * FROM catalog;
| id | journal      | publisher       | edition             | title
            | author      | isPublished |
|  1 | Oracle Magazine | Oracle Publishing | March-April 2005     | Starting w
ith Oracle ADF | Steve Muench   |         1 |
|  2 | Oracle Magazine | Oracle Publishing | November December 2013 | Engineering as a Service | David A. Kelly |         1 |

2 rows in set (0.01 sec)
 
mysql>

Using Repository to Add Data

Two options are available to perform operations on an entity, EntityManager, which we already used, and Repository, which we shall discuss in this section. When using a lot of entities Repository is the better option as each entity is associated with its own repository.  Repository provides most of the same functions as EntityManager does.  We added two instances of Catalog using an EntityManager. In this section, we shall add a third Catalog instance using a Repository. We shall be using the same index.js, therefore delete the code used to create a Catalog entry with EntityManager.  Create an instance of Catalog as in the previous section. Obtain a Repository instance from a connection.

let catalogRepository = connection.getRepository(Catalog);

Save the Catalog instance using the save function.

await catalogRepository.save(catalog);

The modified index.ts is listed:

import {createConnection} from "typeorm";
import {Catalog} from "./entity/Catalog";
createConnection({
  type: "mysql",
  host: "localhost",
  port: 3306,
  username: "root",
  password: "mysql",
  database: "mysql",
  entities: [
      __dirname + "/entity/*.ts"
  ],
  synchronize: true,
  logging: false
}).then(async connection => {
 
let catalog = new Catalog();
  catalog.journal = "Oracle Magazine";
  catalog.publisher = "Oracle Publishing";
  catalog.edition = "November December 2013";
  catalog.title = "Quintessential and Collaborative";
  catalog.author = "Tom Haunert";
  catalog.isPublished = true;

  let catalogRepository = connection.getRepository(Catalog);

  await catalogRepository.save(catalog);
  console.log('Catalog has been saved'+'\n');
 
  let [all_Catalogs, CatalogsCount] = await catalogRepository.findAndCount();

  console.log('Catalogs count: ', CatalogsCount+'\n');

}).catch(error => console.log(error));

Run the application as before and another instance of Catalog gets saved. As we added two catalog entries earlier a catalog count of 3 is listed.

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
Catalog has been saved
Catalogs count:  3

Run an SQL query and three rows of data get listed:

mysql> SELECT * FROM catalog;
| id | journal      | publisher       | edition             | title
                    | author      | isPublished |
|  1 | Oracle Magazine | Oracle Publishing | March-April 2005       | Starting w
ith Oracle ADF      | Steve Muench   |        1 |
|  2 | Oracle Magazine | Oracle Publishing | November December 2013 | Engineerin
g as a Service      | David A. Kelly |        1 |
|  3 | Oracle Magazine | Oracle Publishing | November December 2013 | Quintessen
tial and Collaborative | Tom Haunert  |           1 |
3 rows in set (0.00 sec)
mysql>

Finding Entities With Entity  Manager

In this section, we shall find data using the EntityManager class. EntityManager provides several methods to find data as discussed in Table 2.

Method

Description

find

Finds entities for the supplied options.

findAndCount

Finds and counts entities for the supplied options. Pagination options (from and take) are ignored.

findByIds

Finds entities for the supplied ids.

findOne

Finds the first entity that matches the options.

findOneOrFail

Finds the first entity that matches the options or fails.

Table 2. EntityManager Methods to Find Data

Modify the index.ts to find all Catalog entities. Use the find method to find all catalog entries.

createConnection({
  ...
  ...
}).then(async connection => {
   let savedCatalogs = await connection.manager.find(Catalog);
  console.log("All catalogs from the db: ", savedCatalogs);
}).catch(error => console.log(error));

Run the application. All catalog entries get listed (only two entities are listed as the script is run before adding the third entity with Repository.):

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
All catalogs from the db:  [ Catalog {
  id: 1,
  journal: 'Oracle Magazine',
  publisher: 'Oracle Publishing',
  edition: 'March-April 2005',
  title: 'Starting with Oracle ADF',
  author: 'Steve Muench',
  isPublished: true },
  Catalog {
  id: 2,
  journal: 'Oracle Magazine',
  publisher: 'Oracle Publishing',
  edition: 'November December 2013',
  title: 'Engineering as a Service',
  author: 'David A. Kelly',
  isPublished: true } ]

Finding Entities With Repository

In this section, we shall use the Repository class to find entities. But first, we need to install the class-transformer package, which is used to serialize/deserialize JSON objects.

C:\Typescript\MySQLProject>npm install class-transformer -g
+ class-transformer@0.2.0
added 1 package from 1 contributor in 10.452s

In the index.ts obtain an instance of Repository as before. First, use the find method to find entities. The find method has the syntax:

find(options?: FindManyOptions<Entity>)

The FindManyOptions are specified as one or more properties discussed in Table 3.

Property

Description

cache

Enables or Disables caching of query result.

join

Specifies relations to be loaded.

loadEagerRelations

Specifies whether eager relations are to be loaded. By default, eager relations are loaded with find methods. “Eager” relations loading refers to the loading of associated model data when an entity is loaded.

loadRelationIds

Specifies whether relation ids are to be loaded. If set to true all relation ids are loaded and mapped into relation values.

order

Specifies the order in which entities are to be ordered.

relations

Specifies what relations of entities are loaded.

select

Specifies which columns should be selected.

skip

The number of entities to skip or offset from entities should be taken.

take

The maximum number of entities to take.

where

Specifies where conditions to apply.

Table 3. FindManyOptions Properties

Modify index.ts to find entities using the find methods. First, obtain a Repository object. Next, using the find method find all entities using the select option to select only title and author columns.

     let allCatalogs = await catalogRepository.find({ select: ["title", "author"] });

Use the serialize method to serialize the result as JSON.

     console.log("All Catalogs from the db: ", serialize(allCatalogs)+'\n');

Use the findOne method to find entity with id as 1. Output the result as JSON with the serialize method.

let firstCatalog = await catalogRepository.findOne(1);
  console.log('First Catalog from the db: ', serialize(firstCatalog)+'\n');

Next, using the findOne method find the first entity with title as “Engineering as a Service”.

  let specificTitleCatalog = await catalogRepository.findOne({ title: "Engineering as a Service"});

Output the result as JSON.

console.log("'Engineering as a Service' Catalog from the db: ", serialize(specificTitleCatalog)+'\n');

Find all entities with edition as "November December 2013".

let allSpecificEditionCatalogs = await catalogRepository.find({ edition: "November December 2013"});
  console.log('All November December 2013 Catalogs: ', serialize(allSpecificEditionCatalogs)+'\n');

Find all entities with isPublished as true.

let allPublishedCatalogs = await catalogRepository.find({ isPublished: true });
  console.log('All published Catalogs: ', serialize(allPublishedCatalogs)+'\n');

Run the application and the result is listed:


C:\Typescript\MySQLProject>npm start

> MySQLProject@0.0.1 start C:\Typescript\MySQLProject

> ts-node src/index.ts



All Catalogs from the db:  [{"title":"Starting with Oracle ADF","author":"Steve

Muench"},{"title":"Engineering as a Service","author":"David A. Kelly"},{"title"

:"Quintessential and Collaborative","author":"Tom Haunert"}]



First Catalog from the db:  {"id":1,"journal":"Oracle Magazine","publisher":"Ora

cle Publishing","edition":"March-April 2005","title":"Starting with Oracle ADF",

"author":"Steve Muench","isPublished":true}



'Engineering as a Service' Catalog from the db:  {"id":2,"journal":"Oracle Magaz

ine","publisher":"Oracle Publishing","edition":"November December 2013","title":

"Engineering as a Service","author":"David A. Kelly","isPublished":true}



All November December 2013 Catalogs:  [{"id":2,"journal":"Oracle Magazine","publ

isher":"Oracle Publishing","edition":"November December 2013","title":"Engineeri

ng as a Service","author":"David A. Kelly","isPublished":true},{"id":3,"journal"

:"Oracle Magazine","publisher":"Oracle Publishing","edition":"November December

2013","title":"Quintessential and Collaborative","author":"Tom Haunert","isPubli

shed":true}]



All published Catalogs:  [{"id":1,"journal":"Oracle Magazine","publisher":"Oracle

Publishing","edition":"March-April 2005","title":"Starting with Oracle ADF","author":"Steve 

Muench","isPublished":true},{"id":2,"journal":"Oracle Magazine","publisher":"Oracle 

Publishing","edition":"November December 2013","title":"Engineering as a Service","author":"David A. 

Kelly","isPublished":true},{"id":3,"journal":"Oracle Magazine","publisher":"Oracle 

Publishing","edition":"November December 2013","title":"Quintessential and 

Collaborative","author":"Tom Haunert","isPublished":true}]

Updating an Entity

In this section, we shall update a Catalog entity. Both the EntityManager and Repository classes provide the update method to update an entity partially. The update method is fast and efficient but has the following limitations:

  • Performs a primitive operation without cascades, relations and other operations included.
  • Does not verify if entity exists in the database.

The save method updates an entity if it already exists and adds a new entity if it does not.  The save method does not have the limitations the update has. As an example, we shall update the title of an entity, the Catalog entity with id 1, from “Starting with Oracle ADF” to “Beginning with Oracle ADF”. Obtain an instance of Repository for Catalog entity.

Find the entity with id as 1.

let catalogToUpdate = await catalogRepository.findOne(1);

Set the title to “Beginning with Oracle ADF”.

catalogToUpdate.title = "Beginning with Oracle ADF";

Save the entity with the save method.

await catalogRepository.save(catalogToUpdate);

Subsequently, find the entity with id 1 again.

let updatedCatalog  = await catalogRepository.findOne(1);

Output the updated entity as JSON.

console.log('Updated Catalog from the db: ', serialize(updatedCatalog)+'\n');

Run the application and the entity gets updated. The updated entity gets output.

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
First Catalog from the db:  {"id":1,"journal":"Oracle Magazine","publisher":"Ora
cle Publishing","edition":"March-April 2005","title":"Beginning with Oracle ADF"
,"author":"Steve Muench","isPublished":true}

Removing an Entity

In this section, we shall remove an entity. Both the EntityManager and Repository classes provide the methods discussed in Table 4 to remove an entity.

Method

Description

delete

Deletes entities using the provided conditions that cannot be empty. Has the same drawbacks as the update method that it does not verify if entity exists and cascades and relations are not included. Otherwise delete is fast and efficient.

remove

Removes an entity from database.

Table 4. Methods to remove Entities

Modify index.ts to demonstrate removing an entity. Obtain an instance of Repository for Catalog entity as before.

Find an entity with id 1 using the findOne method.

let catalogToRemove = await catalogRepository.findOne(1);

Remove the entity using the remove method.

await catalogRepository.remove(catalogToRemove);

Subsequently, find and output the same entity to verify if it has been removed. If removed the result should not list any entities.

let firstCatalog = await catalogRepository.findOne(1);
    console.log("First Catalog from the db: ", serialize(firstCatalog));

Run application and the result of findOne is undefined. Which implies that the entity has been removed.

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
First Catalog from the db:  undefined

Run an SQL query in MySQL CLI and the data row with id as 1 is not listed.

mysql> SELECT * FROM catalog;
| id | journal       | publisher      | edition             | title
                    | author      | isPublished |
|  2 | Oracle Magazine | Oracle Publishing | November December 2013 | Engineerin
g as a Service      | David A. Kelly |        1 |
|  3 | Oracle Magazine | Oracle Publishing | November December 2013 | Quintessen
tial and Collaborative | Tom Haunert  |           1 |
2 rows in set (0.00 sec)
mysql>

Creating a One-to-One Relation between Entities

TypeORM supports several kinds of relations between entities:

  • One To One
  • One To Many, Many To One
  • Many To Many

Decorators and functions are provided in typeorm for each of these as discussed in Table 5.

Function

Description

OneToOne

Specifies a one to one relation between entities

JoinColumn

Specifies the owning side of a one to one relation

OneToMany

Specifies a one to many relation between entities

ManyToOne

Specifies a many to one relation between entities

ManyToMany

Specifies a many to many relation between entities

JoinTable

Specifies the owning side of a many to many relation

Table 5. Functions for Relations

In this section, we shall discuss one-to-one relation between two entities.  Define a  second entity CatalogTimestamp  for Catalog timestamp.  Create a CatalogTimestamp.ts script in entity directory. Import the OneToOne and JoinColumn functions in addition to other functions.

import {Entity, Column, PrimaryGeneratedColumn, OneToOne, JoinColumn} from "typeorm";
import {Catalog} from "./Catalog";

Export class CatalogTimestamp and use the @Entity() decorator to make the class an entity.

@Entity()
export class CatalogTimestamp {
…
}

Declare a primary key column id.

    @PrimaryGeneratedColumn()
  id: number;

Add columns firstAdded,      firstUpdated and lastUpdated, all of type string.

@Column()
  firstAdded: string;
  @Column()
  firstUpdated: string;
 
  @Column()
  lastUpdated: string;

Add a one-to-one relation with Catalog entity using the   @OneToOne decorator.  A relation could be unidirectional or bi-directional. We shall specify a bi-directional  relation. The type => Catalog function, which could also be specified as () => Catalog returns the class of the entity with which a one-to-one relation exists.  The function catalog => catalog.timestamp returns the inverse side of the relation. The @JoinColumn() decorator specifies the owning side of a one-to-one  relationship and only one side could be the owning side.

  @OneToOne(type => Catalog, catalog => catalog.timestamp)
  @JoinColumn()
  catalog: Catalog;

The CatalogTimestamp entity is listed on the GitHub; copy the listing to the CatalogEntity.ts script.

We need to modify the Catalog entity to specify the one-to-one relation with CatalogTimestamp. Import the OneToOne and JoinColumn functions in addition to the other functions from typeorm. Import the CatalogTimestamp class.

import {Entity, Column, PrimaryGeneratedColumn, OneToOne, JoinColumn} from "typeorm";
import {CatalogTimestamp} from "./CatalogTimestamp";

The rest of the Catalog entity is the same as before except that we need to declare a @OneToOne decorator.  The type => CatalogTimestamp function specifies the entity with which the relation exists.  The timestamp => timestamp.catalog function specifies the inverse relation. The cascade option is set to true to save related entities whenever an entity is saved, which implies that if an instance of Catalog is saved the associated instance of CatalogTimestamp is also saved.

@OneToOne(type => CatalogTimestamp, timestamp => timestamp.catalog,{
      cascade: true,
  })
  timestamp: CatalogTimestamp;

Next, we shall modify the index.ts to use the Catalog and CatalogTimestamp entities to create a one-to-one relation.  Import the CatalogTimestamp class additionally.

import {CatalogTimestamp} from "./entity/CatalogTimestamp";

 Create a Catalog entity instance as before.

let catalog = new Catalog();
  catalog.journal = "Oracle Magazine";
  catalog.publisher = "Oracle Publishing";
  catalog.edition = "March-April 2005";
  catalog.title = "Starting with Oracle ADF";
  catalog.author = "Steve Muench";
  catalog.isPublished = true;

         Additionally, create a CatalogTimestamp entity instance.

  let timestamp = new CatalogTimestamp();
  timestamp.firstAdded = "Apr-8-2014-7:06:16-PM-PDT";
  timestamp.firstUpdated = "Apr-8-2014-7:06:20-PM-PDT";
  timestamp.lastUpdated = "Apr-8-2014-7:06:20-PM-PDT";

         Associate or connect the CatalogTimestamp and Catalog entity instances.

timestamp.catalog = catalog; 

Obtain entity repository for Catalog.

let catalogRepository = connection.getRepository(Catalog);

 Save Catalog entity with save method.

   await catalogRepository.save(catalog);
    console.log("Catalog has been saved");

The modified Catalog.ts script is listed in the GitHub project.

Run the application and the entities including relation between them gets saved. The CatalogTimestamp entity gets saved because we specified cascade.

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
Catalog has been saved
Timestamp is saved, and relation between timestamp and catalog is created in the database too

Finding Objects With Relations

In this section, we shall find objects with relations between them. The find* methods from EntityManager or Repository may be used to find objects with relations. Modify index.ts. Obtain a Repository instance for Catalog. Find all entities including relations. The relations property of FindManyOptions option to find method specifies the timestamp relation.

let catalogs = await catalogRepository.find({relations: ["timestamp"]});

Output the result as JSON.

console.log(serialize(catalogs));

Run the application to output the newly added Catalog entity and the associated CatalogTimestamp entity. Only one of the three Catalog entities has an associated non-null timestamp.

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
 
[{"id":6,"journal":"Oracle Magazine","publisher":"Oracle Publishing","edition":"
March-April 2005","title":"Starting with Oracle ADF","author":"Steve Muench","is
Published":true,"timestamp":{"id":1,"firstAdded":"Apr-8-2014-7:06:16-PM-PDT","fi
rstUpdated":"Apr-8-2014-7:06:20-PM-PDT","lastUpdated":"Apr-8-2014-7:06:20-PM-PDT
"}},{"id":2,"journal":"Oracle Magazine","publisher":"Oracle Publishing","edition
":"November December 2013","title":"Engineering as a Service","author":"David A.
 Kelly","isPublished":true,"timestamp":null},{"id":3,"journal":"Oracle Magazine"
,"publisher":"Oracle Publishing","edition":"November December 2013","title":"Qui
ntessential and Collaborative","author":"Tom Haunert","isPublished":true,"timest
amp":null}]

While using find* methods along with the FindOneOptions and FindManyOptions is suitable for most queries, the QueryBuilder is more suitable for complex queries as it provides more options and settings for specifying the subset of entities to find including WHERE expression, HAVING expression, ORDER BY expression, GROUP BY expression, LIMIT expression, and OFFSET expression, in addition to joining relations, inner and left joins, join without selection, joining and mapping functionality, pagination, and subqueries. As an example,  obtain a repository and create a QueryBuilder.  Specify an INNER JOIN with innerJoinAndSelect. To get multiple results use getMany.  To get only one result getOne should be used.  

  let catalogs = await connection
            .getRepository(Catalog)
            .createQueryBuilder("catalog")
            .innerJoinAndSelect("catalog.timestamp", "timestamp")
          .getMany();
    console.log(serialize(catalogs));

 Run application to output the Catalog entity that has a one-to-one relation with CatalogTimestamp.

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
[{"id":6,"journal":"Oracle Magazine","publisher":"Oracle Publishing","edition":"
March-April 2005","title":"Starting with Oracle ADF","author":"Steve Muench","is
Published":true,"timestamp":{"id":1,"firstAdded":"Apr-8-2014-7:06:16-PM-PDT","fi
rstUpdated":"Apr-8-2014-7:06:20-PM-PDT","lastUpdated":"Apr-8-2014-7:06:20-PM-PDT
"}}]

List MySQL tables in mysql database from MySQL CLI and the catalog and catalog_timestamp  tables get listed.

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
| catalog                |
| catalog_timestamp         |

Describe the catalog_timestamp table and the foreign key catalogId gets listed in addition to the other columns.

mysql> DESC catalog_timestamp;
+--------------+--------------+------+-----+---------+----------------+
| Field     | Type      | Null | Key | Default | Extra        |
+--------------+--------------+------+-----+---------+----------------+
| id        | int(11)   | NO   | PRI | NULL | auto_increment |
| firstAdded   | varchar(255) | NO   |     | NULL |             |
| firstUpdated | varchar(255) | NO   |  | NULL    |             |
| lastUpdated  | varchar(255) | NO   |     | NULL |             |
| catalogId | int(11)   | YES  | UNI | NULL |             |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

Run a SELECT statement on catalog_timestamp and the query result includes the foreign key column value 6, which is the id of the catalog table row with which the timestamp is associated.

mysql> SELECT * FROM catalog_timestamp;
| id | firstAdded                | firstUpdated           | lastUpdated
      | catalogId |

|  1 | Apr-8-2014-7:06:16-PM-PDT | Apr-8-2014-7:06:20-PM-PDT | Apr-8-2014-7:06:2
0-PM-PDT |      6 |
1 row in set (0.00 sec)

Run a SELECT statement on catalog and a row with id 6 should get listed. The example data could be different but a foreign key value is associated with a timestamp.

mysql> SELECT * FROM catalog;
| id | journal      | publisher       | edition             | title
                    | author      | isPublished |
|  2 | Oracle Magazine | Oracle Publishing | November December 2013 | Engineerin
g as a Service      | David A. Kelly |        1 |
|  3 | Oracle Magazine | Oracle Publishing | November December 2013 | Quintessen
tial and Collaborative | Tom Haunert  |         1 |
|  6 | Oracle Magazine | Oracle Publishing | March-April 2005     | Starting w
ith Oracle ADF      | Steve Muench   |           1 |
3 rows in set (0.00 sec)

Similarly, one-to-many and many-to-many relations may be used. Next, we shall discuss an example of a one-to-many relation.

Creating a One-to-Many Relation

To demonstrate the use of one-to-many we again need two entities and for this section we shall use a CatalogEdition entity that has a one-to-many with a CatalogEntry entity. The CatalogEdition entity specifies columns edition and isPublished in addition to primary column id. The @OneToMany decorator defines a one-to-many relation with CatalogEntry including an inverse relation; a bi-directional relation is defined.

@OneToMany(type => CatalogEntry, catalogEntry => catalogEntry.catalogEdition) 
  catalogEntries: CatalogEntry[];

The CatalogEdition entity is listed in the GitHub project.

The CatalogEntry entity specifies columns title, author, isPublished in addition to primary column id. The @ManyToOne decorator specifies a many-to-one relation with CatalogEdition including an inverse relation.

  @ManyToOne(type => CatalogEdition, catalogEdition => catalogEdition.catalogEntries)
  catalogEdition: CatalogEdition;

The CatalogEntry entity is listed in the GitHub project.

Modify the index.ts as follows.

import "reflect-metadata";
import {createConnection} from "typeorm";
import {CatalogEdition} from "./entity/Edition";
import {CatalogEntry} from "./entity/Section";
import {serialize} from "class-transformer";
 
createConnection({
  type: "mysql",
  host: "localhost",
  port: 3306,
  username: "root",
  password: "mysql",
  database: "mysql",
  entities: [
      __dirname + "/entity/*.ts"
  ],
  synchronize: true,
  logging: false
}).then(async connection => {
  // Create entity instances and save data to entities
}).catch(error => console.log(error));

Run the application to create tables and relations.

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts

Listing tables in mysql database should list the catalog_edition and catalog_entry tables in addition to other tables if any.

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| catalog                   |
| catalog_edition           |
| catalog_entry             |
| catalog_timestamp         |

Describe the catalog_edition table and it lists the three columns id, edition and isPublished.

mysql> DESCRIBE catalog_edition;
| Field   	| Type     	| Null | Key | Default | Extra      	|
| id      	| int(11)      | NO   | PRI | NULL	| auto_increment |
| edition 	| varchar(255) | NO   | 	| NULL    |            	|
| isPublished | tinyint(4)   | NO   | 	| NULL    |            	|
3 rows in set (0.01 sec)

Describe the catalog_entry table and it should include a foreign key column catalogEditionId.

mysql> DESCRIBE catalog_entry;
| Field         | Type      | Null | Key | Default | Extra        |
| id            | int(11)   | NO   | PRI | NULL | auto_increment |
| title         | varchar(255) | NO   |   | NULL    |             |
| author        | varchar(255) | NO   |   | NULL    |             |
| isPublished   | tinyint(4)   | NO   |     | NULL  |             |
| catalogEditionId | int(11)      | YES  | MUL | NULL |             |
5 rows in set (0.01 sec)

Creating a Many-to-Many Relation

In this section, we shall demonstrate creating a many-to-many relation between entities for which we again need two entities. We shall use two new entities Edition and Section. The Section entity defines columns id and name. The many-to-many bi-directional relation with Edition is defined with @ManyToMany decorator as follows.

@ManyToMany(type => Edition, edition => edition.sections)
  editions: Edition[];

The Section entity is listed in the GitHub project.

The Edition entity specifies columns id and name. The @ManyToMany decorator defines a relation with Section including the inverse relation. The @JoinTable() decorator indicates the owning side of the relation and only one side can be owning.

  @ManyToMany(type => Section, section => section.editions)
  @JoinTable()
  sections: Section[];

The Edition entity is listed in the GitHub project.

Modify the index.ts to demonstrate the many-to-many relation between entities Edition and Section. Create two instances of Edition entity and save to database.

let edition1 = new Edition();
edition1.name = "January February 2019";
await connection.manager.save(edition1);
 
let edition2 = new Edition();
edition2.name = "November December 2018";
await connection.manager.save(edition2);

Create an instance of Section entity.

let section = new Section();
section.name = "Application Development";

 Set the editions relation to edition1 and edition2.

section.editions = [edition1, edition2];

Save the Section entity to database.

await connection.manager.save(section);

Create another Section entity instance and set relation editions to just one edition, edition1. Save the Section entity instance.

let section2 = new Section();
section2.name = "DBA";
section2.editions = [edition1];
await connection.manager.save(section2);

Create a third Section entity instance, associate with edition1 and save to database. Subsequently, find one Section entity with findOne method including Edition entities associated by editions relation.

const loadedSection = await connection
  .getRepository(Section)
  .findOne(1, { relations: ["editions"] });

Output result as JSON.

console.log(serialize(loadedSection));

The modified index.ts is listed in the GitHub project.

Run the application to create a many-to-many relation between Edition and Section. The Section entity with id 1 and associated Editions are also output as JSON.

C:\Typescript\MySQLProject>npm start
> MySQLProject@0.0.1 start C:\Typescript\MySQLProject
> ts-node src/index.ts
{"id":1,"name":"Application Development","editions":[{"id":2,"name":"January Feb
ruary 2019"},{"id":3,"name":"November December 2018"}]}

List tables and the edition and section tables get listed. A junction table edition_sections_section  also gets listed.

mysql> show tables;
| Tables_in_mysql           |
| edition                   |
| edition_sections_section  |
| section                   |

Summary

TypeScript is a typed open-source superset of JavaScript and provides object-oriented features such as interfaces in addition to static typing, type annotations and type inference. TypeScript includes an experimental feature called Decorators to decorate/annotate classes and class members to provide additional features. TypeScript is designed for modern, large-scale applications. In this article, we discussed using TypeScript with MySQL database. The TypeORM library is used to connect with database, create and save entities, create relations between entities, find entities, update and delete entities. We demonstrated using TypeScript with an end-to-end object-relational-mapping application that runs on Node.js and connects to MySQL database to model a magazine catalog.

About the Author

Deepak Vohra is a Sun Certified Java Programmer and Sun Certified Web Component Developer. Deepak has published Java and Java EE related technical articles in  WebLogic Developer's Journal, XML Journal, ONJava, java.net, IBM developerWorks, Java Developer’s Journal, Oracle Magazine, and devx. Deepak has also published a book on Asynchronous JavaScript and XML;  Ajax in Oracle JDeveloper.

Rate this Article

Adoption
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Community comments

  • Useful post

    by Christa Elrod,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Thanks for sharing this useful piece of content.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

BT