Node.js将一个文件夹的文件装载到PostgreSQL数据库BLOB列,并将BLOB列下载到另一个文件夹

红太狼 2023-09-29 10:44 8阅读 0赞

/* global Buffer */
    
    const fs = require('fs');
    
    
    const {Client} = require('pg');
    const connectionString = 'postgres://postgres:postgres@localhost:5432/testdb';
    
    const client = new Client({
        connectionString: connectionString
    });
    client.connect();
    
    
    
    
    // directory path
    const dir = './InputFiles/';
    
    // list all files in the directory
    try {
        const files = fs.readdirSync(dir);
    
        // files object contains all files names
        // log them on console
        files.forEach(file => {
            console.log(file);
            // Read buffer of an image file:
            const data = readImageFile("./InputFiles/" + file); // `data`'s type is Buffer
            const name = file.toString();
            client.query("INSERT INTO bindata(name,data) VALUES($1, $2)", [name, data], function (err, res) {
                if (err)
                    throw err;
                console.log("BLOB data inserted!");
            });
    
        });
    
    } catch (err) {
        console.log(err);
    }
    
    
    function readImageFile(file) {
        // read binary data from a file:
        const bitmap = fs.readFileSync(file);
        const buf = Buffer.from(bitmap);
        return buf;
    }

/* global Buffer */
    
    const fs = require("fs");
    const {Client} = require('pg');
    const connectionString = 'postgres://postgres:postgres@localhost:5432/testdb';
    
    const client = new Client({
        connectionString: connectionString
    });
    client.connect();
    
    
    // Check to read it from DB:
    client.query("select * from bindata", function (err, res) {
        const data = res.rows;
        console.log('all data');
        data.forEach(row => {
            //console.log(`Id: ${row.id} Name: ${row.name} Data: ${row.data}`);
            // Converted to Buffer:
            const buf = Buffer.from(row.data, "binary");
            // Write new file out:
            fs.writeFileSync("DownloadFiles/" + row.name, buf);
            console.log("New file output:", row.name);
        })
    
    });

DROP TABLE public.bindata;
     
    CREATE TABLE public.bindata (
    	id serial4 NOT NULL,
    	"name" varchar NULL,
    	"data" bytea NULL
    );