> ## Documentation Index
> Fetch the complete documentation index at: https://docs.mirage.strukto.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL in Python

> Virtualize a filesystem kept in SQL tables from Python.

`SqlVFS` keeps a filesystem in a database's tables: a SQLite file, or a
Postgres server through its driver. Every command that writes works, and the
files persist with the database. Setup is on the [SQL page](https://docs.mirage.strukto.ai/home/setup/sql.md).

## Install

```bash theme={null}
pip install mirage-ai
```

A SQLite file needs no extra: the mount uses Python's own `sqlite3`. Postgres
needs `pip install "mirage-ai[postgres]"`.

## Configure

```python theme={null}
from mirage import MountMode, Workspace
from mirage.vfs.sql import SqlVFS

ws = Workspace({"/sql": SqlVFS("./workspace.db")}, mode=MountMode.WRITE)

result = await ws.shell("echo hello > /sql/note.txt && ls /sql")
print(await result.stdout_str())
await ws.close()
```

Leave out the path to keep the database in memory. `capacity="100MB"` caps the
bytes the database's files hold; see [capacity](https://docs.mirage.strukto.ai/home/setup/vfs.md#mount-capacity).
To keep the tables in Postgres, pass its driver:

```python theme={null}
import os

from mirage.vfs.sql import PostgresSqlDriver, SqlVFS

driver = PostgresSqlDriver(os.environ["DATABASE_URL"])
vfs = SqlVFS(driver=driver)
```

### Fields

| Field | Default | Notes |
| - | - | - |
| `path` | in memory | The SQLite file, opened and closed with the mount. |
| `capacity` | none | The most bytes the database's files may hold, like `100MB`. |
| `driver` | none | A database to borrow instead of a file: `SqliteDriver` or `PostgresSqlDriver`. The caller closes it. Not with `path`. |
| `root` | `/` | The folder of the database the mount serves, made if missing. |

## Layout

```text theme={null}
/sql/
  notes/
    hello.txt
  users.csv
```

The tables are laid out as [AgentFS](https://github.com/tursodatabase/agentfs)'s:

* `fs_inode`: one row per file and folder, with its size and mtime.
* `fs_dentry`: each entry's name under its parent folder.
* `fs_data`: a file's bytes, in rows of the database's chunk size (4 KiB unless
  the database says otherwise).
* `fs_config`: the chunk size.

## Behavior

* **One filesystem, both SDKs:** the TypeScript mount reads and writes the
  same tables, so a Python and a Node process on one file serve one filesystem.

* **Several mounts on one database:** a mount serves one folder of its
  database, `/` by default. Hand two mounts one driver to serve the whole
  database and one folder as a mount of its own:

  ```python theme={null}
  from mirage.vfs.sql import SqliteDriver

  driver = SqliteDriver("./workspace.db")
  ws = Workspace(
      {
          "/": SqlVFS(driver=driver, capacity="1GB"),
          "/workspace": SqlVFS(driver=driver, root="/workspace", capacity="1GB"),
      },
      mode=MountMode.WRITE,
  )
  ```

* **Capacity:** `capacity` counts every file in the database, whichever mount
  wrote it. Mounts on one driver with the same `capacity` share one count, so
  their writes together stay within it. Mounts on separate drivers count apart,
  even on one file, and together they can pass it.

* **Snapshots:** a snapshot keeps the mount's own folder, and loading it
  replaces that folder. The rest of the database stays as it was.

## VFS.md

The [VFS.md](https://docs.mirage.strukto.ai/home/setup/vfs-md.md) an agent reads with SQL mounted at `/<path>`.
`<mode>` is the mount's mode: `read`, `write` or `exec`. The commands shown are
those of a `write` mount.

> [tab] Rendered
>
> **Virtual Filesystem**
>
> | Path | Description |
> | - | - |
> | `/<path>` | `sql`, `<mode>` |
>
> **Supported Unix Commands for VFS**
>
> Unix commands work on each path as on a local filesystem, as far as the path supports them:
>
> | Path | Supported |
> | - | - |
> | `/<path>` | Almost all Unix commands. `grep -r` and `rg` can ask SQLite which files may match. Postgres searches walk the files. |
>
> **Limitations of Unix Commands for VFS**
>
> Where a path differs from a local filesystem:
>
> | Path | Limitations |
> | - | - |
> | `/<path>` | `grep -r`, `find` and `du` stop after 10000 files; a glob matching more, `tar -c`, `cp -r` and `rm -r` are refused. |
>
> Run `man <cmd>` or `<cmd> --help` for one command's usage.

> [tab] Raw
>
> ```markdown theme={null}
> ---
> name: workspace-filesystem
> description: The virtual filesystem layout of this workspace.
> ---
>
> # Virtual Filesystem
>
> | Path | Description |
> | --- | --- |
> | `/<path>` | `sql`, `<mode>` |
>
> ## Supported Unix Commands for VFS
>
> Unix commands work on each path as on a local filesystem, as far as the path supports them:
>
> | Path | Supported |
> | --- | --- |
> | `/<path>` | Almost all Unix commands. `grep -r` and `rg` can ask SQLite which files may match. Postgres searches walk the files. |
>
> ## Limitations of Unix Commands for VFS
>
> Where a path differs from a local filesystem:
>
> | Path | Limitations |
> | --- | --- |
> | `/<path>` | `grep -r`, `find` and `du` stop after 10000 files; a glob matching more, `tar -c`, `cp -r` and `rm -r` are refused. |
>
> Run `man <cmd>` or `<cmd> --help` for one command's usage.
> ```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.