MySQL (SQL Database)

MySQL is one way to store backend data in a SQL (Structured Query Language) database on node.js app.

Characteristics of SQL Database

• Data tables that have relations ( => relational database): a) one-to-one relation b) one-to-many relation c) many-to-many relation
• Structure has to fit (uses schemas), each row has all fields (columns) in table
• Horizontal scaling (buying more servers) can be difficult
• Limitations for read/write queries per second
• Used via SQL queries, e.g.

SELECT * FROM USERS WHERE age > 30

• MySQL is one of the most popular SQL databases

🐬 using MySQL

Option 1: write queries as Strings with MySQL

utils/database.js

const mysql = require("mysql2");

const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  // these you will set in MySQL workbench
  database: "node-complete",
  password: "test"
});

module.exports = pool.promise();

Avoid SQL injection by using '?' in models/product.js

const mySqlDb = require("../util/database");

 mySqlDb.execute(
      "INSERT INTO products (title, price, imageURL, description) VALUES (?,?,?,?)"
    ),
      [this.title, this.price, this.imageUrl, this.description];

Option 2: Use Sequelize (3rd party package)

Sequelize is an object-relational mapping library that simplifies working with SQL queries in JavaScript generating the queries on your behalf. Instead of strings queries, it uses objects to define what to do.

const Sequelize = require('sequelize');

const sequelize = require('../util/database');

const Product = sequelize.define('product', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true
  },
  title: Sequelize.STRING,
  price: {
    type: Sequelize.DOUBLE,
    allowNull: false
  },
  imageUrl: {
    type: Sequelize.STRING,
    allowNull: false
  },
  description: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

You will access data with specified Product.findAll() and Product.findByPk() methods

You can define relations (associations) with Sequelize like this:

sequelize.sync();
product.belongsTo(User, { constraints: true, onDelete: 'CASCADE' });
User.hasMany(Product);
User.hasOne(Cart);
Cart.belongsTo(User);
Cart.belongsToMany(Product, { through: CartItem });
Product.belongsToMany(Cart, { through: CartItem });
Order.belongsTo(User);
User.hasMany(Order);
Order.belongsToMany(Product, { through: OrderItem });

User middleware defined in app.js gives access to User model anywhere in the app via req.user (e.g. req.user.getCart().then(...).catch(...))