"schemaName"."tableName“生成"schemaName.tableName”-续写- Postgres

Question

我是新使用类型记录，我正在开发一个API NodeJS与类型记录和postgres。

我正在将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的结构如下：

公共(schema)

• operation (schema)

• postgres

我需要使用操作模式。

我的模型是：

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)

我不知道是什么原因改变了这个“操作”。“考试”被"operation.Exams“转换。能帮我吗？

Answer 1

下面是一个有用的例子：

步骤1:在create schema "operation"服务器中执行PostgreSQL SQL。

步骤2:执行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";
[]