How To Install Nodemon In Node Js
Using MySQL with Node.js and the mysql JavaScript Client
NoSQL databases are rather popular amidst Node developers, with MongoDB (the "Thousand" in the Hateful stack) leading the pack. When starting a new Node project, nonetheless, you shouldn't just take Mongo as the default option. Rather, the type of database you lot cull should depend on your project's requirements. If, for example, you lot need dynamic table cosmos, or existent-fourth dimension inserts, then a NoSQL solution is the way to go. If your projection deals with complex queries and transactions, on the other hand, an SQL database makes much more sense.
In this tutorial, we'll take a look at getting started with the mysql module — a Node.js client for MySQL, written in JavaScript. I'll explicate how to employ the module to connect to a MySQL database and perform the usual CRUD operations, earlier looking at stored procedures and escaping user input.
This popular article was updated in 2022 to reflect current practices for using MySQL with Node.js. For more on MySQL, read Jump Start MySQL.
Quick Start: How to Use MySQL in Node
If y'all've arrived hither looking for a quick mode to get up and running with MySQL in Node, we've got you covered!
Hither's how to use MySQL in Node in five easy steps:
- Create a new project:
mkdir mysql-exam && cd mysql-test. - Create a
package.jsonfile:npm init -y. - Install the mysql module:
npm install mysql. - Create an
app.jsfile and copy in the snippet beneath (editing the placeholders every bit appropriate). - Run the file:
node app.js. Find a "Connected!" message.
const mysql = require ( 'mysql' ) ; const connexion = mysql. createConnection ( { host: 'localhost' , user: 'user' , password: 'password' , database: 'database name' } ) ; connection. connect ( ( err ) => { if (err) throw err; panel . log ( 'Connected!' ) ; } ) ; Installing the mysql Module
Now let'south have a closer await at each of those steps.
mkdir mysql-test cd mysql-test npm init -y npm install mysql First of all we're using the command line to create a new directory and navigate to it. Then we're creating a package.json file using the control npm init -y. The -y flag means that npm will employ defaults without going through an interactive process.
This step also assumes that you have Node and npm installed on your organisation. If this is not the instance, then check out this SitePoint article to notice out how to do that: Install Multiple Versions of Node.js using nvm.
After that, we're installing the mysql module from npm and saving it as a project dependency. Project dependencies (every bit opposed to devDependencies) are those packages required for the application to run. You tin can read more nigh the differences between the two hither.
If yous need further aid using npm, then be sure to check out this guide, or ask in our forums.
Getting Started
Before we get on to connecting to a database, it'due south important that you take MySQL installed and configured on your machine. If this is non the case, please consult the installation instructions on their home page.
The next matter nosotros need to do is to create a database and a database table to work with. Yous tin can do this using a
graphical interface, such as Adminer, or using the command line. For this commodity I'll exist using a database chosen sitepoint and a table called authors. Hither's a dump of the database, so that you tin go up and running speedily if you wish to follow along:
CREATE DATABASE sitepoint CHARACTER Set up utf8 COLLATE utf8_general_ci; Utilise sitepoint; CREATE Tabular array authors ( id int ( 11 ) NOT Zip AUTO_INCREMENT , name varchar ( 50 ) , metropolis varchar ( l ) , Master Primal (id) ) ENGINE = InnoDB DEFAULT CHARSET =utf8 AUTO_INCREMENT = 5 ; INSERT INTO authors (id, name, urban center) VALUES ( ane , 'Michaela Lehr' , 'Berlin' ) , ( two , 'Michael Wanyoike' , 'Nairobi' ) , ( three , 'James Hibbard' , 'Munich' ) , ( 4 , 'Karolina Gawron' , 'Wrocław' ) ; 
Connecting to the Database
At present, let's create a file called app.js in our mysql-exam directory and see how to connect to MySQL from Node.js.
const mysql = require ( 'mysql' ) ; // Offset you demand to create a connection to the database // Be sure to replace 'user' and 'countersign' with the correct values const con = mysql. createConnection ( { host: 'localhost' , user: 'user' , password: 'password' , } ) ; con. connect ( ( err ) => { if (err) { console . log ( 'Error connecting to Db' ) ; return ; } console . log ( 'Connection established' ) ; } ) ; con. end ( ( err ) => { // The connection is terminated gracefully // Ensures all remaining queries are executed // Then sends a quit bundle to the MySQL server. } ) ; At present open up upward a terminal and enter node app.js. In one case the connectedness is successfully established you should be able to come across the "Connection established" message in the console. If something goes wrong (for case, you enter the wrong password), a callback is fired, which is passed an instance of the JavaScript Error object (err). Endeavour logging this to the panel to see what additional useful information it contains.
Using nodemon to Watch the Files for Changes
Running node app.js past hand every time we make a change to our code is going to go a fleck tedious, so let'due south automate that. This office isn't necessary to follow forth with the rest of the tutorial, only volition certainly save you some keystrokes.
Let's beginning off by installing a the nodemon package. This is a tool that automatically restarts a Node application when file changes in a directory are detected:
npm install --salvage-dev nodemon Now run ./node_modules/.bin/nodemon app.js and brand a change to app.js. nodemon should detect the change and restart the app.
Note: we're running nodemon straight from the node_modules folder. Yous could too install it globally, or create an npm script to kicking it off.
Executing Queries
Reading
Now that you know how to establish a connection to a MySQL database from Node.js, let'southward see how to execute SQL queries. We'll start by specifying the database name (sitepoint) in the createConnection command:
const con = mysql. createConnection ( { host: 'localhost' , user: 'user' , password: 'countersign' , database: 'sitepoint' } ) ; Once the connection is established, nosotros'll use the con variable to execute a query confronting the database table authors:
con. query ( 'SELECT * FROM authors' , ( err,rows ) => { if (err) throw err; console . log ( 'Information received from Db:' ) ; panel . log (rows) ; } ) ; When you lot run app.js (either using nodemon or by typing node app.js into your last), you should be able to meet the data returned from the database logged to the terminal:
[ RowDataPacket { id: 1 , name: 'Michaela Lehr' , city: 'Berlin' } , RowDataPacket { id: 2 , proper noun: 'Michael Wanyoike' , city: 'Nairobi' } , RowDataPacket { id: three , name: 'James Hibbard' , metropolis: 'Munich' } , RowDataPacket { id: 4 , proper noun: 'Karolina Gawron' , metropolis: 'Wrocław' } ] Data returned from the MySQL database can be parsed by but looping over the rows object.
rows. forEach ( ( row ) => { console . log ( ` ${row. name } lives in ${row. city } ` ) ; } ) ; This gives you the following:
Michaela Lehr lives in Berlin Michael Wanyoike lives in Nairobi James Hibbard lives in Munich Karolina Gawron lives in Wrocław Creating
You lot can execute an insert query against a database, like so:
const author = { name: 'Craig Buckler' , city: 'Exmouth' } ; con. query ( 'INSERT INTO authors Gear up ?' , author, ( err, res ) => { if (err) throw err; panel . log ( 'Final insert ID:' , res. insertId ) ; } ) ; Note how we can get the ID of the inserted record using the callback parameter.
Updating
Similarly, when executing an update query, the number of rows afflicted can be retrieved using result.affectedRows:
con. query ( 'UPDATE authors Prepare metropolis = ? Where ID = ?' , [ 'Leipzig' , 3 ] , ( err, result ) => { if (err) throw err; console . log ( ` Changed ${outcome. changedRows } row(south) ` ) ; } ) ; Destroying
The aforementioned matter goes for a delete query:
con. query ( 'DELETE FROM authors WHERE id = ?' , [ 5 ] , ( err, upshot ) => { if (err) throw err; console . log ( ` Deleted ${result. affectedRows } row(southward) ` ) ; } ) ; Advanced Utilize
I'd like to finish off past looking at how the mysql module handles stored procedures and the escaping of user input.
Stored Procedures
Put but, a stored procedure is prepared SQL code that you can salve to a database, so that it tin can hands be reused. If you're in demand of a refresher on stored procedures, and then check out this tutorial.
Let'south create a stored procedure for our sitepoint database which fetches all the writer details. We'll phone call it sp_get_authors. To practise this, you'll need some kind of interface to the database. I'm using Adminer. Run the following query against the sitepoint database, ensuring that your user has admin rights on the MySQL server:
DELIMITER $$ CREATE PROCEDURE `sp_get_authors` ( ) BEGIN SELECT id, proper name, metropolis FROM authors; END $$ This will create and store the procedure in the information_schema database in the ROUTINES table.
Note: if the delimiter syntax looks strange to you, it's explained here.
Next, establish a connection and use the connectedness object to call the stored procedure as shown:
con. query ( 'CALL sp_get_authors()' , role ( err, rows ) { if (err) throw err; panel . log ( 'Information received from Db:' ) ; console . log (rows) ; } ) ; Salvage the changes and run the file. Once it's executed, you should exist able to view the data returned from the database:
[ [ RowDataPacket { id: 1, proper name: 'Michaela Lehr', city: 'Berlin' }, RowDataPacket { id: ii, proper noun: 'Michael Wanyoike', city: 'Nairobi' }, RowDataPacket { id: 3, name: 'James Hibbard', metropolis: 'Leipzig' }, RowDataPacket { id: 4, name: 'Karolina Gawron', city: 'Wrocław' }, OkPacket { fieldCount: 0, affectedRows: 0, insertId: 0, serverStatus: 34, warningCount: 0, message: '', protocol41: truthful, changedRows: 0 } ] Along with the data, it returns some boosted information, such equally the afflicted number of rows, insertId etc. You need to iterate over the 0th index of the returned data to go employee details separated from the rest of the information:
rows[ 0 ] . forEach ( ( row ) => { console . log ( ` ${row. proper noun } lives in ${row. metropolis } ` ) ; } ) ; This gives you the following:
Michaela Lehr lives in Berlin Michael Wanyoike lives in Nairobi James Hibbard lives in Leipzig Karolina Gawron lives in Wrocław Now permit'due south consider a stored procedure which requires an input parameter:
DELIMITER $$ CREATE PROCEDURE `sp_get_author_details` ( in author_id int ) Begin SELECT proper name, city FROM authors where id = author_id; Stop $$ We can pass the input parameter while making a call to the stored procedure:
con. query ( 'CALL sp_get_author_details(1)' , ( err, rows ) => { if (err) throw err; console . log ( 'Data received from Db:\n' ) ; console . log (rows[ 0 ] ) ; } ) ; This gives you the following:
[ RowDataPacket { name: 'Michaela Lehr', metropolis: 'Berlin' } ] Nearly of the time when we endeavour to insert a tape into the database, nosotros need the last inserted ID to be returned as an out parameter. Consider the following insert stored procedure with an out parameter:
DELIMITER $$ CREATE PROCEDURE `sp_insert_author` ( out author_id int , in author_name varchar ( 25 ) , in author_city varchar ( 25 ) ) Begin insert into authors(name, urban center) values (author_name, author_city) ; set author_id = LAST_INSERT_ID( ) ; END $$ To brand a procedure call with an out parameter, we first demand to enable multiple calls while creating the connection. Then, modify the connection by setting the multiple statement execution to true:
const con = mysql. createConnection ( { host: 'localhost' , user: 'user' , password: 'password' , database: 'sitepoint' , multipleStatements: true } ) ; Next, when making a phone call to the procedure, set an out parameter and pass it in:
con. query ( "SET @author_id = 0; Call sp_insert_author(@author_id, 'Craig Buckler', 'Exmouth'); SELECT @author_id" , ( err, rows ) => { if (err) throw err; console . log ( 'Data received from Db:\n' ) ; console . log (rows) ; } ) ; As seen in the to a higher place lawmaking, nosotros have set an @author_id out parameter and passed it while making a call to the stored procedure. Once the call has been made we need to select the out parameter to access the returned ID.
Run app.js. On successful execution you lot should be able to see the selected out parameter forth with various other information. rows[2] should give yous access to the selected out parameter:
[ RowDataPacket { '@author_id' : half dozen } ] ] Note: To delete a stored procedure you lot need to run the control Driblet Procedure <procedure-name>; against the database you created it for.
Escaping User Input
In gild to avoid SQL Injection attacks, y'all should always escape whatever data you receive from users before using it within an SQL query. Let's demonstrate why:
const userSubmittedVariable = '1' ; con. query ( ` SELECT * FROM authors WHERE id = ${userSubmittedVariable} ` , ( err, rows ) => { if (err) throw err; console . log (rows) ; } ) ; This seems harmless enough and even returns the right event:
{ id: i , name: 'Michaela Lehr' , city: 'Berlin' } Nevertheless, try changing the userSubmittedVariable to this:
const userSubmittedVariable = 'i OR i=one' ; We suddenly have access to the entire data ready. At present alter it to this:
const userSubmittedVariable = 'one; DROP TABLE authors' ; Nosotros're now in proper trouble!
The expert news is that help is at paw. You lot simply have to use the mysql.escape method:
con. query ( ` SELECT * FROM authors WHERE id = ${mysql. escape (userSubmittedVariable) } ` , ( err, rows ) => { if (err) throw err; console . log (rows) ; } ) ; You tin can also use a question mark placeholder, as nosotros did in the examples at the beginning of the commodity:
con. query ( 'SELECT * FROM authors WHERE id = ?' , [userSubmittedVariable] , ( err, rows ) => { if (err) throw err; console . log (rows) ; } ) ; Why Not Just Utilise an ORM?
Before we go into the pros and cons of this approach, let'south take a 2d to await at what ORMs are. The post-obit is taken from an answer on Stack Overflow:
Object-Relational Mapping (ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm. When talking near ORM, most people are referring to a library that implements the Object-Relational Mapping technique, hence the phrase "an ORM".
Then this means y'all write your database logic in the domain-specific linguistic communication of the ORM, every bit opposed to the vanilla approach nosotros've been taking and so far. To give you an thought of what this might look like, here's an example using Sequelize, which queries the database for all authors and logs them to the console:
const sequelize = new Sequelize ( 'sitepoint' , 'user' , 'password' , { host: 'localhost' , dialect: 'mysql' } ) ; const Writer = sequelize. ascertain ( 'author' , { name: { type: Sequelize . Cord , } , city: { type: Sequelize . Cord } , } , { timestamps: fake } ) ; Author . findAll ( ) . then ( authors => { console . log ( "All authors:" , JSON . stringify (authors, cipher , iv ) ) ; } ) ; Whether or not using an ORM makes sense for you will depend very much on what you're working on and with whom. On the 1 hand, ORMS tend to make developers more than productive, in role by abstracting away a big part of the SQL then that not everyone on the team needs to know how to write super efficient database specific queries. It's besides easy to motion to unlike database software, because you're developing to an abstraction.
On the other manus withal, it is possible to write some actually messy and inefficient SQL every bit a result of non understanding how the ORM does what information technology does. Functioning is also an issue in that information technology's much easier to optimize queries that don't have to become through the ORM.
Whichever path you lot take is up to you, only if this is a decision y'all're in the procedure of making, check out this Stack Overflow thread: Why should you lot use an ORM?. As well check out this post on SitePoint: 3 JavaScript ORMs You Might Non Know.
Conclusion
In this tutorial, we've installed the mysql client for Node.js and configured information technology to connect to a database. We've also seen how to perform Grime operations, piece of work with prepared statements and escape user input to mitigate SQL injection attacks. And yet, we've just scratched the surface of what the mysql client offers. For more detailed information, I recommend reading the official documentation.
And delight comport in mind that the mysql module is not the only prove in boondocks. There are other options too, such every bit the popular node-mysql2.
Source: https://www.sitepoint.com/using-node-mysql-javascript-client/
Posted by: hendersonfachur56.blogspot.com
0 Response to "How To Install Nodemon In Node Js"
Post a Comment