TypeScript Upsert PostgreSQL-sekvensfejl: "Relation 'customers_sq' eksisterer ikke"

Forstå PostgreSQL-sekvensfejl i Upserts

Arbejde med PostgreSQL og TypeScript, især under en upsert-handling, kan nogle gange føre til uventede sekvensfejl. En sådan almindelig fejl indebærer, at databasen ikke genkender en sekvens, hvilket fører til meddelelser som "relation 'customers_sq' does not exist". Denne fejl opstår typisk, når der henvises til sekvenser forkert i SQL-forespørgsler.

I denne artikel vil vi udforske et scenarie i den virkelige verden, hvor en udvikler støder på dette problem, mens han udfører en upsert. Vi vil diskutere, hvordan sekvenser fungerer i PostgreSQL og identificere almindelige fejl, når vi refererer til dem, især i TypeScript.

Ofte opstår disse fejl på grund af forkert syntaks eller skemaomfang, især når man har at gøre med sekvenser i forskellige databaseskemaer eller navnerum. Fejlretning af problemet kræver omhyggelig opmærksomhed på, hvordan PostgreSQL forventer, at der refereres til sekvenser i forespørgsler.

Ved slutningen af ​​denne vejledning vil du have en klarere forståelse af, hvorfor denne "relation eksisterer ikke"-fejl opstår, og de trin, du kan tage for at rette den. Dette inkluderer praktiske tips til at løse sekvensreferenceproblemer og sikre, at dine upserts fungerer efter hensigten i PostgreSQL.

Sådan håndteres PostgreSQL-sekvensfejl i Upserts

Scripts i de tidligere eksempler er designet til at løse et almindeligt problem, der opstår, når der refereres til sekvenser i PostgreSQL, især under en oprøre operation i TypeScript. En upsert-operation indsætter enten nye poster eller opdaterer eksisterende, hvilket gør den korrekte brug af sekvenser afgørende for at opretholde unikke primærnøgler. Nøgleproblemet her stammer fra forkert sekvenshenvisning, hvilket fører til fejlen: "relation '' eksisterer ikke". Løsningen involverer at kalde den korrekte PostgreSQL-funktion, NEXTVAL, for at generere den næste værdi fra sekvensen, hvilket sikrer, at der tildeles et unikt ID til hver ny post i "kunder"-tabellen.

Det første script løser dette problem ved at sikre, at sekvensen er korrekt refereret med skemabevidsthed. Når vi bruger NEXTVAL('db.customers_sq'), specificerer vi både skemaet ("db") og sekvensen ("customers_sq"), hvilket sikrer, at PostgreSQL søger efter sekvensen i den korrekte kontekst. Hvis skemaet udelades eller refereres forkert, kan PostgreSQL muligvis ikke finde sekvensen, hvilket udløser fejlen. Denne kommando virker inden for en parametriseret forespørgsel i TypeScript, hvilket sikrer, at brugerinput sendes sikkert ind i forespørgslen for at forhindre SQL-injektionsangreb.

Derudover leveres en alternativ løsning ved hjælp af dynamisk sekvenskontrol. Denne tilgang stiller spørgsmål til PostgreSQL-katalogvisningen, pg_sekvenser, for at bekræfte eksistensen af ​​sekvensen, før du forsøger at indsætte eller opdatere posten. Dette tilføjer ikke kun et lag af fejlhåndtering, men sikrer også, at scriptet er fleksibelt og robust, i stand til at tilpasse sig ændringer i databaseskemaet. Ved at søge efter sekvensen dynamisk, kan systemet give en mere informativ fejlmeddelelse, hvis sekvensen mangler eller der henvises til forkert, hvilket forbedrer fejlfindingsprocessen.

Endelig er enhedstest en væsentlig del af løsningen. De Spøg testsuite bruges til at sikre, at upsert-funktionen opfører sig som forventet. I testene håndteres både vellykkede operationer og fejltilfælde, såsom manglende sekvenser. Testcaserne bruger metoder som f.eks mockResolvedValueOnce og mockRejectedValueOnce at simulere, hvordan databasen reagerer på forespørgsler. Ved at verificere, at de korrekte SQL-kommandoer udføres, og at fejl kastes korrekt, når sekvenser mangler, hjælper testcaserne med at sikre pålideligheden af ​​løsningen på tværs af forskellige miljøer.

