What is the correct syntax for including SQL values AND rowsAsArray option in a query?

[millerbryan]

Discussed in #3095

This is really a documentation enhancement request and should not be moved to a discussion.

There is no place in the docs where the correct syntax for using both rowsAsArray and a SQL value is shown.

^{Originally posted by millerbryan October 2, 2024}
I've looked at all the examples in the docs which use rowsAsArray and it isn't clear how to specify it in a query (rather than in the connection) when passing in SQL values. Is this possible to pass both?

// simplified code
const sql = "select county_name from static_web_data.state_counties where state_name = ?";
const state = "TX"

        try {
          const [rows, fields] = await pool.query({sql, [state], rowsAsArray: true});
          if (rows) {
            return rows;
          }
        } catch (err) {
          console.log(err);
        }
```</div>

[wellwelwel]

Thanks, @millerbryan 🤝

Based on the types, there are two ways to use it:

node-mysql2/typings/mysql/lib/protocol/sequences/promise/QueryableBase.d.ts

Lines 13 to 19 in c06d2ae

	query<T extends QueryResult>(
	options: QueryOptions,
	): Promise<[T, FieldPacket[]]>;
	query<T extends QueryResult>(
	options: QueryOptions,
	values: any,
	): Promise<[T, FieldPacket[]]>;

node-mysql2/typings/mysql/lib/protocol/sequences/Query.d.ts

Lines 7 to 15 in c06d2ae

	/**
	* The SQL for the query
	*/
	sql: string;
	/**
	* The values for the query
	*/
	values?: any | any[] | { [param: string]: any };

// A
pool.query({ sql: '', values: [], rowsAsArray: true });

// B
pool.query({ sql: '', rowsAsArray: true }, []);

---

I'll check it out in practice and also what happens if both are used at the same time with different values:

🧐💭

pool.query({ sql: '', values: [], rowsAsArray: true }, []);

[millerbryan]

Thank you @wellwelwel and apologies for missing your response.

I will have a go at your syntax now and see what happens.

[millerbryan]

The syntax works perfectly but the result set is different from what I expected. It includes quotes and [ ] for each item.

JavaScript syntax used: const [rows_array, fields] = await pool.query({ sql: sql, values: [state], rowsAsArray: true });

[millerbryan]

I was hoping for something more like this: