💨

ONSEN GOODS 開発記録 No.7

2025/07/18に公開

データベースの移行

PostgreSQLとその関連ツール(pgadmin4など)のインストールが終わったところから進めていく(記事にしていたのだが保存するの忘れていた)

テーブル作成

まず/backend/database.jsにPostgreSQLと接続するための記述をしていく。

database.js

const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST, 
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

ここに続けてテーブル作成のコードも書こうと思ったのだが、本番環境のことを考えると別のスクリプトにしたほうがいいらしいので、/db/setup-db.jsを作成しそこでテーブル作成、初期データ挿入をしていく

setup-db.js

const pool = require('./database'); // database.jsからプールをインポート

async function setup() {
  try {
    // hot_springsテーブルの作成
    await pool.query(`
      CREATE TABLE IF NOT EXISTS hot_springs (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        location VARCHAR(255) NOT NULL,
        description TEXT,
        image_url VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);

    // ratingsテーブルの作成
    await pool.query(`
      CREATE TABLE IF NOT EXSISTS ratings (
        id SERIAL PRIMARY KEY,
        hot_spring_id INTEGER NOT NULL REFERENCES hot_springs(id),
        user_id INTEGER NOT NULL,
        rating INTEGER CHECK (rating >= 1 AND rating <= 5),
        comment TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);
    // 初期データ挿入
    await pool.query(`
      INSERT INTO hot_springs (name, location, description, image_url)
      VALUES
        ('Onsen A', 'Location A', 'Description A', 'https://example.com/imageA.jpg'),
        ('Onsen B', 'Location B', 'Description B', 'https://example.com/imageB.jpg')
      ON CONFLICT DO NOTHING; -- 重複を避けるため
    `);

    await pool.query(`
      INSERT INTO ratings (hot_spring_id, user_id, rating, commnent) 
      VALUES
        (1, 1, 5.0, 'Great experience!'),
        (1, 2, 4.5, 'Loved it!'),
        (2, 1, 4.0, 'Very nice.'),
        (2, 3, 3.5, 'It was okay.'),
        (1, 1, 4.8, 'Amazing!'),
        (2, 2, 4.1, 'Very relaxing.'),
        (1, 3, 3.3, 'It was okay.')`)
      

    console.log('PostgreSQLテーブル作成完了');
  } catch (error) {
    console.error('PostgreSQLテーブル作成エラー:', error);
  } finally {
    pool.end(); // プールを閉じる
  }
}

setup();

node backend/db/setup-db.jsで動作確認をしてみるとPostgreSQLテーブル作成エラー
どうやらpgライブラリのインストールを忘れていたようだ
npm install pgでインストール
しかし動作確認をするとまたもやエラー
moduleのエクスポートとdotenvパッケージの記述がなかったのが原因
dotenvに関しては過去に何度も同じミスしているので注意。ついでに忘れていたインストールも済ませる。
しかしまだエラー、どうやら.envから変数が読み取れていなかったようだった。原因が分からずかなり苦戦したが、結果的にdatabase.jsのrequire('dotenv').comfig()で明示的に.envファイルのパスを記述することで解決した。

database.js

require('dotenv').config({ path: '../.env' });
const { Pool } = require('pg');


const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST, 
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

module.exports = pool;

APIの修正

データベースの作成と初期データの挿入が完了したので、次はAPIをPostgreSQLに対応させていく。
/backend/controllers/onsenController.jsを変更していく

onsenController.js

const db = require('../db/database'); // db/database.jsからデータベース接続を読み込む

// 1. 全ての温泉情報を取得 (GET /api/onsen)
exports.getAllOnsen = async (req, res) => {
  try {
    const result = await db.query('SELECT * FROM hot_springs');
    res.status(200).json(result.rows); 
  } catch (err) {
    console.error('温泉リスト取得エラー:', err.message); 
    res.status(500).json({ error: '温泉リストの取得中にエラーが発生しました。' }); 
  }
}

//2. 特定の温泉の詳細情報を取得 (GET /api/onsen/:id)
exports.getOnsenById = async (req, res) => {
  const { id } = req.params; // URLパスから温泉IDを取得
  try {
    const result = await db.query('SELECT * FROM hot_springs WHERE id = $1', [id]);
    if (result.rows.length === 0) {
      // 温泉が見つからない場合、404 Not Found
      return res.status(404).json({ message: '指定された温泉が見つかりませんでした。' });
    }
    res.status(200).json(result.rows[0]); 
  } catch (err) {
    console.error('温泉詳細取得エラー:', err.message); // デバッグ用
    return res.status(500).json({ error: '温泉詳細の取得中にエラーが発生しました。' });
  }
};

// 2-1 特定の温泉に対する評価とコメントを取得するAPI
exports.getRatingByOnsenId = async (req, res) => {
  const { id } = req.params; 
  try {
    const result = await db.query('SELECT * FROM ratings WHERE hot_spring_id = $1', [id]);
    if (result.rows.length === 0) {
      return res.status(404).json({ message: '指定された温泉の評価が見つかりませんでした。' });
    }
    res.status(200).json(result.rows); // 評価とコメントのリストを返す
  } catch (err) {
    console.error('温泉評価取得エラー:', err.message); 
    res.status(500).json({ error: '温泉評価の取得中にエラーが発生しました。' });
  }
};

//3. 特定の温泉に対する評価を投稿するAPI(POST /api/onsen/:id/rating)
// ユーザーから評価とコメントを受け取り、ratingsテーブルの保存、hot_springsテーブルの平均評価を更新。
exports.postRating = async (req, res) =>{
  const onsenId = req.params.id; // URLパラメータから温泉IDを取得
  const { userId = 1, rating, comment } = req.body; // リクエストボディから評価とコメントを取得

  // 
  if (typeof rating !== 'number' || rating < 1 || rating > 5) {
    return res.status(400).json({
      error: '評価の値が無効です。',
      details: '評価は1.0から5.0の範囲で指定してください。'
    });
  }

  const client = await db.connect(); // データベース接続を取得
  try {

    // 温泉の存在をチェック
    const onsenResult = await client.query('SELECT * FROM hot_springs WHERE id = $1', [onsenId]);
    if (onsenResult.rows.length === 0) {
      res.status(404).json({ error: '評価対象の温泉が見つかりません。'});
      return;
    }

    await client.query('BEGIN'); // トランザクション開始
    // 評価を挿入
    await client.query(
      'INSERT INTO ratings (hot_spring_id, user_id, rating, comment) VALUES ($1, $2, $3, $4)',
      [onsenId, userId, rating, comment]
    );

    // 平均の評価を再計算して更新
    await client.query(`
      UPDATE hot_springs
      SET rating = (
        SELECT AVG(rating) FROM ratings WHERE hot_spring_id = $1
      )
      WHERE id = $1
    `, [onsenId]);
  
    // 更新後の温泉情報を取得
    const updatedOnsenResult = await client.query('SELECT * FROM hot_springs WHERE id = $1', [onsenId]);
    await client.query('COMMIT'); // トランザクションをコミット
    res.status(200).json(updatedOnsenResult.rows[0]); // 更新された温泉情報を返す
  } catch (err) {
    await client.query('ROLLBACK'); // エラー時はロールバック
    console.error('評価投稿エラー', err.message);
    res.status(500).json({ error: '評価の投稿中にエラーが発生しました。'});
  } finally {
    client.release();
  }
};

具体的な変更点は、

SQLiteではdb.runでデータを取得していたところをdb.queryに
トランザクション処理の記述方法を少し変更

server.jsはそのまま使えるので変更なし。
Postmanで動作確認したところ問題なし。
動作確認もできたので今回はここまで

データベースの移行

テーブル作成

APIの修正

Discussion