DEV Community: hellowwworld

Build One Guarded Prisma Endpoint, Then Break It Five Ways

hellowwworld — Sun, 16 Aug 2026 21:21:06 +0000

Generated routes save time by removing repeated Express handlers. This tutorial builds one small Prisma endpoint, then changes it in five ways. You will see which mistakes stop startup, reject a request, or silently change the query sent to Prisma.

This is a maintainer-built, lab-backed walkthrough of the current implementation—not a report of long-running community usage.

The examples use prisma-guard 1.33.0, Prisma 6.19.3, and Zod 4.4.3. Those versions are pinned because several observations concern exact runtime behavior. The goal is a test you can rerun during upgrades, not a rule inferred from one successful response.

Start with two models. Nursery owns many Plant rows, and Plant.nurseryId is the field the generated tenant filter can add automatically.

/// @scope-root
model Nursery {
  id     String  @id @default(cuid())
  name   String
  plants Plant[]
}

model Plant {
  id          String   @id @default(cuid())
  name        String
  priceCents  Int
  isPublished Boolean  @default(false)
  nurseryId   String
  nursery     Nursery  @relation(fields: [nurseryId], references: [id])
}

The generated router still needs an extended Prisma client and trusted request context. Authentication remains application code. The important detail is that the tenant ID comes from the authenticated session, not from the query string or body.

import { AsyncLocalStorage } from 'node:async_hooks'
import { PrismaClient } from '@prisma/client'
import { guard } from './generated/guard/client'

type RequestContext = { nurseryId: string; audience: 'public' | 'seller' }

const requestStore = new AsyncLocalStorage<RequestContext>()
const prisma = new PrismaClient().$extends(
  guard.extension(() => {
    const context = requestStore.getStore()
    return { Nursery: context?.nurseryId, caller: context?.audience }
  }),
)

Now define one public read contract. In a guard shape, true means the client may choose a value. A literal means the server chose it. force(true) is required to pin a Boolean to true because bare true is already the permission sentinel.

import { force } from 'prisma-guard'

const publicPlants = {
  where: {
    name: { contains: true, mode: 'insensitive' },
    isPublished: { equals: force(true) },
  },
  select: { id: true, name: true, priceCents: true },
  orderBy: { name: true, priceCents: true },
  take: { max: 50, default: 20 },
}

const plantRoutes = {
  findMany: { shape: { public: publicPlants } },
  guard: { resolveVariant: () => 'public' },
}

This contract says more than “validate a query.” It pins publication state, fixes case-insensitive search, limits filter and sort fields, supplies a default projection, and bounds page size. The router selects the public variant on the server. A client cannot upgrade itself by inventing a variant header.

Tenant scope is a separate layer from the public shape. The extension can inject a mapped top-level foreign key when trusted root context exists, but the root model does not scope itself and nested relation reads do not inherit the filter. Keep the authenticated context test separate from the forced publication test so a failure identifies which boundary moved.

With the working shape in place, break it on purpose.

Break: force the field instead of its operator

Change the publication predicate to isPublished: force(true). That resembles the correct mutation syntax, but a where field expects an operator object. Shape construction fails before any client input is examined:

Operator "value" not supported for type "Boolean"

The correction is not to remove the force. Put it under the comparison operator: { equals: force(true) }. Remember the asymmetry: a where shape forces an operator; a data shape forces the field itself.

This is a startup or first-use configuration defect, depending on when the application builds the shape. Retrying the request cannot repair it.

That phase distinction prevents the wrong debugging detour. Shape construction examines the server-authored contract. Request validation examines a particular body. If the same error appears with an empty body and with every caller, reduce the shape before investigating transport encoding. Conversely, a path such as where.name in an invalid-query message identifies the client-facing schema that rejected input. The model and operation in the message are evidence about where the boundary was built.

Break: send a server-owned modifier from the client

The shape lets the client choose contains but fixes mode beside it. That makes mode strict. If a frontend sends the value anyway, even the same value, validation rejects the key:

{
  "where": {
    "name": { "contains": "fern", "mode": "insensitive" }
  }
}

The pinned guard returns Invalid query on model "Plant": where.name: Unrecognized key(s): mode.

This failure is useful. It says the frontend and shape disagree about value ownership. Removing mode from the request preserves the endpoint's case-insensitive contract because the server adds it. Changing the shape to mode: true is a different API: callers may choose, and omission becomes case-sensitive.

The same strict behavior appears in three other positions: forced predicates inside relation filters, forced fields inside a nested include's where, and forced fields in mutation data. Sending a server-owned key in those positions produces a validation error even when the client repeats the correct value. Do not generalize from one forced field. First classify its position, then decide whether the client must omit it or whether a conflicting value will be discarded.

