Database en US - rocambille/start-express-react GitHub Wiki

StartER integrates a MySQL database for persistent data storage. This page explains how to configure, use, and extend the database in your application.

StartER uses environment variables for database configuration. You can define these variables in the .env file at the root of your project.

# Database Configuration
MYSQL_ROOT_PASSWORD=YOUR_MYSQL_ROOT_PASSWORD
MYSQL_DATABASE=YOUR_MYSQL_DATABASE_NAME

These variables are used by Docker to configure the MySQL container and by the application to establish the connection.

The src/database/client.ts file configures the database connection:

// Get variables from .env file for database connection
const { MYSQL_ROOT_PASSWORD, MYSQL_DATABASE } = process.env;

// Create a connection pool to the database
import mysql from "mysql2/promise";

/* ************************************************************************ */

const client = mysql.createPool(
  `mysql://root:${MYSQL_ROOT_PASSWORD}@database:3306/${MYSQL_DATABASE}`,
);

/* ************************************************************************ */

// Ready to export
export default client;

// Types export
import type { Pool, ResultSetHeader, RowDataPacket } from "mysql2/promise";

type DatabaseClient = Pool;
type Result = ResultSetHeader;
type Rows = RowDataPacket[];

export type { DatabaseClient, Result, Rows };

The client uses mysql2 with promise support for seamless integration with JavaScript asynchronous functions.

StarTER checks the database connection via the src/database/checkConnection.ts file at startup:

import client from "./client";

// Try to get a connection to the database
client
  .getConnection()
  .then((connection) => {
    console.info(`Using database ${process.env.MYSQL_DATABASE}`);
    connection.release();
  })
  .catch((error: Error) => {
    console.warn(
      "Warning:",
      "Failed to establish a database connection.",
      "Please check your database credentials in the .env file if you need a database access.",
    );
    console.warn(error.message);
  });

Imported into server.ts, this check allows you to quickly detect configuration problems.

import fs from "node:fs";
import express, { type ErrorRequestHandler, type Express } from "express";
import { rateLimit } from "express-rate-limit";
import { createServer as createViteServer } from "vite";

/* ************************************************************************ */

import "./src/database/checkConnection";

/* ************************************************************************ */

const port = 5173;

createServer().then((server) => {
  server.listen(port, () => {
    console.info(`Listening on http://localhost:${port}`);
  });
});

// ...

The database schema is defined in the src/database/schema.sql file.

In the proposed codebase, the schema declares user and item tables with first rows inserted:

create table user (
  id int unsigned primary key auto_increment not null,
  email varchar(255) not null unique,
  password varchar(255) not null,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp on update current_timestamp,
  deleted_at datetime default null
);

create table item (
  id int unsigned primary key auto_increment not null,
  title varchar(255) not null,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp on update current_timestamp,
  deleted_at datetime default null,
  user_id int unsigned not null,
  foreign key(user_id) references user(id) on delete cascade
);

insert into user(id, email, password)
values
  (1, "[email protected]", "$argon2id$v=19$m=19456,t=2,p=1$M6cNKyAnMbdydp1xs6voqA$BNdO1lV91bQBqzOpvkROZJKbSHqEW5PzFAp5C/bgvwY");

insert into item(id, title, user_id)
values
  (1, "Stuff", 1),
  (2, "Doodads", 1);

This file is automatically executed when the MySQL container is first started. After the first startup, you can reload the schema with the following command:

docker compose run --build --rm server npm run database:sync

Warning: The database:sync script deletes the existing database and creates a new one that is up-to-date with the contents of the src/database/schema.sql file.

Another solution is to import the schema from the Adminer interface provided as a service in StartER.

StartER includes Adminer, a lightweight web interface for managing your database. To access it:

1. Make sure your application is running (docker compose up)
2. Open your browser at http://localhost:8080
3. Log in with the following credentials:
   • System: MySQL
   • Server: database
   • User: root
   • Password: (value of MYSQL_ROOT_PASSWORD in your .env file)
   • Database: (value of MYSQL_DATABASE in your .env file)

To import your database schema, click "Import" (or try this link) and import the src/database/schema.sql file.

We recommend including the following fields in each table (when common sense dictates it's relevant):

• id: Unique, auto-incrementing identifier
• created_at: Creation date and time
• updated_at: Last updated date and time
• deleted_at: Deleted date and time (for soft delete)

Use foreign keys with referential integrity constraints to maintain data consistency:

foreign key(user_id) references user(id) on delete cascade

To access the database from your application, we recommend using the Repository pattern as demonstrated in src/express/modules/item/itemRepository.ts. This pattern encapsulates the data access logic and provides a clear interface for CRUD operations.

import databaseClient, {
  type Result,
  type Rows,
} from "../../../database/client";

class ItemRepository {
  // The C of CRUD - Create operation
  async create(item: Omit<Item, "id">) {
    const [result] = await databaseClient.query<Result>(
      "insert into item (title, user_id) values (?, ?)",
      [item.title, item.user_id],
    );

    return result.insertId;
  }

  // The Rs of CRUD - Read operations
  async read(id: number) {
    const [rows] = await databaseClient.query<Rows>(
      "select * from item where id = ? and deleted_at is null",
      [id],
    );

    return rows[0] as Item | null;
  }

  // ...
}

export default new ItemRepository();

A TypeScript word: The databaseClient.query method is generic for all SQL queries. To allow TypeScript to infer the return type, you must specify whether your query produces rows (select query: use databaseClient.query<Rows>) or an operation result (insert, update, or delete query: use databaseClient.query<Result>).

See the full code for details:

• itemRepository.ts