Skip to content

setup.md

Latest commit

 

History

History
256 lines (179 loc) · 13 KB

setup.md

File metadata and controls

256 lines (179 loc) · 13 KB
Raw

This document describes the process for creating a new Graphene project from scratch, connected to your data. If you just want to take Graphene for a quick spin on some demo data, check out our example project.

Prepare database access

XLSX, CSV, etc. via DuckDB

Graphene can operate over any local data as long as it is converted into a single .duckdb file. If your data isn’t already in this format, it’s very easy these days to tell your coding agent to do it, eg. “convert these 3 .xslx files into a single DuckDB database.”

Postgres

Graphene can connect to any Postgres-compatible database that is reachable from your machine over the standard Postgres protocol. This includes local databases, databases reached through an SSH tunnel or proxy, and hosted Postgres services like Neon, AWS RDS, or Supabase when you already have the host, port, database, user, password, schema, and SSL setting needed to connect.

Provider-specific setup, such as creating an RDS security group rule, starting a tunnel, configuring a cloud SQL proxy, or setting up IAM-based database authentication, should be handled before running the Graphene installer. Graphene stores non-secret connection settings in package.json and writes the password to .env.

To set up Graphene on a Postgres connection you will need the following:

  • A database host and port, or a local tunnel that exposes one
  • A database name and schema, usually public
  • A user with permission to inspect schemas and query the tables/views you want Graphene to use
  • A password for that user
  • Whether the connection requires SSL

Step-by-step instructions

  1. Create a read-only user for Graphene. Adjust the database, schema, and password values before running:

    create user graphene_user with password 'REPLACE_WITH_A_STRONG_PASSWORD';
grant connect on database YOUR_DATABASE to graphene_user;
grant usage on schema YOUR_SCHEMA to graphene_user;
grant select on all tables in schema YOUR_SCHEMA to graphene_user;
grant select on all sequences in schema YOUR_SCHEMA to graphene_user;
alter default privileges in schema YOUR_SCHEMA grant select on tables to graphene_user;
alter default privileges in schema YOUR_SCHEMA grant select on sequences to graphene_user;

  2. Confirm that the database is reachable from the machine where Graphene will run. For local or tunneled databases, this is often localhost:5432. For hosted databases, use the provider’s hostname and SSL requirement.

  3. Run npm create graphene and choose Postgres. The installer will validate the connection with SELECT 1.

Hosted Postgres notes

Hosted Postgres providers usually work with the same Graphene Postgres connector once their provider-specific prerequisites are handled:

  • AWS RDS/Aurora: configure networking/security groups or a tunnel first. If you use IAM database authentication, generate the auth token before starting Graphene and expose it as the Postgres password. RDS connections commonly require TLS; when your connection string uses sslmode=require, Node may need the AWS RDS CA bundle in its trust store, for example NODE_EXTRA_CA_CERTS=/path/to/aws-rds-ca-bundle.pem pnpm graphene check.
  • Neon: use the pooled or direct Postgres hostname from Neon and enable SSL. Put the Neon password in .env, or use DATABASE_URL/POSTGRES_URL if you prefer a full connection string.
  • Supabase, Crunchy Bridge, Cloud SQL, and similar providers: use the provider's Postgres host, port, database, user, password, and SSL requirement. If the provider requires a proxy or custom CA certificate, start the proxy or configure Node's CA trust before running Graphene.

Snowflake

To set up Graphene on a Snowflake connection you will need the following:

  • Your Snowflake account identifier
  • A service account with USAGE and MONITOR privileges on all the databases, warehouses, schemas, tables, and views that you want Graphene to query
  • A .p8 key file for the service account saved to your computer

Step-by-step instructions

  1. Generate a private key file and associated public key. Open Terminal and run the following:

    openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out graphene_snowflake_key.p8 && openssl rsa -in graphene_snowflake_key.p8 -pubout -out graphene_snowflake_key.pub

  2. Pick any passphrase but remember it; you’ll need it later.

  3. Move the .p8 file to a dedicated location and make note of the absolute path.

  4. Then go to app.snowflake.com.

  5. Go to Create (+ icon) → SQL worksheet.

  6. Get your Snowflake account identifier (for Graphene setup later):

    select current_organization_name() || '-' || current_account_name();

  7. Create the service account, assign the public key to it, and grant it the required privileges. Paste in this sequence of SQL commands and replace all the variables with your details. Note that this script grants Graphene query access to all tables and views in the database; if you need more restricted permissions then feel free to alter this as needed.

    use role ACCOUNTADMIN;

-- Create the role and service account
create role if not exists graphene_role;
create user if not exists graphene_user;
grant role graphene_role to user graphene_user;
alter user graphene_user set
  type = service
  default_role = graphene_role
  default_warehouse = 'YOUR_WAREHOUSE'
  rsa_public_key = 'CONTENTS_OF_PUB_RSA_KEY_FILE';

-- Ability to see warehouse, database, all schemas
grant usage, monitor on warehouse YOUR_WAREHOUSE to role graphene_role;
grant usage, monitor on database YOUR_DATABASE to role graphene_role;
grant usage, monitor on all schemas in database YOUR_DATABASE to role graphene_role;
grant usage, monitor on future schemas in database YOUR_DATABASE to role graphene_role;

-- Ability to query all tables in the database
grant select on all tables in database YOUR_DATABASE to role graphene_role;
grant select on future tables in database YOUR_DATABASE to role graphene_role;

-- Ability to query all views in the database
grant select on all views in database YOUR_DATABASE to role graphene_role;
grant select on future views in database YOUR_DATABASE to role graphene_role;

BigQuery

