Security News
MCP Community Begins Work on Official MCP Metaregistry
The MCP community is launching an official registry to standardize AI tool discovery and let agents dynamically find and install MCP servers.
By Sarah Gooding - May 09, 2025
🚀 Big News: Socket Acquires Coana to Bring Reachability Analysis to Every Appsec Team.Learn more →
majo-mysql
Advanced tools
majo-mysql
Majo Mysql is a library that can help you build query, schema, and control your database more easier.
Majo Mysql
Majo Mysql is a library that can help you build query, schema, and control your database more easier.
Majo Mysql can only be used on the NodeJS version 8 or higher. Majo Mysql supports Mysql version 5.7 or higher and MariaDB. For use in other databases different libraries will be created.
Majo Mysql is available for use under MIT LICENSE
This package created by Yudha Pratama If you found bugs or errors, you can report at Github Issue or send a direct message to my twitter.
If you like this project, please support us to give a Coffee
Features
- Full featured query
- Expressive method
- Schema builders
- Database Manager
- Trigger
- Relationships
Installation
npm i majo-mysql
How to use
Initializing the library
The Majo module is itself a function which takes a configuration object for Majo.
const majo = require('majo-mysql')
.connection({
host: 'localhost',
user: 'root',
password: '',
database: 'test'
});
Retrieving results
Retrieving All Rows From A Table
You can get query results as a Array with use .get() method.
majo
.select()
.from('users')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
Retrieving A Single Row / Column From A Table
If you want get single row from query result as Object, you can use .first() method.
majo
.select()
.from('users')
.first()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
Pluck - .pluck(column)
If you want get one column for the result, you can use .pluck() method
majo
.select()
.from('users')
.pluck('email')
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
Query Builders
Select Clause
Select - .select(columns)
If you want select all columns from a database table.
majo
.select()
.from('users')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users
You may not always want to select all columns from a database table. Using the select method, you can specify a custom select clause for the query:
majo
.select('name', 'email as user_email')
.from('users')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT name, email AS user_email FROM users
The distinct method allows you to force the query to return distinct results:
majo
.table('users')
.distinct()
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT DISTINCT * FROM users
Table - .table(table name)
You use .table() or from() for selecting specific table.
majo
.table('users')
.select()
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users
From - .from(table name)
majo
.select()
.from('users')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users
Aggregates
The query builder also provides a variety of aggregate methods such as count, max, min, avg, and sum.
Count - .count(columns, asColumn)
majo
.table('users')
.count()
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT COUNT(*) FROM users
Or you can count as a specific column like this:
majo
.table('users')
.count('*', 'user_total')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT COUNT(*) AS user_total FROM users
Also you can count row with distinct method like this:
majo
.table('users')
.countDistinct('id', 'user_total')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT COUNT(DISTINCT id) FROM users
Avg - .avg(column, asColumn)
majo
.table('orders')
.avg('price')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT AVG(*) FROM orders
Or you can get avg from specific column, with combine method.
majo
.table('orders')
.avg('price', 'average_price')
.where('status', true)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT AVG(*) AS average_price FROM orders WHERE status = true
Sum - .sum(column, asColumn)
majo
.table('orders')
.sum('orderId')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT SUM(orderId) FROM orders
Min - .min(column, asColumn)
majo
.table('orders')
.min('price')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT MIN(price) FROM orders
Max - .max(column, asColumn)
majo
.table('orders')
.max('price')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT MAX(price) FROM orders
Joins
The Majo query builder may also be used to write join statements
Inner Join - .join(tableName, joinColumn, operator, selectColumn)
majo
.select('users.*', 'contacts.phone')
.table('users')
.join('contacts', 'users.id', '=', 'contacts.user_id')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT users.*, contacts.phone FROM users INNER JOIN contacts ON users.id = contacts.user_id
Join Raw - .joinRaw(query)
If you want write it raw, you can use joinRaw() method like this:
majo
.select('users.*', 'contacts.phone')
.table('users')
.joinRaw('INNER JOIN contacts ON users.id = contacts.user_id')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT users.*, contacts.phone FROM users INNER JOIN contacts ON users.id = contacts.user_id
Left Join - .leftJoin(tableName, joinColumn, operator, selectColumn)
majo
.select('users.*', 'contacts.phone')
.table('users')
.leftJoin('contacts', 'users.id', '=', 'contacts.id')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT users.*, contacts.phone FROM users LEFT JOIN contacts ON users.id = contacts.user_id
Right Join - .rightJoin(tableName, joinColumn, operator, selectColumn)
majo
.select('users.*', 'contacts.phone')
.table('users')
.rightJoin('contacts', 'users.id', '=', 'contacts.id')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT users.*, contacts.phone FROM users RIGHT JOIN contacts ON users.id = contacts.user_id
Left Outer Join - .leftOuterJoin(tableName, joinColumn, operator, selectColumn)
majo
.select('users.*', 'contacts.phone')
.table('users')
.leftOuterJoin('contacts', 'users.id', '=', 'contacts.id')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT users.*, contacts.phone FROM users LEFT OUTER JOIN contacts ON users.id = contacts.user_id
Right Outer Join - .rightOuterJoin(tableName, joinColumn, operator, selectColumn)
majo
.select('users.*', 'contacts.phone')
.table('users')
.rightOuterJoin('contacts', 'users.id', '=', 'contacts.id')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT users.*, contacts.phone FROM users RIGHT OUTER JOIN contacts ON users.id = contacts.user_id
Cross Join - .crossJoin(tableName, joinColumn, operator, selectColumn)
majo
.select('users.*', 'contacts.phone')
.table('users')
.crossJoin('contacts', 'users.id', '=', 'contacts.id')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT users.*, contacts.phone FROM users CROSS JOIN contacts ON users.id = contacts.user_id
Where Clause
The Majo query builders may also be used to write where statements. The basics style where statements use three arguments, field, operator and value.
Simple where - .where(column, operator, value)
majo
.table('users')
.where('email', '=', 'test@mail.com')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
You can also without use operator with where statements like this:
majo
.table('users')
.where('email', 'test@mail.com')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
You can write it raw like this:
majo
.table('users')
.whereRaw(`email = 'test@mail.com'`)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE email = 'test@mail.com'
You may use a variety of other operators when writing a where clause:
majo
.table('users')
.where('email', 'like', '%@gmail.com%')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE email LIKE '%@gmail.com%'
majo
.table('orders')
.where('price', '>=', 5000)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders WHERE price >= 5000
You may also pass an object of conditions to the where statement
majo
.table('users')
.where({
first_name: 'Yudha',
last_name: 'Pratama',
})
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE first_name = 'Yudha' AND last_name = 'Pratama'
You may be used Or Where conditions and used orWhere method like this:
majo
.table('orders')
.where('price', '=', 5000)
.orWhere('price', '=', 10000)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders WHERE price = 5000 OR price = 10000
Where In - .whereIn(column, [array] | string | number)
majo
.table('users')
.whereIn('id', [1, 5, 9])
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE id IN (1, 5, 9)
Where Not In - .whereNotIn(column, [array] | string | number)
majo
.table('users')
.whereNotIn('id', [1, 5, 9])
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE id NOT IN (1, 5, 9)
Where Null - .whereNull(column)
majo
.table('users')
.whereNull('last_name')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE last_name IS NULL
Where Not Null - .whereNotNull(column)
majo
.table('users')
.whereNotNull('last_name')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE last_name IS NOT NULL
Where Empty String - .whereEmptyString(column)
majo
.table('users')
.whereEmptyString('last_name')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE last_name = ''
Where Between - .whereBetween(column, startNumber, endNumber)
majo
.table('orders')
.whereBetween('price', 5000, 10000)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders WHERE price BETWEEN 5000 AND 10000
Where Not Between - .whereNotBetween(column, startNumber, endNumber)
majo
.table('orders')
.whereNotBetween('price', 5000, 10000)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders WHERE price NOT BETWEEN 5000 AND 10000
You may be used whereBetwwen method and whereNotBetween with OR statement like this
majo
.table('orders')
.whereNotBetween('price', 1000, 2000)
.whereNotBetween('price', 5000, 10000)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders WHERE price NOT BETWEEN 1000 AND 2000 OR price NOT BETWEEN 5000 AND 10000
Where Column - .whereColumn(firstColumn, operator, secondColumn)
The whereColumn method may be used to verify that two columns are equal
majo
.table('users')
.whereColumn('first_name', 'last_name')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE first_name = last_name
Or you can use with operator like this:
majo
.table('users')
.whereColumn('first_name', '!=' 'last_name')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE first_name != last_name
Or used much columns checking
majo
.table('users')
.whereColumn({
first_name: 'last_name',
username: 'email'
})
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE first_name = last_name AND username = email
You may be used whereColumn methid with OR statement like this:
majo
.table('users')
.whereNotNull('last_name')
.orWhereColumn('first_name', '!=' 'last_name')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE last_name NOT NULL OR WHERE first_name != last_name
Where Exists - .whereExists()
The whereExists method allows you to write where exists SQL clauses. You can write SQL clause with same line without inside block.
majo
.table('users')
.whereExists()
.table('orders')
.whereColumn('users.id', 'orders.user_id')
.endWhereExists()
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE EXISTS ( SELECT * FROM orders WHERE users.id = orders.user_id )
Remember you should write endWhereExists method to end the use of whereExists SQL clause.
Where Not Exists - .whereNotExists()
majo
.table('users')
.whereNotExists()
.table('orders')
.whereColumn('users.id', 'orders.user_id')
.endWhereNotExists()
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users WHERE NOT EXISTS ( SELECT * FROM orders WHERE users.id = orders.user_id )
Remember you should write endWhereNotExists method to end the use of whereNotExists SQL clause.
Ordering, Grouping, Limit & Offset
The Majo query builders may also be used to write order by, group by, offset and limit statement.
Order By - .orderBy(column, [by clause])
majo
.table('users')
.orderBy('created_date')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
Or you can use by clause like this
majo
.table('users')
.orderBy('created_date', 'ASC')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users ORDER BY created_date ASC
You can write it raw like this:
majo
.table('users')
.orderByRaw('created_date ASC')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users ORDER BY created_date ASC
Order By Desc - .orderByDesc(column)
majo
.table('users')
.orderByDesc('created_date')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users ORDER BY created_date DESC
Latest - .latest(column)
You can use latest method without any argument. The latest method argument by default is created_date
majo
.table('users')
.latest('created_date')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users ORDER BY created_date DESC
Oldest - .oldest(column)
You can use oldest method without any argument. The oldest method argument by default is created_date
majo
.table('users')
.oldest('created_date')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users ORDER BY created_date ASC
Group By - .groupBy(columns)
majo
.table('orders')
.groupBy('status')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY status
Or you can use much fields for grouping like this
majo
.table('orders')
.groupBy('status', 'created_date')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
majo
.table('orders')
.groupByRaw('status, created_date')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY status, created_date
Having Clause
The Majo query builders may also be used to write having method. Having clause used to search keywords with aggregates
Having - .having(column, [operator], value)
majo
.table('orders')
.groupBy('price')
.having('price', 5000)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY price HAVING price = 5000
Or you can use having method without operator, like this:
majo
.table('orders')
.groupBy('price')
.having('price', '>', 5000)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY price HAVING price > 5000
Having In - .havingIn(column, values)
majo
.table('orders')
.groupBy('price')
.havingIn('price', [5000, 1000])
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY price HAVING price IN (5000, 1000)
Having Not In - .havingNotIn(column, values)
majo
.table('orders')
.groupBy('price')
.havingNotIn('price', [5000, 1000])
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY price HAVING price NOT IN (5000, 1000)
Having Null - .havingNull(column)
majo
.table('orders')
.groupBy('price')
.havingNull('client_name')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY price HAVING client_name IS NULL
Having Not Null - .havingNotNull(column)
majo
.table('orders')
.groupBy('price')
.havingNotNull('client_name')
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY price HAVING client_name IS NOT NULL
Having Between - .havingBetween(column, values)
majo
.table('orders')
.groupBy('price')
.havingBetween('price', [5000, 1000])
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY price HAVING price BETWEEN 5000 AND 10000
Having Not Between - .havingNotBetween(column, values)
majo
.table('orders')
.groupBy('price')
.havingNotBetween('price', [5000, 10000])
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM orders GROUP BY price HAVING price NOT BETWEEN 5000 AND 10000
Having Exists - .havingExists()
The havingExists method allows you to write having exists SQL clauses. You can write SQL clause with same line without inside block.
majo
.table('users')
.groupBy('id')
.havingExists()
.table('orders')
.whereColumn('users.id', 'orders.user_id')
.endHavingExists()
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users GROUP BY id HAVING EXISTS ( SELECT * FROM orders WHERE users.id = orders.user_id )
Remember you should write endHavingExists method to end the use of havingExists SQL clause.
Having Not Exists - .havingNotExists()
majo
.table('users')
.groupBy('id')
.havingNotExists()
.table('orders')
.whereColumn('users.id', 'orders.user_id')
.endHavingExists()
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users GROUP BY id HAVING NOT EXISTS ( SELECT * FROM orders WHERE users.id = orders.user_id )
Remember you should write endHavingNotExists method to end the use of havingNotExists SQL clause.
Offset & Limit
The Majo query builder may be used to offset and limit statement.
Offset - .offset(number) And Limit - .limit(number)
majo
.table('users')
.limit(100)
.offset(0)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users LIMIT 100 OFFSET 0
Or you can use skip for change offset method, and use take for change limit method.
majo
.table('users')
.take(100)
.skip(0)
.get()
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SELECT * FROM users LIMIT 100 OFFSET 0
Inserts
The Majo query builder also provides an insert method for inserting records into the database table. The insert method accepts an object names and values:
Insert - .insert(object | string argument)
majo
.table('users')
.insert({
first_name: 'Test',
last_name: 'User'
});
INSERT INTO users (first_name, last_name) VALUES ('Test', 'User')
Or you can insert with argument like this:
majo
.table('users')
.insert('first_name', 'Test');
INSERT INTO users (first_name) VALUES ('Test')
You may also want to inserted much values like this:
majo
.table('users')
.insert([
{
first_name: 'Test',
last_name: 'User 1',
},
{
first_name: 'Test',
last_name: 'User 2',
}
]);
INSERT INTO users (first_name, last_name) VALUES ('Test', 'User 1'), ('Test', 'User 2')
And then if you want get last inserted id, you can use insertgetId method, like this:
majo
.table('users')
.insertGetId({
first_name: 'Test',
last_name: 'User'
})
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
INSERT INTO users (first_name, last_name) VALUES ('Test', 'User')
Updates
The Majo query builder also provides an update method for updating records. The update method accepts an object names and values:
Update - .update(object | string argument)
majo
.table('users')
.where('user_id', 1)
.update({
first_name: 'Test',
last_name: 'User',
});
UPDATE users SET first_name = 'Test', last_name = 'User'
Or you can write with string argument like this
majo
.table('users')
.where('user_id', 1)
.update('first_name', 'Test');
UPDATE users SET first_name = 'Test'
Increment & Decrement
The Majo query builder also provides convenient methods for incrementing or decrementing the value of a given column.
Increment - .increment(column, [number])
majo
.table('users')
.increment('votes');
UPDATE users SET votes = votes + 1
Or you can custom value like this:
majo
.table('users')
.increment('votes', 10);
UPDATE users SET votes = votes + 10
Decrement - .decrement(column, [number])
majo
.table('users')
.decrement('votes');
UPDATE users SET votes = votes - 1
Or you can custom value like this:
majo
.table('users')
.decrement('votes', 10);
UPDATE users SET votes = votes - 10
Deletes
The Majo query builder also provides an delete method to remove records from database table.
Delete - .delete()
majo
.table('users')
.where('user_id', 1)
.delete();
DELETE FROM users WHERE user_id = 1
Truncate - .truncate(table)
If you want clear the data from your database table, you can use truncate method.
majo
.truncate('users')
TRUNCATE TABLE users
Another Methods
Column Info
With Majo query builder, you can get all information from your database table like field name, type field and other information.
majo
.columnInfo('users')
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
SHOW COLUMNS FROM users
Schema Builders
Schema
To use schema builder, you have to use schema method.
Majo
.schema();
Also, if you want to use a different database from your Majo configuration, you can use withSchema method like this:
Majo
.schema()
.withSchema('database_name');
Tables
Creating Table
To create a new database table, use createTable method after schema() method. The createTable method accepts two argument, table name string and Closure table.
Majo
.schema()
.createTable('users', (table) => {
table.increment();
table.string('username', 100);
});
When creating the table, you may use any of the schema builder's column methods to define the table's columns.
Available Column Type
| Command | Description |
|---|---|
table.increment('id'); | Auto-incrementing UNSIGNED INT (primary key). If you want not use argument, the default column name is id |
table.tinyIncrement('id'); | Auto-incrementing UNSIGNED TINYINT (primary key). If you want not use argument, the default column name is id |
table.smallIncrement('id'); | Auto-incrementing UNSIGNED SMALLINT (primary key). If you want not use argument, the default column name is id |
table.mediumIncrement('id'); | Auto-incrementing UNSIGNED MEDIUMINT (primary key). If you want not use argument, the default column name is id |
table.bigIncrement('id'); | Auto-incrementing UNSIGNED BIGINT (primary key). If you want not use argument, the default column name is id |
table.bigInteger('data'); | BIGINT equivalent column |
table.binary('data'); | BLOB equivalent column |
table.boolean('status'); | BOOL or TINYINT equivalent column |
table.char('name', 100); | CHAR equivalent column with optional length |
table.date('created_at'); | DATE equivalent column |
table.dateTime('created_at'); | DATETIME equivalent column |
table.decimal('amount', 8, 2); | DECIMAL equivalent column with a precision (total digits) and scale (decimal digits) |
table.double('amount', 8, 2); | DOUBLE equivalent column with a precision (total digits) and scale (decimal digits) |
table.enum('level', ['easy', 'hard]); | ENUM equivalent column |
table.float('amount', 8, 2); | FLOAT equivalent column with a precision (total digits) and scale (decimal digits) |
table.geometry('positions'); | GEOMETRY equivalent column |
table.geometryCollection('positions'); | GEOMETRYCOLLECTION equivalent column |
table.integer('price'); | INTEGER equivalent column |
table.ipAddress('visitor'); | IP address equivalent column |
table.json('options'); | JSON equivalent column |
table.lineString('positions'); | LINESTRING equivalent column |
table.longText('content'); | LONGTEXT equivalent column |
table.macAddress('device'); | MAC address equivalent column |
table.mediumInteger('price'); | MEDIUMINT equivalent column |
table.mediumText('content'); | MEDIUMTEXT equivalent column |
table.multiLineString('positions'); | MULTILINESTRING equivalent column |
table.multiPoint('positions'); | MULTIPOINT equivalent column |
table.multiPolygon('positions'); | MULTIPOLYGON equivalent column |
table.point('positions'); | POINT equivalent column |
table.polygon('positions'); | POLYGON equivalent column |
table.rememberToken(); | Adds a nullable remember_token VARCHAR(100) equivalent column |
table.smallInteger('price'); | SMALLINT equivalent column |
table.string('username', 100); | VARCHAR equivalent column with optional length |
table.text('content'); | TEXT equivalent column |
table.time('sunrise'); | TIME equivalent column |
table.timeStamp('created_at'); | TIMESTAMP equivalent column |
table.timestamps(); | Adds nullable created_at and updated_at TIMESTAMP equivalent columns |
table.tinyInteger('price'); | TINYINT equivalent column |
table.unsignedBigInteger('price'); | UNSIGNED BIGINT equivalent column |
table.unsignedDecimal('amount', 8, 2); | UNSIGNED DECIMAL equivalent column with a precision (total digits) and scale (decimal digits) |
table.unsignedInteger('price'); | UNSIGNED INT equivalent column |
table.unsignedMediumInteger('price'); | UNSIGNED MEDIUMINT equivalent column |
table.unsignedSmallInteger('price'); | UNSIGNED SMALLINT equivalent column |
table.unsignedTinyInteger('price'); | UNSIGNED TINYINT equivalent column |
table.uuid('id'); | UUID equivalent column |
table.year('birth_year'); | YEAR equivalent column |
Column Modifiers
In addition to the column types listed above, there are several column modifiers you may use while adding a column. For example, to make the column nullable, you may use the nullable method like this:
Majo
.schema()
.createTable('users', (table) => {
table.string('username', 100).nullable();
});
Below is a list of all the available column modifiers. This list does not include the index modifiers:
| Modifier | Description |
|---|---|
.after('column_name') | Place the column "after" another column |
.autoIncrement() | Set INT column as AUTO_INCREMENT (primary key) |
.charset('utf8') | Set specify character set for the column |
.collation('utf8_unicode_ci') | Set specify collation for the column |
.comment('write comment') | Add a comment to a column |
.default(value) | Add a default value to a column |
.first() | Place column position to the first |
.nullable() | Set null values, or you can pass arguments with false to make column not null |
.unsigned() | Set INT column as unsigned |
.useCurrent() | Set TIMESTAMP columns to use CURRENT_TIMESTAMP as default value |
Table Options
If you want to schema a database table with your configuration, you can add it to the createTable method like this:
Majo
.schema()
.createTable('users', (table) => {
table.string('username', 100).nullable();
table.setEngine('InnoDB');
});
You may use the following commands on the schema builder to define the table's options:
| Command | Description |
|---|---|
table.setEngine('InnoDB'); | Set specify the table storage engine |
table.setCharset('utf8'); | Set a default character set for the table |
table.setCollation('utf8_unicode_ci'); | Set a default collation for the table |
table.setComment('write comment'); | Add a comment to a database table |
table.setAutoIncrement(1); | Set start auto increment number |
Checking For Table / Column Existence
You may easily check for the existence of a table or column using the hasTable and hasColumn methods:
Majo
.schema()
.hasTable('users')
.then((exists) => {
if (!exists) {
Majo
.schema()
.createTable('users', (table) => {
table.increment();
table.string('username', 100);
});
}
});
Majo
.schema()
.updateTable('users', (table) => {
Majo
.schema()
.hasColumn('users', 'username')
.then((exists) => {
if (!exists) {
table.string('username', 100);
}
});
});
Renaming / Dropping Tables
To rename an existing database table, use the renameTable method:
Majo
.schema()
.renameTable('from', 'to')
.then(() => {
res.status(200);
})
.catch((err) => {
res.status(500).json(err);
});
To drop an existing table, you may use the dropTable or dropTableIfExists methods:
Majo
.schema()
.dropTableIfExists('users')
.then(() => {
res.status(200);
})
.catch((err) => {
res.status(500).json(err);
});
Majo
.schema()
.dropTable('users')
.then(() => {
res.status(200);
})
.catch((err) => {
res.status(500).json(err);
});
Modifying Columns
The updateTable method allows you to modify table. You can create a new column or modify existing column with change method. For example, you want to change type length of a string column. To see the change method in action, let's increase the size of the name column from 25 to 50:
Majo
.schema()
.updateTable('users', (table) => {
table.string('username', 50).change();
});
We could also modify column with available method in column modifiers liekk this:
Majo
.schema()
.updateTable('users', (table) => {
table.string('username', 50).nullable().change();
});
If you want to create a new column for existing table, you can write like this:
Majo
.schema()
.updateTable('users', (table) => {
table.string('email', 50);
});
Renaming Columns
To rename a existing column, you may use the renameColumn method.
Majo
.schema()
.updateTable('users', (table) => {
table.renameColumn('from', 'to');
});
Dropping Columns
To drop a column, use the dropColumn method.
Majo
.schema()
.updateTable('users', (table) => {
table.dropColumn('username');
});
Also you can drop more than one column at once.
Majo
.schema()
.updateTable('users', (table) => {
table.dropColumn('username', 'email');
});
Indexes
Create Indexes
The schema builder supports several types of indexes. To create the index, you can chain the index method onto the column definition like this:
Majo
.schema()
.createTable('users', (table) => {
table.string('email').unique();
});
Alternatively, you may create the index after defining the column. For example:
Majo
.schema()
.updateTable('users', (table) => {
table.unique('email');
});
You may even pass more than one arguments of columns to an index method to create a compound (or composite) index:
table.unique('username', 'email');
Available Index Types
Each index method accepts an optional second argument to specify the name of the index.
| Command | Description |
|---|---|
table.primary('id'); | Adds a primary key |
table.primary('id', 'parent_id'); | Adds primary composite keys |
table.unique('username'); | Adds a unique index |
table.unique('username', 'email'); | Adds a unique compiste keys index |
table.index('username'); | Adds a plain index |
table.index('username', 'email'); | Adds a plain composite keys index |
table.spatial('username'); | Adds a spatial index |
Renaming Indexes
To rename an index, you may use the renameIndex method. Please remember, the index name from Majo uses a combination table name, column name, and index type. For example, you want to create a unique index for username column on users table. Then, the index name will become users_username_unique.
Majo
.schema()
.updateTable('users', (table) => {
table.renameIndex('column_name', 'old_index', 'new_index');
});
If you want renaming another available index type, use this:
| Command | Description |
|---|---|
table.renameIndex('column_name', 'old_key_name', 'new_key_name'); | Rename a basic index |
table.renameUnique('column_name', 'old_key_name', 'new_key_name'); | Rename a unique index |
Dropping Indexes
To drop an index, use your column name only. By default, Majo automatically generate a reasonable name to the indexes. It's combination of table name, column name and index type.
| Command | Description |
|---|---|
table.dropPrimary('id'); | Drop a primary key from the table |
table.dropUnique('email'); | Drop a unique index from the table |
table.dropIndex('state'); | Drop a basic index from the table |
table.dropSpatial('location'); | Drop a spatial index from the table |
Foreign Key
Majo also provides support for creating foreign key constraints, which are used to force referential integrity at the database level. For example, let's define a user_id column on the posts table that references the id column on a users table:
Majo
.schema()
.createTable('posts', (table) => {
table.increment('user_id').primary();
table.foreign('user_id')
.references('id').on('users');
});
You may also specify the desired action for the "on delete" and "on update" properties of the constraint:
Majo
.schema()
.createTable('posts', (table) => {
table.increment('user_id').primary();
table.foreign('user_id')
.references('id').on('users')
.onDelete('cascade');
});
To drop a foreign key, you may use the dropForeign method. Foreign key constraint use the same naming as indexes. The constraint name it's combination of table name, column name and "_foreign". But, you just fill in the column name to drop a foreign key. By default, Majo will generate the constraint name.
Majo
.schema()
.updateTable('posts', (table) => {
table.dropForeign('id');
});
Table Maintenance
Analyze Table
Majo
.schema()
.analyzeTable('users')
.then((results) => {
res.status(200).json(results);
});
Check Table
Majo
.schema()
.checkTable('users')
.then((results) => {
res.status(200).json(results);
});
Checksum Table
Majo
.schema()
.checksumTable('users')
.then((results) => {
res.status(200).json(results);
});
Optimize Table
Majo
.schema()
.optimizeTable('users')
.then((results) => {
res.status(200).json(results);
});
Repair Table
Majo
.schema()
.repairTable('users')
.then((results) => {
res.status(200).json(results);
});
Database Manager
Majo makes it easy for users to manage the database. You can manage databases, privileges and cloning the database.
To use a database manager you must call db() method first.
Majo
.db()
If you want to check the database is connected or not, you can use testConnection() method
Majo
.db()
.testConnection()
.then((results) => {
res.status(200).json(results);
});
Show all available database
Show all available databases with specific information.
Majo
.db()
.showDatabases()
.then((results) => {
res.status(200).json(results);
});
Show specific database
Shows the specific database you selected.
Majo
.db()
.showDatabase('database-name')
.then((results) => {
res.status(200).json(results);
});
Show database tables
You can see the list of tables with specific information.
Majo
.db()
.showDatabaseInfo('database-name')
.then((results) => {
res.status(200).json(results);
});
Show spesific table information from your database
You can see the table information from your database
Majo
.db()
.showTableInfo('database-name', 'table-name')
.then((results) => {
res.status(200).json(results);
});
Show specific table information with columns information from your database
You can see the table information with columns information from your table
Majo
.db()
.showColumn('database-name', 'table-name')
.then((results) => {
res.status(200).json(results);
});
Show indexes
You can see all the indexes listed in your database.
Majo
.db()
.showIndexes('database-name')
.then((results) => {
res.status(200).json(results);
});
Show users with information
You can see all the users in your database.
Majo
.db()
.showUsers()
.then((results) => {
res.status(200).json(results);
});
Show the specific user with information
You can see the specific user in your database.
Majo
.db()
.showUser('user-name')
.then((results) => {
res.status(200).json(results);
});
Show variables
You can see all database variables on GLOBAL and SESSION.
Majo
.db()
.showVariables()
.then((results) => {
res.status(200).json(results);
});
Show specific variable
You can see specific variable on GLOBAL or SESSION.
Majo
.db()
.showVariable('max_connection')
.then((results) => {
res.status(200).json(results);
});
Show system variables sql_mode
You can see sql_mode variable settings, with selectSystemVariable() method.
Majo
.db()
.selectSystemVariable()
.then((results) => {
res.status(200).json(results);
});
Set sql_mode
Majo
.db()
.setSqlMode('value');
Set global variable
To assign a value to global system variable, use setGlobalVariable() method. If you want to set value with default, use only one argument, only name variable.
Majo
.db()
.setGlobalVariable('variable-name', 'value');
Set session variable
To assign a value to global system variable, use setSessionVariable() method. If you want to set value with default, use only one argument, only name variable.
Majo
.db()
.setSessionVariable('variable-name', 'value');
Create database
With majo you can create a new database. You can set the character type and collation like this:
Majo
.db()
.createDatabase('database-name', (database) => {
database.charset('utf8');
database.collation('utf8_general_ci');
});
Or you can create a database by checking whether the database is available or not.
Majo
.db()
.createDatabaseIfNotExists('database-name', (database) => {
database.charset('utf8');
database.collation('utf8_general_ci');
});
Update database
To update charset or collation use this:
Majo
.db()
.updateDatabase('database-name', (database) => {
database.charset('utf8');
database.collation('utf8_unicode_ci');
});
Drop database
To dropping a specific database
Majo
.db()
.dropDatabase('database-name');
Rename database
You can renaming the specific database without losing your data.
Majo
.db()
.renameDatabase('old-database-name', 'new-database-name');
Create user privileges
You can create a new user with specific grants.
Majo
.db()
.createUser('majo', '%', (user) => {
user.grantAll();
user.identified('password');
});
If you want to set the user password use user.identified('password'), you can also use empty password. But, if you used Mysql version 8 and upper, you should use user.identified('password', 'mysql8')
You can use the available grants.
| Command | Description |
|---|---|
user.grantAll() | Grant with all privileges |
user.grantCreateUser() | Grant create user |
user.grantEvent() | Grant event |
user.grantFile() | Grant file |
user.grantProcess() | Grant process |
user.grantReload() | Grant reload |
user.grantReplicationClient() | Grant to replication client |
user.grantReplicationSlave() | Grant to replication slave |
user.grantShowDatabases() | Grant to show database |
user.grantShutdown() | Grant to shutdown database |
user.grantSuper() | Grant super |
user.grantCreateTablespace() | Grant to create tablespace |
user.grantUsage() | Grant without any permission |
Update user privileges
You can change user data such as passwords, add grant or revoke grant.
Majo
.db()
.updateUser('majo', '%', (user) => {
user.revokeAll();
user.identified('new-password');
});
You can use the available revoke grants.
| Command | Description |
|---|---|
user.revokeAll() | Revoke all privileges |
user.revokeCreateUser() | Revoke create user |
user.revokeEvent() | Revoke event |
user.revokeFile() | Revoke file |
user.revokeProcess() | Revoke process |
user.revokeReload() | Revoke reload |
user.revokeReplicationClient() | Revoke replication client |
user.revokeReplicationSlave() | Revoke replication slave |
user.revokeShowDatabases() | Revoke show database |
user.revokeShutdown() | Revoke shutdown database |
user.revokeSuper() | Revoke super |
user.revokeCreateTablespace() | Revoke create tablespace |
Drop user privileges
To dropping a specific user use like this:
Majo
.db()
.dropuser('user-name');
Clone database
You can cloning structured and data from specific database to a new database like this:
Majo
.db()
.cloneDatabase('old-database', 'new-database');
Clone only structured database
You can cloning only structured from specific database to a new database like this:
Majo
.db()
.cloneDatabaseStructured('old-database', 'new-database');
Triggers
Do you want to make a trigger? Majo provides a method for making triggers easily. You can see a list of triggers, create new triggers or delete triggers.
If you want to use trigger, use trigger() method first, like this:
Majo
.trigger()
Show all triggers on database
If you want to see all triggers on the database, you can use showTriggers() method, like this:
Majo
.trigger()
.showTriggers('database-name')
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
Show specific trigger on database
Also, you can see the detail specific trigger from your database. Use method showTrigger() likke this:
Majo
.trigger()
.showTrigger('database-name', 'trigger-name')
.then((results) => {
res.status(200).json(results);
})
.catch((err) => {
res.status(500).json(err);
});
Create new trigger
If you want to make a trigger, fill in the first argument with log inserted table and in the second argument, fill with the trigger table.
Majo
.trigger()
.createAfterInsert('log_users', 'users', (trigger) => {
trigger.field('full_name').value().new('full_name');
trigger.field('description').value('Insert the data');
trigger.field('created_at').value().now();
});
Majo supports all trigger events, along with a list of trigger events that you can make.
| Command | Description |
|---|---|
.createAfterInsert('log_users', 'users') | Create trigger with event after insert |
.createAfterUpdate('log_users', 'users') | Create trigger with event after update |
.createAfterDelete('log_users', 'users') | Create trigger with event after delete |
.createBeforeInsert('log_users', 'users') | Create trigger with event before insert |
.createBeforeUpdate('log_users', 'users') | Create trigger with event before update |
.createBeforeDelete('log_users', 'users') | Create trigger with event before delete |
Also, a little explanation on insert value trigger. Here are some commands that you can use when creating triggers.
| Command | Descripption |
|---|---|
trigger.field('field-name'); | Fill with the name of field |
trigger.field('field-name').value(1); | You can fill value with string or number |
trigger.field('field-name').value().old('trigger-table-field-name'); | The value will be filled with the old data value |
trigger.field('field-name').value().new('trigger-table-field-name'); | The value will be filled with the new data value |
trigger.field('field-name').value().now(); | The value will be filled with the timestamp |
Dropping a specific trigger
If you want to delete a trigger, you can delete only trigger.
Majo
.trigger()
.dropTrigger('database-name', 'trigger-name');
Also, if you want to delete many triggers at once, you can write like this:
Majo
.trigger()
.dropTrigger('database-name', 'trigger-name',
'trigger-name2', 'trigger-name3');
Relationships
Has One
Majo provides feature Relationships table, like has one data or has many data on the table. For example, a users table might be associated with one phone. To use has one relationships, you can use like this:
Majo
.select()
.from('users')
.hasOne('phone', 'Phone', 'user_id', 'user_id')
.then((results) => {
res.status(200).json(results);
});
Has one relationships use hasOne() method, and this method use argument like this ('relation-table', 'result-object-name', 'main-table-column', 'relation-column'). In the example table users set column user_id as a primary key and on the table phone set column user_id as a foreign key.
Then, if you want to use a lot has one relationships, you should use like this:
Majo
.select()
.from('users')
.hasOne(
['phone', 'Phone', 'user_id', 'user_id'],
['address', 'Address', 'user_id', 'user_id'],
)
.then((results) => {
res.status(200).json(results);
});
Has Many
Majo
.select()
.from('users')
.hasMany('orders', 'Order', 'user_id', 'user_id')
.then((results) => {
res.status(200).json(results);
});
Has many relationships use hasMany() method, and this method use argument like this ('relation-table', 'result-object-name', 'main-table-column', 'relation-column'). In the example table users set column user_id as a primary key and on the table orders set column user_id as a foreign key.
Then, if you want to use a lot has many relationships, you should use like this:
Majo
.select()
.from('users')
.hasMany(
['orders', 'Order', 'user_id', 'user_id'],
['payments', 'Payment', 'user_id', 'user_id'],
)
.then((results) => {
res.status(200).json(results);
});
Sometimes, the relationship requires some adjustment. Therefore, you can use some queries for your relationships like this:
Majo
.select()
.from('users')
.hasOne('orders', 'Order', 'user_id', 'user_id', (condition) => {
condition.select('order_id', 'user_id', 'price');
condition.where('price', '>', 5000);
condition.orderBy('date', 'DESC');
})
.then((results) => {
res.status(200).json(results);
});
Majo
.select()
.from('users')
.hasMany('orders', 'Order', 'user_id', 'user_id', (condition) => {
condition.select('order_id', 'user_id', 'price');
condition.where('price', '>', 5000);
condition.orderBy('date', 'DESC');
})
.then((results) => {
res.status(200).json(results);
});
Has Row
If you want to make a custom object relationship you can use hasRow() method. For example, you want to get a total price order but you should sum the price field. You can select a return field and get the value.
The first argument is a name table relationship, the second argument is the object name you want, and third is a return field from querying.
Majo
.select()
.from('users')
.hasRow('orders', 'total_order', 'total_order', 'user_id', 'user_id', (condition) => {
condition.sum('total_price', 'total_order');
})
.first()
.then((results) => {
res.status(200).json(results);
});
Then, the object return looks like this:
{
"id": 1,
"name": "test",
"total_order": 100000,
}
FAQs
Majo Mysql is a library that can help you build query, schema, and control your database more easier.
The npm package majo-mysql receives a total of 43 weekly downloads. As such, majo-mysql popularity was classified as not popular.
We found that majo-mysql 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.
Package last updated on 29 Feb 2020
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.
Related posts
Research
Security News
Malicious npm Packages Use Telegram to Exfiltrate BullX Credentials
Socket uncovers an npm Trojan stealing crypto wallets and BullX credentials via obfuscated code and Telegram exfiltration.
By Kush Pandya - May 08, 2025
Research
Security News
Backdooring the IDE: Malicious npm Packages Hijack Cursor Editor on macOS
Malicious npm packages posing as developer tools target macOS Cursor IDE users, stealing credentials and modifying files to gain persistent backdoor access.
By Kirill Boychenko - May 07, 2025