easy-mysql-js
v1.0.5
Published
Easy MySQL for NodeJS
Downloads
721,214
Readme
easy-mysql-js
You can do MySQL database operations (select, insert, update, delete, insertOrUpdate) easily with this package.
List of Features
- Select (manual query)
- SelectSingle
- SelectMulti
- Insert
- Update
- InsertOrUpdate
- Delete
Download & Installation
1- Install the package
$ npm i easy-mysql-js
2- Initialize Prisma
$ npx prisma init
or
$ sudo npx prisma init
3- Configure schema.prisma file
After run "npx prisma init" command, there will be "prisma" folder in your project.
Open schema.prisma file in the prisma folder and change "postgresql" as "mysql" (for MySQL) or "sqlserver" (for MSSQL).
4- Configure .env file
### MySQL
DATABASE_URL="mysql://your_db_username:your_db_password@your_ip_address:port/your_db_name"
### MSSQL
# DATABASE_URL="sqlserver://your_ip_address:port;database=your_db_name;user=your_db_username;password=your_db_password;trustServerCertificate=true"
NODE_ENV="development"
5- Generate Database Tables
$ npx prisma db pull
$ npx prisma generate
or
$ sudo npx prisma db pull
$ sudo npx prisma generate
NOTE: If you change your MySQL database tables, columns, etc. you need to run:
$ npx prisma db pull
$ npx prisma generate
or
$ sudo npx prisma db pull
$ sudo npx prisma generate
Environment Variables
To run this project, you will need to add the following environment variables to your .env file
NODE_ENV="development"
or NODE_ENV="production"
for MySQL
DATABASE_URL="mysql://your_db_username:your_db_password@your_ip_address:3306/your_db_name"
for MSSQL
DATABASE_URL="sqlserver://your_ip_address:port;database=your_db_name;user=your_db_username;password=your_db_password;trustServerCertificate=true;"
Example Usage
1- Create "index.js" file
const {Select} = require('easy-mysql-js')
async function test() {
const table_name = 'users'
const query = 'select * from '+table_name+' limit 2' // sql query
const select_result = await Select(query)
console.log(select_result)
}
test()
2- Run
$ node index.js
Select
const {Select, SelectSingle, SelectMulti} = require('easy-mysql-js')
async function test() {
/*-----------------------------------------------------------------------------------------
--- SELECT --------------------------------------------------------------------------------
You can get records from the database with your custom sql query.
Returns an array
*/
const query = 'select * from users where id=1' // sql query
const select_result = await Select(query)
console.log(select_result)
// Success => [ { id: 1, name: 'qwe', email: '[email protected]', phone: '1235' } ]
// Error => []
// ----------------------------------------------------------------------------------------
// ----------------------------------------------------------------------------------------
/*-----------------------------------------------------------------------------------------
--- SELECT SINGLE -------------------------------------------------------------------------
You can get single record from the database with unique column.
NOTE: Column in where_condition must be unique or primary key.
Returns an object
*/
const table_name = "users" // your database table name
const where_condition = {id:1} // => where id=1
const return_columns = {id:true,name:true,email:true} // => select id,name,email
const selectSingle_result = await SelectSingle(table_name, where_condition, return_columns)
console.log(selectSingle_result)
// Success => { id: 1, name: 'qwe', email: '[email protected]', phone: '1235' }
// Error => null
// ----------------------------------------------------------------------------------------
// ----------------------------------------------------------------------------------------
/*-----------------------------------------------------------------------------------------
--- SELECT MULTI --------------------------------------------------------------------------
You can get multi records from the database with a column.
Returns an array
*/
const where_condition2 = {name:'test'} // => where name='test'
const return_columns = {id:true,name:true,email:true} // => select id,name,email
const selectMulti_result = await SelectMulti(table_name, where_condition, return_columns)
console.log(selectMulti_result)
// Success => [{ id: 1, name: 'qwe', email: '[email protected]', phone: '1235' },{ id: 2, name: 'asd', email: '[email protected]', phone: '12344955' }, ... ]
// Error => []
// ----------------------------------------------------------------------------------------
// ----------------------------------------------------------------------------------------
}
test()
Insert
const {Insert} = require('easy-mysql-js')
async function test() {
/*-----------------------------------------------------------------------------------------
--- INSERT --------------------------------------------------------------------------------
You can insert a new record to database easily with table_name and data_to_insert parameters.
Returns true or false
*/
const table_name = "users" // your database table name
const data_to_insert = {name:"name",email:"[email protected]",phone:"1234567890"} // your table columns
const insert_result = await Insert(table_name, data_to_insert)
console.log(insert_result)
// Success => true
// Error => false
// ----------------------------------------------------------------------------------------
// ----------------------------------------------------------------------------------------
}
test()
Update
const {Update} = require('easy-mysql-js')
async function test() {
/*-----------------------------------------------------------------------------------------
--- UPDATE --------------------------------------------------------------------------------
You can update the record in the database with table_name, where_condition and data_to_set
parameters.
Returns true or false
*/
const table_name = "users" // your database table name
const where_condition = {email:"[email protected]"} // where email='[email protected]'
const data_to_set = {email:"[email protected]"} // set email='[email protected]'
const update_result = await Update(table_name, where_condition, data_to_set)
console.log(update_result)
// Success => true
// Error => false
// ----------------------------------------------------------------------------------------
// ----------------------------------------------------------------------------------------
}
test()
InsertOrUpdate
const {InsertOrUpdate} = require('easy-mysql-js')
async function test() {
/*-----------------------------------------------------------------------------------------
--- INSERT OR UPDATE ----------------------------------------------------------------------
You can insert or update the record easily with table_name, where_condition, data_to_update
and data_to_insert parameters.
If the record exists then it will be updated. If the record does not exist then it will be
inserted.
Returns true or false
*/
const table_name = "users" // your database table name
const where_condition = {id:1} // where id=1
const data_to_update = {phone:"123123"} // if the record exists then update the columns
const data_to_insert = {name:"name2",email:"[email protected]",phone:"1234567891"} // if the record does not exist then add a new record
const insertOrUpdate_result = await InsertOrUpdate(table_name, where_condition, data_to_update, data_to_insert)
console.log(insertOrUpdate_result)
// Success => true
// Error => false
// ----------------------------------------------------------------------------------------
// ----------------------------------------------------------------------------------------
}
test()
Delete
const {Delete} = require('easy-mysql-js')
async function test() {
/*-----------------------------------------------------------------------------------------
--- DELETE --------------------------------------------------------------------------------
You can delete the records from the database with where_condition.
Returns true or false
*/
const table_name = "users" // your database table name
const where_condition = {id:1} // where id=1
const delete_result = await Delete(table_name, where_condition)
console.log(delete_result)
// Success => true
// Error => false
// ----------------------------------------------------------------------------------------
// ----------------------------------------------------------------------------------------
}
test()
Acknowledgements
License
This project is licensed under the MIT License