Engineering_S/W & H/W

이번 블로그 글은 일대다(One To Many)를 다루어 볼 것입니다. 위 사진처럼 일대다 테이블을 구성할 예정입니다. 사진1) users table 사진2) posts table 3) Swagger - 유저 조회 3-1) 유저ID를 이용해서 DB조회 4) Swagger - 게시글 조회 4-1) post ID를 이용해서 작성자 정보까지 DB에서 조회 자세한 코드는 아래 GitHub를 참고해 주시기 바랍니다. GitHub Link

 이전글 : Docker-Compose yaml파일을 이용하여 MySQL를 Local로 구축하기 초기 Docker Container를 생성할때 초기 상태이다. 이때 미리 데이터베이스와 각각의 테이블을 생성할수 있다. 먼저 init.sql을 작성한다. 아래는 예시 sql문이다. -- Create Users table CREATE TABLE Users ( id serial PRIMARY KEY , -- 유저 고유 ID nickname VARCHAR ( 255 ) UNIQUE NOT NULL , -- 유저 닉네임 고유값 password VARCHAR ( 255 ), -- 유저의 비밀번호 socialUser BOOLEAN , -- 소셜로그인 유저이면 true 입력 socialUserType VARCHAR ( 255 ), -- 소셜로그인 타입 ex) google email VARCHAR ( 255 ) UNIQUE NOT NULL , -- 유저의 Email refrashToken VARCHAR ( 255 ) UNIQUE , -- Access 토큰을 갱신한다. created_at DATE , updated_at DATE ); -- Create Post table CREATE TABLE Post ( id serial PRIMARY KEY , type VARCHAR ( 255 ), -- 글의 타입. (공지글, 일반글). 공지글을 관리자만 작성이 가능하다. title VARCHAR ( 255 ), -- 글의 title body TEXT , -- 글의 내용 userId INT , -- 글 작성자 count INT DEFAULT 0 , -- 조회수 (초기값은 0이다) `like` INT DEFAULT 0 , -- 좋아요 `hate` INT DEFAULT 0 , -- 싫어요 created_at DATE , updated_at DATE ); -- Create...

 dbeaver download : https://dbeaver.io/download/ 출처: https://github.com/dbeaver/dbeaver DBeaver는 데이터베이스 관리 및 개발 도구 중 하나로, 다양한 데이터베이스 시스템과 연동하여 데이터베이스 관리, 쿼리 작성, 스키마 디자인, 데이터 편집, 백업 및 복원 등 다양한 데이터베이스 관련 작업을 수행할 수 있는 오픈 소스 소프트웨어입니다. DBeaver는 크로스 플랫폼 도구로서 Windows, macOS, Linux 등 다양한 운영 체제에서 실행할 수 있습니다. 1. 다양한 데이터베이스 지원: DBeaver는 다양한 데이터베이스 시스템을 지원하며, MySQL, PostgreSQL, Oracle, SQL Server, SQLite, MongoDB 등 다양한 데이터베이스 시스템과 연동하여 사용할 수 있습니다. 2. 시각적 쿼리 작성: 사용자가 SQL 쿼리를 시각적으로 작성하고 실행할 수 있는 편리한 인터페이스를 제공합니다. 3. 데이터 편집 및 관리: 데이터베이스 내의 테이블 데이터를 편집하고 관리할 수 있으며, 데이터의 추가, 수정, 삭제 작업을 지원합니다. 4. 스키마 디자인: 데이터베이스 스키마를 시각적으로 디자인하고 관리할 수 있습니다. 5. 데이터 뷰어: 데이터베이스에서 쿼리한 결과를 그래픽 및 텍스트 모드로 표시할 수 있는 데이터 뷰어를 제공합니다. 6. 플러그인 확장: 다양한 플러그인을 지원하여 사용자의 요구에 따라 기능을 확장할 수 있습니다. 7. 데이터베이스 백업 및 복원: 데이터베이스의 백업과 복원 작업을 지원하여 데이터 손실을 방지할 수 있습니다. 성능 모니터링: 데이터베이스 성능을 모니터링하고 성능 튜닝을 위한 도구를 제공합니다. 설치법 : Ubuntu DBeaber 설치

  postgres에서 해당 테이블을 생성합니다. $ CREATE TABLE users(id INT PRIMARY KEY NOT NULL, name TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT now()) 이후 아래 레코드를 입력합니다. $ INSERT INTO users(id, name, created_at) VALUES (1,'알렉스', '2022-10-12 01:00:00+09'), (2,'존', '2022-10-12 01:10:00+09'), (3,'피닉스', '2022-10-12 11:20:00+09'), (4,'폴', '2022-10-13 01:00:30+09'), (5,'스카', '2022-10-13 07:30:00+09'), (6,'알폰스', '2022-10-14 01:00:00+09'), (7,'미미', '2022-10-15 01:00:00+09'), (8,'크레이토스', '2022-10-15 01:00:00+09'), (9,'제우스', '2022-10-15 01:10:00+09'), (10,'아테나', '2022-10-16 01:00:00+09') 테이블을 조회하면 아래 사진과 같이 나오는 것을 알수 있습니다. 이제 날짜별로 생성된 유저수를 확인할려고 합니다. 현재는 날짜 기준으로 생성날자를 카운트 할려고한다. 문제는 날짜가 같아도 생성 시간이 달라서 GROUP BY를 하지 못합니다. 이때 DATE_TRUNC를 사용합니다. $ SELECT DATE_TRUNC('day', "created_at") AS date, COUNT(*) FROM users GROUP BY date...

  사진1) SQL 인스턴스 만들기 GCP에서 SQL 인스턴스를 만들기 버튼을 클릭합니다. 사진2) DBMS 선택 해당 데이터베이스에 사용할 DBMS을 선택합니다. (위 글에서는 postgreSQL을 선택했습니다.) 사진3) DBMS 세부설정 DBMS이름 및 사양 등을 설정합니다. 사진4) DBMS 리전 선택 사진5) 영역가용성 선택 사진6) 옵션설정 사진7) 외부에서 접근 가능한 IP 추가 사진8) IP추가 저는 터미널로 작업하는것을 선호하기 때문에 사진 7,8과 같이 승인 네트워크를 추가합니다. 해당 IP주소는 터미널로 DBMS에 접근이 가능합니다. 사진9) 데이터 보호 옵션 사진9에서는 기본적으로 '삭제 보호 사용 설정'이 되어 있습니다. 하지만 이 글에서는 삭제를 보여주기 위해 해제합니다. 사진10) 데이터 베이스 생성 사진11) 데이터 베이스 접근 사진11과 같이 DB가 생성되면 터미널로 접속이 가능합니다. postgres의 터미널 접속방법에 대해서는 아래 링크를 참고해 주시기 바랍니다. 링크 : PostgreSQL CLI 사진12) 데이터 베이스 삭제 선택 사진13) 데이터 베이스 삭제 절차 사진14) 삭제 완료 사진12 ~ 14까지 데이터베이스 삭제 방법입니다. 다만 사진9에서 삭제보호가 활성화 되어 있다면 비활성화 한다음에 삭제가 가능합니다.

 해당 스키마는 아래 글을 참고로 만들었습니다. 링크 : SQL 관계도 Many to Many(다대다 관계) 해당 링크의 스키마를 작성하기 위해서 아래와 갖이 코드를 작성합니다. // prisma/schema.prisma // This is your Prisma schema file, // learn more about it in the docs: https://pris.ly/d/prisma-schema generator client { provider = "prisma-client-js" } datasource db { provider = "postgresql" url = env ( "DATABASE_URL" ) } model users { id Int @default ( autoincrement ()) @id full_name String @unique created_at DateTime @default ( now ()) @db.Timestamptz ( 3 ) comments joinTable [] } model joinTable { users users @relation ( fields : [userId], references : [id] ) // users와joinTable은 1:N이다 userId Int comment comments @relation ( fields : [commentId], references : [id] ) // comments와 joinTable은 1:N이다. commentId Int @@id ( [userId, commentId] ) // userId, commentId는 Primary key이다 } model comments { id Int @default ( autoincrement ()) @id conten...

  위 사진처럼 커피(coffees)와 향(flavor) 테이블 사이에 Join 테이블이 있습니다. 해당 Join테이블은 한개의 row에 하나의 flavor만 조회가 가능합니다. 만약 2개 이상의 향이 있는 커피를 찾는다면 아래 쿼리문을 사용해야 합니다. -- SQL Query1  SELECT DISTINCT title   FROM coffees JOIN coffees_flavors_flavor ON coffees.id = "coffeesId"                 JOIN flavor ON flavor.id = "flavorId"  WHERE flavor.name = 'choco' OR flavor.name = 'banana'; Query1을 사용하면 Join, OR문을 이용하여 choco 또는 banana가 있는 레코드를 조회합니다. 문제는 choco는 있지만 banana가 없거나 반대의 경우도 모두 조회를 한다는 것입니다. 이를 위해서 GROUP BY를 사용하며 해당 갯수를 count해서 조회해야 합니다. -- SQL Query2   SELECT DISTINCT title, COUNT(*) as count   FROM coffees JOIN coffees_flavors_flavor ON coffees.id = "coffeesId"                 JOIN flavor ON flavor.id = "flavorId"  WHERE flavor.name = 'choco' OR flavor.name = 'banana'  GROUP BY title  HAVING flavor.count = 2; Query2를 사용하면 구할려고 하는 해당 title을 조회할수 있습니다.

 기존에는 'getManager().query('[sql문]')'을 작성해서 DB에서 데이터를 받아왔습니다. 하지만 0.3.0버전 이후 getManager가 Deprecated되서 최신버전의 typeorm에서 다른방법으로 NestJS에서 쿼리문을 사용할 방법을 찾았습니다. // src/users/users.service.ts import { Injectable } from '@nestjs/common' ; import { InjectDataSource , InjectRepository } from '@nestjs/typeorm' ; import { Repository , DataSource } from 'typeorm' ; // DataSource를 사용 import { UserEntity } from './entity/user.entity' ; @ Injectable () export class UsersService { constructor ( @ InjectRepository ( UserEntity ) private readonly userEntity : Repository < UserEntity >, // DataSource를 해당 서비스에 주입한다. @ InjectDataSource () private dataSource : DataSource , ){} async getAllUsers (){ // dataSource에 쿼리문을 입력하면 바로 DB에서 해당 쿼리문의 결과가 리턴된다. return await this . dataSource . query ( `SELECT * FROM users LIMIT` ) } } 'DataSource'를 이용하여 쿼리문을 작성하면...

  NestJS 설치 : nest js - install & Create Project from Linux or Ubuntu NestJS CLI로 TypeORM 및 sqlite설치 npm 모듈은 nestjs에서 TypeORM 으로 sqlite을 사용할수 있는 모듈이다. 자새한 사항은 링크 참조 $ npm i --save sqlite3 typeorm @nestjs/typeorm copy 참고로 사용하는 버전은 다음과 같습니다. @nestjs/typeorm : ^9.0.1 sqlite3 : ^5.0.11 typeorm : ^0.3.9 // src/app.module.ts import { Module } from '@nestjs/common' ; import { TypeOrmModule } from '@nestjs/typeorm' ; import { AppController } from './app.controller' ; import { AppService } from './app.service' ; @ Module ({ imports : [ TypeOrmModule . forRoot ({ type : 'sqlite' , // 사용하는 DBMS종료 database : ` ${ __dirname . split ( '/dist' )[ 0 ] } /test.db` , // DB의 위치. 실제 root경로/test.db entities : [ __dirname + "/**/*.entity{.ts,.js}" ], // 엔티티 위치 synchronize : true // 프로그램이 DB구조 변경 허용(true) }) ], controllers : [ AppController ], providers : [ AppService ], }) export class ...

  Package list update하기 sqlite를 설치하기전 apt업데이트를 진행해야 합니다. $ sudo apt update copy SQLite설치하기 sqlite를 설치합니다. $ sudo apt install sqlite3 copy SQLite버전 확인하기 Ubuntu에 설치한 sqlite의 버전을 확인합니다. $ sqlite3 --version copy 이후 SQLite에 대한 자세한 정보나 사용법은 아래 링크를 참고해 주시기 바랍니다. SQLite Tutorial : https://www.sqlitetutorial.net/ 개발자 디스코드 서버에 초대합니다. 사진) 아래링크 클릭시 커뮤니티로 초대 Comunity Discord Link

 postgres의 컬럼 설정에서는 배열을 넣을수가 있다.  컬럼을 생성할때 위 사진처럼 컬럼 arr을 숫자형 배열로 지정할수 있다 하나의 레코드를 삽입하면 위 사진처럼 id및 arr이 배열로 저장되게 된다. 이때 동일한 배열을 찾을때 WHERE을 이용하여 찾는다. 하지만 모든 배열을 아는것이 아니면 위방법으로 레코드를 조회할수 없다. arr을 텍스트로 변환한 다음 LIKE을 이용하여 조회를 해야 합낟. 이때 arr은 배열에서 문자열로 조회할때만 전환된다. 문자열 배열또한 만들수 있다. 문자열 배열또한 생성, 수정 삭제, 조회가 가능하며 LIKE을 사용할려면 조회할 배열을 text로 변환후 확인해야 한다.

