astro:db: db.run queries are giving different responses from Astro Studio and from local files

[timsexperiments]

Astro Info

Astro                    v4.7.1
Node                     v22.0.0
System                   macOS (arm64)
Package Manager          pnpm
Output                   server
Adapter                  @astrojs/cloudflare
Integrations             astro:db
                         @astrojs/db/file-url
                         @astrojs/tailwind

If this issue only occurs in one browser, which browser is a problem?

No response

Describe the Bug

When running queries with db.run, the result returned from the query differs on file and Astro DB databases. When running a query to a file database (local / using the ASTRO_DATABASE_FILE env var) the query rows are properly aliased with both the index and the alias for the selected column.

Another observation I have noticed is that with the file-based database response, the result.rows values are not iterable where they are iterable from the Astro Studio version. This means that destructuring a row by [first, second, ...] = result.rows[0] will not work on file-based database but will work with an Astro Studio DB.

Assuming the following code was run:

import { db, sql } from  'astro:db';

const result = db.run(sql`SELECT 1 as value`);

The following is an example of what works with the different configurations:

	Example Code	Astro Studio Database value Result	File-Based Databasevalue Result
Access through alias	const { value } = result	undefined	1
Access through index	const { 0: value } = result	1	1
Access through iterable	const [value] = result	1	Throws: TypeError: object is not iterable (cannot read property

What's the expected result?

I would expect:

1. The values are to be accessible through both the alias and the index, no matter what type of database is being used through the Astro DB API.

2. Either both results to be iterable or neither to be iterable.

Link to Minimal Reproducible Example

https://github.com/timsexperiments/astro-sql-file-vs-remote

Participation

• I am willing to submit a pull request for this issue.

[matthewp]

Definitely odd and not something I would expect. Thanks for letting us know.