Using SQL Databases with Node.js

Welcome to Day 12 of our Node.js blog series! 🤔

Today, we'll explore the integration of SQL databases with Node.js. While NoSQL databases like MongoDB offer flexibility, SQL databases provide strong consistency, ACID (Atomicity, Consistency, Isolation, Durability) compliance, and structured query capabilities. We will introduce SQL databases, demonstrate how to connect to MySQL or PostgreSQL, and use an ORM (Object-Relational Mapping) library like Sequelize or TypeORM to perform basic CRUD operations.

Introduction to SQL Databases

SQL (Structured Query Language) databases are relational databases that store data in tables, which consist of rows and columns. Each table has a unique schema, and relationships between tables are established using foreign keys. SQL databases are known for their robustness, support for complex queries, and strong transactional integrity.

Popular SQL databases include:

1. MySQL: Open-source, widely used, and suitable for a variety of applications.

2. PostgreSQL: Known for its advanced features, extensibility, and compliance with SQL standards.

Connecting to MySQL or PostgreSQL

To connect Node.js to an SQL database, you need the respective database client library.

Setting Up MySQL

1. Install MySQL:

   • Download and install MySQL from the official website.

2. Install the MySQL Node.js Client:

   • Use npm to install the MySQL client library.

    npm install mysql2

Setting Up PostgreSQL

1. Install PostgreSQL:

   • Download and install PostgreSQL from the official website.

2. Install the PostgreSQL Node.js Client:

   • Use npm to install the PostgreSQL client library.

    npm install pg

Connecting to the Database

Here’s how to connect to MySQL or PostgreSQL using the respective libraries:

MySQL:

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myDatabase'
});

connection.connect(err => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }
  console.log('Connected to MySQL as id', connection.threadId);
});

// Close the connection
connection.end();

PostgreSQL:

const { Client } = require('pg');

const client = new Client({
  host: 'localhost',
  user: 'postgres',
  password: 'password',
  database: 'myDatabase',
  port: 5432
});

client.connect()
  .then(() => console.log('Connected to PostgreSQL'))
  .catch(err => console.error('Connection error', err.stack))
  .finally(() => client.end());

• Explanation:

  • Both examples demonstrate creating a connection to the database using connection details such as host, user, password, and database name.

  • connect() establishes the connection, and end() closes it after operations are completed.

Using an ORM (Sequelize or TypeORM)

ORMs (Object-Relational Mapping) provide a higher-level abstraction over raw SQL, allowing developers to interact with the database using JavaScript objects and methods. This simplifies database operations and ensures consistency in accessing and manipulating data.

Sequelize

Sequelize is a popular ORM for Node.js that supports multiple SQL databases, including MySQL and PostgreSQL.

1. Installation:

    npm install sequelize mysql2
    # Or for PostgreSQL
    npm install sequelize pg pg-hstore
   

2. Setup and Configuration:

    const { Sequelize, DataTypes } = require('sequelize');
   
    const sequelize = new Sequelize('myDatabase', 'root', 'password', {
      host: 'localhost',
      dialect: 'mysql', // Or 'postgres'
    });
   
    // Test the connection
    sequelize.authenticate()
      .then(() => console.log('Connection established successfully.'))
      .catch(err => console.error('Unable to connect to the database:', err));
   

3. Defining Models:

   Models in Sequelize represent tables in the database.

    const User = sequelize.define('User', {
      name: {
        type: DataTypes.STRING,
        allowNull: false
      },
      age: {
        type: DataTypes.INTEGER,
        allowNull: false
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
      }
    });
   
    // Sync the model with the database
    sequelize.sync()
      .then(() => console.log('User model synced with the database.'))
      .catch(err => console.error('Error syncing model:', err));
   

4. CRUD Operations with Sequelize:

    // Create
    User.create({ name: 'Alice', age: 25, email: 'alice@example.com' })
      .then(user => console.log('User created:', user))
      .catch(err => console.error('Error creating user:', err));
   
    // Read
    User.findAll().then(users => console.log('All users:', users));
   
    // Update
    User.update({ age: 26 }, { where: { email: 'alice@example.com' } })
      .then(result => console.log('Number of affected rows:', result[0]))
      .catch(err => console.error('Error updating user:', err));
   
    // Delete
    User.destroy({ where: { email: 'alice@example.com' } })
      .then(() => console.log('User deleted'))
      .catch(err => console.error('Error deleting user:', err));
   

   • Explanation:

     • User.create() inserts a new record.

     • User.findAll() retrieves all records.

     • User.update() updates records based on a condition.

     • User.destroy() deletes records based on a condition.

TypeORM

TypeORM is another popular ORM for Node.js, supporting TypeScript and JavaScript and compatible with SQL databases like MySQL and PostgreSQL.

1. Installation:

    npm install typeorm reflect-metadata
    # Add database driver
    npm install mysql2
    # Or for PostgreSQL
    npm install pg
   

2. Setup and Configuration:

    require('reflect-metadata');
    const { createConnection, Entity, PrimaryGeneratedColumn, Column } = require('typeorm');
   
    @Entity()
    class User {
      @PrimaryGeneratedColumn()
      id;
   
      @Column()
      name;
   
      @Column()
      age;
   
      @Column()
      email;
    }
   
    createConnection({
      type: 'mysql', // Or 'postgres'
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'password',
      database: 'myDatabase',
      entities: [User],
      synchronize: true,
    }).then(connection => {
      console.log('Connected to the database with TypeORM');
   
      // Create a new user
      const user = new User();
      user.name = 'Bob';
      user.age = 30;
      user.email = 'bob@example.com';
      connection.manager.save(user).then(savedUser => {
        console.log('User saved:', savedUser);
      });
   
      // Find all users
      connection.manager.find(User).then(users => {
        console.log('All users:', users);
      });
   
      // Update a user
      connection.manager.findOne(User, { email: 'bob@example.com' }).then(user => {
        user.age = 31;
        connection.manager.save(user).then(updatedUser => {
          console.log('User updated:', updatedUser);
        });
      });
   
      // Delete a user
      connection.manager.findOne(User, { email: 'bob@example.com' }).then(user => {
        connection.manager.remove(user).then(() => {
          console.log('User deleted');
        });
      });
    }).catch(error => console.error('Error connecting to the database:', error));
   

   • Explanation:

     • createConnection() establishes a connection to the database.

     • Entities (User class) represent tables, and columns are defined using decorators.

     • The synchronize option automatically creates the database schema based on the models.

Conclusion

In this post, we've covered connecting to SQL databases in Node.js, focusing on MySQL and PostgreSQL. We discussed setting up connections, using Sequelize and TypeORM as ORMs to simplify interactions with the database, and performing basic CRUD operations. SQL databases are essential for applications requiring strong data consistency and complex querying capabilities.

In our next post, we'll continue to explore more advanced topics in Node.js like Authentication and Authorization . Stay tuned for more!