Overview

Learn Teide JS in 10 Minutes

A hands-on walkthrough that takes you from zero to productive. By the end, you'll know how to create tables, query data with SQL, aggregate, join, use window functions, load CSV files, and work with the fluent API.

1. Install and Import

npm install teidedb

import { Context, col, lit } from 'teidedb';

const ctx = new Context();

The Context is the entry point. It initializes the Teide engine and gives you a session to run queries against.

2. Create Tables

Use standard SQL CREATE TABLE and INSERT INTO to build tables from scratch:

CREATE TABLE employees (
  id INTEGER,
  name VARCHAR,
  department VARCHAR,
  salary DOUBLE,
  hire_year INTEGER
)

ctx.executeSync(`
  CREATE TABLE employees (
    id INTEGER,
    name VARCHAR,
    department VARCHAR,
    salary DOUBLE,
    hire_year INTEGER
  )
`);

ctx.executeSync(`INSERT INTO employees VALUES (1, 'Alice', 'Engineering', 95000, 2019)`);
ctx.executeSync(`INSERT INTO employees VALUES (2, 'Bob', 'Marketing', 72000, 2020)`);
ctx.executeSync(`INSERT INTO employees VALUES (3, 'Carol', 'Engineering', 105000, 2018)`);
ctx.executeSync(`INSERT INTO employees VALUES (4, 'Dave', 'Marketing', 68000, 2021)`);
ctx.executeSync(`INSERT INTO employees VALUES (5, 'Eve', 'Engineering', 112000, 2017)`);
ctx.executeSync(`INSERT INTO employees VALUES (6, 'Frank', 'Sales', 78000, 2020)`);

3. Query Data

Run SELECT queries to retrieve data. Results come back as Table objects.

const result = ctx.executeSync(`
  SELECT name, department, salary
  FROM employees
  ORDER BY salary DESC
  LIMIT 3
`);

console.log(result.columns);  // ['name', 'department', 'salary']
console.log(result.nRows);    // 3

Use aliases with AS to rename output columns:

SELECT name AS employee, salary AS annual_pay
FROM employees
ORDER BY annual_pay DESC
LIMIT 3

4. Filter with WHERE

The WHERE clause supports comparisons, BETWEEN, IN, and LIKE:

// Comparison
ctx.executeSync(`SELECT name, salary FROM employees WHERE salary > 80000`);

// BETWEEN
ctx.executeSync(`SELECT name, hire_year FROM employees WHERE hire_year BETWEEN 2019 AND 2021`);

// IN
ctx.executeSync(`SELECT name, department FROM employees WHERE department IN ('Engineering', 'Sales')`);

// LIKE
ctx.executeSync(`SELECT name FROM employees WHERE name LIKE 'A%'`);

5. Aggregate with GROUP BY

Use aggregate functions like COUNT, AVG, MIN, MAX, and SUM. Filter groups with HAVING.

const deptStats = ctx.executeSync(`
  SELECT
    department,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary
  FROM employees
  GROUP BY department
  HAVING COUNT(*) > 1
  ORDER BY avg_salary DESC
`);

console.log(deptStats.columns);
console.log(deptStats.nRows);

6. Join Tables

Create a second table and join it with the first:

ctx.executeSync(`
  CREATE TABLE projects (
    project_id INTEGER,
    project_name VARCHAR,
    department VARCHAR,
    budget DOUBLE
  )
`);

ctx.executeSync(`INSERT INTO projects VALUES (1, 'Backend Rewrite', 'Engineering', 500000)`);
ctx.executeSync(`INSERT INTO projects VALUES (2, 'Brand Campaign', 'Marketing', 150000)`);
ctx.executeSync(`INSERT INTO projects VALUES (3, 'ML Pipeline', 'Engineering', 300000)`);

// INNER JOIN
const joined = ctx.executeSync(`
  SELECT e.name, e.department, p.project_name, p.budget
  FROM employees e
  INNER JOIN projects p ON e.department = p.department
  ORDER BY e.name, p.project_name
`);

// LEFT JOIN (includes employees with no matching project)
const leftJoined = ctx.executeSync(`
  SELECT e.name, p.project_name
  FROM employees e
  LEFT JOIN projects p ON e.department = p.department
  ORDER BY e.name
`);

7. Window Functions

Window functions let you compute values across rows without collapsing them. Use ROW_NUMBER(), RANK(), and running totals with SUM() OVER.

// Row numbers within each department
const ranked = ctx.executeSync(`
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department ORDER BY salary DESC
    ) AS rank_in_dept
  FROM employees
`);

// Running total of salary ordered by hire_year
const running = ctx.executeSync(`
  SELECT
    name,
    hire_year,
    salary,
    SUM(salary) OVER (
      ORDER BY hire_year
      ROWS UNBOUNDED PRECEDING
    ) AS running_total
  FROM employees
`);

8. Subqueries

Subqueries work in WHERE clauses, FROM clauses, and with IN:

// Employees earning above average
const aboveAvg = ctx.executeSync(`
  SELECT name, salary
  FROM employees
  WHERE salary > (SELECT AVG(salary) FROM employees)
  ORDER BY salary DESC
`);

// Departments with total salary over 200k
const bigDepts = ctx.executeSync(`
  SELECT name, department
  FROM employees
  WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING SUM(salary) > 200000
  )
`);

9. Load CSV Files

Load external CSV data directly into tables using read_csv():

// Create a table from a CSV file
ctx.executeSync("CREATE TABLE sales AS SELECT * FROM read_csv('sales.csv')");

// Query the loaded data immediately
const topSales = ctx.executeSync(`
  SELECT product, SUM(amount) AS total
  FROM sales
  GROUP BY product
  ORDER BY total DESC
  LIMIT 10
`);

console.log(topSales.columns, topSales.nRows);

10. Use the Fluent API

The fluent API lets you build queries programmatically. Operations are lazy — they only execute when you call collectSync() or collect().

import { Context, col, lit } from 'teidedb';

const ctx = new Context();
ctx.executeSync("CREATE TABLE t AS SELECT * FROM read_csv('data.csv')");
const table = ctx.executeSync("SELECT * FROM t");

// Filter: rows where price > 50
const expensive = table
  .filter(col('price').gt(lit(50)))
  .collectSync();

// Sort and limit
const topFive = table
  .sort('price', { descending: true })
  .head(5)
  .collectSync();

// Group by and aggregate
const summary = table
  .groupBy('category')
  .agg(col('price').sum().alias('total'))
  .sort('total', { descending: true })
  .collectSync();

console.log(summary.columns, summary.nRows);

You can chain as many operations as you need. The query is compiled and executed in a single pass when you call collectSync().

11. Cleanup

Always destroy the context when you are done. This tears down the background Teide thread and frees all C heap memory.

ctx.destroy();

If you are using TypeScript with the using declaration (TC39 explicit resource management), cleanup is automatic:

// With Symbol.dispose support
{
  using ctx = new Context();
  ctx.executeSync("SELECT 1 + 1 AS result");
} // ctx.destroy() is called automatically

Next Steps