I want to store the document id in the form of (IP-01, IP-02, IP-03) in the sequelize table. I tried to use autoincrement function on a string, but it showed an error that it can work only on INTEGER value.
I want to store it in such a form that I can store and search easily in the form of (IP-0X). So please anyone can help me
So I will give you basic idea, implementation is on you. There is no default function available using which you can autoincrement the value of string. My solution might not be the optimal way to do it but I guess it will solve your problem.
There are 2 option, you can implement some logic to achieve our required output.
By using count utility of sequelize you can count the total no of columns and generate your desired string.
//wherever you are creating the document add following
const count = (await Document.count()) + 1;
const newId = 'IP-' + count;
await Document.create({
id: newId,
...
});
You need to create a function which will return the id string in required pattern.
CREATE OR REPLACE FUNCTION get_id()
RETURNS varchar
LANGUAGE plpgsql
AS $$
BEGIN
RETURN (SELECT 'IP-' || (SELECT count(1) from table_name) + 1);
END;
$$;
Now you just need to set default value of your id field to function call.
Before doing below changes you need to make sure that you have created get_id function in you database or else you will get function not defined error from database.
\\add this to file where you define your model
id: {
type: Sequelize.STRING,
defaultValue: Sequelize.fn('get_id')
}
Here is a demo of database level solution. LINK
You can use one more approach instead of counting number of columns you can maintain a Sequence in your database and get the next Integer value from that.
CREATE SEQUENCE document_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;
-- Then create your get_id function as below:
CREATE OR REPLACE FUNCTION get_id()
RETURNS varchar
LANGUAGE plpgsql
AS $$
BEGIN
RETURN (SELECT 'IP-' || nextval('document_id_seq'));
END;
$$;