supabase の Postgres に直接接続する

- [あるWebサービスの開発メモ・目次ページに戻る](https://qiita.com/ishi32/items/152a1ecf7eeee7a8fb57)

## やりたいこと

supabase におけるデータベース操作は [app.supabase.com](https://app.supabase.com/) の　SQLEditor を使っていました。これはこれでよいのですが、psql でデータベースを直接操作した方が、圧倒的に生産性が向上しそうなので、この psql によるデータベース接続を目指してみます

## 方式検討

- DockerHub の PotgreSQL 公式イメージから、ローカルPCの　psql 実行環境を構築する
- ローカルPC から PostgreSQL 間は SSL 接続する

## 公式の情報

- [接続](https://www.supabase.jp/docs/guides/database/connecting-to-postgres#ssl接続)

## docker-compose

### docker-compose.yml

```
% pwd
/Users/c22/Dev/psql
% ls
docker-compose.yml
% cat docker-compose.yml
version: '3'

services:
  db:
    image: postgres:14
    container_name: 'psql14'
    hostname: 'psql14'
    ports:
      - '5432:5432'
    stdin_open: true
    tty: true
    volumes:
      - ./db-store:/var/lib/postgresql/data
      - ./Download:/var/lib/postgresql/Download
    environment:
      - POSTGRES_PASSWORD=hogehoge
volumes:
  db-store:

%
```

- ./db-store でデータを永続化しようとしています
- ./Download　に サーバールート証明書を配置するつもりです

### docker-compose up

```
% docker-compose up
[+] Running 1/1
 ⠿ Container psql14  Created                                                                                                                                                                                                           0.1s
Attaching to psql14
psql14  | The files belonging to this database system will be owned by user "postgres".

(snip)
```

### コンテナ接続

```
c22@c22noMac-mini psql % docker container ls -a
CONTAINER ID   IMAGE            COMMAND                  CREATED        STATUS                    PORTS                    NAMES
3ab2d28beb96   ruby:3.1.3       "irb"                    8 days ago     Exited (1) 31 hours ago                            ruby313
b37079e2e92c   centos:centos7   "/bin/bash"              8 days ago     Exited (137) 6 days ago                            centos7
2631edc32829   node:latest      "docker-entrypoint.s…"   11 days ago    Up 7 days                 0.0.0.0:3000->3000/tcp   unrwebpagemui
09e86eb0f09b   node:latest      "docker-entrypoint.s…"   3 months ago   Up 3 weeks                0.0.0.0:3006->3000/tcp   sirokuro-dev6
c22@c22noMac-mini psql % docker container ls
CONTAINER ID   IMAGE         COMMAND                  CREATED          STATUS          PORTS                    NAMES
f499111d41f4   postgres:14   "docker-entrypoint.s…"   29 seconds ago   Up 29 seconds   0.0.0.0:5432->5432/tcp   psql14
2631edc32829   node:latest   "docker-entrypoint.s…"   11 days ago      Up 7 days       0.0.0.0:3000->3000/tcp   unrwebpagemui
09e86eb0f09b   node:latest   "docker-entrypoint.s…"   3 months ago     Up 3 weeks      0.0.0.0:3006->3000/tcp   sirokuro-dev6
c22@c22noMac-mini psql % docker container exec -it psql14 /bin/bash
root@psql14:/# pwd
/
root@psql14:/# ls
bin  boot  dev	docker-entrypoint-initdb.d  etc  home  lib  media  mnt	opt  proc  root  run  sbin  srv  sys  tmp  usr	var
root@psql14:/#
```

### vim インストール

- Linux ディストリビューション確認

```
root@psql14:~# cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
NAME="Debian GNU/Linux"
VERSION_ID="11"
VERSION="11 (bullseye)"
VERSION_CODENAME=bullseye
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"
root@psql14:~#
```

- インストール (スーパーユーザにて)
  - apt-get update
  - apt-get install vim

## 接続情報とサーバールート証明書を取得

- [app.supabase.com](https://app.supabase.com/)
- (左下) [ProjectSettings] > [Database]
  - Connection info　の Host が接続文字列になるのでメモしておく
- SSL Connection の [Download Certificate]
  - 証明書がダウンロードされる

## クライアント接続

### 証明書を配置する

(ローカルPCのターミナル)

```
% pwd
/Users/c22/Dev/psql/Download
%
```

ここにダウンロードした証明書を prod-ca-2021.cer というファイル名で配置する

### DB接続

(コンテナの /bin/bash で su - postgres しています)

```
postgres@psql14:~/Download$ pwd
/var/lib/postgresql/Download
postgres@psql14:~/Download$ psql "sslmode=verify-full sslrootcert=prod-ca-2021.cer host=db.cyznbgxxxxxxxxxxxxxx.supabase.co dbname=postgres user=postgres"
Password for user postgres:
psql (14.6 (Debian 14.6-1.pgdg110+1), server 14.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>
```

## DB操作

### DB一覧

```
postgres=> \l
                                 List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |      Access privileges
-----------+----------+----------+---------+---------+-----------------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres               +
           |          |          |         |         | postgres=CTc/postgres      +
           |          |          |         |         | dashboard_user=CTc/postgres
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres                +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres                +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)

postgres=> 
```

### DB切替

```
postgres=> \c postgres
psql (14.6 (Debian 14.6-1.pgdg110+1), server 14.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "postgres" as user "postgres".
postgres=> 
```

### テーブル一覧

```
postgres=> \dt;
              List of relations
 Schema |   Name    | Type  |     Owner
--------+-----------+-------+----------------
 public | cats      | table | supabase_admin
 public | comments  | table | supabase_admin
 public | commentsg | table | supabase_admin
 public | posts     | table | supabase_admin
 public | profiles  | table | supabase_admin
 public | replies   | table | supabase_admin
 public | votes     | table | supabase_admin
(7 rows)

postgres=>
```

### テーブル構造の表示

### select

## まとめ

DockerHub の PostgreSQL 公式イメージのおかげで、macOS 上で容易に supabase.com の Postgres にリモート接続できるようになった。