Postgresql과 GraphQL을 연결시켜주는 PostGraphile에 대해 알아봅니다.

 

 

 

1. 소개

 

GraphQL은 서버 측 데이터와 통신하기 위한 환상적인 솔루션입니다. GraphQL을 통해 개발자는 서버와 상호 작용하는 사람들이 서버에서 필요한 데이터의 정확한 구조를 지정할 수 있도록 하여 데이터의 오버 페치 및 언더 페치 문제를 해결하는 빠르고 안정적인 API를 만들 수 있습니다. 이는 GraphQL을 사용해 애플리케이션을 개발하는 개발자들에게 더 나은 개발자 경험을 제공하며 엔드 유저에게는 더욱 빠른 애플리케이션을 제공하는 것을 의미합니다.

 

과거에는 PostGraphQL로 알려졌던 PostGraphile은 개발자가 PostgreSQL 데이터베이스에 데이터를 저장하는데 필요한  모든 기능을 갖춘 GraphQL 서버를 신속하게 구성할 수 있도록 해주며 PostgreSQL과 GraphQL 두 기술을 결합하는 데 큰 역할을 합니다. PostGraphile은 데이터베이스 기반(DB-driven) 개발을 활용하여 Postgres 데이터베이스 스키마에서 Graphql 서버를 생성 및 업데이트하여 스키마 변경 사항을 자동으로 감지하고 이 변경사항에 따라 서버를 업데이트합니다.

 

그들의 말에 따르면 :

PostgreSQL에는 이미 놀라운 인증 및 관계 인프라가 있습니다. 왜 커스텀 API에서 해당 로직을 복제해야 합니까?

 

PostGraphile은 개발자가 제품에 집중할 수 있도록 성능 및 표준 호환 GraphQL API 계층 생성을 처리합니다. 또한 개발 시간이 크게 단축됩니다.

 

게다가 PostGraphile은 다양한 방식으로 기능을 확장하는 데 도움이 되는 여러 커뮤니티 개발 플러그인을 갖춘 강력한 플러그인 시스템을 자랑합니다.

 

이 글에서는 PostGraphile을 사용하여 몇 분 만에 모든 기능을 갖춘 서버를 설치하고 실행할 수 있는 방법을 살펴보겠습니다.

 

 

 

2. PostGraphile의 사용법

 

PostGraphile은 세 가지 주요 방법으로 사용할 수 있습니다.

 

PostGraphile CLI: 터미널에서 PostGraphile API를 바로 시작하는 가장 쉽고 빠른 방법입니다. 


미들웨어: postgraphile 패키지에서 가져온 PostGraphile 인스턴스를 NodeJS 서버에 마운트 합니다.

 

도커: PostGraphile을 도커 이미지로 가져와 CLI 옵션으로 Docker 컨테이너에 전달하여 발생합니다.

 

이 글에서는 CLI를 사용하는 방법에 대해서 알아보도록 하겠습니다.

 

 

 

3. PostGraphile 시작하기.


PostGraphile을 사용하려면 Node.js v8.6 이상을 설치해야 합니다. 아직 설치하지 않은 경우 Node 웹 사이트에서 원하는 버전을 찾아 설치하시기 바랍니다.

 

PostgreSQL v9.6.0 이상 버전이 필요합니다. PostgreSQL 다운로드 페이지에서 찾을 수 있습니다.

 

 

3.1. DB 설치하기.

 

이 글에선 별도로 Postgresql DB를 설치하는 방법에 대해 다루지 않습니다. PostgreSql을 설치하는 방법은 이 글에서 확인해 주시기 바랍니다.

 

 

 

3.2. PostGraphile 설치하기.

 

다음 명령어로 npm을 통해 PostGraphile을 설치합니다.

 

> npm i postgraphile

 

 

 

3.3 Express와 연동하기.

 

이제 실제 Express서버에 Postgraphile을 연동해 봅시다.

 

const express = require("express");
const { postgraphile } = require("postgraphile");
const vals = require('./const');
const app = express();

const connectionString = `postgres://${vals.user}:${vals.pass}@${vals.host}:${vals.port}/${vals.db}`

