Skip to main content

Data Viewer

In data-driven applications, providing users with intuitive tools to explore and visualize data is essential for informed decision-making and insights discovery. The users' explorations often involve filtering, sorting, and generally making complex queries on data. Those queries are often offloaded to the provider of the data, which can be slow due to round-trip delays, expensive as all computations are done on the server, and if the client querying is offline or with poor network connectivity, queries may fail and return no results, leading to a poor user experience.

With ElectricSQL, the local-first development approach uses a local database for efficient querying, sorting, and filtering of data, enabling users to quickly and interactively explore datasets with ease, without worrying about network connectivity. By leveraging the power of SQL queries, developers can perform complex data manipulations and retrieve specific subsets of data based on user-defined criteria, ensuring a tailored and responsive viewing experience.

This recipe demonstrates how to use ElectricSQL to create a table and chart data viewer for a simple e-commerce-like orders database, delegating pagination, sorting, filtering, aggregation, and arbitrary queries to the underlying local database.

Schema​

Adapt the schema and DDLX commands below to match your specific use-case.

-- Create an orders table.
-- Can be extended or modified to any arbitrary table
-- to fit your use case.
CREATE TABLE IF NOT EXISTS commerce_orders (
  order_id UUID PRIMARY KEY NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  price_amount REAL NOT NULL,
  price_currency VARCHAR NOT NULL,
  promo_code VARCHAR,
  customer_full_name VARCHAR NOT NULL,
  country VARCHAR NOT NULL,
  product VARCHAR NOT NULL
);

-- Index for timestamp column in commerce_orders table
CREATE INDEX commerce_orders_idx_timestamp ON commerce_orders(timestamp);

-- Index for country column in commerce_orders table
CREATE INDEX commerce_orders_idx_country ON commerce_orders(country);

-- ⚡ Electrify the table
ALTER TABLE commerce_orders ENABLE ELECTRIC;

Data Access​

Adapt the headless components below and enhance them with additional features.

import { useMemo } from 'react'
import { useElectric } from '../electric/ElectricWrapper'
import { useLiveQuery } from 'electric-sql/react'
export interface PaginationState {
  pageIndex: number
  pageSize: number
}

export interface SortingState {
  field: string
  order?: 'asc' | 'desc'
}

export const useTableData = ({
  sorting = [],
  pagination,
  whereClause = '1=1',
}: {
  sorting: SortingState[]
  pagination: PaginationState
  whereClause?: string
}) => {
  const { db } = useElectric()!

  // Build the ORDER BY clause from the sorting state
  const orderByClause = useMemo(() => {
    const sortStatements = sorting
      .filter((sortState) => !!sortState.order)
      .map((sortState) => `${sortState.field} ${sortState.order}`)
    return sortStatements.length > 0 ? `ORDER BY ${sortStatements.join(',')}` : ''
  }, [sorting])

  // Get the order data for the given query
  const { results: orders = [] } = useLiveQuery(
    db.liveRawQuery({
      sql: `
      SELECT * FROM commerce_orders
      WHERE ${whereClause}
      ${orderByClause}
      LIMIT ${pagination.pageSize}
      OFFSET ${pagination.pageIndex * pagination.pageSize}
    `,
    }),
  )

  // Also get a count for the total data matching the filters
  // such that pagination can be handled correctly
  const totalNumberOfOrders =
    useLiveQuery(
      db.liveRawQuery({
        sql: `
      SELECT COUNT(*) AS count FROM commerce_orders
      WHERE ${whereClause};
    `,
      }),
    ).results?.[0]?.count ?? 0
  return {
    orders,
    totalNumberOfOrders,
  }
}

Usage​

Connect the schema and headless components with your UI library of choice to get a working component.

import { useState } from 'react'
import { ColumnDef, TableView } from './components/TableView'
import { PaginationState, SortingState, useTableData } from './use_table_data'

export const TableDataViewer = ({
  columns,
  whereClause,
}: {
  columns: ColumnDef[]
  whereClause: string
}) => {
  // Keep pagination state to only load necessary data
  const [pagination, setPagination] = useState<PaginationState>({
    pageIndex: 0,
    pageSize: 5,
  })

  // Keep an order by clause generated by the sorting of columns
  const [sorting, setSorting] = useState<SortingState[]>([])

  const { orders, totalNumberOfOrders } = useTableData({
    sorting,
    pagination,
    whereClause,
  })

  return (
    <TableView
      columns={columns}
      rows={orders}
      totalNumberOfRows={totalNumberOfOrders}
      pagination={pagination}
      onPaginationChange={setPagination}
      sorting={sorting}
      onSortingChange={setSorting}
    />
  )
}