How to Use a Local MySQL Database with a Nuxt.js App ​

Hi Nuxters👋🏾!

In this blog post I will show you how to store data from a Nuxt.js application into a local MySQL database.

The term "local" refers to a database hosted on the same machine as the Nuxt.js application.

Prerequisites ​

To test the MySQL database operations in this article, download the MySQL Database Software and the Workbench app at the links below:

• MySQL Database Software(for Mac, I'm on Mac): Open Source client/server database management system
• MySQL Workbench: to visualize your tables

To connect and interact with a local MySQL database from a Nuxt.js app, you need the following two Node.js libraries:

• MySQL2: to create a connection to the database
• drizzle-orm: to create schema, generate and apply migrations, and provide a better development experience when interacting with the database.

Install these libraries in your project by running the following command from the root directory of your project:

npm install drizzle-orm mysql2

npm install drizzle-orm mysql2

1. Create Your Database Schema ​

The journey to storing your app data in a MySQL (or any SQL) database starts with a schema. A schema is a blueprint that defines the database structure, including the tables, fields, relationships between tables, indexes, and more.

Create the Schema ​

To create a schema for your database with Drizzle, create the schema file that defines your tables and their fields. For example, to define a users table, your schema would look like below:

// schema.ts
import { index, int, mysqlTable, varchar } from "drizzle-orm/mysql-core";
export const users = mysqlTable("users", {
  id: int().autoincrement().primaryKey(),
  username: varchar({ length: 88 }).notNull(),
  password: varchar({ length: 88 }).notNull(),
  email: varchar({ length: 88 }).notNull(),
});

// schema.ts
import { index, int, mysqlTable, varchar } from "drizzle-orm/mysql-core";
export const users = mysqlTable("users", {
  id: int().autoincrement().primaryKey(),
  username: varchar({ length: 88 }).notNull(),
  password: varchar({ length: 88 }).notNull(),
  email: varchar({ length: 88 }).notNull(),
});

2. Configure Drizzle ​

Add a file named drizzle.config.ts to the root folder of your project. In this file, specify the SQL database dialect (e.g., sqlite, mysql, etc.), the paths to the schema file, and where the migrations will be generated.

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "mysql",
  schema: "./server/utils/database/sql/schema.ts",
  out: "./server/utils/database/sql/migrations",
});

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "mysql",
  schema: "./server/utils/database/sql/schema.ts",
  out: "./server/utils/database/sql/migrations",
});

3. Generate Migrations from the Schema ​

Generating a migration from a schema means creating SQL code to create the tables or update the database according to the schema.

You can generate the schema migration by running the following command from the project root:

drizzle-kit generate

drizzle-kit generate

4. Run the Migrations to Create Tables in Your Database ​

brew services start mysql

brew services start mysql

To apply the generated migrations and create the tables in your database, run the following command:

drizzle-kit migrate

drizzle-kit migrate

5. Create a Connection Between Your App and the Database ​

Before your app can conduct operations on your MySQL database, you need to create a connection to the database from your app.

To create a simple connection to the database, you can use the database connector and the createDatabase functions from the UnJs db0 library, available by default in Nuxt.

import mysql from "db0/connectors/mysql2";
import { createDatabase } from "db0";

const db = createDatabase(
  mysql({
    ...JSON.parse(process.env.DATABASE_OPTS),
  })
);

import mysql from "db0/connectors/mysql2";
import { createDatabase } from "db0";

const db = createDatabase(
  mysql({
    ...JSON.parse(process.env.DATABASE_OPTS),
  })
);

where DATABASE_OPTS is your database connection values in your .env with the following structure:

DATABASE_OPTS={ "database": "my_database", "user": "root", "password": "pass", "socketPath": "/tmp/mysql.sock" }

DATABASE_OPTS={ "database": "my_database", "user": "root", "password": "pass", "socketPath": "/tmp/mysql.sock" }

MySQL Interactions Without an ORM ​

With the above MySQL connection established, stored in the db variable, you can start querying your database with no ORM, for example by inserting values, as follows:

async function createUser(user: IUser): Promise<number> {
  let userId: number = 0;
  try {
    const sql =
      "INSERT INTO `users` (`username`, `password`, `email`) VALUES (?, ?, ?)";

    const stmt = this.db.prepare(sql); // Prepared statement

    const result = (await stmt.all(
      ...Object.values(user)
    )) as unknown as ResultSetHeader;

    userId = result.insertId;
  } catch (error) {
    console.error("Error creating user", error);
  }
  return userId;
}

async function createUser(user: IUser): Promise<number> {
  let userId: number = 0;
  try {
    const sql =
      "INSERT INTO `users` (`username`, `password`, `email`) VALUES (?, ?, ?)";

    const stmt = this.db.prepare(sql); // Prepared statement

    const result = (await stmt.all(
      ...Object.values(user)
    )) as unknown as ResultSetHeader;

    userId = result.insertId;
  } catch (error) {
    console.error("Error creating user", error);
  }
  return userId;
}

