How to insert blob using knex?

Question

Currently, I have an upload system using ng-file-upload to another server, which is working well thanks to CORS.

To manage my database I use knex (migrations and seed), and I have a specific table with a bytea column.

PostgreSQL database.

to make upload possible, I've added the busboy module to allow express to manage multipart requests, and the file is being saved to the disk with no problem.

but what I really want is to save it in the table, in the bytea column, and right now I'm with no luck on such quest.

Any guidance and better documentation are welcome.

The best approach untill now seems to use a specific postgresql function as shown here: http://dba.stackexchange.com/a/95456/76920 still looking for another option, but save to disk and then insert works for now — Sombriks, Sep 27 '15 at 18:18

score 4 · Accepted Answer · answered Aug 17 '16 at 03:45

After a long time i figured it out.

In the end it is dead simple to make upload works with angular+express+knex+postgres

first of all, there's no need to busboy, instead, you'll need the bodyParser's raw mode

second, adjust it to comport a reasonable upload size.

third, ng-file-upload will help with the upload part.

here's a few snippets if anyone is in need of it:

Upload button:

<div layout="row" layout-align="center center">
  <md-button ngf-select ng-model="arquivo" class="md-raised md-primary">Selecionar arquivo</md-button>
  <md-button ng-show="arquivo" ng-click="arquivo = null" class="md-raised md-warn">Cancelar</md-button>
  <md-button ng-show="arquivo" ng-click="sendarquivo(arquivo)" class="md-raised md-primary" ng-disabled="arquivo.size > 4096 * 1024">Enviar arquivo</md-button>
</div>

controller.sendarquivo:

$scope.sendarquivo = function (arquivo) {
  enqueteservice.uploadanexo(idenquete, arquivo).then(function () {
    $scope.list();
    $scope.arquivo = null;
  });
};

enqueteservice.uploadanexo:

// serviço de enquete
angular.module("roundabout").factory("enqueteservice", function($http, Upload) {
  return {
    uploadanexo: function(idenquete, file) {
      return Upload.http({
        url: "/enquete/" + idenquete + "/uploadanexo/" + file.name,
        method: 'POST',
        headers: {
          'Content-Type': 'application/octet-stream' // file.type //  
        },
        data: file
      });
    }
  }
});

on server side, express router:

router.post("/:idenquete/uploadanexo/:descricaoanexoenquete", function (req, res) {
  knex("anexoenquete").insert({
    idenquete: req.params.idenquete,
    descricaoanexoenquete: req.params.descricaoanexoenquete,
    dadoanexoenquete: req.body
  }, "idanexoenquete").then(function (ret) {
    res.send("idanexoenquete:" + ret[0]);
  }).catch(function (err) {
    res.status(500).send(err);
    console.log(err);
  });
});

for reference, the bodyParser setup at index.js

// ...
app.use(bodyParser.json({limit: 1024 * 1024}));// 1MB of json is a lot of json
// parse some custom thing into a Buffer
app.use(bodyParser.raw({limit: 10240 * 1024, type: 'application/octet-stream'})); // 10 MB of attachments

with this setup, ng-file-upload body will arrive at express router as a Buffer, wich you can pass directly to knex insert statement.

download binary content can also be easily solved as follows:

download attachment

router.get("/downloadanexo/:idanexoenquete", function (req, res) {
  knex("anexoenquete").select().where({
    idanexoenquete: req.params.idanexoenquete
  }).then(function (ret) {
    if (!ret.length)
      res.status(404).send("NOT FOUND");
    else {
      var anexoenquete = ret[0];
      res.setHeader("Content-disposition", "attachment;filename=" + anexoenquete.descricaoanexoenquete);
      res.send(anexoenquete.dadoanexoenquete);
    }
  }).catch(function (err) {
    res.status(500).send(err);
    console.log(err);
  });
});

hope this reference helps anyone else in the future, i'm able to shutdown an simple java app which was solving this issue for me.

score -5 · Answer 2 · answered Sep 28 '15 at 20:59

-5

The best approach is using Amazon s3 or other services to store blobs while storing metadata in sql.

If you want to store anyway, you can use sql driver and bluebird.

answered Sep 28 '15 at 20:59

artit91

83
7

11

The best answer to "how do I solve X with technology Y" is usually not to say "use technology Z" – Stuart Watt May 27 '16 at 21:48

How to insert blob using knex?

2 Answers2