用 ChatGPT 帶你快速寫完 side project

Photo by vackground.com on Unsplash

最近 OpenAI 開發的 ChatGPT 的出現掀起了滿大的話題，隨著各式各樣的提問被 ChatGPT 一一解析並加以優雅的擊破，讓人類見識到了人工智慧的威力。

有新聞甚至指出 ChatGPT 擁有高中生的智商水平，也讓我在觀望了一段時間後不禁懷疑 ChatGPT 到底是不是真的有這麼厲害？

最近剛好想來寫一個後端的 side project，根據自己的狀況考慮後，最後決定採用 Node.js + Express + PostgreSQL 來開發 server side 的應用程式，剛好有個機會可以來試試看傳說中的 ChatGPT 是不是能幫得上我的忙。

持久化保存 user 資料的相關經驗

我主要 focus 在 client side 行動 APP 的開發，有事沒事就會想弄個 side project 來玩一下，通常這些 side project 都會有需要將 user 資料保存起來的需求，所以或多或少都會需要寫個 server side 的 app 或是引進 third party 的 SDK 來保存 user 資料。以往我採取過方案有：

自架主機方案

• LAMP → Linux + Apache + MySQL + PHP

Serverless

• Firebase Realtime Database
• Firebase Firestore
• Firebase Cloud Functions

我會認為如果開發上的 scope 不會很大，採用 Firebase 提供的非關聯式資料庫 solution 會是個不錯的選擇（Firebase Realtime Database / Firebase Firestore）。
但這次考慮到想要在之後的 backend 程式碼上取得更大的彈性、想提高資料的一致性，加上自己對於關聯式資料庫的技能掌握度也比較高，因此這次會選擇採用 Node.js + Express + PostgreSQL 來開發 backend application，並且最終將它部署在自己租用的雲端主機上。

調整心態，好好當個伸手牌 XD

既然本次最大的目的就是想來測試一下 ChatGPT 的極限，基本上我已經做好整個專案都交給 ChatGPT 來寫的心理準備了。

基本上我就是想寫個 CRUD 的 app，我打算來問幾個問題，看看 ChatGPT 能幫助我走到哪裡。

話不多說，我們馬上開始吧！

🤔 第 1 題 → show me sample code of Express + PostgreSQL

ChatGPT 解答：

Sure! Here is an example of how you can use the Express web framework with a PostgreSQL database to create a simple API that performs CRUD (create, read, update, delete) operations on a table of users:

const express = require('express');
const app = express();
const bodyParser = require('body-parser');

