Admin Users Count

Hi. I am currently trying to get just the count of admin users. My query looks like the following:

// Admin count query - separate optimized query just for counting
    const { data: adminData } = useQuery<UsersResponse>({
        queryKey: ["users-admin-count"],
        queryFn: async () => {
            try {
                const data = await client.admin.listUsers(
                    {
                        query: {
                            limit: 1, // We only need the total count, not the actual users
                            filterField: "role",
                            filterOperator: "eq",
                            filterValue: "admin"
                        }
                    },
                    {
                        throw: true,
                    },
                );
                return data as UsersResponse;
            } catch (error: any) {
                console.error("Failed to fetch admin count:", error);
                return { users: [], total: 0 } as UsersResponse;
            }
        },
        // Keep the count cached for longer since it doesn't change as frequently
        staleTime: 5 * 60 * 1000, // 5 minutes
    });

// Admin count query - separate optimized query just for counting
    const { data: adminData } = useQuery<UsersResponse>({
        queryKey: ["users-admin-count"],
        queryFn: async () => {
            try {
                const data = await client.admin.listUsers(
                    {
                        query: {
                            limit: 1, // We only need the total count, not the actual users
                            filterField: "role",
                            filterOperator: "eq",
                            filterValue: "admin"
                        }
                    },
                    {
                        throw: true,
                    },
                );
                return data as UsersResponse;
            } catch (error: any) {
                console.error("Failed to fetch admin count:", error);
                return { users: [], total: 0 } as UsersResponse;
            }
        },
        // Keep the count cached for longer since it doesn't change as frequently
        staleTime: 5 * 60 * 1000, // 5 minutes
    });

but the response looks like the following although the docs state that the "total" in the meta data is the count after the filters:

{
    "users": [
        {
            "id": "****",
            "name": "***",
            "email": "***",
            "emailVerified": ***,
            "image": "***",
            "createdAt": "****",
            "updatedAt": "****",
            "role": "admin",
            "banned": null,
            "banReason": null,
            "banExpires": null
        }
    ],
    "total": 6,
    "limit": 1
}

{
    "users": [
        {
            "id": "****",
            "name": "***",
            "email": "***",
            "emailVerified": ***,
            "image": "***",
            "createdAt": "****",
            "updatedAt": "****",
            "role": "admin",
            "banned": null,
            "banReason": null,
            "banExpires": null
        }
    ],
    "total": 6,
    "limit": 1
}

Any insight into this would be awesome.

18 Replies

yoyojoeOP•2mo ago

Here is a snip of the admin dashboard I am creating

Lick A Brick•2mo ago

What are you expecting as the output? You are querying users with the role 'admin', as per your response you have 6 users matching this filter? You are only seeing 1 user because you set the limit to 1. Limit != Filter.

yoyojoeOP•2mo ago

Whats up @Lick A Brick. I'm expecting the total to be equal to the number of users with the given filter criteria. I only have 1 admin user so I would expect "total":6 Right now its returning the total number of all users

Lick A Brick•2mo ago

Hmm... the docs seem kinda confusing to me tbh. At https://www.better-auth.com/docs/plugins/admin#list-users it mentions the parameter filter which accepts an array of objects, but in the example it shows filterField, filterOperator and filterValue as seperate parameters. Should't it be filter: [{ field: "role", operator: "eq", value: "admin"}] what happens if you remove the limit: 1 does it return all 6 users or does it still only return the single admin user?

yoyojoeOP•2mo ago

Sorry. Just realized I didn’t respond. I did try this but got a type error. I looked into it further and filter isn’t a query option. I’m going to update and see if that changes anything

Netrifier•2mo ago

Can you share which adapter you are using and its configuration?

yoyojoeOP•2mo ago

Hey @Netrifier. See below for my config:

import { createAuthClient } from "better-auth/react"
import { adminClient } from "better-auth/client/plugins"

export const authClient = createAuthClient({
    baseURL: "http://localhost:3000", // the base url of your auth server
    plugins: [adminClient()]
})

import { createAuthClient } from "better-auth/react"
import { adminClient } from "better-auth/client/plugins"

export const authClient = createAuthClient({
    baseURL: "http://localhost:3000", // the base url of your auth server
    plugins: [adminClient()]
})

