SQL API

MUD initial data hydration, and therefore filtering, comes in two flavors: SQL and generic. Note that this is for the initial hydration, currently limits on on-going synchronization are limited to the generic method.

	SQL	Generic
Filtering	Can filter on most SQL functions	Can only filter on tables and the first two key fields (limited by `eth_getLogs` (opens in a new tab) filters)
Availability	Redstone (opens in a new tab), Garnet (opens in a new tab), or elsewhere if you run your own instance	Any EVM chain
Security assumptions	The indexer instance returns accurate information	The endpoint returns accurate information (same assumption as any other blockchain app)

If there is a SQL-enabled indexer instance serving a blockchain, as there is for Redstone (opens in a new tab) and Garnet (opens in a new tab), you can use it to:

Run queries on the data of any World on that blockchain.
Speed up the initial hydration by reducing the amount of data that needs to be synchronized. This is important for the user experience, because until the initial hydration is done the client is typically unusable.

The query language is a subset of the SQL SELECT command (opens in a new tab).

SQL-enabled indexer URLs

Redstone (opens in a new tab) - https://indexer.mud.redstonechain.com/q
Garnet (opens in a new tab) - https://indexer.mud.garnetchain.com/q

Example `World`

On Garnet there is a World at address 0x95F5d049B014114E2fEeB5d8d994358Ce4FFd06e (opens in a new tab) that runs a slightly modified version of the React template (opens in a new tab). You can see the data schema for the World in the block explorer (opens in a new tab).

Queries

You can run SQL queries by communicating directly with the indexer's API, for example using curl (opens in a new tab). To do so:

Create a file, query.json, which includes the World address and query. For example, you can use this file:

query.json

[
  {
    "address": "0x95F5d049B014114E2fEeB5d8d994358Ce4FFd06e",
    "query": "SELECT id, description FROM app__Tasks WHERE completedAt = 0 limit 2"
  }
]

Run this command (assuming you query from the Garnet chain). Install curl and jq first if necessary.

curl https://indexer.mud.garnetchain.com/q --compressed \
      -H 'Accept-Encoding: gzip'  \
      -H 'Content-Type: application/json' \
      -d @query.json | jq

The output is a mapping with two fields, the block height for which the result is valid, and the result itself. The result is a list of query responses, here it contains just one item because we only submitted a single query. The query response is also a list. The first entry is the field names, and all the other entries are rows returned by SELECT.

{
  "block_height": 5699682,
  "result": [
    [
      [
        "id",
        "description"
      ],
      [
        "0x3100000000000000000000000000000000000000000000000000000000000000",
        "Walk the dog"
      ],
      [
        "0x3e0a112aadc5e02927fb4a91649bea565fd1baa1175aae4cb4957d6348f165cf",
        "Test"
      ],
    ]
  ]
}

Here we only care about the first result, so from now on we use this command line to tell jq to only show us that information.

curl https://indexer.mud.garnetchain.com/q --compressed  \
    -H 'Accept-Encoding: gzip' \
    -H 'Content-Type: application/json' \
    -d @query.json | jq '.result[0]'

Simple query

This query looks for some fields from a single table.

ℹ️

The SQL API does not support SELECT * FROM <table>, you have to specify column names. In the case of Typescript selectFrom, the Typescript code specifies all the columns for you.

SELECT id, description FROM app_Tasks

Conditions

If we want to see only those tasks that haven't been completed we can use a WHERE clause.

SELECT id, description FROM app__Tasks WHERE completedAt=0

Limited results

If you only want to see a few results, you can use a LIMIT clause.

SELECT id, description FROM app__Tasks LIMIT 3

You can use OFFSET to get a paging effect. For example, if you use this query you'll get the last result from the previous query and another one.

SELECT id, description FROM app__Tasks LIMIT 2 OFFSET 2

Sorted results

If you want to control the order in which you get results, you can use an ORDER BY clause.

SELECT description, createdAt FROM app__Tasks ORDER BY createdAt

Multiple tables

You can join multiple tables, using the same syntax SQL uses.

This feature is not yet supported by selectFrom.

SELECT app__Creator.id, description, taskCreator FROM app__Tasks, app__Creator WHERE app__Creator.id=app__Tasks.id

Grouping results

You can use GROUP BY to identify different groups. For example, this query gets you the different task creators.

This feature is not yet supported by selectFrom.

SELECT taskCreator FROM app__Creator GROUP BY taskCreator

Metadata

You can use the /tables path to get the list of either all tables, or all tables that match a string. As per the SQL standard, the wildcard is %.

Create a file, tables.json.

tables.json