app.use(
  postgraphile(
    process.env.DATABASE_URL || connectionString,
    "public",
    {
      watchPg: true,
      graphiql: true,
      enhanceGraphiql: true,
    }
  )
);

app.listen(process.env.PORT || 3300);

 

** connectionString에 필요한 변수는 const.js에 별도로 정의해 사용하고 있습니다.

 

 

 

3.4 Graphiql로 직접 데이터 조회해보기.

 

위의 코드를 실행시키고 graphiql 페이지로 이동한 뒤 Explorer 버튼을 눌러봅시다.

 

 

좌측에 우리가 만들지 않은 여러 옵션이 보이시나요?

 

우리는 직접 리졸버를 만들지 않았습니다만 이미 우리가 만들었던 테이블을 쿼리 할 수 있는 옵션이 존재합니다. Postgraphile에서 알아서 해줍니다!

 

이제 미리 만들어둔 users 테이블의 모든 데이터를 조회해 봅시다. 직접 타자 칠 필요 없이 익스플로러에서 클릭만으로 쿼리를 만들 수 있습니다.

 

 

단지 allUsers->nodes를 클릭한 후 컬럼을 순서에 맞게 클릭했을 뿐입니다. 여기서 nodeId라는 것이 보일 텐데 이 id는 postgraphile내에서 node마다 유지되는 고유한 ID입니다. 

 

 

 

4. 마무리

 

이렇듯 postgraphile을 사용하면 별도의 추가 작업 없이 postgresql에 연결해주는 것만으로도 바로 graphql을 사용할 수 있습니다. 다음에는 직접 클라이언트에서 호출해 사용하는 방법에 대해 알아보도록 하겠습니다.

 

 

 

 

 

 

 

 

 

본격적인 postgresql 사용에 앞서 Database와 유저를 생성하고 pgAdmin4를 통해 외부 네트워크에서 접속을 시도해 봅니다.

 

 

 

1.  DATABASE 생성

 

이제 외부 접속을 허용했으니 사용할 DB를 생성합니다. 다음 명령어를 통해 Postgresql Shell을 실행합니다.

 

$ sudo su postgres

$ psql

 

 

이제 사용할 DB를 생성합니다. 

 

CREATE DATABASE smoh;

 

 

 

 

2. USER 생성

 

그다음 유저를 생성하고 권한을 부여합니다. 개발용 계정으로 사용할 예정이기 때문에 SUPAERUSER 권한을 줬습니다.

 

CREATE USER {user_name} WITH PASSWORD '{user_password}';

 

ALTER USER {user_name} WITH SUPERUSER;

 

 

만약 해당 유저에게 특정 권한만 주고 싶다면 다음 명령어를 사용하시면 됩니다.

 

GRANT {permissions} ON DATABASE {db_name} TO {user_name};

 

권한 리스트는 여기에서 자세히 알아볼 수 있습니다.

 

 

** 기본 계정 설정 변경.

기본 계정인 postgres의 기본 암호는 없습니다. 만약 새로 계정을 생성하지 않고 이 기본 계정을 사용하려면 암호를 변경해야만 합니다. 

 

기본 계정의 암호는 다음과 같은 SQL로 변경할 수 있습니다.

 

ALTER USER postgres WITH PASSWORD '{new_pass}';

 

 

 

3. pgAdmin 설치 및 사용

 

Postgresql을 위해 pgAdmin을 사용합니다. 여기서 OS 및 버전을 골라 다운로드하여 설치합니다.

 

실행하면 다음과 같은 화면이 나올 텐데 먼저 기본 암호를 설정해 줍니다.

 

 

암호 설정 후 서버를 추가합니다.

 

 

이후 서버 접속정보에 위에서 생성한 계정 정보 및 DB 정보를 입력하면 정상 사용 가능합니다.

 

 

 

 

 

 

외부 네트워크에서 postgresql에 접속할 수 있도록 하는 방법에 대해 알아봅니다.

 

 

 

1. postgresql.conf

 