There is also a construction-only edge outside that position matrix. On the pinned guard, a forced condition under a negative relation operator such as to-many none or to-one isNot is rejected while the shape is built. The message discusses mixing client and forced conditions, but the rejection also occurs when the negative branch is wholly forced. Positive some, every, and to-one is shapes accept the corresponding condition. Treat that behavior as version-specific and keep it in the upgrade suite.

Break: expect a conflicting top-level force to throw

Now send isPublished.equals: false while the shape forces true. It is tempting to expect a 400. On the pinned runtime, that expectation is wrong. A wholly forced scalar predicate at the top level of where is the one lenient forced position. Client input for that field is accepted, discarded, and replaced.

const publicationBoundary = {
  where: { isPublished: { equals: force(true) } },
  take: { max: 50, default: 20 },
}

const args = guard
  .query('Plant', 'findMany', publicationBoundary)
  .parse({ where: { isPublished: { equals: false } } })

console.log(JSON.stringify(args))
// {"take":20,"where":{"isPublished":{"equals":true}}}

The same outcome occurs when the client supplies true, false, or another supported operator. That does not make the field client-controlled. It means the runtime merges this position rather than removing it from the request schema.

A test that sends a foreign value and checks only for success proves nothing. The request succeeds with the forced predicate, and it can also succeed after a refactor removes the force. Either inspect emitted arguments directly or seed two tenants with distinguishable rows and assert the returned ownership boundary.

The runtime does detect a different kind of conflict: two incompatible forced values authored inside the server shape. For example, forcing publication true at the top level and false inside a combinator fails shape construction. That protects configuration consistency. It does not turn conflicting client input into an error. Tests should keep those cases separate because one proves the server contract is internally coherent and the other proves how the contract handles an untrusted request.

Break: assert that a forced predicate is always flat

The previous probe emitted where.isPublished directly because the request touched the same field. Add a client filter on a different field and the guard preserves both conditions by wrapping them in AND:

{
  "where": {
    "AND": [
      { "name": { "contains": "fern" } },
      { "isPublished": { "equals": true } }
    ]
  },
  "take": 20
}

An assertion such as args.where.isPublished.equals === true now fails even though the boundary is intact. The emitted container depends on the request. Tests should walk Boolean branches instead of assuming one layout.

type QueryNode = Record<string, QueryNode | QueryNode[] | string | number | boolean>

const findPredicate = (node: QueryNode | undefined, field: string): QueryNode | undefined => {
  if (!node) return undefined
  const direct = node[field]
  if (direct && !Array.isArray(direct) && typeof direct === 'object') return direct

  for (const key of ['AND', 'OR', 'NOT']) {
    const branch = node[key]
    const entries = Array.isArray(branch) ? branch : branch ? [branch] : []
    for (const entry of entries) {
      if (typeof entry !== 'object' || Array.isArray(entry)) continue
      const found = findPredicate(entry, field)
      if (found) return found
    }
  }
  return undefined
}

The walker also matters for forced NOT. With no client NOT, the emitted value can be an object. Once the client supplies a NOT, the emitted value becomes an array containing separate client and forced branches. Indexing one spelling makes the test request-dependent.

Forced values written inside AND or OR introduce another structural change: they are lifted out and applied as top-level AND constraints. That is safe for restrictions such as “public only,” but it cannot express “owned by me or public.” The force turns that intended disjunction into a conjunction. Use a single scope column, a purpose-built server query, or database policy for a genuinely disjunctive authorization rule. A generated shape should not be stretched past the Boolean policy it can represent.

Break: test default projection through the parser

Read projection is both a whitelist and an execution-time default. But guard.query().parse() does not auto-apply that default. The guarded delegate does. A parser-only projection assertion reports a missing boundary that execution would have applied:

guard.query('Plant', 'findMany', {
  select: { id: true, name: true },
  take: { max: 10 },
}).parse({})
// {}

prisma.plant.guard({
  select: { id: true, name: true },
  take: { max: 10 },
}).findMany({})
// delegate receives { select: { id: true, name: true } }

Use the parser for request-schema behavior, forced predicates, and bounds. Use a guarded delegate, real or fake, for projection auto-application and mutations. Use an HTTP/database test when the claim concerns status mapping, returned rows, or transaction behavior. These tests overlap in subject matter but not in what they prove.

Mutation projection has the opposite default from the read example. A select declared in a mutation shape is a permission boundary for client-supplied projection, but it does not automatically become the response projection unless enforceProjection is enabled. Test the deployed setting. Otherwise a create or update can return the full Prisma record even though the shape shows a narrow select.

