データベースを使ってみる(前編)

2024-12-27

ご注意

この記事内の画像は、私(ふるおろ)の実データを使用しています。様々な部分が お見せできないよ！ になっていますのでご了承ください。
専門的なはなし多めです。ソースコードがたくさん出てくるので、ぷよぐやみんぐアレルギーの方は注意
半分以上は備忘録目的です。だいじな部分以外(フロントエンド)は書き方がてきとーです。

きっかけ

私は家計簿の管理におカネレコというアプリを使っています(~~課金圧がすごいですが~~無料の範囲で)。けっこうまめに記録しています(ｵｶﾈﾅｶﾞﾚﾊｱｸﾀﾞｲｼﾞ)。このアプリ、無料版でも月に1度だけEmailアドレス宛でデータのバックアップができるのですが、バックアップで出力されたファイルが……

ウワーッ！ 💥 で、データベースだぁぁ！ﾄﾞｺﾄﾞｺ┗(^o^)┛ﾄﾞｺﾄﾞｺ┏(^o^)┓

Excel/CSV出力やドライブバックアップ機能は全て課金機能……。 SQLiteを送りつけてくるということは、こういうのに詳しくない一般人にバックアップデータを解読させる気がないということですね……(というより、アプリ側がデータをSQLiteで管理しているだけだと思う)

一応、DB Browser で中身を見ることはできますが……

……何に何円使ったか解読する気がなくなりますね💫

家計簿バックアップ確認アプリをつくる

早速結論ですが、このNanimo Wakaranaiデータベースファイルをちゃんと表示してくれるアプリを作りました(制作時間5時間くらい)。完成形は以下の通りです。

各出費(収入)に対して、「日付」「時刻」「金額」「カテゴリー」「メモ」を並べた表を出力する(これ以外の項目は私には必要ないでしょう)
支出と収入の円グラフをカテゴリー別で出力する
フィルタリング機能の実装。出費のカテゴリー¹(最上部のボタン)と期間(その下の範囲指定)。
勉強も兼ねて、実行ファイル(.exe)ではく、Next.jsのWebアプリとして実装する(もちろんWeb上に公開するつもりはないよ)。これでWebからのデータベースの操作もできるようになりますね。

これらを目標に設計しました。 UI設計とスタイリングは面倒だったので本サイトのものを幾らか使いまわししました。

というわけで、久々のお勉強会です。れっつご～！ (ぷよぐやみんぐよくわからにゃいにゃんぷっぷーは「まとめ」まで飛ばしましょう)

データベースを読み込んで必要なデータを出力するAPIエンドポイントを作成する

まずは今回の核心からやっていきます。まずはいつものコマンド²でプロジェクトを作ります。

1pnpm create next-app

必要なパッケージも入れちゃいましょう。

1pnpm add sqlite sqlite3
2pnpm add -D @types/sqlite3

プロジェクトのルートディレクトリに先ほど手に入れたMMJ.sqliteを置いておき、APIのエンドポイント³を作成します。

src/app/api/route.ts
1import { NextResponse } from "next/server";
2import { existsSync } from "fs";
3import { open } from "sqlite";
4import { Database } from "sqlite3";
5import type { Category, CategoryDetail, SQLData } from "@/types/data";
6
7export const GET = async () => {
8  // ファイルパス
9  const filepath: string = "./MMJ.sqlite";
10
11  if (existsSync(filepath)) {
12    // データベースを開く
13    const db = await open({
14      filename: filepath,
15      driver: Database,
16    });
17
18    // データ抽出
19    const category = (await db.all("SELECT * FROM Category")) as Category[];
20    const detail = (await db.all(
21      "SELECT * FROM Category_detail"
22    )) as CategoryDetail[];
23    const data: SQLData = {
24      category: category,
25      detail: detail,
26    };
27    return NextResponse.json(data);
28  } else {
29    return NextResponse.json({ message: "File not found" }, { status: 404 });
30  }
31};

ここで

データベースを開く(12～16行目)
必要なデータを抽出(18～26行目)
JSONで返す(27行目)
ファイルが見つからない場合は404を返す(11, 28～30行目)

工程を一気にやりました。 DB Browserを見たところ、必要そうなデータはCategory (カテゴリー一覧)とCategory_detail (実際の出費の一覧)の2つのテーブル以外にはなさそうでしたので、SQLのコマンドをたたいて2つのテーブル内に入っているデータを全てオブジェクト型で引っ張り出します。その後、データを整形してレスポンスを返しています。

DB Browserを見ながら、テーブルの型定義も地道に作ってあげましょう(つくった)

