Postgresqlバージョン

AWS RDS
Postgresqlバージョン: 12, 13を使っています。

SQL開発方法

UVではORMをあまり使わないです。
主にstoreprocedureのSQLを書きます。

DROP TYPE IF EXISTS type_sep_get_list_administrators CASCADE;
CREATE TYPE type_sep_get_list_administrators AS (
  uuid UUID
  ,mail TEXT
);

-- 管理者 一覧
-- 引数
--   p_uuid : UUID
--   p_mail : メール
-- 戻り値
--   uuid : UUID
--   mail : メール
-- 例外
--   なし
CREATE OR REPLACE FUNCTION get_list_administrators (
  p_uuid UUID DEFAULT NULL
  ,p_mail TEXT DEFAULT NULL
  ,p_limit BIGINT DEFAULT NULL
  ,p_offset BIGINT DEFAULT 0
  ,p_with_count_flag BOOLEAN DEFAULT FALSE
) RETURNS SETOF type_get_list_administrators AS $FUNCTION$
DECLARE
  w_record type_sep_get_list_administrators;
BEGIN
  IF p_with_count_flag IS TRUE THEN
    SELECT
      t1.uuid
      ,t1.mail
    INTO
      w_record
    FROM
      public.administrators AS t1
    LIMIT
      1
    ;
    IF NOT FOUND THEN
      RETURN;
    END IF;

    SELECT
      COUNT(*)::TEXT
    INTO
      w_record.bk
    FROM
      public.administrators AS t1
    WHERE
      (p_uuid IS NULL OR t1.uuid = p_uuid)
      AND (p_mail IS NULL OR t1.mail = p_mail)
    ;
  END IF;

  RETURN QUERY SELECT
    t1.uuid
    ,t1.mail
  FROM
    public.administrators AS t1
  WHERE
    (p_uuid IS NULL OR t1.uuid = p_uuid)
    AND (p_mail IS NULL OR t1.mail = p_mail)
  LIMIT
    p_limit
  OFFSET
    p_offset
  ;

  IF p_with_count_flag IS TRUE THEN
    RETURN NEXT w_record;
  END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;

Webフレームワークでストレスを呼びます。 Rails

class Admin < ApplicationRecord
  include SerializeJsonColumn

  self.primary_key = :uuid

  def self.get_list_administrators(params)
    sql = <<-SQL
      SELECT
        uuid
        ,mail
      FROM
        get_list_administrators(
          p_limit := 100
          ,p_offset := 0
        );
    SQL
    find_one_by_sql([sql, params])
  end
end

Webフレームワークでストレスを呼びます。 Rust

use crate::{error::SepError, external_interfaces::postgresql::*, PgClient};
use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use uuid::Uuid;

#[derive(Serialize, Deserialize, Debug, Clone)]
pub struct DbResult {
    pub uuid: Uuid,
    pub mail: String,
}

impl DbResult {
    pub fn to_json(&self) -> serde_json::Value {
        let mut json = serde_json::to_value(self).unwrap();
        json
    }
}

#[derive(Serialize, Deserialize, Debug, Clone, Default)]
pub struct DbParameter {
    pub uuid: Option<Uuid>,
    pub mail: Option<String>,
    pub limit: Option<i64>,
    pub offset: Option<i64>,
    pub with_count_flag: Option<bool>,
}

#[derive(Serialize, Deserialize, Debug, Clone, Default)]
pub struct QueryParameter {
    pub uuid: Option<Uuid>,
    pub mail: Option<String>,
    pub limit: Option<i64>,
    pub offset: Option<i64>,
}

impl QueryParameter {
    pub fn to_params(self, with_count_flag: Option<bool>) -> DbParameter {
        DbParameter {
            limit: self.limit,
            offset: self.offset,
            with_count_flag,
            uuid: self.uuid,
            mail: self.mail,
        }
    }
}

pub async fn execute(conn: &PgClient, params: DbParameter) -> Result<Rows<DbResult>, SepError> {
    query(
        conn,
        r#"
            SELECT
                to_json(t1.*)
            FROM
                get_list_administrators(
                    p_uuid := $1
                    ,p_mail := $2
                    ,p_limit := $3
                    ,p_offset := $4
                    ,p_with_count_flag := $5
                ) AS t1
            "#,
        &[
            &params.uuid,
            &params.mail,
            &params.limit,
            &params.offset,
            &params.with_count_flag,
        ],
    )
    .await
}

