Data Tables

This page is part of our section on Persistent storage & databases which covers where to effectively store and manage the data manipulated by Windmill. Check that page for more options on data storage.

Windmill Data Tables let you store and query relational data with near-zero setup using databases managed automatically by Windmill. They provide a simple, safe, workspace-scoped way to leverage SQL inside your workflows without exposing credentials.

---

Getting started

Go to workspace settings -> Data Tables and configure a Data Table :

Superadmins can use a Custom Instance Database and get started with no setup.

Usage

Typescript

import * as wmill from 'windmill-client';

export async function main(user_id: string) {
	// let sql = wmill.datatable('named_datatable');
	let sql = wmill.datatable();

	// This string interpolation syntax is safe
	// and is transformed into a parameterized query
	let friend = await sql`SELECT * FROM friend WHERE id = ${user_id}`.fetchOne();
	// let allFriends = await sql`INSERT INTO friend VALUES ('John', 21)`.fetch();

	return friend;
}

Python

import wmill

def main(user_id: str):
    # db = wmill.datatable('named_datatable')
    db = wmill.datatable()

    # Postgres scripts use positional arguments
    friend = db.query('SELECT * FROM friend WHERE id = $1', user_id).fetch_one()
    # all_friends = db.query('SELECT * FROM friend').fetch()

    return friend

DuckDB

-- $user_id (bigint)

-- ATTACH 'datatable://named_datatable' AS dt;
ATTACH 'datatable' AS dt;
USE dt;

SELECT * FROM friend WHERE id = $user_id;

---

Assets integration

Data tables are assets in Windmill. When you reference a data table in a script, Windmill automatically parses the code and detects them. You can then click on it and explore the data table in the Database Explorer.

Windmill auto detects if the data table was used in Read (SELECT ... FROM) or Write mode (UPDATE, DELETE ...). Assets are displayed as asset nodes in flows, making it easy to visualize data dependencies between scripts.

---

Workspace-Scoped

Data tables are scoped to a workspace. All members of the workspace can access its data tables. Credentials are managed internally by Windmill and are never exposed to users.

Special Data Table: main

The data table named main is the default data table. Scripts can access it without specifying its name.

Example:

# Uses the 'main' data table implicitly
wmill.datatable()

---

Database Types

Windmill currently supports two backend database types for Data Tables:

1. Custom Instance Database

• Uses the Windmill instance database.
• Zero-setup, one-click provisioning.
• Requires superadmin to configure.
• Although the database exists at the instance level, it is only accessible to workspaces that define a data table pointing to it.
• See Custom Instance Database for more details.

2. Postgres Resource

• Attach a workspace Postgres resource to the data table.
• Ideal when you want full control over database hosting, but still benefit from Windmill's credential management and workspace scoping.

---

Permissions

Currently, Windmill does not enforce database-level permissions in data tables.

• Any workspace member can execute full CRUD operations.
• Table/row-level permissions may be introduced in a later version.

Windmill ensures secure access by handling database credentials internally.

---

Summary