Interact with MySQL Database Using Drizzle ORM ​

For a better dev experience when interacting with a database, you can use an ORM, such as Drizzle. To use something that fits well in the Nuxt.js pipeline, you could use drizzle through the db0 library.

But at the time of writing, the latest version(0.3.1) of the db0 library only supports Drizzle for SQLite.

To interact with your MySQL database using Drizzle, you need to bypass the db0 library and using drizzle-orm and MySQL2 libraries directly. The following are the steps to do so:

1. Create a Database Connection with the mysql2 Library ​

mysql2, that you imported in Prerequisites, allows you to create a connection using either a callback- or promise-based API. Using the promise-based API, you create the connection as follows:

import mysql from "mysql2/promise";

export async function getConnection() {
  if (!process.env.DATABASE_OPTS) {
    throw new Error("DATABASE_OPTS is not set");
  }
  try {
    return await mysql.createConnection(JSON.parse(process.env.DATABASE_OPTS));
  } catch (error) {
    console.error("Error getting connection", error);
    throw error;
  }
}

import mysql from "mysql2/promise";

export async function getConnection() {
  if (!process.env.DATABASE_OPTS) {
    throw new Error("DATABASE_OPTS is not set");
  }
  try {
    return await mysql.createConnection(JSON.parse(process.env.DATABASE_OPTS));
  } catch (error) {
    console.error("Error getting connection", error);
    throw error;
  }
}

2. Create a Drizzle Instance for a Connection ​

To create an ORM for the MySQL database connection above, with drizzle, use the drizzle function from drizzle-orm/mysql2 and the getConnection function you created in the preceding step as follows:

async initialize() {
    const connection = await getConnection();
    this.drizzleORM = drizzle(connection);
  }

async initialize() {
    const connection = await getConnection();
    this.drizzleORM = drizzle(connection);
  }

For the complete code, see UserLocalSQLDBService.ts.

With the drizzle instance created, you can now interact with the database as follows:

const result = this.drizzleORM.insert(tables.users).values({ ...user });

const result = this.drizzleORM.insert(tables.users).values({ ...user });

For the complete code, see UserLocalSQLDBService.ts.

Secure Your Queries with Prepared Statements ​

To create queries with prepared statements, you can call the prepare() method on the MySqlInsertBase object.

const preparedStatement = this.drizzleORM
  .insert(tables.users)
  .values({ ...user })
  .prepare();
const [result, fields] = await preparedStatement.execute();
console.log("Created user with id: ", result.insertId);

const preparedStatement = this.drizzleORM
  .insert(tables.users)
  .values({ ...user })
  .prepare();
const [result, fields] = await preparedStatement.execute();
console.log("Created user with id: ", result.insertId);

Optimize Your SQL Queries ​

Optimizing SQL queries is crucial for improving the performance and efficiency of your database operations.

By optimizing your queries, you can reduce the time it takes to retrieve data, minimize the load on your database, and ensure a smoother user experience.

Below are some techniques to help you optimize your SQL queries.

Indexes ​

One of the ways to optimize frequently queried columns is to use indexes.

An index is a dictionary-like data structure created from table columns for more efficient data retrieval.

For example, to add indexes to the users table schema:

export const users = mysqlTable(
  "users",
  {
    id: int().autoincrement().primaryKey(),
    username: varchar({ length: 88 }).notNull(),
    password: varchar({ length: 88 }).notNull(),
    email: varchar({ length: 88 }).notNull(),
    // createdAt: datetime({ mode: 'string' }).notNull(),
  },
  (table) => [
    index("username_idx").on(table.username),
    index("email_idx").on(table.email),
  ]
);

export const users = mysqlTable(
  "users",
  {
    id: int().autoincrement().primaryKey(),
    username: varchar({ length: 88 }).notNull(),
    password: varchar({ length: 88 }).notNull(),
    email: varchar({ length: 88 }).notNull(),
    // createdAt: datetime({ mode: 'string' }).notNull(),
  },
  (table) => [
    index("username_idx").on(table.username),
    index("email_idx").on(table.email),
  ]
);

In this example, indexes are created on the username and email columns in the users, which are frequently queried.

Using the WHERE Clause Before JOINs ​

The WHERE clause helps reduce the amount of data to operate on before performing JOIN operations, which can significantly improve query performance.

More Ways to Optimize SQL Queries ​

Using indexes and the WHERE clause are just a few ways to optimize your database-related tasks.

For more SQL query optimization techniques, see Top 10 SQL Query Optimization Techniques on syncfusion.com.