an example of getting costs for Tableland write queries

drivers-gas.js

// Dependencies: @tableland/sdk, @tableland/local
// Run `npx local-tableland` and then run this script in another window

import { Database, helpers } from "@tableland/sdk";
import { Wallet, getDefaultProvider, utils } from "ethers";

async function getCosts(tx, provider) {
  const receipt = await provider.getTransactionReceipt(tx);
  const effectiveGasPrice = receipt.effectiveGasPrice;
  const txGasUsed = receipt.gasUsed;
  console.log(`Tx gas used: ${txGasUsed}`);
  const gasUsedETH = effectiveGasPrice.mul(txGasUsed);
  const gasCostETH = utils.formatEther(gasUsedETH);
  console.log(`Total gas cost (ETH): ${gasCostETH}`);
  const gasCostUSD = utils.formatEther(gasUsedETH.mul(1580)); // Example: ETH:USD price == $1580
  console.log(`Total gas cost (USD): $${gasCostUSD}\n`);
  return txGasUsed;
}

async function main() {
  // Hard code Hardhat private key
  const privateKey =
    "0x5de4111afa1a4b94908f83103eb1f1706367c2e68ca870fc3fb9a804cdab365a";
  // Define the signer and connect to the provider.
  const wallet = new Wallet(privateKey);
  // A local Hardhat node from running `npx local-tableland`, but replace
  // with any provider URL (e.g., Alchemy, Infura, Etherscan, etc.).
  const provider = getDefaultProvider("http://127.0.0.1:8545");
  const signer = wallet.connect(provider);

  const chainId = await signer.getChainId();
  const db = new Database({
    signer,
    baseUrl: helpers.getBaseUrl(chainId),
  });
  const { meta: create } = await db
    .prepare(
      `CREATE TABLE drivers (permit_id integer primary key, race_team text, is_holo int, is_revealed int, driver_id int, permit_pfp_address text, permit_pfp_id int, is_discord_contributor int);`
    )
    .run();
  // Call `wait()` to await the tx to complete, then, grab the table's
  // auto-generated name (`{prefix}_{chainId}_{tableId}`) and ID.
  await create.txn?.wait();
  if (create.txn?.transactionHash) {
    console.log("Create tx");
    console.log("------------");
    getCosts(create.txn?.transactionHash, provider);
  }

  const { name: tableName } = create.txn;
  console.log(`Table name: ${tableName}`);

  const values = Array.from({ length: 2888 }, (_, k) => k).map((v) => {
    return {
      permitId: v,
      raceTeam: "KILL SWITCHES",
      isHolo: 0,
      isRevealed: 1,
      driverId: v,
    };
  });
  const formatValues = values.map(
    (v) =>
      `(${v.permitId},'${v.raceTeam}',${v.isHolo},${v.isRevealed},${v.driverId})`
  );
  // This string would be of length ~90000 characters long, so split it up into
  // somewhat arbitrary batches that are smaller than 35000
  const chunkSize = 1000;
  const chunks = [];
  for (let i = 0; i < formatValues.length; i += chunkSize) {
    const chunk = formatValues.slice(i, i + chunkSize);
    chunks.push(chunk);
  }
  // INSERT INTO drivers_1_20(permit_id,race_team,is_holo,is_revealed,driver_id)
  // VALUES(0,'KILL SWITCHES',0,1,0),(1,'KILL SWITCHES',0,1,1)...,(2887,'KILL
  // SWITCHES',0,1,2887)
  let gasUsed = 0;
  for (const chunk of chunks) {
    const statement = `INSERT INTO ${tableName}(permit_id,race_team,is_holo,is_revealed,driver_id) VALUES${chunk.join()}`;
    const { meta: insert } = await db.prepare(statement).run();
    await insert.txn.wait();
    if (insert.txn?.transactionHash) {
      console.log("Write tx");
      console.log("------------");
      const gas = await getCosts(insert.txn?.transactionHash, provider);
      gasUsed += Number(gas);
    }
  }
  // Total gas for all writes: 2604456
  console.log(`Total gas used: ${gasUsed}`);

  // And if you want to read the final table values
  // const { results } = await db.prepare(`SELECT * FROM ${tableName};`).all();
  // console.log(results);
}

await main();