05 - JS Monorepo : Mettre en place une base de données

❯ mysql -u NOMDETONUTILISATEUR -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.1.0 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE wildseries;
mysql> USE wildseries;
Database Changed

❯ mysql -u NOMDETONUTILISATEUR -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.1.0 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE wildseries;
Query OK, 1 row affected (0.00 sec)
mysql> USE wildseries;
Database Changed

❯ mysql -u NOMDETONUTILISATEUR -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.1.0 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE wildseries;
Query OK, 1 row affected (0.00 sec)
mysql> USE wildseries;
Database Changed

mysql> CREATE TABLE program(
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL
);

mysql> SELECT * FROM program;
Empty set (0,00 sec)

mkdir mysql-sandbox
cd mysql-sandbox
npm init -y

npm install mysql2

import mysql from "mysql2/promise";

const createDB = async () => {
  try {
    // Create a specific connection to the database
    const database = await mysql.createConnection({
      host: "localhost",
      port: "3306",
      user: "NOMDETONUTILISATEUR", // Change this
      password: "MOTDEPASSEDETONUTILISATEUR", // Change this
    });

    // Create a new database with the specified name
    await database.query("CREATE DATABASE wildseries");

    // Switch to the newly created database
    await database.query("USE wildseries");

    // Execute the SQL statements to update the database schema
    await database.query(`CREATE TABLE program(
      id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
      title VARCHAR(100) NOT NULL
    )`);

    // Close the database connection
    database.end();

    console.info("wildseries updated 🆙");
  } catch (err) {
    console.error("Error updating the database:", err.message, err.stack);
  }
};

// Run the createDB function
createDB();

node index.js

import mysql from "mysql2/promise";

const createDB = async () => {
  try {
    // Create a specific connection to the database
    const database = await mysql.createConnection({
      host: "localhost",
      port: "3306",
      user: "NOMDETONUTILISATEUR", // Change this
      password: "MOTDEPASSEDETONUTILISATEUR", // Change this
    });

    // Drop the existing database if it exists
    await database.query("DROP DATABASE IF EXISTS wildseries");

    // Create a new database with the specified name
    await database.query("CREATE DATABASE wildseries");

    // Switch to the newly created database
    await database.query("USE wildseries");

    // Execute the SQL statements to update the database schema
    await database.query(`CREATE TABLE program(
      id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
      title VARCHAR(100) NOT NULL
    )`);

    // Close the database connection
    database.end();

    console.info("wildseries updated 🆙");
  } catch (err) {
    console.error("Error updating the database:", err.message, err.stack);
  }
};

// Run the createDB function
createDB();

node index.js

npm install dotenv

DB_USER=NOMDETONUTILISATEUR
DB_PASSWORD=MOTDEPASSEDETONUTILISATEUR

import "dotenv/config";

import mysql from "mysql2/promise";

const createDB = async () => {
  try {
    // Create a specific connection to the database
    const database = await mysql.createConnection({
      host: "localhost",
      port: "3306",
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
    });

    // Drop the existing database if it exists
    await database.query("DROP DATABASE IF EXISTS wildseries");

    // Create a new database with the specified name
    await database.query("CREATE DATABASE wildseries");

    // Switch to the newly created database
    await database.query("USE wildseries");

    // Execute the SQL statements to update the database schema
    await database.query(`CREATE TABLE program(
      id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
      title VARCHAR(100) NOT NULL
    )`);

    // Close the database connection
    database.end();

    console.info("wildseries updated 🆙");
  } catch (err) {
    console.error("Error updating the database:", err.message, err.stack);
  }
};

// Run the createDB function
createDB();

// Load environment variables from .env file
import "dotenv/config";

import fs from "node:fs";
import path from "node:path";

// Build the path to the schema SQL file
const schema = path.join(__dirname, "../../server/database/schema.sql");

// Get database connection details from .env file
const { DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME } = process.env;

// Update the database schema
import mysql from "mysql2/promise";

const migrate = async () => {
  try {
    // Read the SQL statements from the schema file
    const sql = fs.readFileSync(schema, "utf8");

    // Create a specific connection to the database
    const database = await mysql.createConnection({
      host: DB_HOST,
      port: DB_PORT as number | undefined,
      user: DB_USER,
      password: DB_PASSWORD,
      multipleStatements: true, // Allow multiple SQL statements
    });

    // Drop the existing database if it exists
    await database.query(`drop database if exists ${DB_NAME}`);

    // Create a new database with the specified name
    await database.query(`create database ${DB_NAME}`);

    // Switch to the newly created database
    await database.query(`use ${DB_NAME}`);

    // Execute the SQL statements to update the database schema
    await database.query(sql);

    // Close the database connection
    database.end();

    console.info(`${DB_NAME} updated from '${path.normalize(schema)}' 🆙`);
  } catch (err) {
    const { message, stack } = err as Error;
    console.error("Error updating the database:", message, stack);
  }
};

