endpointnode-postgresbyteasveltekit

SvelteKit and retrieving images from PostgreSQL via node-postgres


Storing ~100 JPEGs (24K-100K each) in a table in PostgreSQL as a bytea column. The <img/> tag src attributes reference the SvelteKit endpoint:

<img src="/api/file/18.json" alt="Person"/>

Getting this error:

Invalid response from route /api/file/76.json: Uint8Array body must be accompanied by content-type: application/octet-stream header

export async function get({ params }) {
  const { id } = params
  const sql = `SELECT _id, name, type, data FROM files WHERE _id = $1;`
  const { rows } = await db.query(sql, [id])
  const file = rows[0]
  return {
    headers: {
      'Content-disposition': `attachment; filename=${file.name}`,
      'Content-type': file.type
    },
    body: file.data
  }
}

As SvelteKit endpoints don't interact with req/res objects because they're only available on certain platforms, I can't just write out the bytea value as a stream to a response object but I'm not sure what the right approach is.

Also tried this SQL statement...

SELECT _id, name, type, encode(data, 'base64') as data FROM files WHERE _id = $1;

But it didn't help.

Any thoughts?

UPDATE: The problem may be related to a SvelteKit bug - see https://github.com/sveltejs/kit/issues/1438.


Solution

  • The limitation in SvelteKit was resolved on June 11th. I also needed to put the file.data in a new Uint8Array...

    // routes/api/file/[filenum].ts
    
    import type { RequestHandler } from '@sveltejs/kit'
    import { query } from '../_db'
    
    export const get: RequestHandler = async ({ params }) => {
      const { filenum } = params
      const { rows } = await query(`SELECT * FROM find_file($1);`, [filenum])
      if (rows) {
        const file = rows[0]
        return {
          headers: {
            'Content-Disposition': `attachment; filename="${file.name}"`,
            'Content-Type': file.type
          },
          body: new Uint8Array(file.data)
        }
      } else return {
        status: 404
      }
    }
    
    

    Here's source for the find_file() function in PostgreSQL:

    CREATE OR REPLACE FUNCTION public.find_file(
        integer,
        OUT _id integer,
        OUT name character varying,
        OUT type character varying,
        OUT data bytea)
        RETURNS record
        LANGUAGE 'sql'
        COST 100
        VOLATILE PARALLEL UNSAFE
    AS $BODY$
    SELECT _id, name, type, data FROM files WHERE _id = $1 LIMIT 1
    $BODY$;
    

    and the query() function...

    import pg from 'pg'
    
    const pgNativePool = new pg.native.Pool({
      max: 10, // default
      connectionString: <string> import.meta.env.VITE_DATABASE_URL,
      ssl: {
        rejectUnauthorized: false
      }
    })
    
    type QueryResponse = (sql: string, params?: Array<any>) => Promise<pg.QueryResult<any>>
    export const query: QueryResponse = (sql: string, params?: Array<any>) => pgNativePool.query(sql, params)