MariaDB CRUD Tutorials in JavaScript/Node.js: A Step-by-Step Guide

MariaDB is a powerful open-source relational database management system that can be seamlessly integrated with JavaScript and Node.js applications. In this tutorial, we will explore how to perform CRUD (Create, Read, Update, Delete) operations with MariaDB using JavaScript and Node.js. By the end of this article, you’ll have a solid understanding of how to interact with a MariaDB database from your JavaScript applications.

Prerequisites

1. Node.js installed on your machine.
2. A MariaDB server set up and running.
3. Basic knowledge of JavaScript and Node.js.

Step 1: Install Required Packages

Before we start working with MariaDB, let’s make sure we have the necessary packages installed. We’ll use the mariadb package for connecting to the database and executing queries.

npm install mariadb

Step 2: Set Up the Database Connection

In your Node.js application, you need to establish a connection to the MariaDB database. Create a JavaScript file (e.g., db.js) and add the following code:

const mariadb = require("mariadb")

const pool = mariadb.createPool({
  host: "your-database-host",
  user: "your-username",
  password: "your-password",
  database: "your-database-name"
})

module.exports = pool

Replace 'your-database-host', 'your-username', 'your-password', and 'your-database-name' with your MariaDB server details.

Step 3: Create a New Record (Create - C)

Now, let’s create a function to insert data into a MariaDB table. Create a JavaScript file (e.g., create.js) and add the following code:

const pool = require("./db")

async function createRecord(name, email) {
  const conn = await pool.getConnection()
  try {
    const result = await conn.query(
      "INSERT INTO users (name, email) VALUES (?, ?)",
      [name, email]
    )
    console.log(`New record added with ID: ${result.insertId}`)
  } catch (err) {
    console.error("Error creating record:", err)
  } finally {
    conn.release()
  }
}

createRecord("John Doe", "[email protected]")

This code defines a function createRecord that inserts a new user into a ‘users’ table.

Step 4: Read Data (Read - R)

To read data from the database, create another JavaScript file (e.g., read.js) with the following code:

const pool = require("./db")

async function readRecords() {
  const conn = await pool.getConnection()
  try {
    const rows = await conn.query("SELECT * FROM users")
    rows.forEach((row) => {
      console.log(`ID: ${row.id}, Name: ${row.name}, Email: ${row.email}`)
    })
  } catch (err) {
    console.error("Error reading records:", err)
  } finally {
    conn.release()
  }
}

readRecords()

This code defines a function readRecords that retrieves all user records from the ‘users’ table and prints them to the console.

Step 5: Update Data (Update - U)

To update existing records, create a JavaScript file (e.g., update.js) with the following code:

const pool = require("./db")

async function updateRecord(id, newName) {
  const conn = await pool.getConnection()
  try {
    const result = await conn.query("UPDATE users SET name = ? WHERE id = ?", [
      newName,
      id
    ])
    console.log(`Record updated: ${result.affectedRows} rows affected`)
  } catch (err) {
    console.error("Error updating record:", err)
  } finally {
    conn.release()
  }
}

updateRecord(1, "Jane Doe")

This code defines a function updateRecord that updates a user’s name based on their ID.

Step 6: Delete Data (Delete - D)

To delete records, create a JavaScript file (e.g., delete.js) with the following code:

const pool = require("./db")

async function deleteRecord(id) {
  const conn = await pool.getConnection()
  try {
    const result = await conn.query("DELETE FROM users WHERE id = ?", [id])
    console.log(`Record deleted: ${result.affectedRows} rows affected`)
  } catch (err) {
    console.error("Error deleting record:", err)
  } finally {
    conn.release()
  }
}

deleteRecord(1)

This code defines a function deleteRecord that deletes a user record based on their ID.

Conclusion

In this article, we’ve covered the fundamental CRUD operations using MariaDB with JavaScript and Node.js. You’ve learned how to establish a database connection, create, read, update, and delete records. This knowledge will empower you to build more complex applications that interact with databases seamlessly. Remember to handle errors gracefully and adapt these examples to suit your specific project requirements.