How can I see the exact SQL statement that Drizzle is sending to Neon?
I am trying to execute an upsert on Neon table with a unique constraint. The upsert fails with an error message citing the unique constraint. I was expecting the upsert to succeed because I am using the same value. In other words, I am not inserting a new row with a duplicate value in the column with the unique constraint.
In a situation like this, I want to understand how I would troubleshoot using a combination of Drizzle and Neon.
14 Replies
optimistic-goldOP•2y ago
This is the error message: Unhandled Runtime Error
Error: duplicate key value violates unique constraint "user_settings_user_id_key"
This is the CREATE TABLE statement for the relevant table:
CREATE TABLE IF NOT EXISTS public.user_settings
(
id integer NOT NULL DEFAULT nextval('user_settings_id_seq'::regclass),
clerk_user_id character varying(50) COLLATE pg_catalog."default" NOT NULL,
content_lang character varying(10) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
explain_lang character varying(10) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
CONSTRAINT user_settings_pkey PRIMARY KEY (id),
CONSTRAINT user_settings_user_id_key UNIQUE (clerk_user_id)
)
Here is the Drizzle TypeScript code to perform the upsert:
import { integer, pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
export const userSettings = pgTable('user_settings', {
id: serial('id').primaryKey(),
clerkUserId: text('clerk_user_id'),
contentLang: text('content_lang'),
explainLang: text('explain_lang'),
});
export type UserSettings = typeof userSettings.$inferSelect; // return type when queried
export type NewUserSettings = typeof userSettings.$inferInsert; // insert type
export async function upsertUserSettings(
userSetting: NewUserSettings,
): Promise<UserSettings[]> {
// Using Neon serverless driver
const dbUrl = process.env.DATABASE_URL!;
const sql = neon(dbUrl);
const db = drizzle(sql);
return db
.insert(userSettings)
.values(userSetting)
.onConflictDoUpdate({
target: userSettings.id,
set: {
contentLang: userSetting.contentLang,
explainLang: userSetting.explainLang,
},
})
.returning();
}
I think I can workaround the issue by removing the unique constraint. But more importantly for the future, I want to know that I can see the SQL statement and get other troubleshooting information. This is just a prototype.
other-emerald•2y ago
you can enable logging in drizzle https://orm.drizzle.team/docs/goodies#logging
Drizzle ORM - Goodies
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
other-emerald•2y ago
should do it for you
optimistic-goldOP•2y ago
Thanks @Flo , if I enable logging, and I am using Next.js and Vercel, where would I actually see the logs in production? Would it be in Vercel?
other-emerald•2y ago
I assume so, it logs to the standard output
you could implemented your own logger tho
incase you want to send the logs somewhere else
optimistic-goldOP•2y ago
All the Drizzle documentation examples use pool
const db = drizzle(pool, { logger: true });
Must I use a pooled connection, then?
And specifically to Neon, does Neon show a transaction history of operations such as this upsert? My Neon console Operations webapge seems to be able the system operations such as "suspend_compute" action. And it seems there is no actions pertaining to calls from my Next.js application.
@Flo - Looks like I do not need a pool. I enabled logging and here is the console error:
Query: insert into "user_settings" ("id", "clerk_user_id", "content_lang", "explain_lang") values (default, $1, $2, $3) on conflict ("id") do update set "content_lang" = $4, "explain_lang" = $5 returning "id", "clerk_user_id", "content_lang", "explain_lang" -- params: ["user_2a6AcS5j7rZkR49nTOwMAx7RSxB", "th", "en", "th", "en"]
⨯ node_modules/@neondatabase/serverless/index.mjs (1543:17) @ execute
⨯ NeonDbError: duplicate key value violates unique constraint "user_settings_user_id_key"
at async upsertUserSettingsAction (settings/userSettingsActions.ts:18:5)
So, this is an insert into statement. And because there is a unique constraint, the insert fails, even though the I am not violating the spirit of the uniqueness of clerk_user_id column.
So, as a separate finding, the Drizzle upsert doesn't work on a Neon table with a unique constraint when actually trying to update.
other-emerald•2y ago
no you don't need to use a pooled connection if you are using serverless technoligy you maybe should
optimistic-goldOP•2y ago
So as a Neon developer, do I need to generate my own logging? Or does/will the Neon console assist me for application-specific transactions?
other-emerald•2y ago
you should do
because your id is the auto increment id which you don't have at the time of your insert it will always be unique but you want to update a user once you hit the clerkUserId constriant
I didn't find any site where it shows you all the queries you are executing so I would think so yes.
other-emerald•2y ago
There is this https://neon.tech/docs/extensions/pg_stat_statements but I dont think this covers what you want
Neon
The pg_stat_statements extension - Neon Docs
The pg_stat_statements extension provides a detailed statistical view of SQL statement execution within a Postgres database. It tracks information such as execution counts, total and average execution...
optimistic-goldOP•2y ago
@Flo - Nice, setting the target to clerkUserId did indeed resolve that issue regarding upsert against a unique constraint .
Good to know about pg_stat_statements. That does help for analysis.
My immediate need to find the SQL statement is resolved by your suggestion to use Drizzle for logging.
In the bigger picture, it seems like I would need to build my own application's SQL logging, not Neon
other-emerald•2y ago
you could probably throw all the logs into axiom.co or something similar
as far as I know they have a vercel integration
optimistic-goldOP•2y ago
Ah interesting, thanks @Flo !
@Flo - if I can ask, do you have a live app on Neon yourself? Is this SQL logging an actual issue for you?
rival-black•2y ago
We're working on a better experience to potentially track queries and query performance. For now, what @Flo said is correct, you need to use a combination of the Drizzle logger and pg_stats