이번 postgres을 사용하다가 해당 쿼리문을 사용할수 없어서 확인하다가 확장 모듈이 필요하다는 것을 알고 기록을 위해 블로그로 작성하였습니다.  테이블을 생성하는 도중 column의 데이터 타입을 uuid_generate_v4()로 설정을 하고 테이블을 생성할려고 할때 아래 사진과 같이 에러가 발생했습니다. 사진1) 에러 상황 위 에러가 발생하면 가장 먼저 해당 extension이 사용 가능하지 확인을 해야 합니다. psql에서 설치 가능한 extension을 확인한다. $ SELECT * FROM pg_available_extensions; copy 사진2) 설치 가능한 extension 사진2에서 가장 마지막에 있는 'uuid-ossp'이 있습니다. 이 확장 모듈이 uuid생성을 도와줍니다. 이제 'uuid-ossp'를 설치해 보도록 하겠습니다. psql에서 extension가 존재하지 않을때 설치. $ CREATE EXTENSION IF NOT EXISTS "extension name"; copy 위 블로그에서는 uuid를 생성할수 있어야 하기 때문에 'uuid-ossp'를 설치하도록 하겠습니다. 해당 sql문은 CREATE EXTENSION IF NOT EXISTS "uuid-ossp" 으로 실행하면 됩니다. 사진3) "uuis-ossp" 설치 사진4) 생성 완료 원하던 쿼리문이 정상적으로 실행 된것을 확인할수 있었습니다.  아래는 실행한 해당 쿼리문 입니다 CREATE TABLE users (   id UUID NOT NULL DEFAULT uuid_generate_v4(),   email VARCHAR NOT NULL,   password VARCHAR NOT NULL,   username VARCHAR NOT NULL,   socialSignUP Boolean NOT NULL DEFAULT false,   soc...

 안녕하세요. 알렉스 입니다. 이번에는 8-1 ~ 8-3까지 배운 이론을 실제 MySQL에 적용해 보도록 하겠습니다. 해당 테이블의 인덱스 상태를 확인합니다. $ SHOW INDEX FROM [Table Name] copy 사진1) 테이블 생성후 인덱스 확인 사진1에서 테이블 을 생성할때 col1은 PRIMARY, col2는 UNIQUE로 지정하였습니다. 사진1에서 나온 인덱스 결과는 col1, col2만 나왔는데 col3는 아무것도 지정하지 않았기 때문입니다.   여기서 col1은 PRIMARY KEY(클러스터형 인덱스)이고 col2는 보조 인덱스 입니다. 사진2) 테이블 Record 생성 사진2에서 테이블의 Record를 생성할때 col1은 순서없이 INSERT하였습니다. 하지만 SELECT 구문을 사용하여 출력할때는 정렬이 되 있는것을 알수 있습니다. 이렇든 PRIMARY로 지정이 되면 MySQL에서 자동 정렬을 수행합니다.   그러나 Record수가 적을때는 쉽게 하지만 만약 Record수가 수만, 수십만이게 되면 DB입장에서 PRIMARY키를 정렬하는데도 상당한 시간이 걸립니다. 따라서 PRIMARY키는 Record가 없는 초기때 생성하는 것이 성능향상에 도움이 많이 됩니다. 사진3) 고유보조인덱스 생성 컬럼에 고유인덱스 생성 $ CREATE UNIQUE INDEX [Key_name] ON [Table Name] ([Column Name]) copy 사진4) 인덱스 삭제 컬럼에 인덱스 삭제(컬럼삭제가 아님) $ DROP INDEX [Key_name] ON [Table Name] copy 사진5) 보조인덱스 추가 사진5에서 인덱스를 설정했음에도 되지 않는 이유는 해당 테이블을 분석하지 않았기 때문입니다.  컬럼에 인덱스(중복가능) 생성 $ CREATE INDEX [Key_name] ON [Table Name] ([Column Name]) copy 해당 테이블의...