How to concat columns in Sequelize with SQLite database

Question

I'm using Sequelize for a express project I'm working on.
In one query I want to retrieve a concatenated result of two columns.

Like:

SELECT first_name || ' ' || last_name AS full_name FROM table

I tried the following syntax and got an error

router.get('/persons', function(req, res, next) {
  models.Person.findAll({
    attributes: [models.sequelize.fn('CONCAT', 'first_name', 'last_name')]
  })
    .then(function(persons) {
      res.send(persons);
    });
});

The error message:

SELECT CONCAT('first_name', 'last_name') FROM `Persons` AS `Person`;
Possibly unhandled SequelizeDatabaseError: Error: SQLITE_ERROR: no such function: CONCAT

Natal

Have you tried using literal? I don't have Sequelize set up anywhere so I can't check but maybe something like models.sequelize.literal("first_name || ' ' || last_name") would work. – Nellnella

Thank you! it's working now (: – Natal

Answer 1

I used models.sequelize.literal, that creates a object representing a literal, inside nested array to give it an alias.

The result:

router.get('/persons', function(req, res, next) {
  models.Person.findAll({
    attributes: [models.sequelize.literal("first_name || ' ' || last_name"), 'full_name']
  })
    .then(function(persons) {
      res.send(persons);
    });
});

Natal

How can I concat string with column value of attributes? – Kissel

Answer 2

This is working for me. Instead of use setter and getter methods

Annihilator

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Whippletree

Answer 3

I had the same issues, I solve like this

const { fn, col } = Person.sequelize;

const res = Person.findAll({
   attributes: [ [fn('concat', col('first_name'), ' ', col('last_name')), "FullName"], ...OthersColumns ]
})

I hope help you or others

Zito