Eli Posts - Answer Overflow

Eli

•Created by Eli on 6/21/2024 in #help

All values of query result are of type string

I I use Drizzle as query builder

    let query = qb
        .select( {
            Time: sql<string>`${ schema.goals.time }`.as( 'Time' ),
            Period: sql<number>`${ schema.goals.period }`.as( 'Period' ),
            GoalType: sql<string>`${ schema.goals.goalType }`.as( 'GoalType' ),
            GameId: sql<number>`${ schema.goals.gameId }`.as( 'GameId' ),
            IsJunior: sql<boolean>`${ schema.leagues.isJunior }`.as( 'IsJunior' ),
            IsFemale: sql<boolean>`${ schema.leagues.isFemale }`.as( 'IsFemale' ),
            FieldSize: sql<string>`${ schema.leagues.fieldSize }`.as( 'FieldSize' ),
            LeagueId: sql<number>`${ schema.leagues.id }`.as( 'LeagueId' ),
        } )
        .from( schema.goals )
        .leftJoin( schema.games, eq( schema.goals.gameId, schema.games.id ) )
        .leftJoin( schema.leagues, eq( schema.leagues.id, schema.games.leagueId ) )

    const data = await fetchFromMyDb( query.$dynamic(), serverLoadEvent.fetch )
    const result = data as unknown as typeof data._.result

    // somehow IsFemale is string!?
    console.dir( result )
    console.log( result[ 0 ].IsFemale , typeof result[ 0 ].IsFemale )
    console.log( result[ 0 ].Period, typeof result[ 0 ].Period )

    let query = qb
        .select( {
            Time: sql<string>`${ schema.goals.time }`.as( 'Time' ),
            Period: sql<number>`${ schema.goals.period }`.as( 'Period' ),
            GoalType: sql<string>`${ schema.goals.goalType }`.as( 'GoalType' ),
            GameId: sql<number>`${ schema.goals.gameId }`.as( 'GameId' ),
            IsJunior: sql<boolean>`${ schema.leagues.isJunior }`.as( 'IsJunior' ),
            IsFemale: sql<boolean>`${ schema.leagues.isFemale }`.as( 'IsFemale' ),
            FieldSize: sql<string>`${ schema.leagues.fieldSize }`.as( 'FieldSize' ),
            LeagueId: sql<number>`${ schema.leagues.id }`.as( 'LeagueId' ),
        } )
        .from( schema.goals )
        .leftJoin( schema.games, eq( schema.goals.gameId, schema.games.id ) )
        .leftJoin( schema.leagues, eq( schema.leagues.id, schema.games.leagueId ) )

    const data = await fetchFromMyDb( query.$dynamic(), serverLoadEvent.fetch )
    const result = data as unknown as typeof data._.result

    // somehow IsFemale is string!?
    console.dir( result )
    console.log( result[ 0 ].IsFemale , typeof result[ 0 ].IsFemale )
    console.log( result[ 0 ].Period, typeof result[ 0 ].Period )

Output:

  {
    Time: '0:41',
    Period: '1',
    GoalType: 'regular',
    GameId: '35463',
    IsJunior: '0',
    IsFemale: '1',
    FieldSize: 'KF',
    LeagueId: '1541'
  }
]
1 string
1 string

  {
    Time: '0:41',
    Period: '1',
    GoalType: 'regular',
    GameId: '35463',
    IsJunior: '0',
    IsFemale: '1',
    FieldSize: 'KF',
    LeagueId: '1541'
  }
]
1 string
1 string

The intellisense in VSCode shows me the correct types on hover (as specified in the sql template strings) However the actual data in result is all string I know it is a bit of a hacky solution, as I use Drizzle to generate a raw SQL query with typings applied later on. Since there is currently no other way of getting the types otherwise () am I doing something wrong here that I do not get the types right (anylinger - it seemed to work up until now or I haven't checked it thorough)

2 replies

DTDrizzle Team

•Created by Eli on 5/25/2024 in #help

Get return-type from QueryBuilder

I do not have a connection to a DB, I just use the query-builder for SQL generation.
I try to write a simple function, that takes a querybuilder query in and should return a object of the result type, as if I have awaited a DB with the query:
(I use another db to fetch data - but I need the type from drizzle)

const playerQuery = await queryBuilder
    .select( /* doesnt need to be just a table */ )
    .from( schema.players )
    //.join(...)

const players = MockRequest( playerQuery )

export async function MockRequest<T extends MySqlSelectQueryBuilder>( query: T ): Promise<Awaited<T[]>>
{
    const untypedData = {}
    const data = untypedData as Awaited<T[]>

    return data
}

const playerQuery = await queryBuilder
    .select( /* doesnt need to be just a table */ )
    .from( schema.players )
    //.join(...)

const players = MockRequest( playerQuery )

export async function MockRequest<T extends MySqlSelectQueryBuilder>( query: T ): Promise<Awaited<T[]>>
{
    const untypedData = {}
    const data = untypedData as Awaited<T[]>

    return data
}

This is the return-type:

MySqlSelectQueryBuilderBase<MySqlSelectQueryBuilderHKT, "players", {
    id: MySqlColumn<{
        name: "id";
        tableName: "players";
        dataType: "number";
        columnType: "MySqlInt";
        data: number;
        driverParam: string | number;
        notNull: true;
        hasDefault: false;
        enumValues: undefined;
        baseColumn: never;
    }, object>;
    playerId: MySqlColumn<...>;
    firstName: MySqlColumn<...>;
    lastName: MySqlColumn<...>;
}, ... 6 more ..., {
    ...;
}>

MySqlSelectQueryBuilderBase<MySqlSelectQueryBuilderHKT, "players", {
    id: MySqlColumn<{
        name: "id";
        tableName: "players";
        dataType: "number";
        columnType: "MySqlInt";
        data: number;
        driverParam: string | number;
        notNull: true;
        hasDefault: false;
        enumValues: undefined;
        baseColumn: never;
    }, object>;
    playerId: MySqlColumn<...>;
    firstName: MySqlColumn<...>;
    lastName: MySqlColumn<...>;
}, ... 6 more ..., {
    ...;
}>

When it should be:

{
    id: number;
    playerId: number | null;
    firstName: string;
    lastName: string;
}[]

{
    id: number;
    playerId: number | null;
    firstName: string;
    lastName: string;
}[]

How do I type this correctly?

4 replies

DTDrizzle Team

•Created by Eli on 5/23/2024 in #help

Get raw SQL string with parameters

I use drizzle to build up SQL strings - but I need to get the final SQL string. I can use query.toSQL() and then do a string-replace on ? with toSQL().sql | toSQL().params. My questions are: - is this the right way to do it? - if so, is it safe to do a simple text-replace on the question-marks?

3 replies

Gaming

Programming