refineorm
v1.0.0
Published
Refine is a type-safe ORM for TS/JS in both code-first and database-first paradigm that uses lambda/arrow function as the query definition that inspired by LINQ query style in C#. Refine are highly inspired by those popular .NET ORM such as Entity Framewo
Downloads
4
Readme
Refine ORM
WIP, some functionality could be not working as expected!
Refine is a type-safe ORM for TS/JS in both code-first and database-first paradigm that uses lambda/arrow function as the query definition that inspired by LINQ query style in C#. Refine are highly inspired by those popular .NET ORM such as Entity Framework and Linq2db, and also TS ORM such as TypeORM.
In other word, you may query in database as how you work with array. It allow you to write your query in a JS/TS' lambda to enable fast database prototyping on Node.js, React Native and Ionic, and support most popular database such as MySQL, MSSQL, PostgreSQL, Oracle and SQLite.
Refine may not as complicated and complex as many other ORM in JS/TS world but it will give you a very strong resemblance than any other ORM in way to query your data.
Working feature listed as:
- Query API to let you work in lambda/arrow function
- Model and data configuration with decorator
- Associations in bi-directional, while uni-directional are partially supported for One-to-one
- Inheritance pattern
- Transaction
- Multiple migration strategy
- Lazy and eager loading for associations
- Promise-based
- EF-like DataContext
Expected features:
- Fluent model configurator for Javascript
- Custom repository
- Navigation property for associations
Installation
npm install --save refineorm
# one of
npm install --save mysql named-placeholder
npm install --save pg
npm install --save mssql
npm install --save oracledb
npm install --save sqlite
If you use Typescript make sure to include following configuration in your tsconfig.json
"emitDecoratorMetadata": true,
"experimentalDecorators": true,
Also since Refine rely very much on ES6 arrow function, you must set your target to minimum of es6
, however esnext
are more reccomended. You will know why after you read the detail below.
Get Started
Configuring your model
Refine model are defined through a class, so you may start with creating your class as like the following
export class User {
name: string;
job: string;
age: number;
maritalStatus: boolean;
address: string;
}
Now if you done with your model, you may start decorating your class with metadata that needed to sturcture out your physical database
@Table('users')
export class User {
@Key()
name: string;
@Column()
job: string;
@Column()
age: number;
@Column('isMarried')
maritalStatus: boolean;
@Column()
address: string;
}
From the latter, we decorate our class with a @Table
and @Column
for the properties. Both decorator may accept string paramater to rename your table or property into more suited name of your choice.
Also we use @Key
to decorate name
as our primary key. Notice how we only use @Key
without a @Column
. Since @Key
indicate that it is a primary key and since primary key must be a column, you may omit the @Column
from it.
However the @Key
decorator don't accept any parameter as how @Column
accept string as the physical column name. So if you want to name your column, you have to pass it with the @Column
as the following example
@Key() @Column('userName')
name: string;
Fluent model configuration
If you use a plain Javascript and can't work with decorator, or you may but you hate it because it's not a standard even in ESNext, you may use fluent model configurator to describe your table structure. You can follow the example below
// TO DO
Creating the DataContext
All interaction with database in Refine are passed through DataContext
. In simple word, it is a representation of your connection to the database and even the strcuture of the database itself. First of all, you need to create a class that extends the base DataContext
class. Let's define it as the following
export class MyContext extends DataContext {
static readonly config: ConnectionConfig = {
database: 'skrpsi',
host: 'localhost',
password: '',
port: 3306,
type: 'mysql',
user: 'root'
};
constructor() {
super(MyContext.config, new MysqlProvider());
}
}
You can suit the config
as your own connection configuration. In the example above, we use MySQL as the database provider and we define it from the type
property inside the config. The valid type are mysql
, pgsql
, mssql
, oracle
, and sqlite
. If you want to change your vendor or provider, you can simply change it.
Adding your DataSet
If DataContext
is a represetation of your connection and database, DataSet
is a representation of the table inside your database. Since the tables are bound to database, so does the DataSet
are bound to DataContext
. To add your dataset to your context, you may see the following
export class MyContext extends DataContext {
static readonly config: ConnectionConfig = {
database: 'skrpsi',
host: 'localhost',
password: '',
port: 3306,
type: 'mysql',
user: 'root'
};
readonly users: DataSet<User> = this.fromSet(User);
constructor() {
super(MyContext.config);
}
}
We add the dataset of type User
as a property and initialize it with this.fromSet(..)
. We have to pass the class, in this case User
, as the parameter because TS don't let us to read the generic type. Also notice that readonly
modifier are added to ensure no side-effect are happening to the dataset.
Setting up your Migration Strategy
In Refine, migration strategy are the way to handle migration when changes detected in your model. Migration strategy are defined with MigrationStrategy
enum, and the most basic is Never
, OnEmpty
and OnChange
. Never
are the only valid strategy if you want to work in database-first, while the other can be used to handle the code-first paradigm.
By default, DataContext
will implement OnEmpty
as it strategy, meaning that the database generation only happen when the database are completely empty. If you want to change it, you may override the migrationStrategy
in your data context as below
get migrationStrategy(): MigrationStrategy {
return MigrationStrategy.RecreateOnChange;
}
Setting your strategy to OnChange
will ensure that the database are generated everytime a change happen in the model.
Working with your first query
Inserting your object
After you have done with your model, data context and the data set, now you only need to write somewhere in your code your first query. Let's start with inserting your first User
.
async function someWhereInCode() {
const user = new User();
user.name = 'John Doe';
user.job = 'JS Developer';
user.age = 33;
user.maritalStatus = false;
user.address = 'Somewhere in NPM';
const context = new MyContext();
await context.users.insert(user);
await context.release();
}
Notice that we use async
modifier in the function and await
for the insert
and release
. If your environment yet to support it you can use the common then
as how you work in promise
. And don't forget to call release
in the end if you want to close and release the resource immediately.
Reading your data
Now since the table have a record, we can begin to write our query to read the data. To read the user by it's name we can write it as
async function someWhereInCode() {
// -- the previous code
const john = await context.users.first(x => x.name == 'John Doe');
console.log(john.job) // output: JS Developer
await context.release();
}
The first
method are equivalent with find
in array, it return the first element. Query from above will return the first element with name = 'John Doe'
.
If you want to read only one data and such data must be entirely unique, you could use single(...)
instead
async function someWhereInCode() {
// -- the previous code
try {
const john = await context.users.single(x => x.name == 'John Doe');
console.log(john.job) // output: JS Developer
}
catch(err) {
console.log("User with name 'John Doe' are more than 1");
}
await context.release();
}
The single
method will throw an error when data that fulfill the predicate are more than one record. However, if you sure that it may only exists one record and you didn't need it to be checked, you should use first
as it will only send the query once, while single
will send 2 query in which first checking whether such data only have 1 record and the second is to read the actual data.
Selecting and filtering your data
If you already have 10 users in the database, and you want to filter it by age that is greater or equals 30, you can use the below query
const filtered = context.users.where(x => x.age > 30);
for await (const user of filtered) {
// do something with the filtered data here
}
That's how you're querying with the where clause. But why didn't where
awaited as how we did it with first
and how did it get executed? The query get executed when an access or iteration happens to the filtered
or an element that it held. In the above, it's happen in the for
statement.
Simply, unlike first
which return an object, where
method will return an AsyncCollection
which is also a base interface of DataSet
that will let you interact with the the table. In other word, this mechanism allow you to chain or build your query dynamically as how common QueryBuilder
pattern in other ORM. But instead of a string, it typed in a valid expression. Another example can be seen below
let filtered = context.users
.where(x => x.age > 30)
.select(x => x.name);
for await (const name of filtered) {
// name will be string
// do something with name
}
The example will return names of users with age >= 30
. The select
method is equivalent with map
in array. If you need to get more than one column, you could use the object literal instead
let filtered = context.users
.where(x => x.age > 30)
.select(x => ({
name: x.name,
job: x.job
}));
for await (const name of filtered) {
// name will be string
// do something with name
}
If your current environment has yet to support the asyncIterable
or for await
statement, as it was only supported in ES2018, you may cast your data set immediately with toArray()
or toCollection()
.
const filtered = ... // -- defined as previously
const arr = await filtered.toArray();
for(const data of arr) {
// do something with data
}
Calling both method will immediately execute the stacked query without the need to accessing or iterating over it's elements. The only difference between both are toCollection()
will return a Collection
that was very similar to AsyncCollection
but without the async
.
CAUTION! If you plan to iterate your query or collection more than one, you should always save the returned data from toArray()
or toCollection()
before you begin your first iteration, and use the value returned from the method as the iterable source (the most right side in for .. of/in ..
) in all the iteration. Each iteration to raw AsyncCollection
will execute the query each time and none of the returned result will be saved unless you do it manually.
const filtered = context.users.where(x => x.age > 30);
// === this is bad - DON'T ===
for await (const user of filtered) {
// ...
if(user.age < 25)
break;
}
// ....
for await (const user of filtered) {
// filtered query will get executed again before reaching this line for the first time
// do something with user for the second time
}
// =======================
// === this is good - DO ===
const data = await filtered.toArray();
for await (const user of data) {
// ...
if(user.age < 25)
break;
}
// ....
for await (const user of data) {
// keep iterating with records in data
// do something with user for the second time
}
// ========================
Reading all data from table
If you want to read all the data in the DataSet
, you only need to iterate over it like how we did it in the previous, or how the below code written
for await (const user of context.users) {
// do something with user here
}
Paginating your query
Refine make it easy to paginate or limiting you query. This achieved through the take
and skip
methods
const limitPerPage = 8;
let currentPage = 1;
const context = new MyContext();
const query = context.users
.where(x => x.age >= 30)
.orderBy(x => x.age)
.skip((currentPage - 1) * limitPerPage)
.take(limitPerPage);
for await (const user of query) {
// do something
}
await context.release();
The example will limiting your result to just 8 records. Also notice that we use orderBy
with x.age
as it selector. If you want to order or sort it by descending, you can use orderByDesc
instead.
You can read more about composing your query and all the possible option here
Manipulating your data
Updating and deleting one record
To update or deleting one data in the database, you can simply use update
or delete
with your object as it paramater as how you do it with insert operation
const context = new MyContext();
const mark = await context.users.first(x => x.name == "Mark");
mark.job = '.NET Developer';
mark.age = 30;
await context.users.update(mark);
const alex = await context.users.first(x => x.name == "Alex");
await context.delete(alex);
await context.release();
Updating many
If you need you need to updating records based on a condition, AsyncCollection
have a set
method that can be chained with finishing update
method to update all data by the composed query
const context = new MyContext();
const updated = await context.users
.where(x => x.age < 25)
.set(x => x.maritalStatus, false);
.execute();
await context.release();
You need to specify what column to be updated with set
method. First parameter are the selector of column that want to be changed, while the second parameter is the value.
Alternatively, set
also have an overload that let you to write an assigment expression instead.
const context = new MyContext();
const updated = await context.users
.where(x => x.age < 25)
.set(x => x.maritalStatus = false);
.execute();
await context.release();
If the given expression is not an assigment, set will throw an error.
Deleting many
Similar to the update, AsyncCollection
also have delete
method to delete all records that matched the composed query
const context = new MyContext();
const updated = await context.users
.where(x => x.age < 20)
.delete();
await context.release();
Forcing database synchronization
You may asks where did we create the table since we didn't mention it anywhere on above. Well the migration are taken in place before your first query are sent to the database. So keep in mind your first query could be cumbersome somehow. And if you somehow didn't want to wait until your first query, you may call the forceSynchronize()
const context = new MyContext();
await context.forceSynchronize();
// -- our first query in below
Keep in mind that forceSynchronize()
can only ran one time after your application started, and will throw an Error if a call happened again.
Creating association
In Refine, association are built with @HasOne(...)
and @HasMany(...)
decorator. The most easy way to define your association is by using bi-directional way, in which both model must decorate it's related property with either @HasOne
or @HasMany
, although some cases will enable you to write your association in a uni-directional way.
One-to-one relation
We will modify the defined User
model from the above and give it a one-to-one relation with, let say, a UserBilling
in which a user may only have it once. Let create the UserBilling
class first and decorate it as how we do it with User
previously.
@Table('user_billing')
export class UserBilling {
@Key()
@HasOne(of => User)
user: User;
@Column()
isActive: boolean;
@Column()
isSuspended: boolean;
@Credentials()
securityKey: string;
}
The @HasOne
decorator accept a first argument to be a selector of the class that being referenced. It also has an optional second argument that accept a selector to referenced property of the first argument. This optional arguments will be explained in detail further on below.
Notice that in the UserBilling
, we decorate the property user
which have type of User
as the primary key. This format alone would be enough to make a uni-directional one-to-one relationship, in which a user
column, which is a primary key, will have a reference or foreign key to column name
in User
.
Also note the @Credentials
decorator in property securityKey
. This decorator will hide the securityKey
column while reading the database unless it requested manually by select
or loadWith
. This decorator is good when you want to restrict a sensitive data, like password or token, being loaded unexpectedly.
Now we will also give a property that have Billing as it type and decorate it with @HasOne
too to make a bidirectional relation.
@Table('users')
export class User {
// -- another property up here
@HasOne(of => UserBilling)
billing: UserBilling;
}