Configuration

Tuning is optional. Defaults work without any configuration; knobs exist for schemas that need different safety boundaries or cost assumptions.

Generator configuration

generator sql {
  provider   = "prisma-sql-generator"
  dialect    = "postgres"
  output     = "./generated/sql"
  skipInvalid = "true"
}

Field	Default	Description
`provider`	required	Use `prisma-sql-generator`
`dialect`	inferred when possible	`postgres` or `sqlite`
`output`	`./generated/sql`	Generated SQL client output path
`skipInvalid`	false	Skip invalid @optimize directives instead of failing generation

Extension config

Pass limits and/or strategy directly to speedExtension. Both accept partial objects.

const db = prisma.$extends(
  speedExtension({
    postgres: sql,
    limits: {
      MAX_INCLUDES_PER_LEVEL: 20,
      MAX_INCLUDE_DEPTH: 8,
    },
    strategy: {
      defaultFanOut: 5,
      roundtripRowEquivalent: 40,
    },
  }),
)

Programmatic API

import {
  setLimits, getLimits, resetLimits,
  setStrategyConfig, getStrategyConfig,
  rebuildQueryCache,
} from 'prisma-sql'

setLimits({ MAX_INCLUDES_PER_LEVEL: 25 })
rebuildQueryCache()   // required after changing QUERY_CACHE_SIZE
setStrategyConfig({ correlatedBoundedFactor: 0.3 })
resetLimits()

Query builder limits

Key	Default	Description
`MAX_INCLUDE_DEPTH`	5	Max depth of nested include/select relations
`MAX_INCLUDES_PER_LEVEL`	10	Max relations included at one nesting level
`MAX_TOTAL_SUBQUERIES`	100	Total correlated subqueries allowed across the tree
`MAX_SELF_REFERENTIAL_DEPTH`	2	Max times a model can appear in its own include chain
`MAX_QUERY_DEPTH`	50	Max nesting depth for WHERE clauses
`MAX_NOT_DEPTH`	50	Max nesting depth for NOT composition
`MAX_HAVING_DEPTH`	50	Max nesting depth for HAVING clauses
`MAX_NESTED_JOIN_DEPTH`	10	Max depth for join-based relation traversal
`MAX_RELATION_ORDER_BY_DEPTH`	10	Max depth for relation orderBy resolution
`MAX_WHERE_IN_RECURSIVE_DEPTH`	10	Max recursion depth for where-in segment resolution
`MAX_ARRAY_SIZE`	10000	Max elements in array params such as in / hasSome
`MAX_STRING_LENGTH`	10000	Max string length for LIKE and JSON string operators
`MAX_LIMIT_OFFSET`	2147483647	PostgreSQL engine limit for LIMIT / OFFSET
`MIN_NEGATIVE_TAKE`	-10000	Minimum allowed negative take value
`MAX_ALIAS_COUNTER_THRESHOLD`	1000	Safety threshold before alias counter overflow
`QUERY_CACHE_SIZE`	1000	Max entries in the SQL query cache (call rebuildQueryCache after changing)
`STMT_CACHE_SIZE`	1000	Max entries in the SQLite prepared statement cache per client

Strategy cost-model parameters

These control how the planner chooses between query strategies.

Key	Default	Description
`roundtripRowEquivalent`	73	Cost in row-equivalents of one extra database roundtrip. Lower favors multi-roundtrip strategies.
`jsonRowFactor`	1.5	Multiplier for JSON aggregation overhead per row
`correlatedBoundedFactor`	0.5	Cost factor for correlated subqueries when the child has LIMIT
`correlatedUnboundedFactor`	3.0	Cost factor for correlated subqueries when the child is unbounded
`correlatedWherePenalty`	3.0	Extra cost when a child relation has where inside a correlated subquery
`defaultFanOut`	10	Assumed average children per parent without stats
`defaultParentCount`	50	Assumed parent row count when take is not specified
`dynamicTakeEstimate`	10	Assumed take value when actual value is a runtime parameter
`singleParentMaxFlatJoinDepth`	2	Max include depth allowing flat-join for findFirst/findUnique
`minStatsCoverage`	0.1	Minimum relation stats coverage to trust collected data
`largeChildTableRows`	100000	Child table threshold for the large to-many include guard
`smallParentCountThreshold`	1000	Parent estimate threshold for the large to-many include guard

Tuning examples

// low-latency local database
speedExtension({ postgres: sql, strategy: { roundtripRowEquivalent: 20 } })

// remote database with high latency
speedExtension({ postgres: sql, strategy: { roundtripRowEquivalent: 200 } })

// large child tables that are well indexed
speedExtension({ postgres: sql, strategy: { largeChildTableRows: 500000 } })