MikroORM 3: Knex.js, CLI, Schema Updates, Entity Generator and more…
Tags: javascript, typescript, node, showdev
Published: 2020-01-16 17:03:51

---
title: MikroORM 3: Knex.js, CLI, Schema Updates, Entity Generator and more…
published: true
date: 2020-01-16 16:27:43 UTC
tags: javascript,typescript,nodejs,showdev
canonical_url: ''
---

New major version of the TypeScript ORM has been released, read about its new features and breaking changes.

### In case you don’t know…

If you never heard of [MikroORM](https://github.com/mikro-orm/mikro-orm), it’s a TypeScript data-mapper ORM with Unit of Work and Identity Map. It supports MongoDB, MySQL, PostgreSQL and SQLite drivers currently. Key features of the ORM are:

- [Implicit transactions](https://github.com/mikro-orm/mikro-orm#implicit-transactions)
- [ChangeSet based persistence](https://github.com/mikro-orm/mikro-orm#changeset-based-persistence)
- [Identity map](https://mikro-orm.io/docs/identity-map/)

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

You can read the full [introductory article here](https://medium.com/dailyjs/introducing-mikro-orm-typescript-data-mapper-orm-with-identity-map-9ba58d049e02) or [browse through the docs](https://mikro-orm.io).

### Integrated Knex.js

You probably know Knex.js already, but if you don’t, it is a “batteries included” SQL query builder for **Postgres** , **MSSQL** , **MySQL** , **MariaDB** , **SQLite3** , **Oracle** , and **Amazon Redshift** designed to be flexible, portable, and fun to use.

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

Knex.js is now used as both a query builder and a query runner for all SQL drivers. This allows to simplify SQL driver implementations as well as brings some new possibilities.

#### Using Knex.js

You can access configured knex instance via qb.getKnexQuery() method. Then you can execute it via the Connection.execute() and map the results via EntityManager.map().

{% gist https://gist.github.com/B4nan/ed1fd1c1d8c37b7ebe80d86bc26a7c05 %}

You can also get clear and configured knex instance from the connection via getKnex() method. As this method is not available on the base Connection class, you will need to either manually type cast the connection to AbstractSqlConnection (or the actual implementation you are using, e.g. MySqlConnection), or provide correct driver type hint to your EntityManager instance, which will be then automatically inferred in em.getConnection() method.

> Driver and connection implementations are not directly exported from mikro-orm module. You can import them from mikro-orm/dist (e.g. import { PostgreSqlDriver } from 'mikro-orm/dist/drivers/PostgreSqlDriver').

{% gist https://gist.github.com/B4nan/7539d60a9ae1127d3747c11edabe60bb %}

#### Connection Pooling

With Knex.js used as a query runner, support for connection pooling is finally available. [Tarn.js](https://github.com/vincit/tarn.js) is used for this internally, using connection pool with min: 2, max: 10 for the MySQL and PG libraries, and a single connection for sqlite3 by default. Use pool option to change this when initializing the ORM.

{% gist https://gist.github.com/B4nan/6dffed1a264481a9ec1c72d8ce8a8760 %}

#### More SQL Drivers?

One of the strongest reasons to integrate Knex.js was that it allows to simplify and unify SQL drivers and opens doors for implementing new SQL drivers. Knex.js currently supports (apart from those currently supported by MikroORM): MSSQL, Oracle and Amazon Redshift.

Thanks to AbstractSqlDriver and AbstractSqlConnection classes it should be fairly simple to implement them. I am open for PRs for those drivers, as I would like to focus on developing new ORM features mainly, instead of learning new SQL dialects I have never used. I will be happy to assist to anybody interested — feel free to reach me out either via Slack, email or GitHub issues.

### Simplified Entity Definition

Now it is no longer needed to merge entities with IEntity interface, that was polluting entity's interface with internal methods. New interfaces IdentifiedEntity\<T\>, UuidEntity\<T\> and MongoEntity\<T\> are introduced, that should be implemented by entities. They are not adding any new properties or methods, keeping the entity's interface clean.

IEntity interface has been renamed to AnyEntity\<T, PK\> and it no longer has public methods like toJSON(), toObject() or init(). One can use wrap() method provided by ORM that will enhance property type when needed with those methods (e.g. await wrap(book.author).init()). To keep all methods available on the entity, you can still use interface merging with WrappedEntity\<T, PK\> that both extends AnyEntity\<T, PK\> and defines all those methods.

You will need to mark the entity by implementing one of \*Entity interfaces:

- IdEntity\<T\> for numeric/string PK on id property (id: number)
- UuidEntity\<T\> for string PK on uuid property (uuid: string)
- MongoEntity\<T\> for mongo, where id: string and \_id: ObjectId are required
- AnyEntity\<T, PK\> for other possible properties (fill the PK property name to PK parameter, e.g.: AnyEntity\<Book, 'myPrimaryProperty'\>')

{% gist https://gist.github.com/B4nan/fb04f5827d6e1f4aef0c7682a9c12cb6 %}

To keep all public methods that were part of IEntity interface in v2, you can use [WrappedEntity\<T, PK\> via interface merging](https://mikro-orm.io/docs/defining-entities#using-wrappedentity-interface).

### Nested Queries

SQL driver now support nested where and orderBy conditions. This means that you can query by properties of a relationship and the relation will be automatically joined for you. They are available both in EntityManager and QueryBuilder APIs.

{% gist https://gist.github.com/B4nan/0edd90c8e328a72c5d3d62270df1511b %}

### Strict Typing of Queries

Previously the where parameter of EntityManager’s find methods (find(), findOne(), count()) was weakly typed. It allowed users to pass pretty much anything there.

Now the query is strictly typed, only entity properties and operators can be used and the type of property value is also checked.

{% gist https://gist.github.com/B4nan/ff800fac1e60d98e1dcf5662c8f5a3b8 %}

### Improved Schema Generator

SchemaGenerator now supports creating, updating and dropping the schema. You can either get the SQL queries as array of strings or directly run them on the database.

> Always check the generated SQL first before running it.

There is also new columnType property attribute you can use to specify the database specific column type explicitly.

### Migrations

![](https://cdn-images-1.medium.com/max/628/0*b3RWZY_ROCrJs3RE.jpeg)

Better way to handle schema updates than using the SchemaGenerator directly is to use Migrations. MikroORM 3 has [integrated support for migrations](https://mikro-orm.io/docs/migrations) via [umzug](https://github.com/sequelize/umzug). It allows you to generate migrations with current schema differences.

By default, each migration will be all executed inside a transaction, and all of them will be wrapped in one master transaction, so if one of them fails, everything will be rolled back.

### Generating Entities from Current Database

As a counterpart to the SchemaGenerator that propagates changes in your entities to the database schema, there is now [EntityGenerator](https://mikro-orm.io/docs/entity-generator) to help you with reverse engineering current database schema and creating entities based on it.

It supports basic entity definition including ManyToOne and OneToOne relationships. Currently ManyToMany will be generated as additional entity with two ManyToOne relations and you will need to refactor this yourself.

While it can help a lot, there is quite a lot of room for improvement. In future I would like to implement proper support for ManyToMany relations as well for enums and indexes. Another possible extension would be to allow editing existing entities (syncing them with current schema).

### CLI

While you can use SchemaGenerator and EntityGenerator manually, much easier way is to use [new CLI tool](https://mikro-orm.io/docs/installation#setting-up-the-commandline-tool). Simply create configuration file in root directory or add its path to package.json. TypeScript files are also supported via ts-node:

{% gist https://gist.github.com/B4nan/1f4b89aa1f0f9c92a27973ac8a3d18e7 %}

Now you can use the CLI with help of [npx](https://github.com/npm/npx):

{% gist https://gist.github.com/B4nan/61dd8a59792239afa3f6c15e7c3f3425 %}

To verify your setup, you can use the mikro-orm debug command. Once you have it configured properly, you can also re-use it when initializing the ORM:

```
// when no options parameter is provided, CLI config will be used
const orm = await MikroORM.init();
```

### Custom Mapping Types

![](https://cdn-images-1.medium.com/max/500/0*zAn0BtH_iz7b8Ywj.jpg)

With [Custom Types](https://mikro-orm.io/docs/custom-types/) we can now enhance how the database value will be represented in the ORM. You can define custom types by extending Type abstract class, it has 4 optional methods:

- convertToDatabaseValue(value: any, platform: Platform): any

Converts a value from its JS representation to its database representation of this type. By default returns unchanged value.

- convertToJSValue(value: any, platform: Platform): any

Converts a value from its database representation to its JS representation of this type. By default returns unchanged value.

- toJSON(value: any, platform: Platform): any

Converts a value from its JS representation to its serialized JSON form of this type. By default converts to the database value.

- getColumnType(prop: EntityProperty, platform: Platform): string

Gets the SQL declaration snippet for a field of this type. By default returns columnType of given property.

Here is a simplified version of DateType that is already present in the ORM:

{% gist https://gist.github.com/B4nan/37b000a25dae30401569e11cf44ae3c3 %}

### And Many More…

There are many more new features, see the [changelog](https://github.com/mikro-orm/mikro-orm/blob/master/CHANGELOG.md) to read the full list. Here are few of them worth mentioning:

- [Improved support for References](https://mikro-orm.io/docs/entity-references/)
- [Navite Enum support](https://mikro-orm.io/docs/defining-entities/#enums)
- [em.findAndCount()](https://mikro-orm.io/docs/entity-manager#fetching-paginated-results) and [em.findOneOrFail()](https://mikro-orm.io/docs/entity-manager#handling-not-found-entities) methods
- [ReflectMetadataProvider](https://mikro-orm.io/docs/metadata-providers/#reflectmetadataprovider) as a fast alternative to ts-morph reflection
- Improved logging with query highlighting
- [Support for bundling via Webpack](https://mikro-orm.io/docs/deployment/#deploy-a-bundle-of-entities-and-dependencies-with-webpack)
- Eager loading
- [Read Connections](https://mikro-orm.io/docs/read-connections)
- More strict entity definition validation

### Notable Breaking Changes

Here is a short list of breaking changes. You can see the full list in the docs: [https://mikro-orm.io/docs/upgrading-v2-to-v3/](https://mikro-orm.io/docs/upgrading-v2-to-v3/).

#### Auto-flushing Disabled by Default

> _If you had_ _autoFlush: false in your ORM configuration before, you can now remove this line, no changes are needed in your app._

Default value for autoFlush is now false. That means you need to call em.flush() yourself to persist changes into database. You can still change this via ORM's options to ease the transition but generally it is not recommended as it can cause unwanted small transactions being created around each persist.

{% gist https://gist.github.com/B4nan/3a697961ce3f334095af3bb1d41c47d3 %}

#### Transactions API

Transactions now require using em.transactional() method, previous methods beginTransaction/commit/rollback are now removed.

{% gist https://gist.github.com/B4nan/5afb06d2a3e500e43d4334ed770a091f %}

### Making it a bit more Professional…

Not a big deal, but probably worth mentioning — MikroORM’s repository has been transferred to new [MikroORM GitHub Organization](https://github.com/mikro-orm) and the website is now moved to [mikro-orm.io](https://mikro-orm.io). Old links should be properly redirected, if you find some 404, please let me know thru GitHub issues!

Website has also been redesigned — now it is built with Docusaurus (v2) and provides fulltext search by Algolia. The docs are now also [versioned](https://mikro-orm.io/versions).

[Check it out!](https://mikro-orm.io)

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

### What’s next?

Here are some features I am planning to work in the near future:

- Composite primary keys
- Transactions in MongoDB
- Complex hydration of joined result sets
- Slow query log
- M:N support in entity generator

There are also some interesting suggestion in the Github issues, like [Dataloader integration](https://github.com/mikro-orm/mikro-orm/issues/266).

#### WDYT?

So that is MikroORM 3, what do you think about it? What features or changes would you like to see next? Or what part of the documentation should be improved and how?

> _Like_ [_MikroORM_](https://mikro-orm.io)_? ⭐️_ [_Star it_](https://github.com/mikro-orm/mikro-orm) _on GitHub and share this article with your friends._

* * *