Minorm can be installed using yarn
or npm
.
yarn add minorm
npm install --save minorm
To use minorm all you need is to make a configuration for a connection in format:
export default {
// Your other configs
db: {
host: process.env.MYSQL_HOST || 'localhost',
user: process.env.MYSQL_USER || 'user',
password: process.env.hasOwnProperty('MYSQL_PASS')
? process.env.MYSQL_PASS
: 'password',
database: process.env.MYSQL_DB || 'app_db',
},
}
If you want to read about other conenction configuration parameters please read mysqljs/mysql doc.
Please note that minorm by default use connection pool, so there's additional config here
After you added your config all you need is to create a minorm manager:
import { createManager } from 'minorm'
import config from '../config'
export const manager = createManager(config.db)
And also in some bootstrap function you will need to call:
import { manager } from './models/manager'
//...
await manager.ready()
We recommend to do it at place where you making your routers or setuping some other connection.
That's it! There's no need to manually map all your database tables or marking relations, because minorm loads it by default
Minorm manager provide many functionality by default, but let's start from somethis easy.
For example you have database users
that contains some data, so to work with it you can:
const repo = manager.getRepository('users')
Now if you want to find something by id
you will:
const user = await repo.find(5)
if (user == null) {
// Not found
}
If you want to find some data by simple condition you also can:
const user = await repo.findOneBy({
login: 'admin',
group: {
$in: [OWNER, ADMIN],
},
status: {
$not: BLOCKED,
},
})
There's also available helpers like $notIn
and $like
that you can use.
If you want to use the same logic to select an array of results you can use:
const users = await repo.findBy({
//...some cond
})
If you need to do some complex query you can use Query Builder:
const user = await repo
.startQuery()
.where('id = ?', id)
.where('group IN ?', [OWNER, ADMIN])
.execute()
minorm uses squel Qeury Builder so you can check their docs, but with some additional helpers.
If you fetch data using find
, findOneBy
or findBy
you receive a wrapped instance of your data with additional functionality like save()
, populate()
and remove()
You can use them as:
const user = await repo.find(5)
user.login = 'new-login'
user.nonExistingColumn = 'some data'
await user.save()
const user = await repo.find(5)
user.populate({
login: 'new-login',
nonExistingColumn = 'some data',
})
await user.save()
const user = await repo.find(5)
await user.remove()
minorm knows about your database structure so there's no need to manually map all fields. That's why you can add some helper data to models and everything will work like expected
Minorm uses Squel as Query Builder but we added some helpers that can help you with your queries.
Let's start from criteria()
helper. It's accesible in select()
, update()
, remove()
and insert()
queries.
It's used in examples with repo.findOneBy
and repo.findBy
, so you can do:
const users = await repo
.startQuery('user')
.criteria({
'user.status': {
$not: BLOCKED,
},
'user.group': {
$in: [OWNER, ADMIN],
},
})
.execute()
The next helper is include()
which helps you to automate JOIN's.
Let's imagine that you have table posts
which have reference to users
table by creator_id
field. In SQL you will write:
SELECT post.*, creator.*
FROM posts post
INNER JOIN users creator ON (post.creator_id = creator.id)
But minorm knows about this relation and have ability to automate this by using:
const repo = manager.getRepository('posts')
repo
.startQuery('post')
.field('creator.*')
.include('post', 'creator_id')
Or the same without repo
manager
.startQuery()
.select()
.field('post.*')
.field('creator.*')
.from('posts', 'post')
.include('post', 'creator_id')
So in your Query you have table alias post
and you want to join with referenced table for a field creator_id
. That's it.
By default with JOIN include()
will create an alias by removing _id
from a column name, but if you want, you can change it by providing third argument like creator
in example above
Another example is tryInclude()
which automate LEFT JOIN
s.
Let's do more complex example. The same query as above, but you want to also fetch user avatar if it exists. So in SQL you will do:
SELECT post.*, creator.*, avatar.*
FROM posts post
INNER JOIN users creator ON (post.creator_id = creator.id)
LEFT JOIN avatars avatar ON (creator.avatar_id = avatar.id)
Using minorm you can write:
const repo = manager.getRepository('posts')
repo
.startQuery('post')
.field('creator.*')
.field('avatar.*')
.include('post', 'creator_id')
.tryInclude('creator', 'avatar_id')
How to fetch all of this data with this complex examples?
For this purpose we have a mapper. So when you will execute:
const repo = manager.getRepository('posts')
const posts = await repo
.startQuery('post')
.field('creator.*')
.field('avatar.*')
.include('post', 'creator_id')
.tryInclude('creator', 'avatar_id')
.getMapper()
.fetch()
posts will be an array of:
posts = [
{
//Content from a posts table
id: 5,
title: 'some',
creator_id: 2,
creator: {
//Content from a users table
id: 2,
login: 'some',
avatar_id: 100,
avatar: {
//Content from a avatars table
id: 100,
path: 'dsd',
},
},
},
{
//..Other post
},
]
So minorm automatically made a structure that shows the real relations between data
Another helper that are available in Query Builder is execute()
which can be used to receive result from a request:
const users = await repo
.startQuery('user')
.criteria({
'user.status': {
$not: BLOCKED,
},
'user.group': {
$in: [OWNER, ADMIN],
},
})
.execute()
But this helper can accept additional parameter to receive other fields that we JOIN'ed or calculated. For this we will use:
const repo = manager.getRepository('posts')
const posts = await repo
.startQuery('post')
.field('creator.*')
.field('avatar.*')
.include('post', 'creator_id')
.tryInclude('creator', 'avatar_id')
.execute(true)
In this example posts will be an array with format:
posts = [
{
post: {
//Content from a posts table
id: 5,
title: 'some',
creator_id: 2,
},
creator: {
//Content from a users table
id: 2,
login: 'some',
avatar_id: 100,
},
avatar: {
//Content from a avatars table
id: 100,
path: 'dsd',
},
},
{
//..Other post
},
]
So this is similar to a mapper, but this format sometimes helps when you don't need a structured data so you will have flatten data