Postgresql은 기본적으로 외부에서의 접속을 막습니다. 따라서 외부에서 접속할 수 있도록 설정 변경이 필요합니다.

해당 설정은 postgresql.conf 파일에 있습니다. 해당 파일의 위치는 다음과 같습니다.

 

$ cat /etc/postgresql/12/main/postgresql.conf

 

 

이제 여기서 "listen_address"값을 수정해야 합니다. "CONNECTIONS AND AUTHENTICATION"섹션에 있습니다.

이 값을 vi로 수정해 줍시다. 만약 기본 포트를 사용하고 싶지 않다면 여기서 수정해 줍니다.

 

 

코멘트에 쓰여있는 대로 postgresql을 재시작해줍시다.

 

$ sudo service postgresql restart

 

 

 

2. pg_hba.conf

 

이 외에도 pg_hba.conf 파일 또한 수정해 줘야 합니다. 이 파일은 다음 위치에 존재합니다.

 

 

여기서 "IPv4 local connection"을 찾아 127.0.0.1/32가 아닌 모든 ip에서 접속할 수 있도록 수정합니다.

 

다시 한번 postgresql 서비스를 재시작합니다.

 

$ sudo service postgresql restart

 

 

 

 

 

Ubuntu OS에 Postgresql을 설치하는 방법에 대해서 알아봅니다.

 

 

 

1. Ubuntu OS 준비.

 

Ubuntu OS를 미리 준비합니다. 이 글에서는 ubuntu server 18.04.3 버전을 사용합니다. 

Postgresql 공식 홈페이지의 설명에 따르면 Postgresql은 Ubuntu 16.04, 18.04 이상의 버전을 요구합니다.

우분투 설치 방법에 대해서는 별도로 다루지 않습니다.

 

 

 

2. Postgresql 설치 버전 확인.

 

우선 다음 명령어를 통해 현재 사용 가능한 postgresql 버전을 확인합니다.

 

$ apt show postgresql

 

 

버전 정보가 보이시나요? 현재 posrgresql로 설치하면 postgresql 10 버전이 설치됩니다.

만약 별다른 추가 작업 없이 그냥 pgsql10 버전을 설치하고 싶다면 다음 명령어로 바로 설치할 수 있습니다.

 

$ sudo apt install postgresql postgresql-contrib

 

하지만 아래의 Postgresql 공식 홈패이지 우분투 배포 정보 페이지를 보면 글 작성 시점에 10 버전보다 최신 버전이 이미 배포되어 있습니다.

 

 

이제 postgresql-11의 apt정보를 봐 볼까요?

 

$ sudo apt show postgresql-11

 

 

안타깝게도 postgresql 10 이후의 버전은 별도의 리포지토리 등록 작업이 필요합니다.

 

 

 

3. Postgresql 설치를 위한 리포지토리 등록.

 

우선 pgdg.list 파일을 만든 후 다음 내용을 추가해 줍니다.

 

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

 

 

 

그런 후 다음 명령어를 통해 GPG키를 추가합니다.

 

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - 

 

마지막으로 업테이트를 수행해 줍니다.

 

$ sudo apt-get update

 

 

이제 진짜 postgresql의 최신 버전을 사용할 수 있는지 확인해 봅시다.

 

$ sudo apt show postgresql

 

 

 

4. Postgresql 설치.

 

이제 현재 사용할 수 있는 최신버전의 postgresql-12를 설치해 보도록 합니다.

 

다음 명령어를 통해 postgresql을 설치합시다.

 

$ sudo apt-get install postgresql postgresql-contrib

 

 

** postgresql-contrib이란?

postgresql-contrib 또는 contrib 패키지는 핵심 PostgreSQL 패키지의 일부가 아닌 일부 추가 유틸리티와 기능으로 구성됩니다. 대부분의 경우 PostgreSQL 코어와 함께 contrib 패키지를 설치하는 것이 좋습니다.

 

이제 다음 명령어로 postgresql이 정상적으로 설치, 동작하는 것을 확인할 수 있습니다.

 

$ service postgresql status

 

 

 

 

 

