---
# System prepended metadata

title: 資料庫影像儲存
tags: [Smart Construction]

---

# 資料庫影像儲存
###### tags: `Smart Construction`

## Buffer package
- 處理前端顯示`base64` encoded的影像
- [@craftzdog/react-native-buffer, react-native-quick-base64](https://www.npmjs.com/package/@craftzdog/react-native-buffer)

## ORM package
- [sequalize](https://sequelize.org/docs/v6/)
- [prisma](https://www.prisma.io/)

### 類似
- [knex](https://www.npmjs.com/package/knex)

## 問題
1. 送`POST` request時，無法將影像和metadata一起送
    - [https://stackoverflow.com/questions/40124548/uploading-files-and-json-data-in-the-same-request-express-node-js](https://stackoverflow.com/questions/40124548/uploading-files-and-json-data-in-the-same-request-express-node-js)
    - https://stackoverflow.com/questions/4083702/posting-a-file-and-associated-data-to-a-restful-webservice-preferably-as-json
    - https://stackoverflow.com/questions/3938569/how-do-i-upload-a-file-with-metadata-using-a-rest-web-service
    - 解決：[調整request body中影像和metadata的順序](https://stackoverflow.com/questions/39589022/node-js-multer-and-req-body-empty)

2. `JSON.parse() v.s. JSON.stringify()`
    - 透過`JSON.parse()`將metadata轉成JSON格式並用在資料庫中

3. 在React-Native中display影像
```javascript
import { Image } from 'react-native';

fetch(`http://yourserver.com/image/${id}`)
  .then(response => response.json())
  .then(({ project_name, corporation, image }) => {
    <View>
      <Text>{project_name}</Text>
      <Text>{corporation}</Text>
      <Image source={{ uri: `data:image/jpeg;base64,${Buffer.from(image).toString('base64')}` }} />
    </View>
  })

```

## 二次開發使用ORM
- [Connect a Relational DB and Backend with Node.js, Sequelize, PostgreSQL](https://javascript.plainenglish.io/nodejs-sequelize-postgresql-wire-up-a-relational-db-and-backend-f7d3c4587ed0)
- [Upload and Download Images to PostgreSQL (Node.js)](https://robert-keller22.medium.com/upload-and-download-images-to-a-postgres-db-node-js-92e43f232ae4)
- [[week 17] 後端中階 - 淺談 Sequelize：使用 ORM 框架串接資料庫](https://hackmd.io/@Heidi-Liu/note-be201-sequelize)
- [[ 筆記 ] Express 03 - ORM ＆ Sequelize](https://mtr04-note.coderbridge.io/2020/10/10/sequelize/)
- [ORM介紹](https://medium.com/johnliu-%E7%9A%84%E8%BB%9F%E9%AB%94%E5%B7%A5%E7%A8%8B%E6%80%9D%E7%B6%AD/%E5%BE%8C%E7%AB%AF%E5%B7%A5%E7%A8%8B%E5%B8%AB%E7%9A%84%E7%AC%AC%E4%B8%80%E5%A0%82%E8%AA%B2-20-%E7%8F%BE%E4%BB%A3%E7%B3%BB%E7%B5%B1%E8%B3%87%E6%96%99%E5%B7%A5%E5%85%B7-orm-359da9a1d14a)

## 觀念
- [Base64 v.s. Multipart](https://nimesha-dilini.medium.com/send-image-files-in-an-api-post-request-aa1af1c4a7fb)



## 方案
- 存在Disk Storage: `multer`
- 直接存在PostgreSQL中的`BYTEA` type

## Disk方案
- 單張影像
```javascript
// server-side
app.get('/image', (req, res) => {
  pool.query('SELECT image_path, image_name FROM images WHERE id = $1', [req.query.id], (err, result) => {
    if (err) {
      console.error('Error executing query', err);
      res.status(500).send('Error executing query');
    } else {
      const imageData = {
        imagePath: result.rows[0].image_path,
        imageName: result.rows[0].image_name
      };
      res.json(imageData);
    }
  });
});

// client-side
fetch('http://your-server-url/image?id=1')
    .then((response) => response.json())
    .then((imageData) => {
        console.log(imageData.imagePath);
        console.log(imageData.imageName);
    });

```

- 多張影像
```javascript
// server-side
app.get('/images', (req, res) => {
  pool.query('SELECT image_path, image_name FROM images', (err, result) => {
    if (err) {
      console.error('Error executing query', err);
      res.status(500).send('Error executing query');
    } else {
      let imagesData = [];
      result.rows.forEach((row) => {
        imagesData.push({
          imagePath: row.image_path,
          imageName: row.image_name
        });
      });
      res.json(imagesData);
    }
  });
});

// client-side
fetch('http://your-server-url/images')
    .then((response) => response.json())
    .then((imagesData) => {
        imagesData.forEach((imageData) => {
            console.log(imageData.imagePath);
            console.log(imageData.imageName);
        });
    });
```

- In React-Native
```javascript
import FastImage from 'react-native-fast-image';

const ImageView = (props) => {
  return (
    <FastImage
      source={{ uri: `http://your-server-url/image?id=${props.imageId}` }}
      style={{ width: 200, height: 200 }}
    />
  );
};

export default ImageView;
```