data.ts
1/**
2 * カテゴリーの型
3 */
4export type Category = {
5  CategoryID: number;
6  Name: string;
7  Count: number;
8  NameJa: string;
9  sort: number;
10  colorindex: number;
11  nameCHT: string;
12  nameCHS: string;
13  refer_no: string;
14  delete_flag: number;
15  sync_flag: 0;
16  is_default: 0 | 1;
17  client_refer_no: string;
18  NameFR: string;
19  NameES: string;
20  income_flag: 0 | 1;
21  CategoryBudgetValue: number | null;
22  isCategoryBudget: number;
23};
24
25/**
26 * 詳細の型
27 */
28export type CategoryDetail = {
29  ID: number;
30  ID_Category: number;
31  Date: string;
32  Time: string;
33  Price: string;
34  Photo: "";
35  Count: 0;
36  Memo: string;
37  CategoryName_detail: "";
38  YearMonth: string;
39  createDate: string;
40  createTime: string;
41  createYearMonth: string;
42  ID_Payment: -4;
43  refer_no: "";
44  delete_flag: 0;
45  sync_flag: 0;
46  sync_photo: 0;
47  photo_server: "";
48  shopID: number;
49  client_refer_no: string;
50  client_create_at: string | null;
51};
52
53/**
54 * 取得したSQLデータの型
55 */
56export type SQLData = {
57  category: Category[];
58  detail: CategoryDetail[];
59};

この時点でpnpm devで開発サーバーを立ち上げてhttp://localhost:3000/apiにアクセスすると……

ちゃんとデータベースからデータが取得できていそうでした！これで弊観測所もデータベースへの接続・操作が実装できるようになるよ！やったねめいちゃん！⁴

APIルートを叩いて情報を取得

続いてフロントエンド側です。トップページのページファイルpage.tsx (サーバーコンポーネント)を書き換えます。

src/app/page.tsx
1import { NextPage } from "next";
2import { headers } from "next/headers";
3import { SQLData } from "@/types/data";
4import { Main } from "@/components/Main";
5
6const Home: NextPage = async () => {
7  // ホストとプロトコルを取得
8  const headersData = await headers();
9  const protocol: string = headersData.get("x-forwarded-proto") || "http";
10  const host: string | null = headersData.get("host");
11
12  // 絶対パスで指定
13  const apiBase: string = `${protocol}://${host}`;
14
15  // データを取得
16  const res: Response = await fetch(`${apiBase}/api`);
17  const data = await res.json();
18
19  // ファイルがあるか確認
20  if (res.ok) {
21    return <Main data={data as SQLData} />;
22  } else {
23    return data.message;
24  }
25};
26
27export default Home;

page.tsxでは、以下の処理を行っています。

データをfetchするアドレスを整形(7～13行目)
/apiからfetchする(16行目)
ファイルがあれば(正確には、レスポンスが正常なら)<Main />コンポーネント(クライアントコンポーネント)にデータを渡し、データが見つからなかったらレスポンスで受け取ったNot foundのメッセージdata.messageを表示(19～24行目)

後は、<Main />コンポーネントへと渡ってきたpropsをひたすらいじくり回してデータを表示するだけです。状態はuseStateで管理し、リストや円グラフの更新はuseEffectで発火させました。また、円グラフの表示にはグラフの表示ができるライブラリrechartsを使用しました。

1pnpm add recharts

<Main />コンポーネント内はひたすらReactのはなしなので、ソースコードは適宜省略して詳細の説明は割愛します(データベースのはなしから逸れるので)。