pub async fn execute_with_count(
    conn: &PgClient,
    params: DbParameter,
) -> Result<(Vec<serde_json::Value>, i64), SepError> {
    let mut list = execute(conn, params).await?.as_list()?;
    let count = if list.is_empty() {
        0
    } else {
        list.pop().unwrap().bk.unwrap().parse::<i64>().unwrap()
    };
    let result = list
        .iter()
        .map(|it| it.to_json())
        .collect::<Vec<serde_json::Value>>();
    Ok((result, count))
}

テーブル作成・自動生成ファイル

VS Code の UVのERDツールを使います。: https://marketplace.visualstudio.com/items?itemName=uniquevision.erd-tool
ERDをJSON形でテーブルを定義します。

  {
      "lname": "メディア",
      "pname": "media",
      "columns": [
        {
          "domain": "UUID",
          "pk": true,
          "default": "gen_random_uuid()"
        },
        {
          "lname": "ファイル",
          "pname": "file",
          "domain": "名前"
        },
        {
          "lname": "MIMEタイプ",
          "pname": "mime_type",
          "domain": "コード"
        },
        {
          "lname": "バイト",
          "pname": "byte",
          "domain": "数"
        },
        {
          "lname": "S3パス",
          "pname": "s3_path",
          "domain": "コード"
        }
      ]
    }

そのあとに、ERDツールは自動でテーブル作成SQLを生成してくれます。

DROP TABLE IF EXISTS source.media CASCADE;

CREATE TABLE source.media (
  uuid UUID NOT NULL DEFAULT gen_random_uuid() -- UUID
  ,file_nm TEXT NOT NULL DEFAULT '' -- ファイル名
  ,mime_type_code TEXT NOT NULL DEFAULT '' -- MIMEタイプコード
  ,byte_count BIGINT NOT NULL DEFAULT 0 -- バイト数
  ,s3_path_code TEXT NOT NULL DEFAULT '' -- S3パスコード
  ,created_uuid UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
  ,updated_uuid UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
  ,deleted_uuid UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
  ,created_at TIMESTAMPTZ NOT NULL
  ,updated_at TIMESTAMPTZ NOT NULL
  ,deleted_at TIMESTAMPTZ
  ,created_pg TEXT NOT NULL DEFAULT ''
  ,updated_pg TEXT NOT NULL DEFAULT ''
  ,deleted_pg TEXT NOT NULL DEFAULT ''
  ,bk TEXT
  ,PRIMARY KEY(uuid)
);

CREATE TABLE public.media (
  LIKE source.media INCLUDING ALL
) INHERITS (source.media);


CREATE TABLE garbage.media (
  LIKE source.media INCLUDING ALL
) INHERITS (source.media);

ストレスの自動生成
- レコード作成ストプロ
- レコード編集ストプロ
- レコード削除
- レコード取得
エラーハンドリングの自動生成

        {
          "lname": "データなし",
          "pname": "not_found",
          "code": "04",
          "dbCode": "U0003",
          "message": "データが見つかりませんでした。",
          "warning": false
        }

--  データなし
CREATE OR REPLACE FUNCTION RAISE_RESULT_CODE_NOT_FOUND(
  p_invalid_flag BOOLEAN DEFAULT TRUE
  ,p_message TEXT DEFAULT ''
) RETURNS VOID AS $FUNCTION$
BEGIN
  IF p_invalid_flag THEN
  	RAISE SQLSTATE 'U0003' USING MESSAGE = p_message;
  END IF;
END;
$FUNCTION$ LANGUAGE plpgsql;

SQLチェック: Language Server

Vs codeのExtensionsを使う: https://marketplace.visualstudio.com/items?itemName=uniquevision.vscode-plpgsql-lsp
https://github.com/UniqueVision/plpgsql-lsp

DB移行・Migration Tool

移行内容
- テーブル編集
- ストプロ更新
- DBのデータ変更
https://github.com/UniqueVision/db-migration-tool
UVが作ったツールを使います。

その他

バッチ: Progresql cron : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html
詳細設計のとき、SQLを書きます。