Data Management with RDBMS in Remix

Emmanuel BRUNO

emmanuel.bruno@univ-tln.fr

Université de Toulon

LIS UMR CNRS 7020

2024-11-03

Introduction

Objective: Learn how to manage data in Remix using RDBMS.
Tools: PostgreSQL or MySQL, with or without Prisma.
Why Use SQL?
- Direct Database Interaction: SQL allows you to interact directly with the database using queries.
- Flexibility: You can write complex queries and optimize them for performance.
- Control: Full control over the database schema and data manipulation.
Why Use Prisma?
- ORM (Object-Relational Mapping): Prisma provides a type-safe API to interact with the database.
- Productivity: Simplifies database operations and reduces boilerplate code.
- Migrations: Built-in support for database migrations.
- Type Safety: Ensures type safety and reduces runtime errors.
Choosing the Right Tool
- SQL: Use SQL for direct control and complex queries.
- Prisma: Use Prisma for productivity, type safety, and simplified database operations.

CREATE DATABASE order_database;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE DATABASE order_database;

USE order_database;

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Insert Data

INSERT INTO customers (name, email) VALUES ('John Doe', 'john@example.com'), ('Jane Smith', 'jane@example.com');

INSERT INTO products (name, price) VALUES ('Product 1', 10.00), ('Product 2', 20.00);

INSERT INTO orders (customer_id) VALUES (1), (2);

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 2, 10.00), (1, 2, 1, 20.00), (2, 1, 1, 10.00);

Connecting to the Database

Install the database driver (e.g., pg for PostgreSQL or mysql for MySQL).
- with the blues stack template we have pg already installed
- for mysql: npm install mysql

Set the credentials for the database connection in .env.

for PostgreSQL:

DATABASE_URL=postgresql://username:password@localhost:5432/order_database

for MySQL:

DATABASE_URL=mysql://username:password@localhost:3306/order_database

Data Access Object (DAO) in Remix

Objective: Create a Data Access Object (DAO) to handle database operations in Remix.
Create a db.server.ts file to handle the database connection:

PostgreSQL
MySQL

// app/utils/db.server.ts
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export { pool };

// app/utils/db.server.ts
import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
});

export { pool };

Create a customer.dao.ts file for customer-related database operations:

// app/dao/customer.dao.ts
import { pool } from '~/utils/db.server';

export async function createCustomer(name: string, email: string) {
  const res = await pool.query(
    'INSERT INTO customers (name, email) VALUES ($1, $2) RETURNING *',
    [name, email]
  );
  return res.rows[0];
}

export async function getCustomers() {
  const res = await pool.query('SELECT * FROM customers');
  return res.rows;
}

export async function getCustomerById(id: number) {
  const res = await pool.query('SELECT * FROM customers WHERE id = $1', [id]);
  return res.rows[0];
}

export async function updateCustomer(id: number, name: string, email: string) {
  const res = await pool.query(
    'UPDATE customers SET name = $1, email = $2 WHERE id = $3 RETURNING *',
    [name, email, id]
  );
  return res.rows[0];
}

export async function deleteCustomer(id: number) {
  const res = await pool.query('DELETE FROM customers WHERE id = $1 RETURNING *', [id]);
  return res.rows[0];
}

Using the DAO in Remix Loaders and Actions

You can now use these DAO functions in your Remix loaders and actions. For example, in a route file:

// app/routes/customers.tsx
import { LoaderFunction, ActionFunction, json } from 'remix';
import { createCustomer, getCustomers } from '~/dao/customer.dao';

export let loader: LoaderFunction = async () => {
  const customers = await getCustomers();
  return json(customers);
};

export let action: ActionFunction = async ({ request }) => {
  const formData = await request.formData();
  const name = formData.get('name') as string;
  const email = formData.get('email') as string;
  const newCustomer = await createCustomer(name, email);
  return json(newCustomer);
};

This setup allows you to manage your database operations in a clean and organized manner using DAOs in Remix.

Conclusion

SQL: Direct interaction with the database using SQL queries.
Prisma: An ORM that simplifies database interactions with a type-safe API.
Choose the right tool: Depending on your project requirements and preferences.