try {
const query = c.req.query("q");
if (!query || query.trim().length === 0) {
return c.json({
data: []
});
}
const db = createDatabase(c.env);
const searchTerm = query.trim();
// Use raw SQL to query the materialized view with text search
const results = await db.execute(sql`
SELECT
id,
species_code as "speciesCode",
primary_com_name as "primaryComName",
sci_name as "sciName",
bird_order as "birdOrder",
family
FROM ebird_taxonomy_v2024
WHERE
primary_com_name ILIKE ${`%${searchTerm}%`} OR
sci_name ILIKE ${`%${searchTerm}%`}
ORDER BY primary_com_name
LIMIT 20
`);
return c.json({
data: results.rows
});
} catch (error) {
return c.json(standardErrorResponse(error, "searching eBird taxonomy"), 500);
}