fluent-mysql
Advanced tools
fluent-mysql is a query builder for MySQL. It performs basic database operations using mysql library.
Inspired by Laravel Query Builder
Use the node package manager npm to install fluent-mysql.
npm install fluent-mysql
const DB = require('fluent-mysql');
The parameters are the same with mysql library. You can list all options from here
const DB = require('fluent-mysql');
let connection = DB.connect({
host : process.env.DB_HOST,
user : process.env.DB_USER,
password : process.env.DB_PASSWORD,
port : process.env.DB_PORT,
database : process.env.DB_DATABASE,
});
connection.then(result => {
console.log(result.message); // Connected to MySQL Database
if(result.success){
// ...
}
}).catch(err => console.log(err));
This function provides connection pooling using createPool(config). This function gets pool connection, query and release it.
const DB = require('fluent-mysql');
DB.createPool({
host : process.env.DB_HOST,
user : process.env.DB_USER,
password : process.env.DB_PASSWORD,
port : process.env.DB_PORT,
database : process.env.DB_DATABASE,
});
let query = DB.table('users').get();
query.then(results => {
//...
}
}).catch(err => console.log(err));
It is necessary to specify table in all queries except for writing your own query with query method.
// Get all records from users table
let users = DB.table('users').get();
users.then( results => {
//...
});
Using the select method, you can specify a custom select clause for the query.
let users = DB.table('users').select('name', 'phone', 'age').get();
users.then( results => {
//...
});
You can also use the distinct method to force the query to return distinct results.
let users = DB.table('users').select('name').distinct().get();
users.then( results => {
//...
});
NOTE: All queries return Promise, for this reason use async/await or then() if you need query results immediately before any action.
get must be the last method in methods chain.
let users = DB.table('users').get();
users.then( results => {
//...
});
You can get only the first row from all results.
let users = DB.table('users').first();
users.then( result => {
//...
});
To retrieve a single row by its id column value, use the find method:
let users = DB.table('users').find(10);
users.then( result => {
//...
});
You can also write your own query with query method.
let users = DB.query(`SELECT * FROM users WHERE name = "John"`);
users.then( results => {
//...
});
let users = DB.table('users').where('userName', '=', 'John' ).get();
users.then( results => {
//...
});
let users = DB.table('users').where('userName', '=', 'John' ).orWhere('age', '>', 20 ).get();
users.then( results => {
//...
});
// Get users whose ages between 20 and 30
let users = DB.table('users').whereBetween('age', 20, 40 ).get();
users.then( results => {
//...
});
let users = DB.table('users').where('name', '=', 'John' ).orWhereBetween('age', 30, 40 ).get();
users.then( results => {
//...
});
let users = DB.table('users').whereNotBetween('age', 50, 60 ).get();
users.then( results => {
//...
});
let users = DB.table('users').whereBetween('salary', 1000, 2000 ).orWhereNotBetween('age', 50, 60 ).get();
users.then( results => {
//...
});
let users = DB.table('users').whereIn('age', [25,35,45] ).get();
users.then( results => {
//...
});
let users = DB.table('users').where('userName', '=', 'John' ).orWhereIn('age', [25,35,45] ).get();
users.then( results => {
//...
});
let users = DB.table('users').whereNotIn('age', [25,35,45] ).get();
users.then( results => {
//...
});
let users = DB.table('users').where('userName', '=', 'John' ).orWhereNotIn('age', [20,30,40] ).get();
users.then( results => {
//...
});
let users = DB.table('users').whereNull('phone').get();
users.then( results => {
//...
});
let users = DB.table('users').whereNull('phone').orWhereNull('email').get();
users.then( results => {
//...
});
let users = DB.table('users').whereNotNull('phone' ).get();
users.then( results => {
//...
});
let users = DB.table('users').where('age', '>', 25 ).orWhereNotNull('email').get();
users.then( results => {
//...
});
The orderBy method allows you to sort the result of the query by a given column. The first argument to the orderBy method should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc.
let users = DB.table('users').orderBy('name', 'ASC').get();
users.then( results => {
//...
});
To limit the number of results returned from the query, you may use the limit method.
let users = DB.table('users').limit(20).get();
users.then( results => {
//...
});
To skip a given number of results in the query, you may use the offset method.
let users = DB.table('users').limit(20).offset(10).get();
users.then( results => {
//...
});
The groupBy and having methods may be used to group the query results.
let authorBooks = DB.table('books')
.select('author', 'COUNT(bookID) AS totalBook')
.groupBy('author')
.having('totalBook', '>', 10)
.get();
authorBooks.then( results => {
//...
});
The fluent-mysql may also be used to write join statements. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You can even join to multiple tables in a single query.
let users = DB.table('users')
.join('contacts', 'users.id', '=', 'contacts.user_id')
.join('orders', 'users.id', '=', 'orders.user_id')
.select('users.*', 'contacts.phone', 'orders.price')
.get();
users.then( results => {
//...
});
let users = DB.table('users')
.leftJoin('contacts', 'users.id', '=', 'contacts.user_id')
.select('users.*', 'contacts.phone')
.get();
users.then( results => {
//...
});
let users = DB.table('users')
.rightJoin('contacts', 'users.id', '=', 'contacts.user_id')
.select('users.*', 'contacts.phone')
.get();
users.then( results => {
//...
});
You may call any of these methods after constructing your query. min, max, avg and sum methods take two parameters. First is the name of column in database. Second one is the column name after query. It may be anything you want. Second parameter is optional.
let usersCount = DB.table('users').count();
// SELECT MIN(age) AS minAge FROM users
let minAge = DB.table('users').min('age', 'minAge');
// SELECT MAX(age) AS maxAge FROM users
let maxAge = DB.table('users').max('age', 'maxAge');
// SELECT AVG(age) AS avgAge FROM users
let avgAge = DB.table('users').avg('age', 'avgAge');
// SELECT SUM(age) AS sumAge FROM users
let sumAge = DB.table('users').sum('age', 'sumAge');
Instead of using the count method to determine if any records exist that match your query's constraints, you may use the exists method.
let exists = DB.table('users').where('name', '=', 'John').exists();
let insertion = DB.table('users')
.insert({id: 50, name: 'John', age:25});
insertion.then( result => {
// ...
});
This method updates a record, if not exists creates it.
let update = DB.table('users')
.insertOrUpdate({name: 'John', age:25});
insertion.then( result => {
// ...
});
let update = DB.table('users')
.update({id: 125, name: 'John', age:35});
insertion.then( result => {
// ..
});
Also you can use where method in order to specify conditions.
(Be careful about putting where method before update.)
let update = DB.table('users')
.where('age', '>',40)
.update({salary : 5000});
insertion.then( result => {
// ..
});
let deletion = DB.table('users')
.where('name','=','John')
.delete();
deletion.then( result => {
// ..
});
Example of using with multiple methods:
let users = DB.table('users')
.select('name','age')
.where('age', '>', 30)
.where('name', '=', 'John')
.limit(5)
.orderBy('age', 'DESC')
.get();
users.then( results => {
// ...
});
As long as you use the last method correctly, you can change the order of methods
//Both functions returns the same result
let users = DB.table('users').select('name','age').where('age', '>', 30).get();
let users = DB.where('age', '>', 30).select('name','age').table('users').get();
users.then( results => {
//...
});
Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.
The fluent-mysql is open-sourced software licensed under the MIT license. MIT
FAQs
MySQL query builder to perform database operations
The npm package fluent-mysql receives a total of 4 weekly downloads. As such, fluent-mysql popularity was classified as not popular.
We found that fluent-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.
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
OpenSSF has published OSPS Baseline, an initiative designed to establish a minimum set of security-related best practices for open source software projects.
Security News
Michigan TypeScript founder Dimitri Mitropoulos implements WebAssembly runtime in TypeScript types, enabling Doom to run after processing 177 terabytes of type definitions.
Security News
vlt's new "reproduce" tool verifies npm packages against their source code, outperforming traditional provenance adoption in the JavaScript ecosystem.