src/components/Main.tsx
1"use client";
2import { ChangeEvent, useCallback, useEffect, useState } from "react";
3import type { CategoryDetail, SQLData } from "@/types/data";
4import { PieChart, Pie, ResponsiveContainer, Tooltip, Cell } from "recharts";
5
6...
7
8/**
9 * propsの型
10 */
11type Props = {
12  data: SQLData;
13};
14
15...
16
17export const Main = (props: Props) => {
18  // 選択日付範囲
19  const [start, setStart] = useState<Date>(
20    new Date(new Date().getFullYear(), 0, 1)
21  );
22  const [end, setEnd] = useState<Date>(new Date());
23
24  // 表示中のリスト
25  const [lists, setLists] = useState<CategoryDetail[]>([]);
26
27  ...
28
29  // カテゴリー絞り込み
30  const [filter, setFilter] = useState<number[]>([]);
31
32  ...
33
34  // リスト更新
35  useEffect(() => {
36    /* リスト更新の処理 */
37  }, [start, end, props.data]);
38
39  // 円グラフ更新
40  useEffect(() => {
41	  /* 円グラフ更新の処理 */
42  }, [lists, filter, props.data]);
43
44  ...
45
46  return (
47    <main>
48      <h1>家計簿</h1>
49      <div>
50        {props.data.category.map((v) => {
51          /* カテゴリーフィルタリング用のボタン */
52        })}
53      </div>
54      <p>
55        <strong>日付範囲：</strong>
56        {/* 日付範囲指定ボタン */}
57      </p>
58      <div>
59        <table>
60          <thead>
61            <tr>
62              <th>日付</th>
63              <th>時刻</th>
64              <th>金額</th>
65              <th>カテゴリー</th>
66              <th>メモ</th>
67            </tr>
68          </thead>
69          <tbody>
70            {lists.map((list) => {
71              /* 条件分岐でフィルタリングし、<tr>タグで出力してあげる */
72            })}
73          </tbody>
74        </table>
75        <div>
76          ...
77          <ResponsiveContainer width="100%" height={800}>
78            <PieChart>
79              <Pie>
80                {/* 収入の円グラフを<Cell />コンポーネントで出力 */}
81              </Pie>
82              <Pie>
83                {/* 支出の円グラフを<Cell />コンポーネントで出力 */}
84              </Pie>
85              <Tooltip />
86            </PieChart>
87          </ResponsiveContainer>
88          ...
89        </div>
90      </div>
91    </main>
92  );
93};

……フロントエンドはかなり省略しましたが、これで先ほどの完成形が出力できるようになりました！

まとめ

今回は、

Next.jsのAPIルートを使用してAPIを実装する
実装するAPIの中でデータベースに接続し、必要なデータをレスポンスとして返す
フロントエンドからAPIルートにアクセスし、受け取ったデータで家計簿のデータを表示する

を行いました。Next.jsを始めてからこの手の処理はやってみたことがなかったので、かなりいい勉強になったと思います。

さて、後編では、弊観測所が実際にデータベース導入した際の(より実用的な⁵)あれこれを書こうかと思っています。それでは～

(どうでもいいはなし) カテゴリーの「嗜好品」は、🍺や🚬は摂取しないので全部あまあじ(+ コーヒー)代です。
(どうでもいいはなし) 最近本サイトを含め、全てのプロジェクトのパッケージマネージャをYarn (v1)からpnpmに移行しました。pnpmって「ぷぬぷむ」って読みたくなるよね(かわいい)
当たり前ですが、データベースへの接続などの実装は全てバックエンド側です( ~~どうせWebに公開しないならpublicフォルダにMMJ.sqliteを突っ込めばよくない？~~ )。とはいえ、Next.jsってフロントエンドに加えてAPIとかサーバー側の処理とか全部書けるのすごいですよね。これが令和最新版かぁ……
芽火
そもそもこのサイトでSQLみたいな高度なデータベースを使ってデータ管理とかするつもりないですし……(導入してもなにするん？ってなる)

戻る

データベースを使ってみる(前編)

2024-12-27

開発

ご注意

この記事内の画像は、私(ふるおろ)の実データを使用しています。様々な部分が お見せできないよ！ になっていますのでご了承ください。
専門的なはなし多めです。ソースコードがたくさん出てくるので、ぷよぐやみんぐアレルギーの方は注意
半分以上は備忘録目的です。だいじな部分以外(フロントエンド)は書き方がてきとーです。

きっかけ

ウワーッ！ 💥 で、データベースだぁぁ！ﾄﾞｺﾄﾞｺ┗(^o^)┛ﾄﾞｺﾄﾞｺ┏(^o^)┓

一応、DB Browser で中身を見ることはできますが……

……何に何円使ったか解読する気がなくなりますね💫

家計簿バックアップ確認アプリをつくる

各出費(収入)に対して、「日付」「時刻」「金額」「カテゴリー」「メモ」を並べた表を出力する(これ以外の項目は私には必要ないでしょう)
支出と収入の円グラフをカテゴリー別で出力する
フィルタリング機能の実装。出費のカテゴリー¹(最上部のボタン)と期間(その下の範囲指定)。
勉強も兼ねて、実行ファイル(.exe)ではく、Next.jsのWebアプリとして実装する(もちろんWeb上に公開するつもりはないよ)。これでWebからのデータベースの操作もできるようになりますね。

これらを目標に設計しました。 UI設計とスタイリングは面倒だったので本サイトのものを幾らか使いまわししました。

というわけで、久々のお勉強会です。れっつご～！ (ぷよぐやみんぐよくわからにゃいにゃんぷっぷーは「まとめ」まで飛ばしましょう)

