Raw mysql query not accepting variable input

Example in the docs: https://orm.drizzle.team/docs/sql#sql-template

Code that works for me:

    async listAllContent(rowNames: string[]) {
        const result = await db.execute(sql`SELECT * FROM tracked_address`)
        console.log('All content:', result)
    }

    async listAllContent(rowNames: string[]) {
        const result = await db.execute(sql`SELECT * FROM tracked_address`)
        console.log('All content:', result)
    }

    async listAllContent(rowNames: string[]) {
        const result = await db.execute(sql`SELECT * FROM tracked_address`)
        console.log('All content:', result)
    }

    async listAllContent(rowNames: string[]) {
        const result = await db.execute(sql`SELECT * FROM tracked_address`)
        console.log('All content:', result)
    }

Code that fails for me:

    async listAllContent(rowNames: string[]) {
        const command = 'SELECT * FROM tracked_address'
        const result = await db.execute(sql`${command}`)
        console.log('All content:', result)
    }

    async listAllContent(rowNames: string[]) {
        const command = 'SELECT * FROM tracked_address'
        const result = await db.execute(sql`${command}`)
        console.log('All content:', result)
    }

    async listAllContent(rowNames: string[]) {
        const command = 'SELECT * FROM tracked_address'
        const result = await db.execute(sql`${command}`)
        console.log('All content:', result)
    }

    async listAllContent(rowNames: string[]) {
        const command = 'SELECT * FROM tracked_address'
        const result = await db.execute(sql`${command}`)
        console.log('All content:', result)
    }

Error message:

    indexer:dev:             throw new DatabaseError(error.message, 400, error);
    indexer:dev:                   ^
    indexer:dev:
    indexer:dev: DatabaseError: syntax error at position 32 near 'SELECT * FROM tracked_address'
    indexer:dev:     at Connection.execute (/node_modules/.pnpm/@planetscale+database@1.11.0/node_modules/@planetscale/database/dist/cjs/index.js:99:19)
    indexer:dev:     at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    indexer:dev:   status: 400,
    indexer:dev:   body: {
    indexer:dev:     message: "syntax error at position 32 near 'SELECT * FROM tracked_address'",
    indexer:dev:     code: 'UNKNOWN'
    indexer:dev:   }
    indexer:dev: }

    indexer:dev:             throw new DatabaseError(error.message, 400, error);
    indexer:dev:                   ^
    indexer:dev:
    indexer:dev: DatabaseError: syntax error at position 32 near 'SELECT * FROM tracked_address'
    indexer:dev:     at Connection.execute (/node_modules/.pnpm/@planetscale+database@1.11.0/node_modules/@planetscale/database/dist/cjs/index.js:99:19)
    indexer:dev:     at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    indexer:dev:   status: 400,
    indexer:dev:   body: {
    indexer:dev:     message: "syntax error at position 32 near 'SELECT * FROM tracked_address'",
    indexer:dev:     code: 'UNKNOWN'
    indexer:dev:   }
    indexer:dev: }

    indexer:dev:             throw new DatabaseError(error.message, 400, error);
    indexer:dev:                   ^
    indexer:dev:
    indexer:dev: DatabaseError: syntax error at position 32 near 'SELECT * FROM tracked_address'
    indexer:dev:     at Connection.execute (/node_modules/.pnpm/@planetscale+database@1.11.0/node_modules/@planetscale/database/dist/cjs/index.js:99:19)
    indexer:dev:     at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    indexer:dev:   status: 400,
    indexer:dev:   body: {
    indexer:dev:     message: "syntax error at position 32 near 'SELECT * FROM tracked_address'",
    indexer:dev:     code: 'UNKNOWN'
    indexer:dev:   }
    indexer:dev: }

    indexer:dev:             throw new DatabaseError(error.message, 400, error);
    indexer:dev:                   ^
    indexer:dev:
    indexer:dev: DatabaseError: syntax error at position 32 near 'SELECT * FROM tracked_address'
    indexer:dev:     at Connection.execute (/node_modules/.pnpm/@planetscale+database@1.11.0/node_modules/@planetscale/database/dist/cjs/index.js:99:19)
    indexer:dev:     at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    indexer:dev:   status: 400,
    indexer:dev:   body: {
    indexer:dev:     message: "syntax error at position 32 near 'SELECT * FROM tracked_address'",
    indexer:dev:     code: 'UNKNOWN'
    indexer:dev:   }
    indexer:dev: }

Sorry if this question has been asked, I tried searching but failed to find anything. Thank you

Drizzle ORM - Magic sql`` operator

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

SaoriOP•2/17/24, 5:48 PM

figured it out:

    async listAllContent(rowNames: string[]) {
        for (const rowName of rowNames) {
            const allContent = await db.execute(
                sql`SELECT * FROM ${sql.identifier(rowName)}`
            )
            console.log(`All content from ${rowName}:`, allContent.rows)
        }
    }

    async listAllContent(rowNames: string[]) {
        for (const rowName of rowNames) {
            const allContent = await db.execute(
                sql`SELECT * FROM ${sql.identifier(rowName)}`
            )
            console.log(`All content from ${rowName}:`, allContent.rows)
        }
    }

Mykhailo•2/17/24, 5:48 PM

Hello, @Saori! Try to use sql.raw()sql.raw()

const command = 'SELECT * FROM tracked_address';
const result = await db.execute(sql.raw(`${command}`));

const command = 'SELECT * FROM tracked_address';
const result = await db.execute(sql.raw(`${command}`));

here you can find explanation for differences between just sql and sql.raw()sql.raw()
https://orm.drizzle.team/docs/sql#sqlraw

Drizzle ORM - Magic sql`` operator

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

SaoriOP•2/17/24, 5:49 PM

ahh interesting. long live asap

Raw mysql query not accepting variable input

Similar Threads

Similar Threads

Similar Threads