typescript - "schemaName"。 "tableName"生成 "schemaName.tableName"- Sequelize - Postgres

Question

我是使用 typescript 的新手，我正在使用 typescript 和 postgres 开发 API NodeJS。
我正在使用 docker 到 postgres，并且我有以下 docker-compose.yml:

version: '3.1'

services:
  db:
    image: postgres
    restart: always
    volumes:
      - ./builder.sql:/docker-entrypoint-initdb.d/01-init.sql
    ports:
      - '5432:5432'
    environment:
      POSTGRES_PASSWORD: test
      POSTGRES_USER: test
      POSTGRES_DB: test

我的 builder.sql 是:

CREATE SCHEMA operation;

CREATE TABLE operation."Exams" (
  "idExam" SERIAL PRIMARY KEY,
  "createdAt" TIMESTAMP WITH TIME ZONE,
  "updatedAt" TIMESTAMP WITH TIME ZONE,
  "deletedAt" TIMESTAMP WITH TIME ZONE,
  "name" VARCHAR(300)
);

然后，postgres 具有以下结构:

postgres

公共(public)(架构)

操作(模式)

我需要使用操作模式。
我的模型是:

import { DataTypes, Model } from 'sequelize'
import sequelize from '@models/index'

export class Exams extends Model { }

export class ExamsModel {
  id: number
  name: string
  createdAt: Date
  updatedAt: Date
  deleted: boolean
}

Exams.init(
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      field: 'idExam'
    },
    name: {
      type: DataTypes.STRING,
      field: 'name'
    },
    deletedAt: {
      type: DataTypes.BOOLEAN,
      defaultValue: false
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW
    },
  },
  {
    sequelize,
    tableName: 'Exams',
    modelName: 'Exams',
    schema: 'operation',
    underscored: false,
    freezeTableName: true // not pluralizes
  }
)

我在模型文件中定义了哪个模式，但不起作用。
当我运行模型的 findAll 方法时，我收到此错误:

Executing (default):     ;
DatabaseError [SequelizeDatabaseError]: relation "operation.Exams" does not exist
    at Query.formatError (/home/node_modules/sequelize/lib/dialects/postgres/query.js:386:16)
    at Query.run (/home/node_modules/sequelize/lib/dialects/postgres/query.js:87:18)
    at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  parent: error: relation "operation.Exams" does not exist
      at Parser.parseErrorMessage (/home/node_modules/pg-protocol/src/parser.ts:357:11)
      at Parser.handlePacket (/home/node_modules/pg-protocol/src/parser.ts:186:21)
      at Parser.parse (/home/node_modules/pg-protocol/src/parser.ts:101:30)
      at Socket.<anonymous> (/home/node_modules/pg-protocol/src/index.ts:7:48)

我不知道原因改变了这个“操作”。“考试”被“操作。考试”转换。
可以帮帮我吗？

Answer 1

这是一个工作示例:
第一步:执行create schema "operation" PostgreSQL 服务器中的 SQL。
第二步:执行await sequelize.sync({ force: true });做模型同步。它将创建 Exams operation 中的表架构。
第 3 步:然后，您可以运行 Exams.findAll()从表中查询数据记录的方法。

import { DataTypes, Model } from 'sequelize';
import { sequelize } from '../../db';

export class Exams extends Model {}

export class ExamsModel {
  id!: number;
  name!: string;
  createdAt!: Date;
  updatedAt!: Date;
  deleted!: boolean;
}

Exams.init(
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      field: 'idExam',
    },
    name: {
      type: DataTypes.STRING,
      field: 'name',
    },
    deletedAt: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
    },
  },
  {
    sequelize,
    tableName: 'Exams',
    modelName: 'Exams',
    schema: 'operation',
    underscored: false,
    freezeTableName: true, // not pluralizes
  },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    const data = await Exams.findAll();
    console.log(data);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

执行结果:

Executing (default): DROP TABLE IF EXISTS "operation"."Exams" CASCADE;
Executing (default): DROP TABLE IF EXISTS "operation"."Exams" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "operation"."Exams" ("idExam"  SERIAL , "name" VARCHAR(255), "deletedAt" BOOLEAN DEFAULT false, "createdAt" TIMESTAMP WITH TIME ZONE, "updatedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("idExam"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a, pg_namespace s WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Exams' AND s.oid = t.relnamespace AND s.nspname = 'operation' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): SELECT "idExam" AS "id", "name", "deletedAt", "createdAt", "updatedAt" FROM "operation"."Exams" AS "Exams";
[]