autorm
Advanced tools
A lightweight easy to use Zero Config MySQL ORM for nodejs that 'automagically' builds your database schema.
Readme
A lightweight easy to use Zero Config MySQL ORM for nodejs that 'automagically' builds your database schema.
Install the package with npm:
$ npm i autorm
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);
A super simple CRUD example!
database.connect().then(async () => {
console.log('database is ready')
// Create & Store
let post = new database.row('post', {
title: 'Hello World'
})
await post.store()
// Retrieve
post = await database.load('post', post.id)
// Update
post.body = 'Lorem ipsum dolor sit amet.'
await post.store()
// Delete
await post.delete()
}).catch(err => {
console.log('Error: ', err)
})
Yep, it's that simple!
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()
}
});
// and/or properties can also be added after initialization:
let person = new database.row('person');
person.name = 'lozza';
person.created = new Date();
person.meta = {
last_seen: new Date()
};
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(() => {
// emails (one to many)
var emails = [
{ type: 'personal', value: 'loz@example.com' },
{ type: 'work', value: 'office@example.com' }
];
database.storeRows('email', emails).then(emails => {
// link emails to person
person.addChildren('email', emails).then(emails => {
console.log('emails', emails);
});
});
// tags (many to many)
const tags = [
{ name: 'books' },
{ name: 'cars' },
{ name: 'movies' }
];
person.setLists('tag', tags).then(tags => {
console.log('tags', tags);
});
// parent updated
person.updated = new Date();
person.store(() => {
console.log(person);
});
});
Another way to update is to merge your updated values.
person.merge({
name: 'New name'
}).then(person => {
person.store()
});
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);
});
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); // Adam
});
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); // Adam
});
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);
});
There are two ways to delete records:
.delete(): this function deletes the row:
// user = { id: 6, name: 'Adam', age: '63', created: 2163717392 }
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');
});
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.
You can add a property like tableName + '_id' manually to make a row belonging to another row.
Or you can use this function:
.setParent(parent): Sets a parent to row.
Arguments:
| Parameter | Type | Description |
|---|---|---|
| parent | row | object |
// comment = { id: 1, text: 'I like it', posted: 2163717392 }
// user = { id: 7, name: 'Steve', age: '36', created: null }
comment.setParent(user).then(() => {
console.log(comment);
});
// output: { id: 1, text: 'I like it', posted: 2163717392, userId: 7 }
You can get the parent using this:
.getParent(table): Returns the parent row.
Arguments:
| Parameter | Type | Description |
|---|---|---|
| table | string | The parent row table. |
// comment = { id: 1, text: 'I like it', posted: 2163717392, userId: 7 }
comment.getParent('user').then(function(user) {
console.log(user);
});
// output: { id: 7, name: 'Steve', age: '36', created: null }
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 = { id: 7, name: 'Steve', age: '36', created: null }
// comments = [ { id: 1, text: 'I like it', posted: 2163717392, userId: 7 },
// { id: 2, text: 'wonderful', posted: 2163717392, userId: null } ]
user.addChildren('comment', comments).then(result => {
console.log(result);
});
// output: [ { id: 0, text: 'I like it', posted: 2163717392, userId: 7 },
// { id: 2, text: 'wonderful', posted: 2163717392, userId: 7 } ]
And to get children:
getChildren(table): Returns children of row.
Arguments:
| Parameter | Type | Description |
|---|---|---|
| table | string | The children's table. |
// user = { id: 7, name: 'Steve', age: '36', created: null }
user.getChildren('comment').then(function(comments) {
console.log(comments);
});
// output: [ { id: 0, text: 'I like it', posted: 2163717392, userId: 7 },
// { id: 2, text: 'wonderful', posted: 2163717392, userId: 7 } ]
.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);
});
});
// output: [ { id: 28, first_name: 'WOODY', last_name: 'HOFFMAN' },
// { id: 47, first_name: 'JULIA', last_name: 'BARRYMORE' },
// { id: 55, first_name: 'FAY', last_name: 'KILMER' } ]
.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);
});
});
// output: [ { id: 214, first_name: 'GARRY', last_name: 'LEWIS' },
// { id: 213, first_name: 'JOHN', last_name: 'CENA' } ]
.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);
});
});
});
// output: [ { id: 215, first_name: 'PETER', last_name: 'MALCOLM' },
// { id: 214, first_name: 'GARRY', last_name: 'LEWIS' },
// { id: 213, first_name: 'JOHN', last_name: 'CENA' },
// { id: 216, first_name: 'SAMUEL', last_name: 'HADINBOURG' } ]
.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);
});
});
});
// output: [ { id: 215, first_name: 'PETER', last_name: 'MALCOLM' },
// { id: 214, first_name: 'GARRY', last_name: 'LEWIS' },
// { id: 213, first_name: 'JOHN', last_name: 'CENA' },
// { id: 216, first_name: 'SAMUEL', last_name: 'HADINBOURG' },
// { id: 217, first_name: 'FRED', last_name: 'HAMILTON' } ]
.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);
});
});
});
});
// output: [ { id: 215, first_name: 'PETER', last_name: 'MALCOLM' },
// { id: 214, first_name: 'GARRY', last_name: 'LEWIS' },
// { id: 213, first_name: 'JOHN', last_name: 'CENA' },
// { id: 216, first_name: 'SAMUEL', last_name: 'HADINBOURG' } ]
Note:
In case you want to know whats the intermediate table name between two tables you can use this:
database.getlinkTable(table1, table2);
// for table1 = 'film' and table2 = 'actor'
// it returns 'actor_film'
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)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);
});
// output: 896
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);
});
// output: [ RowDataPacket { title: 'ALIEN CENTER' },
// RowDataPacket { title: 'IRON MOON' },
// RowDataPacket { title: 'KWAI HOMEWARD' },
// RowDataPacket { title: 'LABYRINTH LEAGUE' },
// RowDataPacket { title: 'RIDGEMONT SUBMARINE' } ]
database.exec(sql, vals): alias of query.
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); // output: undefined
comments = database.arrayToRows('comment', comments);
console.log(comments[0].table); // output: comments
You need to change ./tests/test.js and install mocha, then run:
$ npm test
Please see CONTRIBUTING for details.
If you want to show your appreciation, please feel free to donate https://www.paypal.me/lcherone, thanks.
The MIT License (MIT). Please see License File for more information.
FAQs
A lightweight easy to use Zero Config MySQL ORM for nodejs that 'automagically' builds your database schema.
The npm package autorm receives a total of 7 weekly downloads. As such, autorm popularity was classified as not popular.
We found that autorm demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
Security News
As cyber threats become more autonomous, AI-powered defenses are crucial for businesses to stay ahead of attackers who can exploit software vulnerabilities at scale.
Security News
UnitedHealth Group disclosed that the ransomware attack on Change Healthcare compromised protected health information for millions in the U.S., with estimated costs to the company expected to reach $1 billion.