Writes add one more ownership check. A forced field in update data is emitted on every call; it is not merely protected from client control. To prevent a caller changing a field while preserving its stored value, omit the field from the shape. In an upsert, review create and update independently so a safe create default does not become an unintended update side effect.

Keep the endpoint small enough to review

The five breaks produce a practical review order:

Build the shape and catch configuration errors before serving traffic.
Mark every value as client-owned or server-owned.
Assert forced predicates in the emitted argument tree.
Assert projection through execution, not parsing alone.
Add database fixtures only for outcomes that argument inspection cannot establish.

Generated CRUD is useful precisely because repetitive transport code disappears. The saved code does not eliminate decisions about tenant identity, caller routing, permitted filters, write ownership, or returned fields. Keep those decisions together in a small declared contract, and make each test name the layer it examines.

Store normalized output with the dependency versions that produced it. After an upgrade, a changed error string may require only documentation work; a changed emitted predicate or projection can change the contract. The review should compare cases by label rather than relying only on a total pass count. Expected parser/delegate differences belong in that record so their disappearance is as visible as a new divergence.

Finally, run one clean regeneration rather than trusting artifacts left by an earlier schema. Required create fields, relation metadata, and scope mappings are generated facts. A stale output directory can make a test accurately describe code the application no longer builds.

Run that clean regeneration, then execute all five break cases and compare their emitted arguments.

The deeper Prisma Generator Express Field Guide contains the full operation surface, reproduced error index, and evidence notes.

Disclosure: This article was AI-assisted, reviewed by the prisma-generator-express maintainer, and its behavior claims were checked against the repository's pinned lab. The author maintains the open-source project discussed here.

Prisma ORM at Raw SQL Speed? Just Convert JSON to String

hellowwworld — Mon, 19 Jan 2026 22:12:36 +0000

SQL Access Performance: A 50-Year Trade-off

When IBM introduced SQL in 1974, database access was straightforward. Submit a query string, receive results. No intermediate layers existed.

This directness created problems. Changing column names required manual searching across codebases. User input needed manual escaping to prevent SQL injection. Database migrations between systems required query rewrites due to dialect variations.

These issues led to Object-Relational Mappers in the mid-1990s. The concept: represent tables as programming language objects. Write code in your language instead of SQL strings. Let the framework translate.

Early ORM Adoption (1996-2005)

Hibernate released for Java in 2001. Rails incorporated ActiveRecord in 2004. Code transformed from scattered SQL strings to object-oriented patterns:

customer = Customer.find_by_email("customer@example.com")
orders = customer.orders
line_items = order.line_items

This provided type safety and refactorability.

Performance issues appeared immediately.

N+1 Query Problem

A standard pattern emerged:

orders = Order.limit(10)  # 1 query
orders.each do |order|
  puts order.customer.name # 10 more queries
end

ORMs loaded each related record separately. 2 queries became 11. This degraded application performance on larger datasets.

Solution: eager loading via Order.includes(:customer).limit(10)

Developers now needed to understand both their programming language and ORM-specific optimization techniques. The abstraction leaked implementation details.

Stack Overflow accumulated questions about ORM query optimization. The promised SQL abstraction added complexity instead of removing it.

Query Builders Appear (2010-2015)

Knex.js launched in 2012 with different goals: make SQL composable without hiding it.

knex('products')
  .where('category', 'electronics')
  .andWhere('price', '>=', 100)
  .join('inventory', 'products.id', 'inventory.product_id')
  .select('products.*', 'inventory.quantity')

Cleaner than string concatenation. More flexible than full ORMs. Still had method chaining runtime overhead and no type safety - column name typos caused runtime errors.

MongoDB gained adoption with a different pitch: store JSON directly, avoid ORM impedance mismatch entirely.

Companies migrated to NoSQL. SQL appeared outdated.

Production incidents accumulated: data inconsistencies, orphaned records without foreign keys, corrupted state without transactions, full collection scans without query planning.

Teams migrated back to PostgreSQL. Data had schema structure - they'd just been enforcing it poorly in application code.

Observation: SQL databases were slow due to layers between application and database, not SQL itself.

GraphQL Attempted Solution (2015-2020)

Facebook released GraphQL in 2015. Clients specify exact data requirements in one request.

query {
  company(id: 1) {
    name
    employees(limit: 10) {
      fullName
      department { name }
    }
  }
}

Resolvers needed to translate GraphQL into database queries. This created the same N+1 problems:

Company: {
  employees: (parent) => db.query('SELECT * FROM employees WHERE company_id = ?', parent.id)
}
Employee: {
  department: (parent) => db.query('SELECT * FROM departments WHERE id = ?', parent.department_id)
}

DataLoader was created for query batching. Query complexity analysis prevented expensive queries. Persisted queries avoided parsing overhead. The ecosystem built layers to solve layer-created problems.