ORACLE에서 DECODE 함수와 CASE ... THEN 구문의 사용법 및 미묘한 차이점에 대해 알아봅니다.

 

 

 

1. 사용 목적

 

두 기능 모두 사용 목적은 조건문입니다. 두 기능 모두 일반적인 프로그래밍에서의 if/else 기능을 하고 있습니다.

 

 

 

2. DECODE 함수

 

DECODE는 다음과 같이 사용합니다.

 

DECODE( {대상}, {비교 값 1}, {결괏값 1}, {비교 값 2}, {결괏값 2}, ... , {비교 값 N}, {결괏값 N}, {나머지 값} )

 

이렇게만 두고 보면 알기 어려우니 예시를 통해 알아봅시다.

 

다음과 같은 테이블이 있을 때 DECODE 사용 예시와 그에 따른 결괏값 입니다.

 

 

예시 쿼리)

SELECT USER_KEY
	, USER_EMAIL
    , DECODE(USER_KEY, 1, '첫째 유저', 2, '둘째 유저', 3, '셋째 유저', '나머지 유저') USER_DESC 
FROM USERS;

 

결과)

 

 

3. CASE ... THEN 구문

 

CASE ... THEN 구문은 다음과 같이 사용합니다.

 

CASE
    WHEN {조건 1} THEN {결괏값 1}
    WHEN {조건 2} THEN {결괏값 2}
    ...
    WHEN {조건 N} THEN {결괏값 N}
    ELSE {나머지 값}
END

 

이렇게만 두고 보면 알기 어려우니 예시를 통해 알아봅시다.

 

다음과 같은 테이블이 있을 때 CASE 구문의 사용 예시와 그에 따른 결괏값입니다.

 

 

예시 쿼리)

SELECT USER_KEY     
	, USER_EMAIL
    , (CASE
    	WHEN USER_KEY = 1 THEN '첫째 유저'
        WHEN USER_KEY = 2 THEN '둘째 유저'
        WHEN USER_KEY = 3 THEN '셋째 유저'
        ELSE '나머지 유저'
       END) USER_DESC 
FROM USERS;    

 

결과)

 

 

4. 차이점

 

앞서 두 가지를 비교하면서 계속 언급했던 것을 보시면 가장 먼저 차이점이 와 닿을 것입니다. 

 

먼저 DECODE는 "함수"란 것이고 CASE는 "구문"이란것 입니다. 

따라서 DECODE는 SQL에서 사용할 수 없습니다. PL/SQL에서만 사용할 수 있는 함수입니다.

CASE구문은 STATEMENT이므로 SQL에서도 사용 가능합니다.

 

** SQL과 PL/SQL

SQL과 PL/SQL은 다릅니다. 

SQL은 RDBMS에서 데이터에 접근하기 위해 사용하는 "표준언어"입니다.

PL/SQL은 SQL을 사용하여 프로그램을 작성할 수 있게 "확장"해 놓은 오라클의 Procedual Language입니다.

 

 

 

 

 

 

 

1. 생성된 유저 확인

>USE mysql;

>SELECT host, user FROM user;

 

2. 유저 생성

>CREATE USER 'userNameHere'@'accessRange' IDENTIFIED BY 'userPasswordHere';

** Access Range

> '%' 모든 ip주소에 대하여 접근 허용

> 'localhost' 로컬에서의 접근만 허용

> '192.168.0.%' 특정 ip주소에 대하여 접근 허용

 

3. 권한 부여

>GRANT ALL PRIVILEGES ON databaseNameHere.* TO 'userNameHere'@'accessRange';

 

4. 권한 새로고침

>FLUSH PRIVILEGES;

 

References: [ ORACLE 예외처리 개념, 종류, 사용법 - (8) ]



Contents: Predefined ORACLE Exception


- ACCESS_INTO_NULL 

    : 정의되지 않은 오브젝트 속성에 값을 할당하고자 했을 때 발생하는 예외.                    


- CASE_NOT_FOUND 

    : CASE문의 when절에 해당되는 조건이 없고 else절도 없을 경우 발생


