Autorm
A lightweight easy to use Zero Config MySQL ORM for nodejs that 'automagically' builds your database schema.
Install
Install the package with npm:
$ npm i autorm
Usage
Connection Options
Creating a database instance is very similar to the mysql module:
const database = new(require('...'))({
host: '127.0.0.1',
user: '...',
password: '...',
database: '...',
connectionLimit: 10,
waitForConnections: true,
queueLimit: 0,
freeze: false,
underscore: true
});
Internally we use the connection pool method, so the connection object accepts any mysql pool options,
and with the following optional properties specific to this lib.
Parameter | Type | Description | Default |
---|
freeze | boolean | Freeze the database schema. | false |
underscore | boolean | Use an underscore for relationship linking columns. e.g table_id else its tableId | true |
Or you can pass in an existing pool, with the following options:
const database = new(require('...'))({
database: '...',
freeze: false,
underscore: true
}, pool);
Example
A super simple CRUD example!
database.connect().then(async () => {
console.log('database is ready')
let post = new database.row('post', {
title: 'Hello World'
})
await post.store()
post = await database.load('post', post.id)
post.body = 'Lorem ipsum dolor sit amet.'
await post.store()
await post.delete()
}).catch(err => {
console.log('Error: ', err)
})
Yep, it's that simple!
CRUD
Create a new row
Declare a new row object and assign your properties.
database.row(table, properties)
Arguments:
Parameter | Type | Description | |
---|
table | string | Table name in which you want to store the row. | required |
properties | object | An object with the properties of the row, nested properties are JSON stringified. | |
let person = new database.row('person', {
name: 'lozza',
created: new Date(),
meta: {
last_seen: new Date()
}
});
let person = new database.row('person');
person.name = 'lozza';
person.created = new Date();
person.meta = {
last_seen: new Date()
};
Create/Update
Storing a row, either by initial new database.row or updating an existing the row is done by calling the .store()
function, if the row has an id
that exists in the table it will update the row, else it will add a new row.
person.store().then(() => {
console.log(person);
});
You can also modify the user row and then store it to update the row:
person.store().then(() => {
person.name = 'loz';
person.updated = new Date();
person.store(() => {
console.log(person);
});
});
A more advanced example, which links emails and tags to users.
person.store().then(() => {
var emails = [
{ type: 'personal', value: 'loz@example.com' },
{ type: 'work', value: 'office@example.com' }
];
database.storeRows('email', emails).then(emails => {
person.addChildren('email', emails).then(emails => {
console.log('emails', emails);
});
});
const tags = [
{ name: 'books' },
{ name: 'cars' },
{ name: 'movies' }
];
person.setLists('tag', tags).then(tags => {
console.log('tags', tags);
});
person.updated = new Date();
person.store(() => {
console.log(person);
});
});
Merge
Another way to update is to merge your updated values.
person.merge({
name: 'New name'
}).then(person => {
person.store()
});
Store multiple rows
There are two ways to store multiple rows at the same time:
-
storeAll(rows)
: Stores an array of rows.
Arguments:
Parameter | Type | Description |
---|
rows | array | The rows to be stored. |
var rows = [
new database.row('tag', { name: 'orm' }),
new database.row('person', { name: 'Steve', created: new Date() }),
new database.row('person', { name: 'Simon', created: new Date() })
];
database.storeAll(rows).then(result){
console.log(result);
});
-
storeRows(table, array)
: Stores an array of objects.
Arguments:
Parameter | Type | Description |
---|
table | string | The table in which rows will be stored. |
array | array | The objects to be stored. |
const comments = [
{ text: 'Easy peasy orm', posted: new Date() },
{ text: 'cool', posted: new Date() }
];
database.storeRows('comment', comments).then(function(result){
console.log(result);
});
Read
To read data from the database there are 3 ways:
-
load(table, id)
: Loads a row by id.
Arguments:
Parameter | Type | Description |
---|
table | string | The table of the wanted row. |
id | integer | The id of the wanted row. |
database.load('user', 6).then(user => {
console.log(user);
});
If no record found it returns false
-
find(table, sql, values, extra)
: Find rows from a table.
Arguments:
Parameter | Type | Description |
---|
table | string | The table of the wanted row. |
sql | string | SQL you want to put after the WHERE clause. |
values | array | simple |
extra | object | { fields: [], parents: [], children: [], child: [], lists: [] } |
database.find('user', 'name = ?', ['Adam']).then(users => {
console.log(users[0].name);
});
If you want to find all rows, don't put conditions:
database.find('user').then(function(users) {
console.log(users);
});
By default its SELECT *
, to get only some columns, use extra with defined fields:
database.find('user', 'name = ?', ['Adam'], { fields: ['name'] }).then(function(users) {
console.log(users[0].name);
});
-
findOne(table,data)
: this function works the same as find
but returns only one row:
database.findOne('user', 'name = ?', ['Adam'], { fields: ['name'] }.then(user => {
console.log(user.name);
});
Delete
There are two ways to delete records:
-
.delete()
: this function deletes the row:
user.delete().then(function () {
console.log('deleted!');
});
-
delete(table, data)
: this function deletes from a table.
Arguments:
Parameter | Type | Description |
---|
table | string | The table of the wanted row. |
sql | string | SQL you want to put after the WHERE clause. |
values | array | simple |
database.delete('user', 'name = ?', ['Adam']).then(() => {
console.log('deleted');
});
Relations
Below are some helper functions for simple relationship linking based on ids, be aware the lib does not set up foreign keys between tables on the database.
One to many (Parent & Children)
You can add a property like tableName + '_id'
manually to make a row belonging to another row.
Or you can use this function:
You can get the parent using this:
If you have the parent and you want to append children to it do that:
-
.addChildren(table,array)
: Adds children to row.
Arguments:
Parameter | Type | Description |
---|
table | string | The children's table. |
rows | array | The rows to be stored as children. |
user.addChildren('comment', comments).then(result => {
console.log(result);
});
And to get children:
Many to many
-
.getLists(table)
: Returns the lists list.
Arguments:
Parameter | Type | Description |
---|
table | string | The lists table name. |
database.load('film',790).then(function (film) {
film.getLists('actor').then(function (actors) {
console.log(actors);
});
});
-
.setlists(table, newlists)
: Replace the current list by the given array.
Arguments:
Parameter | Type | Description |
---|
table | string | The lists table name. |
newlists | array | An array of objects (not required to be rows). |
database.load('film',790).then(film => {
var actors = [
{ first_name: 'JOHN', last_name: 'CENA' },
{ first_name: 'GARRY', last_name: 'LEWIS' },
];
film.setlists('actor', actors).then(actors => {
console.log(actors);
});
});
-
.addlists(table, newlists)
: Works the same as .setlists
but without deleting the recorded lists.
Arguments:
Parameter | Type | Description |
---|
table | string | The lists table name. |
newlists | array | An array of objects (not required to be rows). |
database.load('film',790).then(function (film) {
var array = [
{ first_name: 'PETER', last_name: 'MALCOLM' },
{ first_name: 'SAMUEL', last_name: 'HADINBOURG' },
];
film.addlists('actor',array).then(function (newActors) {
film.getLists('actor').then(function (actors) {
console.log(actors);
});
});
});
-
.addList(list)
: Add a single list to a row.
Arguments:
Parameter | Type | Description |
---|
item | row | The list item to be added. |
newlists | array | An array of objects (not required to be rows). |
database.load('film', 790).then(function (film) {
var actor = new database.row('actor',{ first_name: 'FRED', last_name: 'HAMILTON' });
film.addlist(actor).then(function () {
film.getLists('actor').then(function (actors) {
console.log(actors);
});
});
});
-
.removeList(list)
: Removes the listity(relation) between the two rows.
Arguments:
Parameter | Type | Description |
---|
item | row | The list item to be unlinked. |
database.load('film', 790).then(function (film) {
database.load('actor', 217).then(function (actor) {
film.removelist(actor).then(function () {
film.getLists('actor').then(function (actors) {
console.log(actors);
});
});
});
});
Note:
In case you want to know whats the intermediate table name between two tables you can use this:
database.getlinkTable(table1, table2);
Datatypes
These are the supported types, any other type may cause undesired results.
integer
: INTdecimal
: DOUBLEboolean
: BOOLstring
: VARCHAR (if length < 256) | TEXT (if length > 255)Date
: DATETIME (parsed on select)object
: TEXT (JSON.stringified on insert, JSON.parse'd on select)
Helpers
Counting
-
database.count(table, data, values)
: Returns the number of rows found.
Arguments:
Parameter | Type | Description |
---|
table | string | The table of the wanted row. |
sql | string | SQL you want to put after the WHERE clause. |
values | array | simple |
database.count('film', 'length > ?', [60]).then(function(res) {
console.log(res);
});
Executing raw MySQL
-
database.query(sql, values)
: Allows you to execute any MySQL query.
Arguments:
Parameter | Type | Description |
---|
table | string | The table of the wanted row. |
sql | string | SQL you want to put after the WHERE clause. |
values | array | simple |
database.query('SELECT title FROM film WHERE length < ?', 47).then(result => {
console.log(res);
});
-
database.exec(sql, vals)
: alias of query.
Converting array to rows
-
database.arrayToRows(table, array)
: Transforms an array of simple objects to an array of rows.
Arguments:
Parameter | Type | Description |
---|
table | string | The table to assign to rows. |
array | array | The array of objects to be transformed into rows. |
var comments = [
{ text: 'First comment!', created: new Date() },
{ text: 'Hi!', created: new Date() }
];
console.log(comments[0].table);
comments = database.arrayToRows('comment', comments);
console.log(comments[0].table);
Testing
You need to change ./tests/test.js
and install mocha, then run:
$ npm test
Contributing
Please see CONTRIBUTING for details.
If you want to show your appreciation, please feel free to donate https://www.paypal.me/lcherone, thanks.
Credits
License
The MIT License (MIT). Please see License File for more information.