Quickstart

The fastest way to try Trilogy is with our interactive demo.

For a deeper experience—writing, reusing, and exploring queries—the Studio offers a browser-based IDE, similar to DBeaver or SQLPad.

Tips

We strongly recommend starting with one of these no-install tools. If Trilogy clicks for you, read on to explore deeper integrations.

Local Development

If you want to use Trilogy programmatically or integrate it into scripts, start with the Python SDK or the VS Code extension.

Python

Trilogy's reference implementation is in Python. It gives you:

A CLI to run .preql files
An API to evaluate Trilogy queries inline
A fully programmatic way to construct queries without writing Trilogy text

Pick the interface that fits your workflow—CLI or Python-native.

Installation

pip install pytrilogy

You can now run Trilogy queries from the CLI:

trilogy run "SELECT 1 + 1->test;" duckdb

This works with backends like DuckDB and BigQuery that support environment-based defaults.

A Longer Hello World

To write a useful script, you'll usually need to define some datasources representing objects in your database.

Hello World

key sentence_id int;
property sentence_id.word_one string; # comments after a definition 
property sentence_id.word_two string; # are syntactic sugar for adding
property sentence_id.word_three string; # a description to it

# comments in other places are just comments

# define our datasources as queries in duckdb
datasource word_one(
    sentence: sentence_id,
    word:word_one
)
grain(sentence_id)
query '''
select 1 as sentence, 'Hello' as word
union all
select 2, 'Bonjour'
''';

datasource word_two(
    sentence: sentence_id,
    word:word_two
)
grain(sentence_id)
query '''
select 1 as sentence, 'World' as word
union all
select 2 as sentence, 'World'
''';

datasource word_three(
    sentence: sentence_id,
    word:word_three
)
grain(sentence_id)
query '''
select 1 as sentence, '!' as word
union all
select 2 as sentence, '!'
''';

# an actual select statement
SELECT
    --sentence_id,
    word_one || ' ' || word_two ||  word_three as hello_world, # outputs must be named, trailing commas are okah
WHERE 
    sentence_id = 1
;
# semicolon termination for all statements

Hello Trilogy

You can run this query online against our demo backend.

My First Query

SELECT 1 + 1->test;

In Python

Trilogy is easy to embed in Python. It returns sql-alchemy result sets, which many tools integrate with.

from trilogy import Dialects, Environment

executor = Dialects.DUCK_DB.default_executor(environment=Environment())

results = executor.execute_text("SELECT 1 + 1->test;")

for rs in results:
    for row in rs.fetchall():
        print(row)

Defining Data Inline

To go beyond toy examples, you’ll define your own data sources.

Environments

An Environment defines available tables, properties, and grain. You can:

Build one inline (as above)
Load it from a .preql file

Imported:

from trilogy import Environment, Dialects
from pathlib import Path

env = Environment.from_file(Path("./model.preql"))
executor = Dialects.DUCK_DB.default_executor(environment=env)

Inline:


from trilogy import Environment

executor = Dialects.DUCK_DB.default_executor(environment=Environment())

results = executor.execute_text("""
key user_id int;
property user_id.name string;

datasource users (
    uid:user_id,
    name:name
)
grain(user_id)
query '''
select 1 uid, 'Bach' as "name"
union all 
select 2, 'Mozart',
union all
select 3, 'Beethoven'
''';

# run our query
select 
    user_id,
    name,
    len(name)->name_length
;

""")

for rs in results:
    for row in rs.fetchall():
        print(row)

Backend Support

Trilogy uses sqlalchemy to connect to databases. You can install the necessary drivers with the following commands:

Tips

DuckDB is the default backend, and requires no additional configuration.

pip install pytrilogy[bigquery]
pip install pytrilogy[snowflake]
...etc

Snowflake Config

Snowflake requires account info.

from trilogy.dialect.config import SnowflakeConfig
executor = Dialects.SNOWFLAKE.default_executor(
            environment=presto_model,
            conf=SnowflakeConfig(
                account="account", username="user", password="password"
            ),
        )

Postgres Config

Postgres requires a connection string.

from trilogy import Dialects
from trilogy.dialect.config import  PostgresConfig

executor = Dialects.POSTGRES.default_executor(
    environment=presto_model,
    conf=PostgresConfig(
        host="account", port="port", username="user", password="password", database="db"
    ),
)

Loading Model File

Models can be imported from a path.

from trilogy import Environment, Dialects
from pathlib import Path

executor = Dialects.DUCK_DB.default_executor(environment=Environment.from_file(Path('./model.preql')))

For more information, see the concepts page.

VSCode Extension

Trilogy has [very alpha] availablity as a VSCode extension. You can install it from the marketplace.