AH Posts - Answer Overflow

Explore posts from servers

•Created by AH on 9/16/2024 in #help-and-questions

schema "extensions" does not exist' error in shadow DB during initial migration on Neon

When running 'prisma migrate dev', I get this error on my first migration: 'Migration 0_init failed to apply cleanly to the shadow database. Error: ERROR: schema "extensions" does not exist' The migration includes 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";'. However, the 'extensions' schema and uuid-ossp extension already exist in my database. What's causing this shadow database issue, and how can I resolve it without editing existing migrations?

2 replies

PPrisma

•Created by AH on 8/28/2024 in #help-and-questions

TypedSQL - JSONB fields

Using TypedSQL, I noticed that JSONB fields are inferred as JsonValue. Is there a way to get more specific type inference for complex JSON structures in PostgreSQL queries, or is this a current limitation of TypedSQL?

2 replies

DTDrizzle Team

•Created by AH on 5/22/2024 in #help

Self-Join on a CTE

Is it possible to perform a self-join on a CTE? The main problem is that I cannot alias the CTE for the self-join operation. This prevents me from referencing the previous year's data within the same query. The documentation example demonstrates how to alias and self-join a regular table, but I am unsure how to apply this to a CTE. Below is a minimal code snippet demonstrating the issue:

const cte = ctx.db.main.$with("cte").as(
  ctx.db.main
    .select({
      year: MainTable.Year,
    })
    .from(MainTable)
);

// can't do this
const cteAlias = alias(cte, "parent");

return ctx.db.main
  .with(cte)
  .select({
    prevYear: sql<number>`CAST(SUM(${cteAlias.year} - 1) AS numeric)`,
  })
  .from(cte)
  .leftJoin(cteAlias, eq(cteAlias.id, cte.id));

const cte = ctx.db.main.$with("cte").as(
  ctx.db.main
    .select({
      year: MainTable.Year,
    })
    .from(MainTable)
);

// can't do this
const cteAlias = alias(cte, "parent");

return ctx.db.main
  .with(cte)
  .select({
    prevYear: sql<number>`CAST(SUM(${cteAlias.year} - 1) AS numeric)`,
  })
  .from(cte)
  .leftJoin(cteAlias, eq(cteAlias.id, cte.id));

4 replies

Gaming

Programming