Netrifier•2mo ago

That is the authClient file. can you share the auth.ts file Also which database are you using? If not configured, you have to configure a database https://www.better-auth.com/docs/installation#configure-database

Installation | Better Auth

Learn how to configure Better Auth in your project.

yoyojoeOP•2mo ago

Here's my auth-client.ts:

import { createAuthClient } from "better-auth/react"
import { adminClient } from "better-auth/client/plugins"

export const authClient = createAuthClient({
    baseURL: "http://localhost:3000", // the base url of your auth server
    plugins: [adminClient()]
})

import { createAuthClient } from "better-auth/react"
import { adminClient } from "better-auth/client/plugins"

export const authClient = createAuthClient({
    baseURL: "http://localhost:3000", // the base url of your auth server
    plugins: [adminClient()]
})

and here is my auth.ts file:

import { betterAuth } from "better-auth";
import { admin, openAPI } from "better-auth/plugins";
import db from "@/db";
import { nextCookies } from "better-auth/next-js";
import { drizzleAdapter } from "better-auth/adapters/drizzle";

export const auth = betterAuth({
    database: drizzleAdapter(db, {
        provider: "sqlite",
      }),
    emailAndPassword: {  
        enabled: true
    },
    socialProviders: {
        google: {
            clientId: process.env.GOOGLE_CLIENT_ID as string,
            clientSecret: process.env.GOOGLE_CLIENT_SECRET as string,
        },
    },
    plugins: [
        openAPI(),
        admin(),
        nextCookies()
    ]
})

import { betterAuth } from "better-auth";
import { admin, openAPI } from "better-auth/plugins";
import db from "@/db";
import { nextCookies } from "better-auth/next-js";
import { drizzleAdapter } from "better-auth/adapters/drizzle";

export const auth = betterAuth({
    database: drizzleAdapter(db, {
        provider: "sqlite",
      }),
    emailAndPassword: {  
        enabled: true
    },
    socialProviders: {
        google: {
            clientId: process.env.GOOGLE_CLIENT_ID as string,
            clientSecret: process.env.GOOGLE_CLIENT_SECRET as string,
        },
    },
    plugins: [
        openAPI(),
        admin(),
        nextCookies()
    ]
})

@Netrifier Now when I query using the admin client I'm not getting a total back at all.

Netrifier•2mo ago

Is the query failing or the total returning 0? I took a look at the listUsers function and it returns the users and also counts the total users without any conditions

const users = await ctx.context.internalAdapter.listUsers(
  Number(ctx.query?.limit) || undefined,
  Number(ctx.query?.offset) || undefined,
  ctx.query?.sortBy
    ? {
      field: ctx.query.sortBy,
      direction: ctx.query.sortDirection || "asc",
    }
    : undefined,
  where.length ? where : undefined,
 );
const total = await ctx.context.internalAdapter.countTotalUsers();

const users = await ctx.context.internalAdapter.listUsers(
  Number(ctx.query?.limit) || undefined,
  Number(ctx.query?.offset) || undefined,
  ctx.query?.sortBy
    ? {
      field: ctx.query.sortBy,
      direction: ctx.query.sortDirection || "asc",
    }
    : undefined,
  where.length ? where : undefined,
 );
const total = await ctx.context.internalAdapter.countTotalUsers();

I do not know if this is the intended behaviour but I think the where clause should be passed to the countTotalUsers function

yoyojoeOP•2mo ago

So with the following query:

// Admin count query
    const { data: adminCountData } = useQuery<UsersResponse>({
        queryKey: ["users-admin-count"],
        queryFn: async () => {
            try {
                const data = await client.admin.listUsers(
                    {
                        query: {
                            limit: 1,
                            filterField: "role",
                            filterOperator: "eq",
                            filterValue: "admin",
                        }
                    },
                    {
                        throw: true,
                    },
                );
                return data as UsersResponse;
            } catch (error: any) {
                console.error("Failed to fetch admin count:", error);
                return { users: [], total: 0, limit: 0 } as UsersResponse;
            }
        },
        staleTime: 5 * 60 * 1000, // 5 minutes
    });