// Run the migration function
migrate();

# Application Configuration
APP_PORT=3310
APP_SECRET=YOUR_APP_SECRET_KEY

# Database Configuration
DB_HOST=localhost
DB_PORT=3306
DB_USER=YOUR_DATABASE_USERNAME # Ton nom d'utilisateur MySQL
DB_PASSWORD=YOUR_DATABASE_PASSWORD # Ton mot de passe MySQL 
DB_NAME=wildseries # Le nom de la base de données que tu va utiliser

# Client URL (for CORS configuration)
CLIENT_URL=http://localhost:3000

# About specific needs, please ask your trainer about the deploiement project name and follow the pattern
# You can add as much variable as needed. Don't forget to tell your trainer about it. Otherwise, it could break on deploiement
PROJECT_NAME_SPECIFIC_NAME=YOUR_SPECIFIC_VALUE

npm run db:migrate

> db:migrate
> tsx ./bin/migrate

wildseries updated from '/home/my/wild-series/server/database/schema.sql' 🆙

// Load environment variables from .env file
import "dotenv/config";

import fs from "node:fs";
import path from "node:path";

// Build the path to the schema SQL file
const schema = path.join(__dirname, "../../server/database/schema.sql");

// Get database connection details from .env file
const { DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME } = process.env;

// Update the database schema
import mysql from "mysql2/promise";

const migrate = async () => {
  try {
    // Read the SQL statements from the schema file
    const sql = fs.readFileSync(schema, "utf8");

    // Create a specific connection to the database
    const database = await mysql.createConnection({
      host: DB_HOST,
      port: DB_PORT as number | undefined,
      user: DB_USER,
      password: DB_PASSWORD,
      multipleStatements: true, // Allow multiple SQL statements
    });

    // Drop the existing database if it exists
    await database.query(`drop database if exists ${DB_NAME}`);

    // Create a new database with the specified name
    await database.query(`create database ${DB_NAME}`);

    // Switch to the newly created database
    await database.query(`use ${DB_NAME}`);

    // Execute the SQL statements to update the database schema
    await database.query(sql);

    // Close the database connection
    database.end();

    console.info(`${DB_NAME} updated from '${path.normalize(schema)}' 🆙`);
  } catch (err) {
    const { message, stack } = err as Error;
    console.error("Error updating the database:", message, stack);
  }
};

// Run the migration function
migrate();

create table user (
  id int unsigned primary key auto_increment not null,
  email varchar(255) not null unique,
  password varchar(255) not null
);

create table item (
  id int unsigned primary key auto_increment not null,
  title varchar(255) not null,
  user_id int unsigned not null,
  foreign key(user_id) references user(id)
);

insert into user(id, email, password)
values
  (1, "jdoe@mail.com", "123456");

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

insert into category(id, name)
values
  (1, "Comédie"),
  (2, "Science-Fiction");

insert into program
  (
    id,
    title,
    synopsis,
    poster,
    country,
    year,
    category_id
  )
values
  (
    1,
    "The Good Place",
    "À sa mort, Eleanor Shellstrop est envoyée au Bon Endroit, un paradis fantaisiste réservé aux individus exceptionnellement bienveillants. Or Eleanor n'est pas exactement une « bonne personne » et comprend vite qu'il y a eu erreur sur la personne. Avec l'aide de Chidi, sa prétendue âme sœur dans l'au-delà, la jeune femme est bien décidée à se redécouvrir.",
    "https://img.betaseries.com/JwRqyGD3f9KvO_OlfIXHZUA3Ypw=/600x900/smart/https%3A%2F%2Fpictures.betaseries.com%2Ffonds%2Fposter%2F94857341d71c795c69b9e5b23c4bf3e7.jpg",
    "USA",
    2016,
    1
  ),
  (
    2,
    "Dark",
    "Quatre familles affolées par la disparition d'un enfant cherchent des réponses et tombent sur un mystère impliquant trois générations qui finit de les déstabiliser.",
    "https://img.betaseries.com/zDxfeFudy3HWjxa6J8QIED9iaVw=/600x900/smart/https%3A%2F%2Fpictures.betaseries.com%2Ffonds%2Fposter%2Fc47135385da176a87d0dd9177c5f6a41.jpg",
    "Allemagne",
    2017,
    2
  );