To set up Graphene on a BigQuery connection you will need the following:

  • Your Google Cloud project ID
  • A service account with “BigQuery Job User” and “BigQuery Data Viewer” roles
  • A .json key file for the service account saved to your computer

Step-by-step instructions

  1. Go to console.cloud.google.com.

  2. In the top right corner, click Activate Cloud Shell (command line icon).

  3. In Cloud Shell, list your project IDs:

    gcloud projects list --format="table(projectId, name)"

  4. Then paste this in, replacing PROJECT_ID with your chosen project ID (you’re free to rename the other two variables as well):

    SA_NAME="graphene-bq"
KEY_FILE="graphene-bq-key.json"

# Change this
PROJECT_ID="PROJECT_ID"

  5. Then run this entire block to create the service account, grant it the required roles, and then generate the key file:

    # Create the Service Account
gcloud iam service-accounts create "${SA_NAME}" \
    --display-name="BigQuery Job and Data Viewer SA" \
    --project="${PROJECT_ID}"

# Grant BigQuery Job User Role
gcloud projects add-iam-policy-binding "${PROJECT_ID}" \
    --member="serviceAccount:${SA_NAME}@${PROJECT_ID}.iam.gserviceaccount.com" \
    --role="roles/bigquery.jobUser" \
    --condition=None

# Grant BigQuery Data Viewer Role
gcloud projects add-iam-policy-binding "${PROJECT_ID}" \
    --member="serviceAccount:${SA_NAME}@${PROJECT_ID}.iam.gserviceaccount.com" \
    --role="roles/bigquery.dataViewer" \
    --condition=None

# Generate the JSON Private Key
gcloud iam service-accounts keys create "${KEY_FILE}" \
    --iam-account="${SA_NAME}@${PROJECT_ID}.iam.gserviceaccount.com" \
    --project="${PROJECT_ID}"

  6. Finally, download the key file by clicking the three-dot menu (⋮) in the Cloud Shell header and then clicking Download. You will need to unzip the archive. Move the .json file into a dedicated location and make note of the absolute path.

MotherDuck

To set up Graphene on a MotherDuck connection you will need an access token.

Step-by-step instructions

  1. Go to app.motherduck.com

  2. Navigate to Settings > Integrations > Access Tokens

  3. Create a token with type "Read Scaling Token".

  4. Store it in your Graphene project .env file (or use npm create graphene to initialize the project, and it will create the .env file for you):

    MOTHERDUCK_TOKEN=<your-token>

MotherDuck uses DuckDB SQL syntax, so GSQL functions and expressions should follow the same rules as local DuckDB projects.

Set up your Graphene project

  1. In the terminal, navigate to where you want the Graphene project to live. It can be a standalone project or a folder within an existing repo.

  2. Run the Graphene installer with npm, yarn, or pnpm:

    npm create graphene

The installer will walk you through a short series of prompts, create your Graphene project, and test to make sure the database connection is working.

Install the IDE extension (optional)

Graphene has extensions for VSCode and Cursor which add syntax highlighting, linting, and hover states to enrich the development experience when working with GSQL and Graphene markdown files.

The extension is called Graphene VSCode Language Support which you can search for and install in View > Extensions for both VSCode and Cursor.

Create your semantic model

Start a new agent session within the Graphene project. Tell your agent:

Add .gsql files in a new folder ./tables for [tables you want exposed in Graphene] following the best practices outlined in model-gsql.md and gsql.md (in the Graphene skill).

Consider adding the following to the prompt when applicable:

  • A link or path to a dbt project
  • A link or path to a semantic model eg. LookML
  • An entity-relationship diagram (ERD)
  • This can be a token-heavy process, so consider working on a small batch first, reviewing the output, and then having your agent delegate to parallel subagents for the remainder. This way, you can catch any errors or stylistic preferences up front.

Add additional context

For best results, we recommend that you add the following to your agent context.

AGENTS.md (or CLAUDE.md)

In addition to the information that the Graphene installer adds, consider adding the following:

  • Short description of your business/use case
  • Short description of the scope of the data available in the project, and where the .gsql files are located
  • A glossary of internal jargon and acronyms
  • If your schema is highly denormalized, describe the conceptual ontology of the entities and relationships in the data with a mermaid-like diagram. For example:
# Ontology

Our Salesforce schema is highly denormalized. The following is a conceptual ontology of the data it describes.

```
erDiagram
   ACCOUNT ||--o{ OPPORTUNITY : "has | snapshot at close_date | opportunities.account_id, opportunities.account_name, opportunities.account_industry, opportunities.account_arr, opportunities.account_segment"
   CONTACT }o--o{ OPPORTUNITY : "primary contact on | snapshot at close_date | opportunities.contact_id, opportunities.contact_name, opportunities.contact_title, opportunities.contact_email"
   REP ||--o{ OPPORTUNITY : "owns | closing owner, not current | opportunities.owner_id, opportunities.owner_name, opportunities.owner_team, opportunities.owner_region"
   TERRITORY ||--o{ OPPORTUNITY : "assigned to | at time of close | opportunities.territory_id, opportunities.territory_name, opportunities.territory_region"
   REP }o--|| TERRITORY : "belongs to | opportunities.owner_region = opportunities.territory_region"
   OPPORTUNITY }o--|| dim_date : "opportunities.close_date → dim_date.date"
```

Skills

Agent skills are great for context that's helpful situationally, but not always, like:

  • Context about a particular department, how it's structured, and how it operates
  • Analytical "cookbooks" that are unique to your business, eg. how to perform cohort analysis in a particular way, how the company expects financial reports to look, etc.

If you aren't familiar with writing agent skills, we recommend you read the excellent guide here.