Learn MySQL with Node.js in one Video

List Of Content

ADS Area (CARBON)

ADS Area (CARBON)

 

Getting Started

MySQL is an open source relational database management system. SQL stands for Structured Query Language and it is the most widely spread Database System used by a lot of companies and teams as their DMS or Database Management System simply because it is simple to use, gives you superpowers with it's easy to read and create queries plus its performance friendly and supported by every language you may be using especially (PHP).

So we will do baby steps on getting started with MySQL in general and how to use it with Node.js, so if you're new either to Node.js or new to MySQL this tutorial should help you learn what you need.

First, whether you working with Node.js or PHP or any other language you prefer you first need to install the MySQL development server on your machine for hosting and managing databases, MySQL is available for cross-platform so you just need to select your Operating System and download then install the MySQL Deamon (Which is basically a Server).

Download & Install MySQL Deamon 

afterward, you need to install the Driver for the specific language you prefer using, and since we are using Node.js so we need to use NPM.

PS: PHP supports MySQL out of the Box (Built-in) supported Driver for connecting and executing MySQL commands.

npm install mysql --save 

Of course, you need a setup project using Node.js if you don't you can take a look at Node.js Course Series.

Now, you need to run the MySQL Deamon server so you Node.js app can communicate with it and can create/delete and manage databases.

Make sure to be in the drive where you had installed the MySQL server so you can execute commands from there, otherwise, if this doesn't work make sure to go to the directory where the binaries are installed then run the server from there.

#let's say that it is installed in C: drive
cd c: 
mysqld --verbose 

Make sure the daemon server is running so you could then connect to the database server and execute queries.

Connecting to MySQL Server

Here is the project structure of our Node.js app that we will work on for managing a MySQL database and see different commands and queries that we can execute.

dist/
src/
--database.js
app.js
package.json

Inside of the database.js, we will have our main database queries and functions.

So let's try to connect to the database on its default port (MySQL daemon runs on a specific PORT by default).

let mysql = require("mysql");
/*you need to create a connection object that specifies the different details about your database server*/
//when installing a new MySQL server by default it has the following values (without a password) 
let connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: ""
});

// connect to the MySQL server
connection.connect(function(err) {
  //Always run mysql queries after a successfull connection, otherwise, you get errors
  if (err) {
    return console.error("ERROR: " + err.message);
  }
  //Here execute your commands and queries related to the database
});
 

You just need to know the details about your MySQL server so you could connect to it easily, by default it has the values used in the examples.

Here we are just connecting to the server but not to a database since we haven't created one yet.

Creating a Database

Databases are the main piece of the puzzle, so you first have to create one then connect to it afterward, you can send queries to your database regarding tables and columns.

So, we will try to create a database if it doesn't already exist, this way we can make sure that we don't create duplicate database instances or overwrite your data which super dangerous when dealing with databases.