データベースを読み込んで必要なデータを出力するAPIエンドポイントを作成する

まずは今回の核心からやっていきます。まずはいつものコマンド²でプロジェクトを作ります。

1pnpm create next-app

必要なパッケージも入れちゃいましょう。

1pnpm add sqlite sqlite3
2pnpm add -D @types/sqlite3

プロジェクトのルートディレクトリに先ほど手に入れたMMJ.sqliteを置いておき、APIのエンドポイント³を作成します。

src/app/api/route.ts
1import { NextResponse } from "next/server";
2import { existsSync } from "fs";
3import { open } from "sqlite";
4import { Database } from "sqlite3";
5import type { Category, CategoryDetail, SQLData } from "@/types/data";
6
7export const GET = async () => {
8  // ファイルパス
9  const filepath: string = "./MMJ.sqlite";
10
11  if (existsSync(filepath)) {
12    // データベースを開く
13    const db = await open({
14      filename: filepath,
15      driver: Database,
16    });
17
18    // データ抽出
19    const category = (await db.all("SELECT * FROM Category")) as Category[];
20    const detail = (await db.all(
21      "SELECT * FROM Category_detail"
22    )) as CategoryDetail[];
23    const data: SQLData = {
24      category: category,
25      detail: detail,
26    };
27    return NextResponse.json(data);
28  } else {
29    return NextResponse.json({ message: "File not found" }, { status: 404 });
30  }
31};

ここで

データベースを開く(12～16行目)
必要なデータを抽出(18～26行目)
JSONで返す(27行目)
ファイルが見つからない場合は404を返す(11, 28～30行目)

DB Browserを見ながら、テーブルの型定義も地道に作ってあげましょう(つくった)

data.ts
1/**
2 * カテゴリーの型
3 */
4export type Category = {
5  CategoryID: number;
6  Name: string;
7  Count: number;
8  NameJa: string;
9  sort: number;
10  colorindex: number;
11  nameCHT: string;
12  nameCHS: string;
13  refer_no: string;
14  delete_flag: number;
15  sync_flag: 0;
16  is_default: 0 | 1;
17  client_refer_no: string;
18  NameFR: string;
19  NameES: string;
20  income_flag: 0 | 1;
21  CategoryBudgetValue: number | null;
22  isCategoryBudget: number;
23};
24
25/**
26 * 詳細の型
27 */
28export type CategoryDetail = {
29  ID: number;
30  ID_Category: number;
31  Date: string;
32  Time: string;
33  Price: string;
34  Photo: "";
35  Count: 0;
36  Memo: string;
37  CategoryName_detail: "";
38  YearMonth: string;
39  createDate: string;
40  createTime: string;
41  createYearMonth: string;
42  ID_Payment: -4;
43  refer_no: "";
44  delete_flag: 0;
45  sync_flag: 0;
46  sync_photo: 0;
47  photo_server: "";
48  shopID: number;
49  client_refer_no: string;
50  client_create_at: string | null;
51};
52
53/**
54 * 取得したSQLデータの型
55 */
56export type SQLData = {
57  category: Category[];
58  detail: CategoryDetail[];
59};

この時点でpnpm devで開発サーバーを立ち上げてhttp://localhost:3000/apiにアクセスすると……

APIルートを叩いて情報を取得

続いてフロントエンド側です。トップページのページファイルpage.tsx (サーバーコンポーネント)を書き換えます。

src/app/page.tsx
1import { NextPage } from "next";
2import { headers } from "next/headers";
3import { SQLData } from "@/types/data";
4import { Main } from "@/components/Main";
5
6const Home: NextPage = async () => {
7  // ホストとプロトコルを取得
8  const headersData = await headers();
9  const protocol: string = headersData.get("x-forwarded-proto") || "http";
10  const host: string | null = headersData.get("host");
11
12  // 絶対パスで指定
13  const apiBase: string = `${protocol}://${host}`;
14
15  // データを取得
16  const res: Response = await fetch(`${apiBase}/api`);
17  const data = await res.json();
18
19  // ファイルがあるか確認
20  if (res.ok) {
21    return <Main data={data as SQLData} />;
22  } else {
23    return data.message;
24  }
25};
26
27export default Home;

page.tsxでは、以下の処理を行っています。

データをfetchするアドレスを整形(7～13行目)
/apiからfetchする(16行目)
ファイルがあれば(正確には、レスポンスが正常なら)<Main />コンポーネント(クライアントコンポーネント)にデータを渡し、データが見つからなかったらレスポンスで受け取ったNot foundのメッセージdata.messageを表示(19～24行目)