GraphQL's flexibility required rate-limiting because users could request excessive data.

TypeScript-Based ORMs (2018-2024)

TypeORM (2016) and MikroORM (2018) added decorators and type safety:

@Entity()
class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  sku: string;

  @ManyToOne(() => Category, category => category.products)
  category: Category;
}

Compile-time type safety. Traditional ORM model with classes, active records, eager/lazy loading.

Drizzle ORM (2022) used SQL-like syntax with TypeScript inference:

const result = await db
  .select()
  .from(products)
  .where(eq(products.inStock, true))
  .leftJoin(categories, eq(products.id, categories.product_id))

Closer to SQL. Better performance than traditional ORMs. Still had runtime query building overhead from method chaining.

Prisma appeared.

Prisma's Architecture (2018-2024)

Prisma 1.0 released in 2018. Prisma 2.0 in 2020 introduced a different model. No classes, decorators, or method chaining. Schema file generates a client:

model Order {
  id          Int      @id
  orderNumber String   @unique
  items       Item[]
}

Declarative query API:

prisma.order.findMany({
  where: { status: 'PENDING', total: { gte: 100 } },
  include: { items: { where: { inStock: true } } },
  orderBy: { createdAt: 'desc' },
  take: 10
})

TypeScript knows all fields, relations, and filters. Column renames produce TypeScript errors everywhere. No runtime surprises.

Developers adopted it. Type safety without codegen scripts. Migrations without manual SQL. Functional IntelliSense.

Performance questions appeared in GitHub issues. Pattern: queries 2-5x slower through Prisma versus raw postgres.js or pg.

Prisma team improved performance across versions. Version 7 rewrote the engine in TypeScript for better characteristics.

Gap remained. Not due to poor engineering. The architecture required a translation layer.

Alternative Approach

Prisma's query format resembles SQL:

{
  where: { status: 'PENDING', total: { gte: 100 } },
  include: { items: { where: { inStock: true } } },
  orderBy: { createdAt: 'desc' },
  take: 10
}

Maps 1:1 to SQL:

where → WHERE clause
include → JOIN or subquery
orderBy → ORDER BY
take → LIMIT

The declarative JSON alone is insufficient. Prisma provides DMMF (Data Model Meta Format) - complete schema metadata:

{
  models: [{
    name: "Order",
    fields: [
      { name: "id", type: "Int", isRequired: true },
      { name: "orderNumber", type: "String", isRequired: true },
      { 
        name: "items", 
        type: "Item",
        isRelation: true,
        relationName: "OrderItems",
        foreignKey: ["orderId"],
        references: ["id"]
      }
    ]
  }]
}

DMMF specifies:

Field existence and types
Relation structure and connections
Foreign key mappings
Required/optional fields
Array/scalar types

Without DMMF, SQL generation requires guessing. With DMMF, it's deterministic. Query JSON + schema metadata = complete SQL generation instructions.

Direct SQL generation from this combination eliminates the query engine and protocol. Query JSON + DMMF → SQL string.

Not novel - Drizzle already builds SQL directly. But Drizzle requires code-based schema. Prisma's DMMF generates from schema file, maintaining single source of truth.

Hypothesis: if query JSON + DMMF contains everything for SQL, the translation layer is overhead.

Implementation

Pattern matching implementation:

WHERE clauses: Recursive tree walking. { AND: [...] } → (...) AND (...). Operators map: gte → >=, contains → LIKE '%value%', in → IN (...).

Relations: Each include → JSON aggregation subquery. DMMF provides foreign key mappings. Postgres: json_agg(), SQLite: json_group_array(). Database handles the work instead of application-side loading and joining.

Pagination: Convert cursor + orderBy into WHERE conditions positioning after cursor. More complex than OFFSET, stable when data changes.

Dialects: Postgres/SQLite differ in arrays, case-insensitive search, JSON. Abstract to functions: arrayContains(), caseInsensitiveLike(). SQL builder calls functions without dialect knowledge.

Result: ~3000 lines of pure functions. Same input → same output. Enables prebaking queries at build time.

Implementation Steps

Step 1: Install

npm install prisma-sql postgres

Step 2: Add three lines

import { PrismaClient, Prisma } from '@prisma/client'
import { speedExtension, convertDMMFToModels } from 'prisma-sql'
import postgres from 'postgres'

const models = convertDMMFToModels(Prisma.dmmf.datamodel)
const sql = postgres(process.env.DATABASE_URL)
const prisma = new PrismaClient().$extends(speedExtension({ postgres: sql, models }))

// Existing code unchanged
const orders = await prisma.order.findMany({
  where: { status: 'PENDING' },
  include: { items: true }
})

No code changes needed.

Behavior:

findMany, findFirst, findUnique, count, aggregate, groupBy → Direct SQL
create, update, delete, transactions → Prisma engine (unchanged)

Existing code works:

// Now 2-7x faster
await prisma.order.findMany({ where: { status: 'PENDING' } })

// Unchanged (uses Prisma engine)
await prisma.order.create({ data: { orderNumber: 'ORD-12345' } })

// Complex includes work, faster
await prisma.order.findMany({
  where: { status: 'PENDING' },
  include: {
    items: {
      where: { inStock: true },
      include: { product: true }
    }
  }
})

Optional debugging:

const prisma = new PrismaClient().$extends(
  speedExtension({ 
    postgres: sql, 
    models,
    debug: true,
    onQuery: (info) => {
      console.log(`${info.model}.${info.method}: ${info.duration}ms`)
    }
  })
)

Optional generator mode:

Schema annotation for hot queries:

/// @optimize {
///   "method": "findMany",
///   "query": { "where": { "status": "" }, "take": "$take" }
/// }
model Order {
  id     Int    @id
  status String
}

Run prisma generate. Prebaked queries have ~0.03ms overhead instead of ~0.2ms.

Three lines to add. Zero lines to change.

Performance Results

Testing 137 queries comparing Prisma v7, Drizzle ORM, direct SQL generation:

PostgreSQL (selected):

Query Type	Prisma v7	Drizzle	Direct SQL	vs Prisma	vs Drizzle
Simple WHERE	0.34ms	0.24ms	0.17ms	2.0x	1.4x
Complex conditions	6.90ms	5.58ms	2.37ms	2.9x	2.4x
With relations	0.72ms	N/A*	0.41ms	1.8x	-
Nested relations	14.34ms	N/A*	4.81ms	3.0x	-
Multi-field ORDER BY	2.38ms	1.54ms	1.09ms	2.2x	1.4x

SQLite (selected):

Query Type	Prisma v7	Drizzle	Direct SQL	vs Prisma	vs Drizzle
Simple WHERE	0.23ms	0.10ms	0.03ms	7.7x	3.3x
Complex conditions	3.87ms	1.85ms	0.93ms	4.2x	2.0x
Relation filters	128.44ms	N/A*	2.40ms	53.5x	-
Multi-field ORDER BY	0.59ms	0.43ms	0.37ms	1.6x	1.2x

* Drizzle lacks equivalent patterns - requires manual subqueries

vs Prisma: 2-7x speedup from bypassing query engine.

vs Drizzle: Drizzle builds SQL directly but has method chaining overhead. Direct approach is 1.4-3.3x faster for comparable queries. Drizzle can't easily express nested relation loading - manual subqueries or multiple roundtrips required.

Relation handling: Largest gap. Prisma's include syntax is powerful but slow through engine. Drizzle lacks equivalent - manual joins required. Direct SQL generation with JSON aggregation subqueries maintains developer experience at raw SQL performance.

Speedup from execution path length:

Prisma: JavaScript → Query Engine Protocol → Engine Translation → SQL Generation → postgres.js → Database

Drizzle: JavaScript → Method Chain Resolution → SQL Building → postgres.js → Database

Direct (runtime): JavaScript → SQL Generation (~0.2ms) → postgres.js → Database

Direct (generator): JavaScript → Map lookup (~0.03ms) → postgres.js → Database

Two fewer layers than Prisma. One fewer than Drizzle. Generator mode eliminates SQL building.

Generator Mode Details

Runtime SQL generation: ~0.2ms per query. For frequent queries, this is eliminable.

The @optimize directive specifies which query shapes to prebake:

/// @optimize {
///   "method": "findMany",
///   "query": {
///     "where": { "status": "" },
///     "orderBy": { "createdAt": "desc" },
///     "take": "$take",
///     "skip": "$skip"
///   }
/// }
model Order {
  id        Int      @id
  status    String
  createdAt DateTime
}

Key point: Actual values in directive are irrelevant. They're placeholders describing query structure:

"status": "" - Any value works. Indicates "filter by status exists"
"take": "$take" - $ prefix is convention. "take": 10 works identically
"orderBy": { "createdAt": "desc" } - Literal defining structure

What matters:

Which fields (status, createdAt)
Which operators (equality for status, ordering for createdAt)
Which parameters from runtime (take, skip, status value)

Becomes parameterized query. Generator creates:

const QUERIES = {
  Order: {
    findMany: {
      '{"where":{"status":""},"orderBy":{"createdAt":"desc"},"take":"$take","skip":"$skip"}': {
        sql: 'SELECT * FROM orders WHERE status = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3',
        params: [],
        dynamicKeys: ['status', 'take', 'skip']
      }
    }
  }
}

