Feedback: Reconsidering Limitations on SQL Views in Prisma 6.13

[renatocron]

Question

Hi Prisma Team & Community,

I'm opening this thread to discuss the recent changes in Prisma 6.13.0, specifically the restrictions introduced on SQL Views, including:

• Disallowing @id, @unique/@@unique, and @relation on views
• Disabling features like findUnique, cursor-based pagination, implicit ordering, and relations involving views

🚩 Why This Matters

In many real-world, complex applications, views aren't just "simple virtual tables." Instead, they're frequently critical for efficiently handling:

• Dynamic, composable filtering at runtime with type safety
• Read-only projections combining data across tables, optimized for frontend consumption
• Performance-optimized joins that abstract complex relationships

While Prisma's new limitations align closely with SQL semantics, they unintentionally eliminate practical, previously functional patterns crucial in sophisticated applications.

With the views, I was able to get prisma to build complex ACL-based filtering logic with dynamic permissions on the real tables, then only use the views for the projections.

If we had a way to convert from Prisma.Enumerable<Prisma.[Table]WhereInput> to SQL from prisma itself, we could theoretically at least use queryRaw and keep the builders with the type safety yet.

📌 Concrete Examples from Real Usage

Below are three real scenarios demonstrating these limitations clearly:

Example 1: Advanced Dashboard with Dynamic Filtering & Relations

// Previously functional in Prisma <6.13
// github is open source: https://github.com/AppCivico/smae/blob/homol/backend/src/variavel/variavel.service.ts
const filterSet = this.getVariavelGlobalWhereSet(filters, palavrasChave);
const linhas = await this.prisma.viewVariavelGlobal.findMany({
    where: {
        AND: filterSet,
        variavel_mae_id: filters.variavel_mae_id ?? null,
    },
    include: {
        orgao: { select: { id: true, sigla: true, descricao: true } },
        variavel: {
            select: {
                supraregional: true,
                variavel_categorica_id: true,
                medicao_orgao: { select: { id: true, sigla: true } },
            },
        },
        orgao_proprietario: { select: { id: true, sigla: true, descricao: true } },
        regiao: {
            select: { 
                id: true, nivel: true, descricao: true, 
                parente_id: true, codigo: true, pdm_codigo_sufixo: true 
            },
        },
    },
    orderBy: [
        { [filters.ordem_coluna]: filters.ordem_direcao === 'asc' ? 'asc' : 'desc' }, 
        { codigo: 'asc' }
    ],
    skip: offset,
    take: ipp,
});

Example 2: Permission-Based Dashboard Consolidation

// Complex permission filtering with counting and pagination
// https://github.com/AppCivico/smae/blob/homol/backend/src/mf/ps-dash/ps-dash.service.ts
const dashPermissionsSet: Prisma.Enumerable<Prisma.view_ps_dashboard_consolidadoWhereInput> = [
    {
        meta: {
            AND: permissionsSet.length > 0 ? { AND: permissionsSet } : undefined,
            pdm_id: filtros.pdm_id,
            id: filtros.meta_id ? { in: filtros.meta_id } : undefined,
        },
        pendente: filtros.apenas_pendentes !== undefined ? filtros.apenas_pendentes : undefined,
        equipes: filtros.equipes && Array.isArray(filtros.equipes) ? { hasSome: filtros.equipes } : undefined,
        equipes_orgaos: filtros.orgao_id && Array.isArray(filtros.orgao_id) ? { hasSome: filtros.orgao_id } : undefined,
        AND: [filtros.visao_pessoal ? { equipes: { hasSome: equipes_pessoa } } : {}],
    },
];

const totalCount = await this.prisma.view_ps_dashboard_consolidado.count({ 
    where: { AND: dashPermissionsSet } 
});

const paginatedResults = await this.prisma.view_ps_dashboard_consolidado.findMany({
    where: { AND: dashPermissionsSet },
    orderBy: [{ order_meta: 'asc' }, { order_iniciativa: 'asc' }, { order_atividade: 'asc' }],
    skip: offset,
    take: ipp,
});

Example 3: IoT Device Management with Complex Relationships

In IoT applications, views are essential for handling device relationships where multiple tables reference external system keys. For instance, detecting inactive devices across organizations:

// Previously worked: views with relations for complex device queries
const inactiveDevices = await prisma.view_inactive_devices.findMany({
    where: { 
        org_id: userOrgId,
        device_register: {
            device_type_hex: { in: allowedDeviceTypes }
        }
    },
    include: {
        device_register: {
            select: {
                device_type_hex: true,
                organization: { select: { name: true } }
            }
        }
    }
});

This pattern enabled efficient querying of complex device states (active/inactive) while maintaining relationships to device metadata, firmware versions, and organizational data - all through a performant view that abstracted the underlying EXISTS/NOT EXISTS logic.

Crucially, passing WHERE conditions directly to the view allows the SQL query planner to push down predicates into the view definition, enabling optimizations like:

• Index usage on the underlying tables within the view's logic
• Early filtering before expensive JOIN operations
• Elimination of unnecessary EXISTS checks when org_id is filtered

Without view relations, developers must either fetch all view results and filter in-memory (defeating the purpose of database optimization) or resort to complex manual SQL that loses type safety and Prisma's composability benefits.

Why TypedSQL Isn't a Complete Solution

While TypedSQL (introduced in v5.19.0) is excellent for static queries, it has significant limitations:

• No dynamic column construction or arbitrary runtime filter building
• Loses composability that makes Prisma's query builder powerful
• Requires static .sql files that can't adapt to runtime conditions
• No type-safe filter composition like the getVariavelGlobalWhereSet() patterns above

TypedSQL works great for known, static queries, but cannot replicate the dynamic filtering and relational traversals that views + Prisma Client enabled.

🚧 Request for Reconsideration

I fully understand and appreciate Prisma's goal of aligning views with SQL semantics, but could we explore alternatives such as:

Option 1: Explicit Read-Only Relations

• Permit clearly documented read-only "virtual" relations on views
• Disable write operations while maintaining read capabilities and includes

Option 2: Opt-In Advanced Mode

• Introduce an explicit opt-in flag (e.g., @@view.strict(false)) for advanced use cases or other keyword eg: model|view|relationed_view
• Maintain flexibility for users who understand the risks and trade-offs

Option 3: Ideas?

Conclusion

The recent Prisma 6.13 changes, while technically correct from a SQL perspective, inadvertently limit crucial real-world use cases that many teams rely on. This undermines Prisma's position as a versatile, developer-friendly ORM.

Views with relations and virtual relations enabled powerful patterns for:

• Security: ACL-based filtering at the database level
• Performance: Optimized joins and projections
• Maintainability: Single source of truth for complex business logic
• Developer Experience: Type-safe, composable queries

The community needs a path forward that balances SQL correctness with practical usability. Perhaps there's a middle-ground solution we can collaboratively explore that preserves both semantic correctness and developer productivity.

I'd love to hear thoughts from both the Prisma team and the broader community. How are others handling these limitations? What migration strategies have worked?

Thanks for reading, and looking forward to a productive discussion!

[renatocron]

thanks! https://github.com/prisma/prisma/releases/tag/6.14.0