node/pg/PSQL:使用Promises创建表

Question

我正在编写一个简单的Node/Express/React/Postgres应用程序，并使用pg包与我的Postgres Server进行交互。

我需要三个表：table1、table2和table3。table2在table1中有一个外键，table3在table 2中有一个外键，所以我需要在其中创建表的顺序是：table1，然后是table2，然后是table3。

我正在尝试使用promises在我的异步表创建调用中强制执行此顺序。我一般都会关注Brian Carlson's suggested Project Structure，但很明显我做错了什么。

以下是我的项目中简化的相关文件：

db.js:

const { Pool } = require('pg');

// Create pool connection to database
const pool = new Pool({
  user: XXXX,
  host: XXXX,
  database: XXXX,
  password: XXXX,
  port: XXXX
});

// Pool emitters
pool.on('connect', () => {
  console.log('Connected a client to the database');
});

pool.on('remove', () => {
  console.log('Disconnected a client from the database');
});

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

// This structure taken from Brian Carlson's pg API Documentation
// https://node-postgres.com/guides/project-structure
module.exports = {
  query: (text, params) => {
    console.log('Making a query!');
    return pool.query(text, params);
  }
};

table_scripts.js:

const db = require('../db');

const Database_Scripts = {
  create_table_1: () => {
    const create_table_1_query = {
      text: `CREATE TABLE IF NOT EXISTS
        public.table_1
      (
        id smallserial,
        name text NOT NULL,
        PRIMARY KEY (id)
      );`
    };
    return db.query(create_table_1_query);
  },

  create_table_2: () => {
    const create_table_2_query = {
      text: `CREATE TABLE IF NOT EXISTS
      public.table_2
    (
        id smallserial,
        table_1_id integer NOT NULL REFERENCES public.table_1(id),
        name text NOT NULL,
        PRIMARY KEY (id)
    );`
    };
    return db.query(create_table_2_query);
  },

  create_projects_table: () => {
    const create_table_3_query = {
      text: `
      CREATE TABLE IF NOT EXISTS
          public.table_3
          (
           id smallserial,
           table_3_id integer NOT NULL REFERENCES public.table_2(id),
           name text NOT NULL,
           PRIMARY KEY (id)
          );`
    };

    return db.query(create_table_3_query);
  }
};

module.exports = Database_Scripts;

create_tables.js:

const Table_Scripts = require('./table_scripts');

Table_Scripts.create_table_1()
  .then(Table_Scripts.create_table_2())
  .then(Table_Scripts.create_table_3())
  .catch(error => console.log(error.stack));

package.json:

{
  "name": "app",
  "version": "0.0.0",
  "scripts": {
    "start": "nodemon ./bin/www",
    "create_tables": "node ./database/scripts/create_tables.js"
  }
}

当我运行我的create_tables脚本(npm run-script create_tables)时，我得到以下(清理后的)错误：

Connected a client to the database
Connected a client to the database
Connected a client to the database
Disconnected a client from the database
(node:13444) UnhandledPromiseRejectionWarning: error: relation "public.table_1" does not exist
(node:13444) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a
catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:13444) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
Disconnected a client from the database
(node:13444) UnhandledPromiseRejectionWarning: error: relation "public.table_2" does not exist
(node:13444) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a
catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 2)
Disconnected a client from the database

我已经能够让这个脚本将函数转换为异步函数，然而，我真的很想了解我在这里做错了什么。

Answer 1

您的问题似乎是您正在并发创建表，尽管显式地需要执行相反的操作。

根据documentation，如果没有提供回调函数作为第三个参数，pool.query()将返回一个promise。您需要等待您的每个db.query()承诺都得到解决。因此，它将等待创建第一个表，然后创建第二个表，最后创建第三个表。

我建议使用async/await语法

async function createTables () {
    try {
        const create_table_1_query = {
            text: `yourQuery1`
        };
        // awaits for the first table to be created
        await db.query(create_table_1_query);

        const create_table_2_query = {
            text: `yourQuery2`
        };
        // awaits for the second table to be created
        await db.query(create_table_2_query);

        const create_table_3_query = {
            text: `yourQuery3`
        };
        // awaits for the third table to be created
        await db.query(create_table_3_query);
    } catch (e) {
        console.error(e.stack);
    }
}

module.exports.createTables = createTables;

然后，您可以调用await createTables();