How to insert an array of strings in javascript into PostgreSQL

Question

I am building an API server which accepts file uploads using multer.

I need to store an array of all the paths to all files uploaded for each request to a column in the PostgreSQL database which I have connected to the server.

Say I have a table created with the following query

CREATE TABLE IF NOT EXISTS records
  (
   id       SERIAL PRIMARY KEY,
   created_on TIMESTAMPTZ NOT NULL DEFAULT NOW(),
   created_by INTEGER,
   title VARCHAR NOT NULL,
   type  VARCHAR NOT NULL
  )

How do I define a new column filepaths on the above table where I can insert a javascript string array (ex: ['path-to-file-1', 'path-to-file-2', 'path-to-file-3']).

Also how do I retrive, update/edit the list in javascript using node-postgres

Answer 1

You have 2 options:

1. use json or jsonb type. In the case string to insert will look:

   '["path-to-file-1", "path-to-file-2", "path-to-file-3"]'
   

   I would prefer jsonb - it allows to have good indexes. Json is rather just text with some additional built-in functions.

2. Use array of text - something like filepaths text[]. To insert you can use:

   ARRAY ['path-to-file-1', 'path-to-file-2', 'path-to-file-3']
   

   or

   '{path-to-file-1,path-to-file-2,path-to-file-3,"path to file 4"}'
   

   You need to use " here only for elements that contain space and so on. But you fill free to use it for all elements too.