{
  "address": "0x95F5d049B014114E2fEeB5d8d994358Ce4FFd06e",
  "query": {
    "name": "%"
  }
}

Run this command.

curl https://indexer.mud.garnetchain.com/tables --compressed \
    -H 'Accept-Encoding: gzip'  \
    -H 'Content-Type: application/json' \
    -d @tables.json | jq

Results

[
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x746273746f72650000000000000000005461626c657300000000000000000000",
    "key_names": ["tableId"],
    "val_names": ["fieldLayout", "keySchema", "valueSchema", "abiEncodedKeyNames", "abiEncodedFieldNames"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x006003025f5f5fc4c40000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x746273746f72650000000000000000005265736f757263654964730000000000",
    "key_names": ["resourceId"],
    "val_names": ["exists"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x0001010060000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x746273746f726500000000000000000053746f7265486f6f6b73000000000000",
    "key_names": ["tableId"],
    "val_names": ["hooks"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x00000001b6000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c640000000000000000004e616d6573706163654f776e65720000",
    "key_names": ["namespaceId"],
    "val_names": ["owner"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x0014010061000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c6400000000000000000042616c616e6365730000000000000000",
    "key_names": ["namespaceId"],
    "val_names": ["balance"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x002001001f000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c64000000000000000000496e7374616c6c65644d6f64756c6573",
    "key_names": ["moduleAddress", "argumentsHash"],
    "val_names": ["isInstalled"],
    "key_schema": "0x00340200615f0000000000000000000000000000000000000000000000000000",
    "val_schema": "0x0001010060000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c640000000000000000005573657244656c65676174696f6e436f",
    "key_names": ["delegator", "delegatee"],
    "val_names": ["delegationControlId"],
    "key_schema": "0x0028020061610000000000000000000000000000000000000000000000000000",
    "val_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c640000000000000000004e616d65737061636544656c65676174",
    "key_names": ["namespaceId"],
    "val_names": ["delegationControlId"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c640000000000000000005265736f757263654163636573730000",
    "key_names": ["resourceId", "caller"],
    "val_names": ["access"],
    "key_schema": "0x003402005f610000000000000000000000000000000000000000000000000000",
    "val_schema": "0x0001010060000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c6400000000000000000053797374656d73000000000000000000",
    "key_names": ["systemId"],
    "val_names": ["system", "publicAccess"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x0015020061600000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c6400000000000000000046756e6374696f6e53656c6563746f72",
    "key_names": ["worldFunctionSelector"],
    "val_names": ["systemId", "systemFunctionSelector"],
    "key_schema": "0x0004010043000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x002402005f430000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x6f74776f726c6400000000000000000046756e6374696f6e5369676e61747572",
    "key_names": ["functionSelector"],
    "val_names": ["functionSignature"],
    "key_schema": "0x0004010043000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x00000001c5000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c6400000000000000000053797374656d486f6f6b730000000000",
    "key_names": ["systemId"],
    "val_names": ["value"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x00000001b6000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c6400000000000000000053797374656d52656769737472790000",
    "key_names": ["system"],
    "val_names": ["systemId"],
    "key_schema": "0x0014010061000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462776f726c64000000000000000000496e69744d6f64756c65416464726573",
    "key_names": [],
    "val_names": ["value"],
    "key_schema": "0x0000000000000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x0014010061000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x7462617070000000000000000000000043726561746f72000000000000000000",
    "key_names": ["id"],
    "val_names": ["taskCreator"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x0014010061000000000000000000000000000000000000000000000000000000",
    "query_name": null
  },
  {
    "address": "0x95f5d049b014114e2feeb5d8d994358ce4ffd06e",
    "table_id": "0x746261707000000000000000000000005461736b730000000000000000000000",
    "key_names": ["id"],
    "val_names": ["createdAt", "completedAt", "description"],
    "key_schema": "0x002001005f000000000000000000000000000000000000000000000000000000",
    "val_schema": "0x004002011f1fc500000000000000000000000000000000000000000000000000",
    "query_name": null
  }
]

To interpret the results, see the table documentation.

Typescript queries

You can query the SQL API from Typescript (opens in a new tab) without using MUD client synchronization.

Create the project (in an empty directory) and install the software.
```
pnpm create ts-node
pnpm install
```

Add the package that includes the library.

pnpm install @latticexyz/store-sync @latticexyz/store

Replace src/main.ts with this file.

main.ts

import { fetchRecords, selectFrom } from "@latticexyz/store-sync/internal";
import { defineStore } from "@latticexyz/store";
 
const config = defineStore({
  namespace: "app",
  tables: {
    Tasks: {
      schema: {
        id: "bytes32",
        createdAt: "uint256",
        completedAt: "uint256",
        description: "string",
      },
      key: ["id"],
    },
    Creator: {
      schema: {
        id: "bytes32",
        taskCreator: "address",
      },
      key: ["id"],
    },
  },
});
 
const queryUncompleted = selectFrom({
  table: config.tables.app__Tasks,
  where: "completedAt = 0",
  limit: 2,
});
 
const queryResult = await fetchRecords({
  indexerUrl: "https://indexer.mud.garnetchain.com/q",
  storeAddress: "0x95F5d049B014114E2fEeB5d8d994358Ce4FFd06e",
  queries: [queryUncompleted],
});
 
console.log("\n\nTwo uncompleted tasks");
console.log(`SQL: ${queryUncompleted.sql}\nResult:`);
console.log(queryResult.result[0].records);

Compile and execute the application.
```
pnpm build && pnpm start
```

Explanation

import { fetchRecords, selectFrom } from "@latticexyz/store-sync/internal";
import { defineStore } from "@latticexyz/store";

Import the necessary definitions.

const config = defineStore({
  namespace: "app",
  tables: {
    ...
  },
})

Create the table configuration. The input to defineStore is the same as used in the the mud.config.ts file.

const queryUncompleted = selectFrom({
  table: config.tables.app__Tasks,
  where: "completedAt = 0",
  limit: 2,
});

Create a query using selectFrom (opens in a new tab). The queries supported by selectFrom are a subset of those the SQL API supports. The results come from a single table, and only WHERE and LIMIT clauses are supported.

const queryResult = await fetchRecords({
  indexrUrl: "https://indexer.mud.garnetchain.com/q",
  storeAddress: "0x95F5d049B014114E2fEeB5d8d994358Ce4FFd06e",
  queries: [queryUncompleted],
});

Run the query.

console.log("\n\nTwo uncompleted tasks");
console.log(`SQL: ${queryUncompleted.sql}\nResult:`);

The SQL query that generated the resulting records.

console.log(queryResult.result[0].records);

The actual records.

MUD state hydration via SQL API

You can also use the SQL API in a MUD client to speed up the initial hydration.

Create a client to access the `World`

These are the steps to create a client that can access the World.

Create and run a react template application.

pnpm create mud@latest tasks --template react
cd tasks
pnpm dev

Browse to the application (opens in a new tab). The URL specifies the chainId and worldAddress for the World.
In MUD DevTools see your account address and fund it on Garnet (opens in a new tab). You may need to get test ETH for your own address, and then transfer it to the account address the application uses.
You can now create, complete, and delete tasks.

To see the content of the app__Creator table, edit packages/contracts/mud.config.ts to add the Creator table definition.

mud.config.ts

import { defineWorld } from "@latticexyz/world";
 
export default defineWorld({
  namespace: "app",
  tables: {
    Tasks: {
      schema: {
        id: "bytes32",
        createdAt: "uint256",
        completedAt: "uint256",
        description: "string",
      },
      key: ["id"],
    },
    Creator: {
      schema: {
        id: "bytes32",
        taskCreator: "address",
      },
      key: ["id"],
    },
  },
});

Updating the client to use the SQL API

The main purpose of the SQL API is to allow MUD clients to specify the subset of table records that a client needs, instead of synchronizing whole tables.

To update the client, you change packages/client/src/mud/setupNetwork.ts to:

setupNetwork.ts

/*
 * The MUD client code is built on top of viem
 * (https://viem.sh/docs/getting-started.html).
 * This line imports the functions we need from it.
 */
import {
  createPublicClient,
  fallback,
  webSocket,
  http,
  createWalletClient,
  Hex,
  ClientConfig,
  getContract,
} from "viem";
 
import { SyncFilter, getSnapshot, selectFrom } from "@latticexyz/store-sync/internal";
 
import { syncToZustand } from "@latticexyz/store-sync/zustand";
import { getNetworkConfig } from "./getNetworkConfig";
import IWorldAbi from "contracts/out/IWorld.sol/IWorld.abi.json";
import { createBurnerAccount, transportObserver, ContractWrite } from "@latticexyz/common";
import { transactionQueue, writeObserver } from "@latticexyz/common/actions";
import { Subject, share } from "rxjs";
 
/*
 * Import our MUD config, which includes strong types for
 * our tables and other config options. We use this to generate
 * things like RECS components and get back strong types for them.
 *
 * See https://mud.dev/templates/typescript/contracts#mudconfigts
 * for the source of this information.
 */
import mudConfig from "contracts/mud.config";
 
export type SetupNetworkResult = Awaited<ReturnType<typeof setupNetwork>>;
 
export async function setupNetwork() {
  const networkConfig = await getNetworkConfig();
 
  /*
   * Create a viem public (read only) client
   * (https://viem.sh/docs/clients/public.html)
   */
  const clientOptions = {
    chain: networkConfig.chain,
    transport: transportObserver(fallback([webSocket(), http()])),
    pollingInterval: 1000,
  } as const satisfies ClientConfig;
 
  const publicClient = createPublicClient(clientOptions);
 
  /*
   * Create an observable for contract writes that we can
   * pass into MUD dev tools for transaction observability.
   */
  const write$ = new Subject<ContractWrite>();
 
  /*
   * Create a temporary wallet and a viem client for it
   * (see https://viem.sh/docs/clients/wallet.html).
   */
  const burnerAccount = createBurnerAccount(networkConfig.privateKey as Hex);
  const burnerWalletClient = createWalletClient({
    ...clientOptions,
    account: burnerAccount,
  })
    .extend(transactionQueue())
    .extend(writeObserver({ onWrite: (write) => write$.next(write) }));
 
  /*
   * Create an object for communicating with the deployed World.
   */
  const worldContract = getContract({
    address: networkConfig.worldAddress as Hex,
    abi: IWorldAbi,
    client: { public: publicClient, wallet: burnerWalletClient },
  });
 
  const indexerUrl = "https://indexer.mud.garnetchain.com/q";
  const yesterday = Date.now() / 1000 - 24 * 60 * 60;
  const filters: SyncFilter[] = [
    selectFrom({
      table: mudConfig.tables.app__Tasks,
      where: `"createdAt" > ${yesterday}`,
    }),
    { table: mudConfig.tables.app__Creator },
  ];
  const { initialBlockLogs } = await getSnapshot({
    indexerUrl,
    storeAddress: networkConfig.worldAddress as Hex,
    filters,
    chainId: networkConfig.chainId,
  });
  const liveSyncFilters = filters.map((filter) => ({
    tableId: filter.table.tableId,
  }));
 
  /*
   * Sync on-chain state into RECS and keeps our client in sync.
   * Uses the MUD indexer if available, otherwise falls back
   * to the viem publicClient to make RPC calls to fetch MUD
   * events from the chain.
   */
  const { tables, useStore, latestBlock$, storedBlockLogs$, waitForTransaction } = await syncToZustand({
    initialBlockLogs,
    filters: liveSyncFilters,
    config: mudConfig,
    address: networkConfig.worldAddress as Hex,
    publicClient,
    startBlock: BigInt(networkConfig.initialBlockNumber),
  });
 
  return {
    tables,
    useStore,
    publicClient,
    walletClient: burnerWalletClient,
    latestBlock$,
    storedBlockLogs$,
    waitForTransaction,
    worldContract,
    write$: write$.asObservable().pipe(share()),
  };
}

Explanation

import { SyncFilter, getSnapshot, selectFrom } from "@latticexyz/store-sync/internal";

Import the definitions we need.

const indexerUrl = "https://indexer.mud.garnetchain.com/q";

The URL for the SQL-enabled indexer. This is simplified testing code, on a production system this will probably be a lookup table based on the chainId.

const yesterday = Date.now() / 1000 - 24 * 60 * 60;

In JavaScript (and therefore TypeScript), time is stored as milliseconds since the beginning of the epoch (opens in a new tab). In UNIX, and therefore in Ethereum, time is stored as seconds since that same point. This is the timestamp 24 hours ago.

  const filters: SyncFilter[] = [

We create the filters for the tables we're interested in.

    selectFrom({
      table: mudConfig.tables.app__Tasks,
      where: `"createdAt" > ${yesterday}`,
    }),

From the app__Tasks table we only want entries created in the last 24 hours. To verify that the filter works as expected you can later change the code to only look for entries older than 24 hours.

    { table: mudConfig.tables.app__Creator },
  ];

We also want the app__Counter table.

const { initialBlockLogs } = await getSnapshot({
  indexerUrl,
  storeAddress: networkConfig.worldAddress as Hex,
  filters,
  chainId: networkConfig.chainId,
});

Get the initial snapshot to hydrate (fill with initial information) the data store. Note that this snapshot does not have the actual data, but the events that created it.

const liveSyncFilters = filters.map((filter) => ({
  tableId: filter.table.tableId,
}));

The synchronization filters are a lot more limited. You can read the description of these filters here.

  const { ... } = await syncToZustand({
    initialBlockLogs,
    filters: liveSyncFilters,
      ...
  });

Finally, we provide initialBlockLogs for the hydration and filters for the updates to the synchronization function (either syncToRecs or syncToZustand).

PostgreSQL for data (and events)World Explorer