Skip to main content

Request-Response

In modern web applications, efficiently handling request-response cycles while ensuring data integrity and responsiveness is paramount. Popular approaches to state transfer like REST and GraphQL can lead to complexities in managing concurrent requests and maintaining consistency across distributed systems, and they incur additional complexities when dealing with offline usage and intermittent connectivity.

While local-first development tends to reduce the need for external connectivity, there will be some sensitive or complex workloads such as payment processing that need to be handled by a secure server. Furthermore, redesigning a large application to be local-first can be a daunting task, and being able to gradually transition is often a safer path.

With ElectricSQL, state transfer is abstracted away by syncing the application's local database, and request-response cycles can be implemented using an event-sourcing pattern. Requests can become entries in a table that get picked up by the server upon syncing using database triggers, and responses can be written to a related table that can be queried by the client. No complex retrying logic necessary, no additional code for queueing requests made while offline - clients submit requests locally, and because of Electric's finality of local writes you can rest assured that the request will eventually be processed.

This recipe demonstrates how to build a declarative, reactive query tool (inspired by TanStack Query), as well as a live request audit log.

Schema​

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

-- Create a requests table.
CREATE TABLE IF NOT EXISTS requests (
  id UUID PRIMARY KEY NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  path TEXT NOT NULL,
  method TEXT NOT NULL,
  data JSONB,
  processing BOOLEAN NOT NULL,
  cancelled BOOLEAN NOT NULL
);

-- Create a responses table referencing requests.
CREATE TABLE IF NOT EXISTS responses (
  id UUID PRIMARY KEY NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  request_id UUID NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
  status_code INTEGER NOT NULL,
  data JSONB
);

-- ⚡ Electrify the requests and responses tables
ALTER TABLE requests ENABLE ELECTRIC;
ALTER TABLE responses ENABLE ELECTRIC;


/* Set up the triggers that will notify the appropriate APIs and perform
 * the work required to process incoming requests on the requests table
 * and generate responses to be added to the responses table, which will
 * in turn sync with the client and emulate the request/response pattern
 */

-- When request is received, set it as processing notify API to process it
CREATE OR REPLACE FUNCTION process_request()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.processing = false AND NEW.cancelled = false THEN
    UPDATE public.requests SET "processing" = 'true' WHERE "id" = NEW.id;
    PERFORM pg_notify('api_trigger', row_to_json(NEW)::TEXT);
  END IF;
  RETURN NULL;
END
$$ LANGUAGE plpgsql;

-- When response is received, mark relevant request as processed
CREATE OR REPLACE FUNCTION finish_processing_request()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE public.requests SET "processing" = 'false' WHERE "id" = NEW.request_id;
  RETURN NULL;
END
$$ LANGUAGE plpgsql;

-- Create a trigger to execute the function on INSERT into "requests" table
CREATE TRIGGER "process_request_trigger"
AFTER INSERT ON requests
FOR EACH ROW
EXECUTE FUNCTION process_request();

-- Create a trigger to execute the function on INSERT into "responses" table
CREATE TRIGGER "finish_processing_request_trigger"
AFTER INSERT ON responses
FOR EACH ROW
EXECUTE FUNCTION finish_processing_request();

-- Enable the triggers on the tables
ALTER TABLE requests ENABLE ALWAYS TRIGGER process_request_trigger;
ALTER TABLE responses ENABLE ALWAYS TRIGGER finish_processing_request_trigger;

Data Access​

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

import { useEffect, useState } from 'react'
import { useElectric } from '../electric/ElectricWrapper'
import { useLiveQuery } from 'electric-sql/react'
import { genUUID } from 'electric-sql/util'
import { JsonValueType, Requests, Responses } from '../generated/client'

export type HttpMethod = 'GET' | 'POST' | 'PUT' | 'DELETE' | 'PATCH'

export const useElectricQuery = ({
  path,
  method = 'GET',
  payload,
}: {
  path: string
  method?: HttpMethod
  payload?: JsonValueType
}) => {
  const { db } = useElectric()!

  // Keep track of the active request ID to match to response
  const [requestId, setRequestId] = useState('')

  // Arbitrary counter to allow re-submitting the same request
  const [refreshCounter, setRefreshCounter] = useState(0)

  useEffect(() => {
    const newRequestId = genUUID()
    setRequestId(newRequestId)
    db.requests.create({
      data: {
        id: newRequestId,
        timestamp: new Date(),
        path: path,
        method: method,
        data: payload,
        processing: false,
        cancelled: false,
      },
    })
  }, [db.requests, path, method, payload, refreshCounter])

  // Reactive query for the active request/response pair
  const request = useLiveQuery(
    db.requests.liveUnique({
      include: { responses: true },
      where: { id: requestId },
    }),
  ).results as undefined | (Requests & { responses: Responses[] })

  const response = request?.responses?.[0]

  return {
    data: response && response.status_code < 400 ? response.data : undefined,
    error: response && response.status_code >= 400 ? response.data : undefined,
    lastUpdatedAt: response?.timestamp,
    isPending: !response && request?.processing == false,
    isFetching: request?.processing == true,
    refresh: () => setRefreshCounter((c) => (c + 1) % 2),
  }
}

Usage​

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

import { useState } from 'react'
import { RequestFormView } from './components/RequestFormView'
import { RequestResultView } from './components/RequestResultView'
import { useElectricQuery, HttpMethod } from './use_electric_query'

const paths = ['/health', '/user/activities', '/payments', '/contacts/new']

export const RequestForm = () => {
  const [requestParams, setRequestParams] = useState<{
    path: string
    method?: HttpMethod
    payload?: string | null
  }>({ path: paths[0] })

  const { data, error, isFetching, isPending, refresh } = useElectricQuery(requestParams)

  return (
    <div>
      <RequestFormView
        paths={paths}
        onSend={(method, path, payload) => {
          setRequestParams({ method, path, payload })
          // refreshing here to allow re-submissions - otherwise
          // request will only be executed once per unique
          // method-path-payload combination
          refresh()
        }}
      />
      <RequestResultView data={data} error={error} isPending={isPending} isFetching={isFetching} />
    </div>
  )
}