Postgres.js problem with double quotes when creating an Enum

General
1

So I’m using Postgres.js to build an application and I’m creating an Enum for user roles like the following

import { PostgresError } from 'postgres';
import sql from './database/index.js';


const RolesEnum = {
    user : 'user',
    moderator : 'moderator',
    admin : 'admin'
} as const;

const Enum = ['user', 'moderator', 'admin'] as const;

async function Build() {
    try {
        await sql`DROP TYPE F_that;`.catch(() => {}); // IF DOESN'T EXIST THEN PASS
        const { statement } = await sql`CREATE TYPE F_that AS ENUM (${sql(
            Enum
        )});`;
        console.log(statement);
    } catch (error) {
        console.error(
            (error as PostgresError).query ?? (error as Error).message ?? error
        );
        /* Logs the following
          CREATE TYPE F_that AS ENUM ( "user", "moderator" , "admin" );*/
    }
}

await Build() // Logs the following: CREATE TYPE F_that AS ENUM ( "user", "moderator" , "admin" );`

as you see, the problem is the double quotes in the Enum values, which gives an Error in postgres my question is how to make them single quotes in the query

the closest solution I got was adding this to the catch block

if (error instanceof postgres.PostgresError) {
            error.query.replaceAll('"', "'"); // gets query with single quotes for the Enum
   }

which gives the right query but i can’t use it like above (Whenever I try to get an error about a positional parameter, i guess it runs at run time?)

2

Postgres.js automatically adds double quotes " around enum values when you use ${sql(Enum)} interpolation because it treats them as identifiers, not string literals.

To get single quotes ' around enum values in your CREATE TYPE ... AS ENUM query, you should build the enum list as a raw SQL string with properly escaped single-quoted values, not use the array interpolation.

Example fix:

const Enum = ['user', 'moderator', 'admin'];

async function Build() {
  try {
    await sql`DROP TYPE IF EXISTS F_that;`;

    // Manually create the enum list with single quotes
    const enumValues = Enum.map(v => `'${v}'`).join(', ');

    // Use raw SQL tag with template literal to insert the string as is
    const { statement } = await sql`
      CREATE TYPE F_that AS ENUM (${sql.raw(enumValues)})
    `;

    console.log(statement);
  } catch (error) {
    console.error(error);
  }
}

await Build();

Key points:

  • Don’t pass the enum array directly to ${sql(...)}; that causes double quotes.
  • Instead, join the array into a string with single quotes manually.
  • Use sql.raw() to inject this string without further quoting.

This will generate:

CREATE TYPE F_that AS ENUM ('user', 'moderator', 'admin');

which is valid Postgres syntax.