- COLLECTION_IS_NULL 

    : 선언되지 않은 컬렉션(nested table, varray)에 존재하는 메서드 이외의 메서드를 사용했을 때 발생되는 예외.


- CURSOR_ALREADY_OPEN 

    : 이미 열려진 커서를 열려고 시도 했을 때 발생하는 예외


- DUP_VAL_ON_INDEX 

    : 유일인덱스에 중복값을 입력햇을 때 발생하는 예외.


- INVALID_CURSOR 

    : 잘못된 커서 조작이 샐행될 때 발생되는 예외.


- INVALID_NUMBER 

    : 문자를 숫자로의 변환 시 실패가 될 때 발생하는 예외.


- LOGIN_DENIED 

    : 잘못된 사용자명이나 암호로 로그인시도시 발생하는 예외.


- NO_DATA_FOUND 

    : PL/SQL Select문이 한 건도 리턴하지 못하는 경우 발생하는 예외.


- NOT_LOGGED ON 

    : 접속되지 않은 상태에서 데이터베이스에 대한 요청이 PL/SQL 프로그램으로 실행된 경우 발생되는 예외.


- PROGRAM_ERROR 

    : PL/SQL이 내부적인 문제를 가지고 있는 경우 발생되는 예외.


- ROWTYPE_MISMATCH 

    : 할당문에서 호스트 커서 변수와 PL/SQL 커서 변수의 데이터 형이 불일치할 때 발생되는 예외


- STORAGE_ERROR 

    : PL/SQL이 실행될 때 메모리가 부족하거나 메모리상에 문제가 일어났을 대 발생하는 예외.


- SUBSCRIPT_BEYOND_COUNT 

    : 컬렉션의 요소 갯수보다 더 큰 첨자 값으로 참조한 경우 발생


- SUBSCRIPT_OUTSIDE_LIMIT 

    : 컬렉션의 첨자 한계를 벗어난 참조가 일어났을 때 발생


- SYS_INVALID_ROWD 

    : 문자열을 ROWID로 변환할 때 무효한 문자열의 표현일 경우 발생되는 예외.


- TIMEOUT_ON_RESOURCE 

    : 자원에 대한 대기시간이 초과했을 때 발생하는 예외.


- TOO_MANY_ROWS 

    : PL/SQL select문이 두건이상의 행을 리턴햇을 때 발생되는 예외.


- VALUE_ERROR 

    : 산술,변환,절삭 크기 제약에 에러가 생겼을 때 발생되는 예외.


- ZERO_DIVIDE

    : 0으로 나누려 했을 때 발생하는 예외.



Example

PROCEDURE TEST

...

EXCEPTION

WHEN NO_DATA_FOUND THEN

...

WHEN OTHERS THEN

...

...

END TEST;






Reference: [Oracle|오라클] 여러 행(ROW)을 하나의 컬럼(COLUMN)으로 합치기 (WM_CONCAT)




Query


SELECT COL1, WM_CONCAT(COL2), WM_CONCAT(COL3), COL4

FROM TABLE
WHERE

SOME CONDITION

GROUP BY COL1, COL4



* 검색 결과에서 COL2와 COL3에 의해 각각 나눠진 Row가 하나로 통합되어 나옴.

* 데이터의 구분자는 (,)로 자동 설정됨.









TestCondition의 값에 따라 where절 조건문 동적 생성.




SELECT ...

FROM ...

WHERE 1=1

<isNotEmpty property="TestCondition" prepend="AND">

<isEqual property="TestCondition" compareValue="A">

--TestCondition의 값이 A일 때 처리

</isEqual>

<isEqual property="TestCondition" compareValue="B">

--TestCondition의 값이 B일 때 처리

</isEqual>

</isNotEmpty>


References : 외래키 조회





1. Query


SELECT uc.constraint_name, uc.table_name, ucc.column_name, uc.constraint_type, uc.r_constraint_name

FROM user_constraints uc, user_cons_columns ucc

WHERE uc.constraint_name = ucc.constraint_name;




2. Constraint_Type


 - P : Primary Key

 - R : Foreign Key

 - U : Unique

 - C : Not Null, Check




+ Recent posts