connection.connect(function(err) {
  //Make sure to initialize the database (create it)
  initDB();
}

function initDB() {
  //Create Database and connect to it (only if it doesn't already exist)
  const createTodosDB = "CREATE DATABASE IF NOT EXISTS APP_DB";
  connection.query(createTodosDB);
  //Change to newly created database (we have to connect to the database)
  connection.changeUser({ database: "APP_DB" });
}

on MySQL, all queries are as simple as reading an English sentence to do a specific job, but of course with a specific syntax. We tell it to CREATE a DATABASE only if it doesn't exist with name APP_DB, very straight-forward.

Queries are simple text string commands then we have to tell the connection object which is the object that holds the instance of the connection to the database to execute the query into the currently connected database (remember we already change to that database).

And finally, we tell the connection to use our database by default (connect to the newly create the database). Of course, you need to call the initDB function after the connection has been initiated in the callback and after making sure that there is no errors so you application wouldn't have undefined errors and bugs.

Adding Tables and Rows

MySQL is a Rational database which means it is composed of tables and columns (rows) in a relational manner, each table has many rows for storing different types of data and each database has many tables to store different entity's specific data.

You can visualize this in your imagination part of your brain so you could easily work with as normal tables with a number set of columns where each column has a specific data type and holds a value with a specified size.

We will create a simple Todo application.

function setupDB() {
  //Sets up the Database Tables and Rows
  //Creating Tables ()
  const createTodosQuery = `CREATE TABLE IF NOT EXISTS todos(id int primary key auto_increment, 
    title varchar(255) not null, 
    completed tinyint(1) not null default 0)`;
  //Execute tables creation query 
  connection.query(createTodosQuery, (err, results, fields) => {
    if (err) {
      return console.error(err.message);
    }
  });
}

For the query to create tables and add rows to it is actually very simple, you specific name of the table then you give it the different parameters (rows details) to create the row, each row is separated by a commas the id row is always present in tables cause it provides a unique key to access the current todo also there is a title of type tinyint and a size of 255bytes plus a completed row that is a tinyint (boolean) with one byte in size and a default 0 value.

When executing the query the function can take a second argument that is a callback that returns and err object, results object and a fields object for getting details about the created table and its rows, mostly you need to make sure that there are no errors during the process.

Also, make sure to call setupDB in the connection callback right after the initialization of the database to make sure it works as expected.

Insert into Database

Insertion is similar to creating a table and adding rows to it cause they share the same row names and attributes.

function insertTodaysTodos(title) {
  //Simple Variable to be used as a VALUE in the SQL Query (use string leteral) to concatenate vardiables
  const insertTodaysTodos = `INSERT INTO todos(title,completed) VALUES('${title}',false)`;
  //Run query 
  const query = connection.query(insertTodaysTodos, (err, result, fields) => {
    if (err) return console.error("Error inserting: ", err.message);
  });
}

In the insertion process, you provide the field names (rows) you which to set data to (also keep in mind that rows that are not created to have a default value or not NULL they will throw an error when you will not provide them with a value). Use string literals `` to be able to concatenate a string with you variables data using the string literals specific syntax ${varName}

Fetch From Database

Obviously, when inserting data into a database you soon or later need to fetch and work with this data. For fetching data from a database is as easy as inserting to it but in this case, we use the SELECT command and we need to tell it which rows we want to fetch.

function showTodaysTodos() {
  //use SELECT * (and specify rows you want to fetch in this case we fetch all)
  //We add a WHERE condition to only get rows that match the condition 
  const fetchTodaysTodos = `SELECT * FROM todos WHERE completed=1`;
  connection.query(fetchTodaysTodos, (err, result, fields) => {
    if (err) return console.error("ERROR, fetching todays todos ", err.message);
    console.log("Result: ", result);
  });
}

For selecting particular rows you can specify them after the SELECT command or specify a wildcard for getting all rows also, you may add a WHERE condition since most of the times you only need to fetch a certain set of rows that match with a condition (for ex: uncompleted todos).

Update Database Records

Updating is as important as any other necessary operation, MySQL allows you to update certain rows that match a certain condition either a single row or multiple ones.

//Update the uncompleted todos and make them completed 
function MarkAsCompleted(id) {
  //use the UPDATE command followed by the table name and SET that specifies which row(s) you need 
  //to update with their values
  const updateTodos = `UPDATE todos SET completed=1 WHERE id=${id}`;
  connection.query(updateTodos, (err, result, fields) => {
    if (err) return console.error("ERROR, updating todays todos ", err.message);
    console.log("Completed tasks: ", fields);
  });
}

the SET command is used to tell MySQL query system which rows of the table you need to update with their updated values and a condition to match the records that will be updated. Then simply execute the query and listen for callback.

Delete Database Records

The DELETE command is also available for deleting one or multiple rows from the database depending on a condition, here let's try to delete all the completed todos (which had been done).

function deleteCompleted(id) {
  //specify the table name and the condition to match in order to delete the record from the 
  //database
  const deleteTodos = `DELETE FROM todos WHERE id=${id}`;
  connection.query(deleteTodos, (err, result, fields) => {
    if (err) return console.error("ERROR, updating todays todos ", err.message);
    console.log("Deleted: ", result);
  });
}

When the specified condition is matched then all the records with the match will be completely removed from the database, so this is a very sensitive operation, therefore, you have to make sure that everything is secure in your application and no external suspicious code is being executed on your query.

 

Order records

Ordering and Sorting data when fetching it from the database is something really important when it comes to fast and a reliable solution, well. mySQL gives you that out of the box using it's order constraint.

Let's say we want to order and fetch the todos stored in the database by alphabet name of the title in Descending order.

function orderTodos() {
  //we fetch all todos and order it by title by default it is going to be DESCENDING or you can specify that by adding ASC|DESC
  const orderTodos = `SELECT * FROM todos ORDER BY title DESC`;
  connection.query(orderTodos, (err, result, fields) => {
    if (err) return console.error("ERROR, Todos Ordered: ", err.message);
    console.log("Deleted: ", result);
  });
}

You can specify any row to order the records according to and which type of order you would like to put records on (Ascending or Descending).

 

What's Next

Well, this just was a very quick and basic introduction to using MySQL with Node.js there are a lot of other features you definitely need to check out.

 

Share Tutorial

Made With By

Ipenywis Founder, Game/Web Developer, Love Play Games