SQL has three parameters ($1, $2, $3). All values from runtime:

const args = { 
  where: { status: 'PENDING' },  // $1
  orderBy: { createdAt: 'desc' },
  take: 10,    // $2
  skip: 20     // $3
}

// Normalize to match prebaked key
const key = normalizeQuery(args) 

// O(1) Map lookup
const prebaked = QUERIES.Order.findMany[key]

if (prebaked) {
  const params = extractDynamicParams(args, prebaked.dynamicKeys)
  // → ['PENDING', 10, 20]

  return execute(prebaked.sql, params)
}

Security: All values parameterized. SQL injection impossible - values never enter SQL string, passed as separate parameters to database driver.

Overhead: Query normalization + Map lookup + parameter extraction. ~0.03ms.

Execution paths:

Prisma: JavaScript → Engine Protocol → Translation → SQL Generation → Parameter Binding → postgres.js → Database

Drizzle: JavaScript → Method Chain → SQL Building → Parameter Binding → postgres.js → Database

Runtime mode: JavaScript → SQL Generation (0.2ms) → Parameter Binding → postgres.js → Database

Generator mode: JavaScript → Map lookup (0.03ms) → Parameter Binding → postgres.js → Database

For endpoints with 50 queries:

Prisma: 50 × engine overhead
Drizzle: 50 × method resolution overhead
Runtime mode: 50 × 0.2ms = 10ms overhead
Generator mode: 50 × 0.03ms = 1.5ms overhead

Queries execute at database speed. Preparation overhead becomes negligible.

Limitations

Read-only optimization. Writes (create, update, delete) use Prisma. Query engine handles transactions, optimistic locking, cascading deletes. That complexity is necessary.

Requires postgres.js or better-sqlite3. Built specifically for these drivers due to speed and predictability.

Doesn't optimize queries. Missing indexes or inefficient query logic remain slow. This removes overhead from well-structured queries only.

Generator mode needs prebaking. Hot queries must be known ahead of time. Dynamic queries use runtime generation.

Additional maintenance. Prisma's query engine is battle-tested with full team support. This is a side project with associated risks.

Pattern Recognition

This isn't breakthrough work. It's pattern recognition.

Prisma's DMMF + query JSON format contains everything for SQL generation. Translation layer between them adds overhead without adding capability. Removing that layer is efficient.

Same pattern exists elsewhere:

Drizzle: SQL-like syntax compiles to SQL directly. No ORM abstraction.

Kysely: TypeScript query builder mirroring SQL structure. Minimal translation overhead.

Slonik: Tagged template literals that are SQL with parameter interpolation.

Common thread: when query format mirrors SQL structure, heavy abstraction is unnecessary overhead.

Prisma's contribution: DMMF - complete, structured database schema from single source of truth. Makes deterministic SQL generation possible without parallel schema definitions.

Generator mode extends this: if SQL generation is deterministic, it happens at build time. Query structure is prebaked, values parameterized, overhead negligible.

Use Cases

This is a performance optimization for specific scenarios, not a Prisma replacement:

High-traffic read queries
APIs with many database calls per request
Serverless functions where cold start matters
Applications measuring query performance

For standard CRUD apps with modest traffic, Prisma's ease of use likely outweighs 2x query performance. Convenience justifies the milliseconds.

For applications at database limits, milliseconds compound to seconds. Optimization becomes worthwhile.

Goal isn't proving Prisma is slow. Goal is offering an option when performance becomes critical without sacrificing type safety.

Observations

DMMF is critical. Structured schema metadata enables deterministic SQL generation. This works because Prisma solved schema representation.

Pure functions scale. Entire SQL builder uses pure functions. Makes testing trivial, enables build-time query prebaking.

Query structure ≠ query values. @optimize directive proves query shape (fields, operators) determines SQL structure. Actual values are always parameterized, never baked.

Abstractions should match reality. When abstraction format mirrors target format, translation layers add overhead without value.

Build-time optimization compounds. Generator mode proves deterministic transformations can move to build time, eliminating runtime overhead while maintaining security through parameterization.

ORMs aren't inherently slow. Slowness from translation complexity, not concept. Simplifying translation brings ORM convenience near raw SQL performance.

Prisma team built quality tooling. This explores whether read query execution could be more direct while keeping everything else Prisma provides.

Install: npm install prisma-sql postgres

Source: https://github.com/multipliedtwice/prisma-to-sql

Removing translation overhead while keeping Prisma's type safety and DMMF. Three lines to add, zero lines to change, 2-7x faster reads.

Generating a Type-Safe Node.js API with Prisma, TypeGraphQL, and GraphQL-Query-Purifier

hellowwworld — Sat, 18 Nov 2023 18:10:32 +0000