// TypeScript - Upsert solution using parameterized query with correct sequence reference
import { Pool } from 'pg';
const pool = new Pool();
const SAVE_CUSTOMER = `
  INSERT INTO "db"."customers" ("id", "name")
  VALUES (NEXTVAL('db.customers_sq'), $1)
  ON CONFLICT ("id") DO UPDATE SET "name" = $1`;
async function saveCustomer(name: string) {
  try {
    await pool.query(SAVE_CUSTOMER, [name]);
    console.log('Customer saved successfully');
  } catch (error) {
    console.error('Error saving customer:', error.message);
  }
}

// TypeScript - Dynamic sequence referencing with schema awareness
import { Pool } from 'pg';
const pool = new Pool();
async function saveCustomer(name: string) {
  try {
    const checkSequence = `SELECT EXISTS (
      SELECT 1 FROM pg_sequences WHERE schemaname = 'db' AND sequencename = 'customers_sq');`;
    const sequenceExists = await pool.query(checkSequence);
    if (!sequenceExists.rows[0].exists) {
      throw new Error('Sequence not found');
    }
    const SAVE_CUSTOMER = `
      INSERT INTO "db"."customers" ("id", "name")
      VALUES (NEXTVAL('db.customers_sq'), $1)
      ON CONFLICT ("id") DO UPDATE SET "name" = $1`;
    await pool.query(SAVE_CUSTOMER, [name]);
    console.log('Customer saved successfully');
  } catch (error) {
    console.error('Error saving customer:', error.message);
  }
}

// Jest - Unit test for saveCustomer function
import { saveCustomer } from './saveCustomer';
import { pool } from 'pg';
jest.mock('pg');
describe('saveCustomer', () => {
  it('should insert new customer if no conflict', async () => {
    pool.query.mockResolvedValueOnce({});
    await saveCustomer('John Doe');
    expect(pool.query).toHaveBeenCalledWith(expect.any(String), ['John Doe']);
  });
  it('should throw error if sequence does not exist', async () => {
    pool.query.mockRejectedValueOnce(new Error('Sequence not found'));
    await expect(saveCustomer('John Doe')).rejects.toThrow('Sequence not found');
  });
});

Nøglefaktorer bag PostgreSQL-sekvensfejl

Et aspekt, der ikke tidligere er dækket, er, hvordan PostgreSQL håndterer følsomhed over for store og små bogstaver når det kommer til databaseobjekter som sekvenser. PostgreSQL behandler som standard identifikatorer uden anførselstegn som små bogstaver. Det betyder, at hvis et sekvensnavn blev oprettet med store bogstaver, men der henvises til uden anførselstegn, vil PostgreSQL automatisk søge efter versionen med små bogstaver. For eksempel, hvis sekvensen blev oprettet som "Customers_SQ", men refereret til som NEXTVAL('customers_sq'), kan det føre til fejlen, "relationen eksisterer ikke". Brug af dobbelte anførselstegn omkring sekvensnavnet, som f.eks NEXTVAL('"Customers_SQ"'), sikrer, at PostgreSQL bruger det nøjagtige tilfælde som defineret.

Et andet afgørende aspekt er skemasynlighed i PostgreSQL. Som standard søger PostgreSQL efter sekvenser i det skema, der er først i søgestien, medmindre et skema er eksplicit defineret. Hvis sekvensen ligger i et andet skema, skal du referere til det uden at specificere skemaet (f.eks. NEXTVAL('db.customers_sq')) kan føre til en sekvens-ikke-fundet fejl. Udviklere skal enten justere søgestien eller eksplicit henvise til skemaet for at undgå dette problem, især i komplekse databasestrukturer med flere skemaer.

Til sidst er det vigtigt at nævne databasen tilladelser. Hvis en bruger ikke har de nødvendige rettigheder til at få adgang til eller ændre en sekvens, kan de støde på fejl som "relationen eksisterer ikke". Tildeling af de korrekte tilladelser til roller, der interagerer med databasesekvensen, sikrer, at de kan hente den næste værdi via NEXTVAL uden problemer. Dette er især vigtigt i produktionsmiljøer med streng adgangskontrol og flere roller, der interagerer med databasen.