// Admin count query
    const { data: adminCountData } = useQuery<UsersResponse>({
        queryKey: ["users-admin-count"],
        queryFn: async () => {
            try {
                const data = await client.admin.listUsers(
                    {
                        query: {
                            limit: 1,
                            filterField: "role",
                            filterOperator: "eq",
                            filterValue: "admin",
                        }
                    },
                    {
                        throw: true,
                    },
                );
                return data as UsersResponse;
            } catch (error: any) {
                console.error("Failed to fetch admin count:", error);
                return { users: [], total: 0, limit: 0 } as UsersResponse;
            }
        },
        staleTime: 5 * 60 * 1000, // 5 minutes
    });

the response cached is the following:

{
    "users": [
        {
            "id": "****",
            "name": "****",
            "email": "****",
            "emailVerified": true,
            "image": "****",
            "createdAt": "2025-03-16T22:53:58.000Z",
            "updatedAt": "2025-03-16T22:53:58.000Z",
            "role": "admin",
            "banned": null,
            "banReason": null,
            "banExpires": null
        }
    ],
    "limit": 1
}

{
    "users": [
        {
            "id": "****",
            "name": "****",
            "email": "****",
            "emailVerified": true,
            "image": "****",
            "createdAt": "2025-03-16T22:53:58.000Z",
            "updatedAt": "2025-03-16T22:53:58.000Z",
            "role": "admin",
            "banned": null,
            "banReason": null,
            "banExpires": null
        }
    ],
    "limit": 1
}

Note: I'm blocking sensitive info in the returned request, that's not the actual response. ahh this might be the case Also note that removing the limit on the query does not change the response but removes the limit amount returned.

{
    "users": [
        {
            "id": "****",
            "name": "****",
            "email": "****",
            "emailVerified": true,
            "image": "****",
            "createdAt": "2025-03-16T22:53:58.000Z",
            "updatedAt": "2025-03-16T22:53:58.000Z",
            "role": "admin",
            "banned": null,
            "banReason": null,
            "banExpires": null
        }
    ]
}

{
    "users": [
        {
            "id": "****",
            "name": "****",
            "email": "****",
            "emailVerified": true,
            "image": "****",
            "createdAt": "2025-03-16T22:53:58.000Z",
            "updatedAt": "2025-03-16T22:53:58.000Z",
            "role": "admin",
            "banned": null,
            "banReason": null,
            "banExpires": null
        }
    ]
}

Netrifier•2mo ago

that would happen if the value of the total comes to be undefined, not sure why it is happening that is expected as it would return the limit only if the limit is present hey @yoyojoe I figured out what is causing it

yoyojoeOP•2mo ago

hell yeah

Netrifier•2mo ago

in the count function on the drizzle adapter there is a bug on the count function https://orm.drizzle.team/docs/guides/count-rows

async count(data) {
    const { model, where } = data;
    const schemaModel = getSchema(model);
    const clause = where ? convertWhereClause(where, model) : [];
    const res = await db
        .select({ count: count() })
        .from(schemaModel)
        .where(...clause);
    return res.count; // this is the bug, it should be res[0].count
},

async count(data) {
    const { model, where } = data;
    const schemaModel = getSchema(model);
    const clause = where ? convertWhereClause(where, model) : [];
    const res = await db
        .select({ count: count() })
        .from(schemaModel)
        .where(...clause);
    return res.count; // this is the bug, it should be res[0].count
},

Drizzle ORM - Count rows

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Netrifier•2mo ago

I had found this when I was adding tests to my pr https://github.com/better-auth/better-auth/pull/1737

GitHub

feat: refactor database adapters by Netrifier · Pull Request #1737 ...

Drizzle Adapter: Refactored the convertWhereClause function to handle all operator conversions. Changed the order of parameters convertWhereClause(model: string, where?: Where[]) to make them cons...

yoyojoeOP•2mo ago

classic hahaha. Thank you again for working through this with me

Netrifier•2mo ago

Hey, @bekacru @Ping could you take a look at this

Ping•2mo ago

I feel like this is probably the 3rd time I've come across this. I thought it was already fixed :/

Tests: Added test for adapter.count() method with and without where clause.

Oh nice, I intended on adding that!

Gaming

Programming

Admin Users Count

Did you find this page helpful?