[nextjs]SNS Server-52(myapp62)[DB 어댑터/DB Adapter]

👉🏻 여러가지 데이터베이스를 사용하기 위해서 DB 어댑터를 만듭니다.
We create a DB adapter to work with various databases.

👉🏻 DB어댑터를 위해서 사용되는 데이터베이스는 sqlite와 mysql입니다.
The databases used for the DB adapter are SQLite and MySQL.

📁 프로젝트 시작 / Project Start

✔️ Nextjs 프로젝트 설치
Setting up a Next.js project

npx create-next-app@latest

📁 모듈 설치 / Module Installation

# db
npm install mysql2        
npm install better-sqlite3

# type
npm i --save-dev @types/better-sqlite3 

📁 .env.local

✔️ 데이터베이스 선택 / Select Database

DB_TYPE=sqlite                              # mysql,sqlite
DB_HOST=localhost
DB_USER=mysns
DB_PASSWORD=""
DB_DATABASE=test
DOMAIN=aloy-horizon.duckdns.org             # only server
NEXT_PUBLIC_DOMAIN=aloy-horizon.duckdns.org # client
JWT_SECRET=your-secret-key

📁 테이블 생성 / Create table

✔️ SQLite

CREATE TABLE IF NOT EXISTS user (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

✔️ Mysql

CREATE TABLE IF NOT EXISTS `user` (
  id VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

📁 라우트 / route

✔️ api/users/route.ts

— list ,insert

// app/api/users/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { getDb } from '@/lib/db';

export async function GET() {
  const db = getDb();
  await db.init();
  const users = await db.listUsers();
  return NextResponse.json(users);
}

export async function POST(req: NextRequest) {
  const db = getDb();
  await db.init();
  const { id, name, email } = await req.json();
  
  const user = await db.createUser({ id, name, email });
  return NextResponse.json(user);
}

✔️ api/users/[id]/route.ts

— delete

// app/api/users/[id]/route.ts
import { NextResponse } from 'next/server';
import { getDb } from '@/lib/db';

export async function DELETE(_: Request, { params }: { params: { id: string } }) {
  const db = getDb();
  await db.init();
  await db.deleteUser(params.id);
  return NextResponse.json({ ok: true });
}

📁 테스트 / Test

✔️ 데이터 베이스 전환 / Database Migration

— 데이터베이스 전환은 .env.local파일의 DB_TYPE을 mysql이나 sqlite로 전환하고 서버 재시작하면 됩니다.
To switch databases, change the DB_TYPE in the .env.local file to either mysql or sqlite, then restart the server.

DB_TYPE=                                   # mysql,sqlite
DB_HOST=localhost
DB_USER=mysns
DB_PASSWORD=""
DB_DATABASE=test
DOMAIN=aloy-horizon.duckdns.org             # only server
NEXT_PUBLIC_DOMAIN=aloy-horizon.duckdns.org # client
JWT_SECRET=your-secret-key

✔️ 데이터 입력 / Data Entry

— 터미널에서 실행하면 됩니다.
You can run it from the terminal.

1)mysql

# mysql
curl -X POST https://aloy-horizon.duckdns.org/api/users \
  -H "Content-Type: application/json" \
  -d '{"id":"u1","name":"Hello","email":"test@test.com"}' 

curl -X POST https://aloy-horizon.duckdns.org/api/users \
  -H "Content-Type: application/json" \
  -d '{"id":"u2","name":"Hello mysql","email":"test@test.com"}' 

2)sqlite

# sqlite
curl -X POST https://aloy-horizon.duckdns.org/api/users \
  -H "Content-Type: application/json" \
  -d '{"id":"u1","name":"Hello sqlite","email":"test@test.com"}'

✔️ sqlite

sqlite> .tables
user
sqlite> .schema
CREATE TABLE user (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);
sqlite> select * from user;
u1|Hello sqlite|test@test.com
sqlite> 

✔️ mysql

mysql> select * from user;
+----+-------------+---------------+
| id | name        | email         |
+----+-------------+---------------+
| u1 | Hello       | test@test.com |
| u2 | Hello mysql | test@test.com |
+----+-------------+---------------+
2 rows in set (0.001 sec)

mysql> 

마태복음 21장 22절 / Matthew 21: 22

“너희가 기도할 때에 무엇이든지 믿고 구하는 것은 다 받으리라 하시니라”

“And everything you ask in prayer, believing, you will receive.”