1pnpm add recharts

src/components/Main.tsx
1"use client";
2import { ChangeEvent, useCallback, useEffect, useState } from "react";
3import type { CategoryDetail, SQLData } from "@/types/data";
4import { PieChart, Pie, ResponsiveContainer, Tooltip, Cell } from "recharts";
5
6...
7
8/**
9 * propsの型
10 */
11type Props = {
12  data: SQLData;
13};
14
15...
16
17export const Main = (props: Props) => {
18  // 選択日付範囲
19  const [start, setStart] = useState<Date>(
20    new Date(new Date().getFullYear(), 0, 1)
21  );
22  const [end, setEnd] = useState<Date>(new Date());
23
24  // 表示中のリスト
25  const [lists, setLists] = useState<CategoryDetail[]>([]);
26
27  ...
28
29  // カテゴリー絞り込み
30  const [filter, setFilter] = useState<number[]>([]);
31
32  ...
33
34  // リスト更新
35  useEffect(() => {
36    /* リスト更新の処理 */
37  }, [start, end, props.data]);
38
39  // 円グラフ更新
40  useEffect(() => {
41	  /* 円グラフ更新の処理 */
42  }, [lists, filter, props.data]);
43
44  ...
45
46  return (
47    <main>
48      <h1>家計簿</h1>
49      <div>
50        {props.data.category.map((v) => {
51          /* カテゴリーフィルタリング用のボタン */
52        })}
53      </div>
54      <p>
55        <strong>日付範囲：</strong>
56        {/* 日付範囲指定ボタン */}
57      </p>
58      <div>
59        <table>
60          <thead>
61            <tr>
62              <th>日付</th>
63              <th>時刻</th>
64              <th>金額</th>
65              <th>カテゴリー</th>
66              <th>メモ</th>
67            </tr>
68          </thead>
69          <tbody>
70            {lists.map((list) => {
71              /* 条件分岐でフィルタリングし、<tr>タグで出力してあげる */
72            })}
73          </tbody>
74        </table>
75        <div>
76          ...
77          <ResponsiveContainer width="100%" height={800}>
78            <PieChart>
79              <Pie>
80                {/* 収入の円グラフを<Cell />コンポーネントで出力 */}
81              </Pie>
82              <Pie>
83                {/* 支出の円グラフを<Cell />コンポーネントで出力 */}
84              </Pie>
85              <Tooltip />
86            </PieChart>
87          </ResponsiveContainer>
88          ...
89        </div>
90      </div>
91    </main>
92  );
93};

……フロントエンドはかなり省略しましたが、これで先ほどの完成形が出力できるようになりました！

まとめ

今回は、

Next.jsのAPIルートを使用してAPIを実装する
実装するAPIの中でデータベースに接続し、必要なデータをレスポンスとして返す
フロントエンドからAPIルートにアクセスし、受け取ったデータで家計簿のデータを表示する

を行いました。Next.jsを始めてからこの手の処理はやってみたことがなかったので、かなりいい勉強になったと思います。

さて、後編では、弊観測所が実際にデータベース導入した際の(より実用的な⁵)あれこれを書こうかと思っています。それでは～

(どうでもいいはなし) カテゴリーの「嗜好品」は、🍺や🚬は摂取しないので全部あまあじ(+ コーヒー)代です。
(どうでもいいはなし) 最近本サイトを含め、全てのプロジェクトのパッケージマネージャをYarn (v1)からpnpmに移行しました。pnpmって「ぷぬぷむ」って読みたくなるよね(かわいい)
当たり前ですが、データベースへの接続などの実装は全てバックエンド側です( ~~どうせWebに公開しないならpublicフォルダにMMJ.sqliteを突っ込めばよくない？~~ )。とはいえ、Next.jsってフロントエンドに加えてAPIとかサーバー側の処理とか全部書けるのすごいですよね。これが令和最新版かぁ……
芽火
そもそもこのサイトでSQLみたいな高度なデータベースを使ってデータ管理とかするつもりないですし……(導入してもなにするん？ってなる)

戻る

データベースを使ってみる(前編)

ご注意

きっかけ

家計簿バックアップ確認アプリをつくる

データベースを読み込んで必要なデータを出力するAPIエンドポイントを作成する

APIルートを叩いて情報を取得

まとめ

脚注

データベースを使ってみる(前編)

ご注意

きっかけ

家計簿バックアップ確認アプリをつくる

データベースを読み込んで必要なデータを出力するAPIエンドポイントを作成する

APIルートを叩いて情報を取得

まとめ

脚注