How do I run a query on the insights database?

Objective: This article explains how to build and execute a query that runs on the insights database.

Prerequisites:

• You have access to the insights database.

Steps:

Insights database queries live in the /integrations/insights integration.

Example:

Template:

import type { Context } from '@hectare/platform.components.context'

export interface InsightsQueryRow {
  ... // Column definitions
}

export const getInsightsQuery = async (context: Context): Promise<InsightsQueryRow[]> => {
  const sql = `<your query here>`

  const parameters = [
    ...
  ]

  const result = (await context.postgres().executeQuery<InsightsQueryRow>(sql, parameters)).rows

  return result
}

Example:

import type { Context } from '@hectare/platform.components.context'

export interface InsightsUser {
  id: string
  email: string
}

export const getUsers = async (context: Context): Promise<InsightsUser[]> => {
  const sql = `
select
    id,
    email
from public.users
where
    id = '$1'
  `

  const parameters = [
    'users/123-A'
  ]

  const result = (await context.postgres().executeQuery<InsightsUser>(sql, parameters)).rows

  return result
}