This guide demonstrates how to quickly build a fully functional, type-safe Node.js API using Prisma, typegraphql-prisma, and graphql-query-purifier. We'll create a full CRUD API with partial access to protect sensitive data, even with autogenerated resolvers.

Full example is available in repo

1. Project Setup and Prisma Integration

First, create a new Node.js project and integrate Prisma for database management.

Code for Project Setup:

mkdir nodejs-api && cd nodejs-api
npm init -y
npm i --save-dev @types/cors@2.8.16 @types/graphql-fields@1.3.9 @types/node@20.9.2 body-parser@1.20.2 cors@2.8.5 express@4.18.2 graphql-query-purifier prisma@5.6.0 ts-node@10.9.1 type-graphql@2.0.0-beta.1 typegraphql-prisma@0.27.1 typescript@5.2.2
npm i @apollo/server@4.9.5 @prisma/client@5.6.0 graphql@16.8.1 graphql-fields@2.0.3 graphql-scalars@1.22.4 reflect-metadata@0.1.13
npx tsc --init
npx prisma init --datasource-provider sqlite

2. Defining Prisma Models

Create a Prisma schema with models representing a company structure, including sensitive salary data.

schema.prisma:

// schema.prisma

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

generator client {
  provider = "prisma-client-js"
}

generator typegraphql {
  provider = "typegraphql-prisma"
  output   = "generated"
}

model Employee {
  id           Int        @id @default(autoincrement())
  name         String
  departmentId Int
  department   Department @relation(fields: [departmentId], references: [id])
  salary       Salary?
  salaryId     Int?
}

model Department {
  id        Int        @id @default(autoincrement())
  name      String
  employees Employee[]
}

model Salary {
  id         Int      @id @default(autoincrement())
  amount     Float
  employeeId Int      @unique
  employee   Employee @relation(fields: [employeeId], references: [id])
}

Run migrations to create the database:

npx prisma migrate dev --name init

Setting up Apollo Server with Express Integrate Apollo Server with Express, using TypeGraphQL for schema generation and resolvers.

Code for Server Setup:

import "reflect-metadata";
import express from "express";
import { ApolloServer } from "@apollo/server";
import { GraphQLQueryPurifier } from "graphql-query-purifier";
import { resolvers } from "../prisma/generated";
import { PrismaClient } from "@prisma/client";

import cors from "cors";
import path from "path";
import { json, urlencoded } from "body-parser";
import { expressMiddleware } from "@apollo/server/express4";
import { buildSchema } from "type-graphql";

const startServer = async () => {
  const app = express();
  const prisma = new PrismaClient();

  app.use(cors(), json(), urlencoded({ extended: true }));

  const gqlPath = path.resolve(__dirname, "../frontend");
  const queryPurifier = new GraphQLQueryPurifier({
    gqlPath,
    allowStudio: true,
    // allowAll: false,
  });
  app.use(queryPurifier.filter);

  const server = new ApolloServer({
    schema: await buildSchema({
      resolvers,
      validate: false,
    }),
  });

  await server.start();

  const context = expressMiddleware(server, {
    context: async (_ctx) => ({
      prisma,
    }),
  });

  app.use("/graphql", context);

  const PORT = process.env.PORT || 3000;
  app.listen(PORT, () => {
    console.log(`Server running on http://localhost:${PORT}/graphql`);
  });
};

startServer();

Testing the API Finally, let's test the API using GraphQL queries.

Legitimate Query:

# Fetch departments and their employees
query {
  departments {
    id
    name
    employees {
      id
      name
    }
  }
}

Malicious Query Attempt:

# Unauthorized attempt to access salaries
query {
  departments {
    id
    name
    employees {
      id
      name
      salary {
        amount
      }
    }
  }
}

As you might've guessed - this query will show only data that is explicitly allowed to be shown by our .gql files.

Conclusion

This guide covered creating a Node.js API with TypeGraphQL, graphql-query-purifier, and architecture. The main takeaways:

TypeGraphQL for Auto-Generated Schema and Resolvers:

Efficiency: TypeGraphQL and Prisma produce database schema-based GraphQL resolvers. This accelerates the building of a GraphQL API with full CRUD capabilities.
Type Safety: Strong API typing. A type-safe environment with TypeGraphQL and TypeScript reduces type-related problems and improves code quality.

Improved Security: graphql-query-purifier

GraphQL query-purifier filters incoming queries to prevent data leaks. Avoiding over-fetching and unauthorized access to sensitive data is crucial for APIs with autogenerated resolvers.

Our API protects sensitive data like employee pay. The graphql-query-purifier protects such data from unauthorized queries, which is crucial for compliance and privacy.

Practical Results:

Combining these tools creates a robust, secure, and scalable API. It handles complicated data structures and relationships securely.

