Convert Image to Varbinary in Javascript without a File Input

Question

I am trying to do an Insert SQL query using the mssql package on Node.js.

But one of the arguments is a varbinary of an image that is "uploaded" in the database.
Example: 0x78DAC... and so on.

I am not using a File Input to read the image. My project involves taking a screenshot from a website (using puppeteer), then I execute a code to pick the image and convert it.

How can I turn an image file (an .jpeg for example) into an array of bytes or a varbinary string, so I can use this on my SQL? Is there a npm package that can help me with this?

https://stackoverflow.com/questions/41042527/node-js-how-to-convert-to-image-from-varbinary-of-ms-sql-server-datatype did not help me. — Renato C.Francisco, Jul 05 '22 at 14:08
@Yogi I am not using a file input. There is going to be an image in the folder of my project that I want converted. — Renato C.Francisco, Jul 05 '22 at 15:37

score 1 · Answer 1 · answered Jul 05 '22 at 18:13

There's probably a better way of doing it, but it worked for me:

const fs = require('fs');
const sql = require('mssql');

var connString = {
    user: 'sa',
    password: '123',
    server: 'localhost',
    database: 'Banco',
    options: {
        trustServerCertificate: true,
    },
};

fs.readFile('img.png', (error, file) => {
    sql.connect(connString).then(() => {
        let insertCommand = 'INSERT INTO _ImageTable (BinaryImage, Date) VALUES (@Image, GETDATE())';
        let prepStatm = new sql.PreparedStatement();
        prepStatm.input('Image', sql.VarBinary);
        let params = { Image: file };
        prepStatm
            .prepare(insertCommand)
            .then((ps) => ps.execute(params))
            .then(() => prepStatm.unprepare())
            .then(createImageFromDB); //THIS IS JUST TO VERIFY IF IT SAVED CORRECTLY AT THE DB
    });
});

const createImageFromDB = () => {
    sql.query`SELECT TOP 1 BinaryImage FROM _ImageTable ORDER BY Date DESC`.then((result) => {
        let varbinaryFromDatabase = result.recordset.at(0).BinaryImage;
        fs.writeFile('imgFromDb.png', varbinaryFromDatabase, () => {});
    });
};

The Puppeteer API can capture the screenshot as binary data which you could then save directly to your database. Saving the data to a file and then reading it back seems completely unnecessary. See: [Puppeteer ScreenshotOptions](https://pptr.dev/api/puppeteer.screenshotoptions) — Yogi, Jul 05 '22 at 19:40
Didn't accept as an answer, I am still trying to understand. I don't really need the mssql part. But thanks, Paulo. — Renato C.Francisco, Jul 06 '22 at 11:08
Hello Renato, I pasted the entire file so you can run. You are going to read the file without specificing enconding (uft-8 for example) this way it will be read as binary :`fs.readFile('img.png', (error, file) => {` and you are going to pass the second parameter `file` directly as a param to SQL Server — Paulo Fernando, Jul 06 '22 at 15:05

Convert Image to Varbinary in Javascript without a File Input

1 Answers1