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 Edition
s 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.