prisma
Remove requirement for a running database when using TypedSQL
Problem
When using the new TypedSQL functionality of Prisma ORM, you need to have a running database. This is because the functionality relies on the database to parse the SQL and return type information. This is causing problem for users running prisma generate in environments where they don't otherwise need a running database. See discussion here: https://github.com/prisma/prisma/discussions/25106#discussioncomment-10493523
Suggested solution
As Postgres is the most used database, we could bundle https://pglite.dev/ with the Prisma CLI and use that for type generation. This would eliminate the need for a running database when using TypedSQL, and would allow us to deprecate the new --sql flag.
Alternatives
We could alternatively replicate the full SQL parsing logic of each database in Prisma CLI. This is the approach we are using for SQLite, as it doesn't support returning type information for a SQL query.
Additional context
@sorenbs +1
Or at least to have a native way to specify where "raw query types" are (and must be). It would allow committing files locally instead of within node_modules, so there is no issue in CI/CD for example.
It's not perfect if the real DB defers, but it would be the same risk with pglite.
A hack is still possible to copy/paste myself types at the right place, but it's a hack.
Or at least to have a native way to specify where "raw query types" are (and must be). It would allow committing files locally instead of within
node_modules, so there is no issue in CI/CD for example.
You should be able to achieve this already by setting a custom output path in the generator block in the schema: https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/generating-prisma-client#using-a-custom-output-path
Or at least to have a native way to specify where "raw query types" are (and must be). It would allow committing files locally instead of within
node_modules, so there is no issue in CI/CD for example.You should be able to achieve this already by setting a custom output path in the generator block in the schema: https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/generating-prisma-client#using-a-custom-output-path
We have to upload the engine itself that's 10mb.
Instead of generating the types at CI/CD time, we are generating them at dev time and including them in our source code via a gulp task (see below).
It would be nice if we could generate these files without reaching inside the npm package.
// Prisma's typedSql capability stores its generated types deep within the pnpm package store.
// We use a gulp task to copy the generated typed sql into ./src/sql/*
gulp.task('copy-prisma-client', () => {
const output = execSync('pnpm list --depth 1 --parseable @prisma/client', {
encoding: 'utf8',
})
const prismaClientLine = output
.split('\n')
.find(line => line.includes('@prisma/client'))
if (!prismaClientLine) {
throw new Error('@prisma/client not found')
}
const prismaClientWithoutSuffix = prismaClientLine.replace(
/@prisma\/client$/,
'',
)
const prismaClientPath = path.join(
prismaClientWithoutSuffix,
'.prisma',
'client',
'sql',
)
return gulp
.src([
`${prismaClientPath}/**/*.mjs`,
`${prismaClientPath}/**/*.d.ts`,
`!${prismaClientPath}/**/*.edge.js`,
`!${prismaClientPath}/**/*.edge.mjs`,
])
.pipe(
rename(path => {
// Although our generated files have .mjs extension, the barrel file must be .js
// in order for BOTH the typescript type check AND the esm package import to work
if (path.basename === 'index' && path.extname === '.mjs') {
path.extname = '.js'
}
}),
)
.pipe(gulp.dest('./src/sql'))
})
PGlite maintainer here, let me know if there is anything you need if you decide to do this. Happy to help out.
Having started using the typedSql feature recently after having to resort to hand-written queries, I can say that this is an awesome feature!
However, the burden of needing a real database available when generating the client is quite high. In fact, I had to modify our CI infrastructure substantially. We have a postinstall hook which would generate the client during pnpm install, which now requires a real database instance to be available. Having split our test, docker builds and release process, it required each step to be modified, availing an instance.
The most cumbersome part was the docker build, which basically had to be put into network=host mode, together with spinning up a database instance.
Bundling something like PGlite would make a massive difference in terms of using this, and improving the (potential) flakiness introduced by this extra, non-trivial dependency.
Personally, I like to have fully reproducible builds, meaning I'm not keen on generating the client and committing it to version control, which is the only workaround that exists today.
For anybody else is struggling with this and not wanting to modify continuous deployment by adding files from local runs, this is our Dockerfile running a postgres server for the build phase:
FROM oven/bun:1.1.24-alpine AS installer
RUN apk add git libstdc++ postgresql postgresql-contrib
RUN mkdir -p /run/postgresql /var/lib/postgresql/data && \
chown -R postgres:postgres /run/postgresql /var/lib/postgresql/data
RUN su postgres -c "initdb -D /var/lib/postgresql/data"
RUN su postgres -c "pg_ctl start -D /var/lib/postgresql/data && \
psql -c \"ALTER USER postgres PASSWORD 'postgres';\" && \
pg_ctl stop -D /var/lib/postgresql/data"
WORKDIR /app
COPY . .
COPY --from=node:22.6.0-alpine3.20 /usr/local/bin/node /usr/local/bin/node
WORKDIR /app/apps/backend
RUN bun install
ENV DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres
RUN su postgres -c "pg_ctl start -D /var/lib/postgresql/data" && \
pg_isready -h localhost -p 5432 -U postgres && \
bunx prisma migrate deploy && \
bunx prisma generate --sql && \
su postgres -c "pg_ctl stop -D /var/lib/postgresql/data"
RUN bun run build
FROM oven/bun:1.1.24-alpine AS runner
WORKDIR /app
COPY --from=installer /app/apps/backend/dist .
COPY --from=installer /app/node_modules node_modules
COPY --from=installer /app/apps/backend/prisma prisma
COPY --from=node:22.6.0-alpine3.20 /usr/local/bin/node /usr/local/bin/node
CMD if [ \"$STAGING\" = \"1\" ]; then bunx prisma migrate reset --force && SEED=1 node main.js && node main.js; else bunx prisma migrate deploy && node main.js; fi```
=> [event-service 3/6] COPY . . 0.1s
=> [event-service 4/6] COPY yarn.lock ./ 0.0s
=> [event-service 5/6] RUN yarn install 36.5s
=> ERROR [event-service 6/6] RUN yarn build-local 3.8s
------
> [event-service 6/6] RUN yarn build-local:
0.363 yarn run v1.22.22
0.394 $ NODE_ENV=local nest build
3.705 src/user-stage/user-stage.repository.ts:5:10 - error TS2305: Module '"@prisma/client/sql"' has no exported member 'getUserGroupedEventsWithStages'.
3.705
3.705 5 import { getUserGroupedEventsWithStages } from '@prisma/client/sql';
3.705 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3.705
3.706 Found 1 error(s).
3.706
3.748 error Command failed with exit code 1.
3.748 info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
Using TypedSql with Docker is even easier in Prisma >= 6.8.0 thanks to the introduction of the dev server.
RUN \
# Start Prisma's local dev server and capture the output
npx prisma dev --port 54321 > /tmp/prisma-output.txt 2>&1 & \
PRISMA_PID=$! && \
# Wait for it to start
sleep 10 && \
# Extract the DATABASE_URL from the output
export DATABASE_URL=$(grep -o 'DATABASE_URL="prisma+postgres://localhost:[0-9]*/?api_key=[^"]*"' /tmp/prisma-output.txt | sed 's/DATABASE_URL=//g' | tr -d '"' || echo "") && \
# If extraction failed, show an error
if [ -z "$DATABASE_URL" ]; then \
echo "Failed to extract DATABASE_URL from Prisma dev output" && \
exit 1; \
fi && \
# Run migrations
npx prisma migrate deploy && \
# Build the server
npx prisma generate --sql && \
npm run build && \
# Kill the Prisma dev server
kill $PRISMA_PID || true
// Updated version.
only problem is that this is "time" based.
Use Case: Build Environment Without Database Access
We're encountering challenges with typed-sql in our GCP Cloud Build + Docker workflow that might be relevant to others in similar situations.
Current Setup:
- Building applications on GCP Cloud Build with Docker
- No database access during build time (by design)
- Using Prisma with typed-sql
The Problem: When building without database access, typed-sql fails because it cannot verify that referenced fields exist in the database schema. This creates a chicken-and-egg problem:
- We need to run migrations before the build can succeed
- But we can't be certain the build will complete successfully before running migrations
- This introduces risk and prevents us from properly splitting our deployment workflow
Impact:
- Reduces our ability to create clean separation between build and deployment phases
- Forces us to either:
- Run migrations speculatively (risky)
- Maintain database connections during build (not ideal for our architecture)
Potential Solutions We're Considering:
- Generate typed-sql at build time with full schema access, but this still requires an open database connection during builds
- Some form of schema caching or offline type generation
I've had to rewrite my shitty feedback with Claude sorry for it.
I had the same issue when containerizing our application. Including a temporary db during build wasn't an option for us, so we used a custom output path for the prisma client as @sorenbs mentioned:
generator client {
provider = "prisma-client-js"
output = "../generated/prisma"
moduleFormat = "esm"
previewFeatures = ["views", "typedSql"]
}
We excluded everything except the TypedSQL-specific files to address the engine size issue mentioned by @afonsomatos (in our case it would have been ~20MB)
# Ignore everything in ./src/generated/prisma
/src/generated/prisma/*
# Allow TypedSQL specific files
!/src/generated/prisma/sql/
!/src/generated/prisma/sql/**
In our Dockerfile build stage, we simply generate the general parts of the client and then copy the pre-generated TypedSQL-specific parts from the codebase:
RUN npm run prisma generate
COPY src/generated/prisma/sql ./src/generated/prisma/sql
This solution is a bit hacky but works for now. hope we see a better one in the future
Here's my approach to include typedSql generated files in your Docker image without modifying your Prisma generator client configuration:
First, update your .gitignore to specifically include the sql folder within node_modules/.prisma/client/:
/node_modules/*
# Un-ignore .prisma/client/sql folder
!/node_modules/.prisma
/node_modules/.prisma/*
!/node_modules/.prisma/client/
/node_modules/.prisma/client/*
!/node_modules/.prisma/client/sql/
Then, in your Dockerfile, copy the necessary Prisma files and your pre-computed SQL declarations:
# Adapt to your favorite node image
FROM node-alpine
WORKDIR /my-app
COPY ./package.json ./
# Adapt to your package manager
COPY ./yarn.lock ./
# Copy schema and migrations
COPY ./prisma ./prisma
# Copy pre-compute sql declaration files
COPY ./node_modules/.prisma/client/sql ./node_modules/.prisma/client/sql
RUN yarn install --frozen-lockfile && yarn cache clean
# Update client declaration files to match `prisma` folder (no db connection needed)
RUN npx prisma generate
# Your port
EXPOSE 1234
# Start your server here
CMD ["npm", "run", "start"]
This method maintains Prisma's default paths while seamlessly embedding the required files in your Docker image.
We are committing the client/sql folder as we don't want to rely on a PostgreSQL database in our CI to generate typings.
None of these alternatives work when using the new prisma-client generator preview (as of Prisma 6.15.0).
Here are the issues:
it deletes the sql folder
- If the client/sql folder exists, running
prisma generate(with ou without--sql), it errors:
prisma/client exists and is not empty but doesn't look like a generated Prisma Client. Please check your output path and remove the existing directory if you indeed want to generate the Prisma Client in that location.
- If we create a
client/client.tsdummy file to bypass this error, it generated everything except it deletes de sql folder (when runningprisma generatewithout--sqlarg)
@danilofuchs When attempting to migrate from prisma-client-js to the new prisma-client provider, I faced the same issue.
The only workaround I found was to have something like this:
generator client {
provider = "prisma-client"
previewFeatures = ["typedSql"]
binaryTargets = ["native", "linux-musl-openssl-3.0.x"]
output = env("PRISMA_OUTPUT")
}
So I can drive the prisma generate output with an environment variable and generate into 2 different folders.
eg.
PRISMA_OUTPUT=../src/generated prisma generate
PRISMA_OUTPUT=../src/generated-sql prisma generate --sql
Then you could completely ignore the src/generated folder and partially ignore the src/generated-sql folder to track the sql part.
Not very elegant but it's the only solution I see at the moment.
@danilofuchs @ctjhoa
Another way is to make output directory empty temporarily before generating prisma client.
Content of prisma/schema.prisma file:
generator client {
provider = "prisma-client"
output = "../generated/prisma"
previewFeatures = ["typedSql"]
}
datasource db {
provider = "postgresql"
}
Content of .gitignore file:
/generated/prisma/*
!/generated/prisma/sql*
Content of Dockerfile:
FROM node:20-alpine AS builder
WORKDIR /app
# Copy package files and install dependencies
COPY package.json yarn.lock ./
RUN yarn install --frozen-lockfile
# Copy source code and Prisma configuration
COPY . .
# Move typedSQL to a temp directory
RUN mkdir -p ./temp
RUN mv generated/prisma/sql* ./temp
# Generate Prisma client
RUN npx prisma generate
# Move typedSQL back to the original location
RUN mv ./temp/* ./generated/prisma/
RUN rm -rf ./temp
# Rest of your docker file ...
