easy-mysql-js

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: '[e-mail]', 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: '[e-mail]', 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: '[e-mail]', phone: '1235' },{ id: 2, name: 'asd', email: '[e-mail]', 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:"[e-mail]",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:"[e-mail]"} // where email='[e-mail]'
	const data_to_set = {email:"[e-mail]"} // set email='[e-mail]'

	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:"[e-mail]",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

• Prisma

License

This project is licensed under the MIT License