Node.js - MySQL Create Database

Hello, aspiring programmers! Today, we're going to embark on an exciting journey into the world of Node.js and MySQL. As your friendly neighborhood computer science teacher, I'm here to guide you through the process of creating a database using Node.js and MySQL. Don't worry if you're new to programming – we'll take it step by step, and before you know it, you'll be creating databases like a pro!

What is Node.js and MySQL?

Before we dive into the nitty-gritty, let's briefly touch on what Node.js and MySQL are:

1. Node.js: This is a powerful JavaScript runtime that allows you to run JavaScript on your computer, not just in a web browser.
2. MySQL: This is a popular database management system that helps you store and organize data.

Together, they form a dynamic duo that can help you build powerful web applications!

Setting Up Your Environment

First things first, we need to make sure you have Node.js and the MySQL module installed. If you haven't already, go ahead and install Node.js from the official website. Once that's done, open your terminal or command prompt and run:

npm install mysql

This command installs the MySQL module for Node.js, which we'll use to connect to our MySQL database.

Connecting to MySQL

Now that we're all set up, let's create a connection to MySQL. Here's a simple example:

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password'
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL server!');
});

Let's break this down:

1. We require the MySQL module.
2. We create a connection using mysql.createConnection(), providing our MySQL server details.
3. We use the connect() method to establish the connection.
4. If there's an error, we throw it. Otherwise, we log a success message.

Creating a Database

Now, let's get to the main event – creating a database! Here's the basic syntax:

connection.query('CREATE DATABASE mydb', (err, result) => {
  if (err) throw err;
  console.log('Database created');
});

This snippet uses the query() method to execute a SQL command that creates a new database named 'mydb'.

Example: Creating a School Database

Let's create a more practical example. Imagine we're building a system for a school and need a database to store information about students and courses.

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password'
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL server!');

  connection.query('CREATE DATABASE school_db', (err, result) => {
    if (err) throw err;
    console.log('School database created!');

    // Use the new database
    connection.query('USE school_db', (err, result) => {
      if (err) throw err;
      console.log('Using school_db');

      // Create tables
      const createStudentsTable = `
        CREATE TABLE students (
          id INT AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(100),
          age INT,
          grade VARCHAR(2)
        )
      `;

      connection.query(createStudentsTable, (err, result) => {
        if (err) throw err;
        console.log('Students table created!');
      });

      const createCoursesTable = `
        CREATE TABLE courses (
          id INT AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(100),
          instructor VARCHAR(100)
        )
      `;

      connection.query(createCoursesTable, (err, result) => {
        if (err) throw err;
        console.log('Courses table created!');
      });
    });
  });
});

This example does the following:

1. Connects to MySQL
2. Creates a new database called 'school_db'
3. Switches to use the new database
4. Creates two tables: 'students' and 'courses'

Each step uses the query() method to execute SQL commands.

The con.query() Method

The query() method is the workhorse of our Node.js MySQL operations. It allows us to execute SQL queries and commands. Here's a breakdown of its syntax:

connection.query(sqlCommand, [values], callback)

• sqlCommand: A string containing the SQL command you want to execute.
• values (optional): An array of values to be inserted into the SQL command (useful for preventing SQL injection).
• callback: A function that's called when the query is complete. It takes three parameters:
  • err: Any error that occurred during the query.
  • result: The result of the query.
  • fields: Information about the returned fields.

Here's a table summarizing some common MySQL commands you can use with query():

Command	Description	Example
CREATE DATABASE	Creates a new database	CREATE DATABASE mydb
USE	Selects a database to use	USE mydb
CREATE TABLE	Creates a new table	CREATE TABLE users (id INT, name VARCHAR(100))
INSERT INTO	Inserts new data into a table	INSERT INTO users (id, name) VALUES (1, 'John')
SELECT	Retrieves data from a table	SELECT * FROM users
UPDATE	Modifies existing data in a table	UPDATE users SET name = 'Jane' WHERE id = 1
DELETE	Removes data from a table	DELETE FROM users WHERE id = 1
DROP TABLE	Deletes a table	DROP TABLE users
DROP DATABASE	Deletes a database	DROP DATABASE mydb

Conclusion

And there you have it, folks! We've journeyed through the basics of creating a database with Node.js and MySQL. Remember, practice makes perfect, so don't be afraid to experiment with different commands and create your own databases.

In my years of teaching, I've found that the best way to learn is by doing. So, here's a little homework for you: try creating a database for your favorite hobby. Whether it's a collection of recipes, a list of movies you've watched, or an inventory of your pet rock collection, the sky's the limit!

Remember, every master programmer started as a beginner. Keep coding, keep learning, and most importantly, have fun! Until next time, happy database creating!

Credits: Image by storyset