postgresql与typeorm使用经验分享

在创建postgresql的表时，定义数组等字段时，根据数组中的元素的类型可以在相应类型后添加一个[]就可以了。但是在定义数组对象或者json时，则需要json或者jsonb
eg:

#!/bin/bash

psql -U ${POSTGRES_USERNAME} -d ${POSTGRES_DBNAME} << "EOSQL"

create table page(
    page_id varchar(36) primary key not null,
    page_name varchar(255) not null,
    status smallint not null default 0,
    public_status smallint,
    url varchar(255) not null,
    preview_img varchar(255) not null,
    creater_id varchar(36) not null,
    category_ids varchar(255)[] not null,
    tags varchar(255)[],
    widgets json,
    medias varchar(255)[],
    contents varchar(255)[],
    css_url varchar(255),
    layout json,
    create_at timestamptz,
    update_at timestamptz
)
;

EOSQL

当然在进行创建的时候，要把sql的格式进行相应的调整。

eg:sql

INSERT INTO page (page_id,page_name,status,public_status,url,preview_img,creater_id,category_ids,tags,widgets,medias,contents,css_url,layout,create_at,update_at) VALUES ('0008',
     'test4',
     0,
     0,
     '/test/testurl',
     'test/img',
     '0',
     '{"0","1","2"}',
     '{"test","test1"}',
     '[{"id":"1","name":"test"}]'::json,
     '{"0001","0002"}',
     '{"0034","0056"}',
     'testCssUrl',
     '{"test": "test"}',
     '2018/12/24',
     '2018/12/24')

eg: typeorm调用时，手写query，因为数组和json在插入时的特殊性，所以要自己写如create语句，createQueryBulider无法满足需要：

import { EntityManager, EntityRepository } from 'typeorm';
import PageRequest from '../../../application/request/PageRequest'
@EntityRepository()
class PageQueryRepository {

  constructor(private manager: EntityManager) {}

  async createPage(pageData: PageRequest): Promise<void> {
    await
    this
    .manager
    .query(
      `INSERT INTO page (
        page_id,
        page_name,
        status,
        public_status,
        url,
        preview_img,
        creater_id,
        category_ids,
        tags,
        widgets,
        medias,
        contents,
        css_url,
        layout,
        create_at,
        update_at) VALUES (
          $1,$2, $3, $4, $5, $6, $7, $8, $9, $10::json, $11, $12, $13, $14, $15, $16)`,
      [
        pageData.pageId,
        pageData.pageName,
        pageData.status,
        pageData.publicStatus,
        pageData.url,
        pageData.previewImg,
        pageData.createrId,
        pageData.categoryIds,
        pageData.tags,
        pageData.widgets,
        pageData.medias,
        pageData.contents,
        pageData.cssUrl,
        pageData.layout,
        pageData.createAt,
        pageData.updateAt
      ],
    );
    return;
  }

  async updatePage(pageData: PageRequest, pageId:string): Promise<PageRequest> {
    let countParams: number = 2;
    const params: any[] = [pageId];
    let sql1: string = `UPDATE page SET`;
    let sql2: string = `page_id = $1`;
    if (pageData.pageName) {
      sql1 = sql1 + ' page_name = $' + countParams + ',';
      params.push(pageData.pageName);
      countParams = countParams + 1;
    }
    if (pageData.status) {
      sql1 = sql1 + ' status = $' + countParams + ',';
      params.push(pageData.status);
      countParams = countParams + 1;
    }
    if (pageData.publicStatus) {
      sql1 = sql1 + ' public_status = $' + countParams + ',';
      params.push(pageData.publicStatus);
      countParams = countParams + 1;
    }
    if (pageData.url) {
      sql1 = sql1 + ' url = $' + countParams + ',';
      params.push(pageData.url);
      countParams = countParams + 1;
    }
    if (pageData.previewImg) {
      sql1 = sql1 + ' preview_img = $' + countParams + ',';
      params.push(pageData.previewImg);
      countParams = countParams + 1;
    }
    if (pageData.createrId) {
      sql1 = sql1 + ' creater_id = $' + countParams + ',';
      params.push(pageData.createrId);
      countParams = countParams + 1;
    }
    if (pageData.categoryIds) {
      sql1 = sql1 + ' category_ids = $' + countParams + ',';
      params.push(pageData.categoryIds);
      countParams = countParams + 1;
    }
    if (pageData.tags) {
      sql1 = sql1 + ' tags = $' + countParams + ',';
      params.push(pageData.tags);
      countParams = countParams + 1;
    }
    if (pageData.widgets) {
      sql1 = sql1 + ' widgets = $' + countParams + ',';
      params.push(pageData.widgets);
      countParams = countParams + 1;
    }
    if (pageData.medias) {
      sql1 = sql1 + ' medias = $' + countParams + ',';
      params.push(pageData.medias);
      countParams = countParams + 1;
    }
    if (pageData.contents) {
      sql1 = sql1 + ' contents = $' + countParams + ',';
      params.push(pageData.contents);
      countParams = countParams + 1;
    }
    if (pageData.cssUrl) {
      sql1 = sql1 + ' css_url = $' + countParams + ',';
      params.push(pageData.cssUrl);
      countParams = countParams + 1;
    }
    if (pageData.layout) {
      sql1 = sql1 + ' layout = $' + countParams + ',';
      params.push(pageData.layout);
      countParams = countParams + 1;
    }
    if (pageData.createAt) {
      sql1 = sql1 + ' create_at = $' + countParams + ',';
      params.push(pageData.createAt);
      countParams = countParams + 1;
    }
    if (pageData.updateAt) {
      sql1 = sql1 + ' update_at = $' + countParams + ',';
      params.push(pageData.updateAt);
      countParams = countParams + 1;
    }
    sql1 = sql1.slice(0, -1)
    let sql: string = sql1 + ' WHERE ' + sql2;
    return await this.manager.query(sql, params);
  }
}

Object.seal(PageQueryRepository);
export default PageQueryRepository;