Less Development Time and Effort: GraphQL schema and resolver auto-generation and security features reduce boilerplate code and increase business logic emphasis.

Modularity and cutting-edge tools prepare the API for future scalability and maintainability issues.

This API development method speeds up creation and adds security and type safety. It shows a fast and secure way to build modern web apps.

Backticks Parsing in Strings

hellowwworld — Mon, 06 Nov 2023 03:23:35 +0000

The Challenge

Parsing Markdown backticks in a text can be complex. Many Markdown parsers are large and handle various functionalities beyond simple backtick parsing. This overcomplication can be an issue when you need a lightweight solution for parsing only inline and block code wrapped in backticks.

The Solution: backticks-codeblocks

To address this, I developed backticks-codeblocks, a minimal JavaScript library focusing solely on parsing backticks in Markdown text. This library identifies and separates inline code (surrounded by single backticks) and block code (enclosed in triple backticks) from the rest of the text.

How It Works

The library processes a text string and returns an array of segments. Each segment is either a string (regular text) or an object representing a code snippet. The object includes the code content and a boolean flag indicating if it's block code.

Usage Example

import { processText } from 'backticks-codeblocks';

const markdown = 'Some `inline code` and a block:\n```

\nconsole.log("Hello, world!");\n

```';
const segments = processText(markdown);

// segments output:
// [
//   "Some ",
//   { code: "inline code", isBlock: false },
//   " and a block:\n",
//   { code: 'console.log("Hello, world!");', isBlock: true }
// ]

Why backticks-codeblocks?

Lightweight: It focuses only on backticks parsing, making it much smaller than full-fledged Markdown parsers.
Simple: Easy to use with a straightforward approach.
Flexible: Useful when only backtick parsing is required without the overhead of full Markdown parsing.

Open for Contributions

The library is open-source, and I welcome contributions. Whether it's suggesting features, reporting issues, or improving the code, your input is valuable.

backticks-codeblocks is a modest effort to simplify a specific aspect of Markdown parsing. I hope you find it useful for your projects.

VSCode: Escaping WebView iframe

hellowwworld — Sun, 17 Sep 2023 12:46:21 +0000

When developing extensions for VSCode using a favored frontend framework like React, Svelte, or Vue.js, a common hurdle encountered is the communication between the VSCode environment and the WebView where your app is rendered. Traditionally, acquireVsCodeApi() is employed to facilitate this, but it becomes undefined once you start using iframe with URL as src, bringing the postMessage API out of reach and crippling the interaction.

In light of this, you could use a strategy that turns extension.ts into a Socket.io server while WebView leverages a locally running React app with a Socket.io client, fostering real-time bi-directional event-based communication.

Setting the Stage

Your extension.ts would take on the role of a Socket.io server, initiating a pathway for continuous communication. Parallelly, the WebView would harbor a React application tuned to be a client for the Socket.io server.

Establishing the Connection

VSCode Extension Side

First, install the necessary socket.io package in your extension project:

$ npm install socket.io

Next, in your extension.ts file, initialize the socket.io server:

import { createServer } from "http";
import { Server } from "socket.io";

const httpServer = createServer();
const io = new Server(httpServer);
io.on("connection", (socket) => {
  console.log("New client connected");

  // Listen for messages from the client
  socket.on("messageFromClient", (msg) => {
    console.log("Received message from client:", msg);
  });

  // Sending a welcome message to the client
  socket.emit("messageFromServer", { data: "Hello, client!" });
});

React App Side

Install socket.io-client in your React project:

$ npm install socket.io-client

In your main React component, initialize the socket.io client and set up event listeners to communicate with the server:

import { useEffect } from "react";
import { io } from "socket.io-client";

// port can be passed as a query parameter
const port = new URLSearchParams(window.location.search).get('port')
const socket =  io(`http://localhost:${port}`);

function App() {
  useEffect(() => {
    // Listen for messages from the server
    socket.on("messageFromServer", (msg) => {
      console.log("Received message from server:", msg);
    });

    // Sending a message to the server
    socket.emit("messageFromClient", { data: "Hello, server!" });
  }, []);

  return <div className="App">Socket.io with VSCode</div>;
}

export default App;

Securing the Communication

Leveraging socket.io for real-time communication opens up a new dimension of interaction between VSCode and WebView, but it comes with its share of security concerns. Securing the data transmitted over the socket is crucial to safeguard sensitive information and maintain user trust.

Conclusion

This communication pathway between extension.ts and a frontend app within WebView, championed by socket.io, promises a rich, real-time bi-directional communication unhindered by the traditional restrictions of VSCode's WebView iframe.

Please share your perspectives and join in refining this architectural blueprint.