// Parse incoming request bodies in a middleware before your handlers, available under the req.body property.
app.use(bodyParser.json());
// Initialize a connection pool using the pg driver
const { Pool } = require('pg');
const pool = new Pool({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});
// Define a route to create a new user
app.post('/users', async (req, res) => {
  try {
    const { name, email } = req.body;
    const result = await pool.query(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      [name, email]
    );
    res.send(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).send(err);
  }
});
// Define a route to get all users
app.get('/users', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM users');
    res.send(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).send(err);
  }
});
// Define a route to get a single user by id
app.get('/users/:id', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM users WHERE id = $1', [
      req.params.id,
    ]);
    res.send(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).send(err);
  }
});
// Define a route to update a user by id
app.put('/users/:id', async (req, res) => {
  try {
    const { name, email } = req.body;
    const result = await pool.query(
      'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *',
      [name, email, req.params.id]
    );
    res.send(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).send(err);
  }
});
// Define a route to delete a user by id
app.delete('/users/:id', async (req, res) => {
  try {
    const result = await pool.query('DELETE FROM users WHERE id = $1', [
      req.params.id,
    ]);
    res.send(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status

感覺上 Express + PostgreSQL 的問題應該是個常見的問題，ChatGPT 馬上猜出我想實作基本的 CRUD，也很貼心的給出 sample code。但可能是因為資訊太多，解答最後被剪掉了一些，沒有拿到完整的解答，但至少知道 PostgreSQL 在 Node.js 可以使用 pg 這個 module。

以下，開始追問：

🤔 第 2 題 → show me sample code of pg + express

ChatGPT 解答：

Here is a sample code that demonstrates how to use the PostgreSQL database with the Express.js web framework in Node.js:

const express = require('express');
const { Client } = require('pg');

const app = express();
// Create a new PostgreSQL client
const client = new Client({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});
// Connect to the database
client.connect();
app.get('/', (req, res) => {
  // Execute a query to retrieve data from the database
  client.query('SELECT * FROM your_table', (err, result) => {
    if (err) {
      console.log(err.stack);
      res.send('Error Occured');
    } else {
      console.log(result.rows);
      res.send(result.rows);
    }
  });
});
app.listen(3000, () => {
  console.log('Server listening on port 3000');
});

In this example, we use the pg module to connect to a PostgreSQL database, and the express module to create a simple server that listens for incoming HTTP requests. When the server receives a request to the / route, it executes a query to retrieve data from the database and sends the results back to the client as a response.

I hope this helps! Let me know if you have any questions.

可以看出 GET 的寫法和第 1 題差不多，但 sample code 裡面使用的是 Client 而不是 Pool（這個問題在後續也會和 ChatGPT 提問！）

🤔 第 3 題 → example code of using pg + express to insert new data

ChatGPT 解答：

Sure! Here is an example of using the PostgreSQL database with Express.js to insert new data into a table:

const express = require('express');
const { Client } = require('pg');

const app = express();
// Create a new PostgreSQL client
const client = new Client({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});
// Connect to the database
client.connect();
app.post('/create', (req, res) => {
  // Insert data into the database
  client.query(
    'INSERT INTO your_table (column1, column2) VALUES ($1, $2)',
    [req.body.value1, req.body.value2],
    (err, result) => {
      if (err) {
        console.log(err.stack);
        res.send('Error Occured');
      } else {
        console.log(result.rows);
        res.send('Data added successfully');
      }
    }
  );
});
app.listen(3000, () => {
  console.log('Server listening on port 3000');
});

In this example, we use the app.post() method to handle HTTP POST requests to the /create route. When the server receives a POST request to this route, it executes an INSERT query to insert the data into the database.

Note that this example assumes that you are using the body-parser middleware to parse the request body and make it available in the req.body object. You will need to install and require this middleware in your application if you want to use it.

I hope this helps! Let me know if you have any questions.

雖然是提問如何將資料存進 DB，但 ChatGPT 也解讀出了我潛在的需求，建議我如果要 insert data，這些 data 應該要從 POST API 取得，並且也建議我可以透過 body-parser 這個 module 來取得 post request 的 body req.body ，我覺得這點是滿智慧也滿貼心的。

🤔 第 4 題 → how to use pg + express to update existing data?

ChatGPT 解答：

To update existing data in a PostgreSQL database using Express.js, you can use the UPDATE SQL statement. Here is an example of how to do this:

const express = require('express');
const { Client } = require('pg');

const app = express();
// Create a new PostgreSQL client
const client = new Client({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});
// Connect to the database
client.connect();
app.put('/update/:id', (req, res) => {
  // Update data in the database
  client.query(
    'UPDATE your_table SET column1 = $1, column2 = $2 WHERE id = $3',
    [req.body.value1, req.body.value2, req.params.id],
    (err, result) => {
      if (err) {
        console.log(err.stack);
        res.send('Error Occured');
      } else {
        console.log(result.rows);
        res.send('Data updated successfully');
      }
    }
  );
});
app.listen(3000, () => {
  console.log('Server listening on port 3000');
});

In this example, we use the app.put() method to handle HTTP PUT requests to the /update/:id route. When the server receives a PUT request to this route, it executes an UPDATE query to update the data in the database. The id of the record to be updated is passed as a URL parameter.

Note that this example assumes that you are using the body-parser middleware to parse the request body and make it available in the req.body object. You will need to install and require this middleware in your application if you want to use it.

I hope this helps! Let me know if you have any questions.

ChatGPT 建議我們如果要更新已存在的資料，應該使用 PUT 的。通常我們在使用 PUT 的時候會在 path 上帶入 id，ChatGPT 除了提供 sample code 以外，也解釋了 :id 是什麼東西。

🤔 第 5 題 → how to use restful put in express?

ChatGPT 解答：

To use the RESTful PUT method in Express.js, you can use the app.put() method. This method allows you to specify a route and a callback function that will be executed when a PUT request is received at that route.

Here is an example of using the app.put() method to handle a PUT request in Express:

const express = require('express');
const app = express();

app.put('/users/:id', (req, res) => {
  // Update the user with the specified ID
  const userId = req.params.id;
  const updatedUser = req.body;
  // Save the updated user to the database
  User.findByIdAndUpdate(userId, updatedUser, (err, user) => {
    if (err) {
      res.send(err);
    } else {
      res.send(user);
    }
  });
});

In this example, the app.put() method is used to handle PUT requests to the /users/:id route. The callback function receives the request and response objects as arguments, and it updates the user with the specified ID using the data provided in the request body.

I hope this helps! Let me know if you have any questions.

單純戳戳看 ChatGPT 會給我什麼建議 XD

🤔 第 6 題 → how to use restful patch in express?

ChatGPT 解答：

To use the RESTful PATCH method in Express.js, you can use the app.patch() method. This method allows you to specify a route and a callback function that will be executed when a PATCH request is received at that route.

Here is an example of using the app.patch() method to handle a PATCH request in Express:

const express = require('express');
const app = express();

app.patch('/users/:id', (req, res) => {
  // Update the user with the specified ID
  const userId = req.params.id;
  const updatedUser = req.body;
  // Save the updated user to the database
  User.findByIdAndUpdate(userId, updatedUser, (err, user) => {
    if (err) {
      res.send(err);
    } else {
      res.send(user);
    }
  });
});

In this example, the app.patch() method is used to handle PATCH requests to the /users/:id route. The callback function receives the request and response objects as arguments, and it updates the user with the specified ID using the data provided in the request body.

I hope this helps! Let me know if you have any questions.

單純戳戳看 ChatGPT 會給我什麼建議 XD

🤔 第 7 題 → how to use pg + express to delete existing data?

ChatGPT 解答：

To delete existing data in a PostgreSQL database using Express.js, you can use the DELETE SQL statement. Here is an example of how to do this:

const express = require('express');
const { Client } = require('pg');

const app = express();
// Create a new PostgreSQL client
const client = new Client({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});
// Connect to the database
client.connect();
app.delete('/delete/:id', (req, res) => {
  // Delete data from the database
  client.query('DELETE FROM your_table WHERE id = $1', [req.params.id], (err, result) => {
    if (err) {
      console.log(err.stack);
      res.send('Error Occured');
    } else {
      console.log(result.rows);
      res.send('Data deleted successfully');
    }
  });
});
app.listen(3000, () => {
  console.log('Server listening on port 3000');
});

In this example, we use the app.delete() method to handle HTTP DELETE requests to the /delete/:id route. When the server receives a DELETE request to this route, it executes a DELETE query to delete the data from the database. The id of the record to be deleted is passed as a URL parameter.

I hope this helps! Let me know if you have any questions.

刪除資料也沒問題。

🤔 第 8 題 → what’s the “Pool” in pg module?

ChatGPT 解答：

In the pg module, the Pool class allows you to manage a pool of connections to a PostgreSQL database. It creates a fixed number of connections to the database when the application starts, and these connections are then used to execute queries.

Using a connection pool has several benefits, such as:

Improved performance: A connection pool allows you to reuse existing connections, rather than creating a new connection for each query. This can significantly improve the performance of your application, especially when dealing with a large number of concurrent requests.

Better resource management: A connection pool helps you to manage your database resources more efficiently, as it allows you to control the maximum number of connections that can be open at any given time. This can prevent your application from using up all available database connections and causing performance issues.

Simplified code: Using a connection pool simplifies your code, as you don’t have to worry about managing individual connections. You can simply execute queries using the query() method provided by the pool, and the pool will handle the details of finding an available connection and releasing it back to the pool when the query is complete.

Here is an example of how to use the Pool class in the pg module:

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

const pool = new Pool({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});
pool.query('SELECT * FROM your_table', (err, result) => {
  if (err) {
    console.log(err.stack);
  } else {
    console.log(result.rows);
  }
});
// Don't forget to close the pool when you're done
pool.end();

I hope this helps! Let me know if you have any questions.

從先前的 sample code 可以看到要和資料庫進行連接時，有 Client 與 Pool 2 種寫法。這邊詢問了 Pool 是什麼，ChatGPT 給出的答案相當仔細，讓我們知道為什麼要使用 Pool 。

🤔 第 9 題 → is it recommended to use pool instead of using client when using pg module?

ChatGPT 解答：

It is generally recommended to use a connection pool (using the Pool class in the pg module) instead of creating a new Client instance for each query, as a connection pool can improve the performance of your application and provide better resource management.

Using a connection pool allows you to reuse existing connections, rather than creating a new connection for each query. This can significantly improve the performance of your application, especially when dealing with a large number of concurrent requests. Additionally, a connection pool helps you to manage your database resources more efficiently, as it allows you to control the maximum number of connections that can be open at any given time.

However, there may be certain cases where it is more appropriate to use a Client instance instead of a connection pool. For example, if you need to execute a large number of queries in a short period of time and you want to ensure that they are all executed on the same connection, you may want to use a Client instance. In this case, you would need to manage the connection yourself and ensure that it is closed when you are finished with it.

Ultimately, the choice between using a connection pool or a Client instance will depend on your specific needs and the requirements of your application.

I hope this helps! Let me know if you have any questions.

從先前的 sample code 可以看到要和資料庫進行連接時，有 Client 與 Pool 2 種寫法。這邊詢問兩者有何差異，ChatGPT 給出的答案相當明確，讓我們知道 Client 與 Pool 合適的使用場景。

🤔 第 10 題 → Which one is better? MySQL or PostgreSQL?

ChatGPT 解答：

Both MySQL and PostgreSQL are popular open-source relational database management systems (RDBMS) that are widely used in web applications. Both databases have their own strengths and weaknesses, and the choice of which one to use will depend on the specific needs and requirements of your application.

Here are a few key differences between MySQL and PostgreSQL:

Data types: PostgreSQL supports a wider range of data types than MySQL, including support for arrays, hstore (a key-value store), and JSON.

Indexing: MySQL supports a wider range of index types than PostgreSQL, including full-text search and spatial indexing.

Concurrency: PostgreSQL uses a more advanced concurrency model than MySQL, which can lead to better performance in high-concurrency environments.

Transactions: PostgreSQL supports more advanced transaction management features than MySQL, including savepoints and two-phase commit.

SQL standards: PostgreSQL has a more comprehensive implementation of SQL standards than MySQL, which can make it easier to port SQL code between different databases.

Ultimately, the choice between MySQL and PostgreSQL will depend on the specific needs and requirements of your application. Both databases are widely used and well-supported, and either one can be a good choice for many applications.

I hope this helps! Let me know if you have any questions.

本來想測試看看 ChatGPT 有沒有什麼獨特的信仰 XD，但看起來還是滿客觀的，列出了 MySQL 與 PostgreSQL 各自的優缺點，讓我們可以很好的來做參考。

side project 實作結果

在 ChatGPT 協助下，簡單的完成了一個具有 CRUD 功能的 app。

ChatGPT 使用心得

以往想完成一個 project 可能需要對於要實作的目標需要有一個比較全面的認識，大致上會需要知道要有哪些角色參與，以及這些角色大概負責什麼樣的職責，在一步一步釐清後大概才能做出和想像中差不多的玩意兒。

現在有 ChatGPT 以後，它就像是個學識淵博的前輩、經驗老道的老司機，如果想做的題目已經被實作過很多遍了，其實直接問他會比較快。和 ChatGPT 提問問題以後，ChatGPT 會站在巨人的肩膀上，根據它經年累月的經驗，告訴我大家大概會怎麼思考、怎麼做，幫助我省下了非常多原本需要慢慢 Google 的時間，減少很多不必要的煩惱。（當然我還是建議事後再進行求證，看看 ChatGPT 提供的解決方案，套用到自己的情境上時到底是不是最合適的）

那你說有了這麼強大的 AI 以後，工程師還會有需要存在的必要嗎？我的答案是肯定的。我會認為 ChatGPT 目前能夠勝任一些較常見、需求明確、難度不高、需要時間就可以解決的任務。我們人類在日常開發中需要理解各式各樣的需求，並且按照目前的系統架構來做出對應的調整，這個過程需要非常多的脈絡與資訊，也需要人和人之間頻繁地進行溝通，甚至是做出「妥協」，這是我目前認為機器還無法達到的境界。

接下來滿期待 Google 會如何回應 ChatGPT 的「挑戰」，在這場人工智慧的軍備競賽上我實在不認為 Google 會乖乖的在一旁看著榮耀被 OpenAI 整碗端走，就讓我們拭目以待吧！

2023/01/28 更新：Google 裁員 1.2 萬人（沒想到這麼狠），宣布紅色警戒！ Google 接下來將加速 AI 技術與相關服務的開發。[1]