trying to retrieve data
trying to retrieve data of table which contain ref id of another table working perfect on my localhost but when i try to get the same data after push the code to domain it shows empty even data is available in db table should i need to define anything else to get the data here is my api endpoint
export const GET = async (request: NextRequest) => {
const token = request.cookies.get('session')?.value as any
console.log(token, 'Token');
const res = await db
.select()
.from(productsTable)
.where(and(eq(productsTable.session, token)))
.orderBy(desc(productsTable.id)).execute();
// .limit(1)
return NextResponse.json(res, { status: 200 })
}
36 Replies
My guess is either the data is not in your DB like you think it is or the value of token is not what you think it is. If
productsTable contains a row where the session column is token, it will be returned.also i have the same functionality working properly but the difference between both table is it contain ref id which might be a problem but the table is working on my local environment only problem iwhen i push the code to git and access the api with domain
Right, which indicates your issue is probably not with your query, but with your remote DB or the session cookie being passed through your server stack
also i have the same session and things in another table and that table is working fine only the product table fetch data is not working because the table contain ref id is difference
Maybe I’m misunderstanding. What do you mean “table contain ref id is difference”.
in my table of product i have one refrence id of order table
export const orderTable = pgTable("orders", {
id: serial("id").primaryKey(),
phone: varchar('phone', { length: 13 }).notNull(),
fullname: varchar('fullname', { length: 255 }).notNull(),
email: varchar('email', { length: 255 }).notNull(),
province: varchar('province', { length: 255 }).notNull(),
city: varchar('city', { length: 255 }).notNull(),
area: varchar('area', { length: 255 }).notNull(),
address: varchar('address', { length: 255 }).notNull(),
doption: varchar('doption', { length: 255 }).notNull(),
poption: varchar('poption', { length: 255 }).notNull(),
card: varchar('card', { length: 255 }).notNull(),
productid: varchar("productid").notNull(),
existingemail: varchar('existingemail', { length: 255 }).notNull(),
session: varchar("session").notNull(),
createdAt: timestamp('created_at').notNull().defaultNow(),
})
export const orderRelations = relations(orderTable, ({ one }) => ({
products: one(productsTable, {
fields: [orderTable.id],
references: [productsTable.orderId],
}),
}))
export const productsTable = pgTable("products", {
id: serial("id").primaryKey(),
orderId: integer("order_id").notNull().references(() => orderTable.id),
_id: varchar("_id").notNull(),
name: varchar("name").notNull(),
price: varchar("price").notNull(),
marketprice: varchar("marketprice").notNull(),
discount: varchar("discount").notNull(),
totalprice: integer("totalprice").notNull(),
totalmarketprice: integer("totalmarketprice").notNull(),
totaldiscount: integer("totaldiscount").notNull(),
quantity: varchar("quantity").notNull(),
session: varchar('session').notNull(),
});
ordertable is working perfectly on both side but the product table fetching data is not working on domain side
in ordertable i dont have relational id but in product table i have so in domain side data fetching its not working
ordertable have one to one relation with product table
The drizzle
relations function only changes the behavior of the relational query builder, it has no effect on the core query builder, which you’re using
I can say with high confidence that if this works locally and your query is returning nothing when deployed, that means there are no entries in your remote products table where the session is equal to the session token being read in your handler.believe me i have data i checked a lot of times
i dont know its problem of drizzle becuase its working fine locally
Did you try logging the token in the handler?
My suspicion if you see the data in your database is that your clients session cookie does not have the value you think it does
yes i login first
Right I mean
console.log(token) in the handleryea im getting token
i also have another table of order with same things and it working properly only problem in products table maybe because of refrence id
The really silly way to verify that this is not a drizzle issue is to select all rows from the products table and loop through them, using == to compare their session property to token
Again, reference has no effect on the code you’re using, it’s only used for db.query
before ref id added it was woring properly
Wait sorry
Did you mean before adding the relations() or before adding the references() to order_id?
For some reason I thought you meant the prior but you probably meant the latter right?
yea before describe relation id my product table was working properly then i need the ref id so i include it after that it working properly on local data getting properly but on domain side its not
It still doesn’t make sense to me, adding a foreign key to a separate table should not change the behavior of filtering on an unrelated column :/
For my own benefit: When you deploy your app, how are you verifying your current session token and the contents of your products table?
data post method is working fine but only getting data of products table is problem
the token session allow to check if the user have token then he can access the data by matching the token which save in table row and the token on api side
I mean when you’re manually checking these things
Not how the API does it
Like you said you checked the data, did you do that via something like the pg cli?
i check it with this method
export const GET = async (request: NextRequest) => {
const token = request.cookies.get('session')?.value as any
console.log(token, 'Token');
return NextResponse.json(token,{ status: 200 })
}
Ok, and how about the contents of the table?
im not getting you are you talking about the connections?
Like how did you verify that your products table actually did not have a row with that session value?
How did you do this
simply by opening table on postgress database
Using a DB browser of some sort?
using vercel db postgress
Ok, thanks for helping me understand your debugging steps
Something else you could consider doing is logging the SQL query with toSQL (or the drizzle query logging feature), inspecting it to see if anything looks off, and running that query directly (maybe with CLI? assuming vercel db lets out do that)
where to do this things ?
Instead of calling .execute, call .toSQL (and don’t await). That will give you the query
(Btw you don’t need to call .execute directly, awaiting the query will do that anyways)
yea i also try this
And what was the results?
same
getting empty array
when i try to get data with my domain on console here is resuly
GET /api/products HTTP/1.1
email: [email protected]
password: asd123
User-Agent: PostmanRuntime/7.36.0
Accept: /
Postman-Token: 10ea0dd5-7ab1-4f30-9e7f-5f146b3c141e
Accept-Encoding: gzip, deflate, br
Connection: keep-alive
Cookie: session=r5l4ijkme0qjwv4f9iygpb
Referer: http://www.rateoye.com/api/products
Host: www.rateoye.com
HTTP/1.1 200 OK
Accept-Ranges: bytes
Access-Control-Allow-Origin: *
Age: 163113
Cache-Control: public, max-age=0, must-revalidate
Connection: keep-alive
Content-Disposition: inline
Content-Length: 2
Content-Type: application/json
Date: Mon, 25 Dec 2023 14:52:43 GMT
Etag: "d751713988987e9331980363e24189ce"
Server: Vercel
Strict-Transport-Security: max-age=63072000
Vary: RSC, Next-Router-State-Tree, Next-Router-Prefetch, Next-Url
X-Matched-Path: /api/products
X-Vercel-Cache: HIT
X-Vercel-Id: bom1::z6k55-1703515963699-9d74c1eb1ba7
[]
So you got the SQL query, the SQL looked reasonable, and running the SQL text directly returned no data?
its console result of postman when try to get the data with api
Try what I’m suggesting. Get the SQL query from drizzle and execute that query against the database directly
Unfortunately I need to go offline for a while. Good luck in your debugging in the mean time
ok ill try thank you for your time ❤️