Prisma Client returning an empty array on query

Question

I am setting up my backend project and have switched from Sequelize to Prisma for more robust querying. Going through Prisma's docs was hard, however I feel like I have all the bases covered. Basically, I created the generator client and datasource db, I ran prisma db pull to generate the model, then ran prisma generate to update the prisma client. My client has basic logging enabled, verifying that it isconnected to the database.

const prisma = new PrismaClient({
    log: ['query', 'info', 'warn', 'error'],
})

prisma:info Starting a mssql pool with 9 connections.
prisma:info Performing a TLS handshake
prisma:warn Trusting the server certificate without validation.
prisma:info TLS handshake successful
prisma:warn Turning TLS off after a login. All traffic from here on is not encrypted.
prisma:info Database change from 'RiffTheoryScales' to 'master'
prisma:info Changed database context to 'RiffTheoryScales'.
prisma:info SQL collation changed to windows-1252
prisma:info Microsoft SQL Server version 1443102736
prisma:info Packet size change from '4096' to '4096'
prisma:query SELECT [dbo].[Scales].[ID], [dbo].[Scales].[name]
WHERE [dbo].[Scales].[ID] = @P1
[]

My server looks like this

const prisma = new PrismaClient({
    log: ['query', 'info', 'warn', 'error'],
})

const app = express()
app.use(cors());
app.use(express.json())

// ROUTES
app.use('/api/scales', router);

const server = app.listen(3000, () =>
    console.log(`RUNNING ON PORT 3000`),
)

export default prisma

My schema looks like this

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

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

model Scales {
  ID                   Int     @id(map: "PK__Scales__3214EC27E22038C6") @default(autoincrement())
  name                 String  @db.NVarChar(100)

}

My basic test route looks like this

import { Router } from "express" ;
import getScaleByName from '../controllers/prismacontrol.js'

const router = Router(); 

router.get("/name", getScaleByName);

export default router

import prisma from "../servertwo.js";

const getScaleByName = async (req, res) => {
    try {
        const scales = await prisma.scales.findMany()
        console.log(scales)

        if (!scales) {
            res.status(400).json({
                success: false,
                message: 'No scales found!',
            });
        } else {
            res.status(200).json({
                scales,
                success: true,
                message: 'All scales returned'
            })
        }
    } catch (error) {
        console.log(error)
        res.status(400).json({
            success: false,
            message: `ERROR: ${error.message}`
        });
    }
}


export default getScaleByName

I thought I had all my bases covered but if anyone sees something missing or coding suggestions, point it out. It might even be something simple...

Answer 1

I was confused with what you wanted, so I'll answer to two questions.

1. If your problem is that the empty arrays are going to the route with status 200, you just need to change the if(!scales) to if(scales.length === 0), because in Javascript, an empty array is considered an existing array, so it will return true, just like an empty object.

2. If the problem is that it is returning an empty array instead of returning all the rows of a table, the only problem I could see is that your DB is not populated, or if the connection string is wrong.

For the last case, try using npx prisma studio to check if all data is being rendered by Prisma

Also check if the env is being read in the schema