SQLite-only Rate Limiting

Application-layer rate limiting is a good way to enforce client resource limits, while at the same time allowing you to place the rate limiting configuration and current statistics next to the application data.

This approach should not be employed for defending against application layer or network layer (D)DoS attacks; rather, such defense should be pushed towards the edge, utilizing Web Application Firewalls or analogous policy agents that can drop suspicious traffic with minimal overhead.

In contrast, this approach is more suitable for enforcing fair use limits or limits that are part of a license, which restricts the number of operations an actor can perform.

SQLite is used to:

• store the limits configuration, each with a maximum count per configurable time period
• store instances of active counters for actors (we’ll use “visitors” for this example)
• manage expiration

Interface

The goal is that with a single query executed for every operation, rate limiting calculations are performed automatically:

INSERT INTO visitor_limits(visitor_id, limit_id)
VALUES (1, 1)
ON CONFLICT DO UPDATE SET count = count + 1
RETURNING denied, resets_at;

• We inform the database that visitor 1 is attempting to perform an operation limited by limit 1.
• If the limit is not yet reached, the current counter is incremented
• If the limit is reached, denied will be 1 (SQLite has no booleans), and resets_at is defined as the next UTC unix epoch timestamp after which the limit resets. resets_at is not defined when the request isn’t denied

This allows the counter to be incremented by other positive integer values, for example, if the “cost” between operations varies, which is useful for consumption-based enforcement.

Database Setup

Limits

The schema for this is quite simple. First, we define a static configuration table for the limits:

CREATE TABLE IF NOT EXISTS limits (
  id INTEGER PRIMARY KEY ASC
  ,name TEXT UNIQUE NOT NULL
  ,maximum INTEGER NOT NULL CHECK ( maximum > 0 )
  ,period_minutes INTEGER NOT NULL CHECK ( period_minutes >= 1 )

  ,UNIQUE (name, maximum)
) STRICT;

• name uniquely identifies the limit, this should be the limited operation, e.g. send_message
• maximum is the maximum number of allowed operations within the period. This is inclusive, so a maximum of 5 allows 5 requests in a period and denies the 6th
• period_minutes defines the time between counter resets.

The period can alternatively be expressed in seconds, which is the highest usable time resolution in SQLite.

To add a new limit:

-- allow 5 messages per 2 minutes
INSERT INTO limits(name, maximum, period_minutes)
VALUES ('send_message', 5, 2);

Counters

Next, create a table for the counter instances. These will be lazily populated - if a visitor never executes a given operation, there’s no need to create a row for it:

CREATE TABLE IF NOT EXISTS visitor_limits (
  id INTEGER PRIMARY KEY ASC
  ,visitor_id INTEGER NOT NULL REFERENCES visitors(id)
  ,limit_id INTEGER NOT NULL REFERENCES limits(id)

  ,denied INTEGER NOT NULL DEFAULT 0 CHECK ( denied IN (0, 1) )

  -- set by trigger
  ,count INTEGER CHECK ( count >= 0 )
  ,resets_at INTEGER CHECK ( resets_at > 1680373723 )

  ,UNIQUE (visitor_id, limit_id)
) STRICT;

Note: this references another table visitors, this may not be the case in your application. Use whichever actor identifier fits your model best.

A sanity check is added to resets_at to protect against logic bugs, using a reasonably recent timestamp and making it clear in the DDL that this column will be a unix epoch in seconds.

Triggers

To implement the add and reset logic, two triggers are required:

CREATE TRIGGER IF NOT EXISTS init_visitor_limit_counter
AFTER INSERT
ON visitor_limits
WHEN (NEW.count IS NULL OR NEW.resets_at IS NULL)
BEGIN
  UPDATE visitor_limits
    SET
      count = 1
      ,resets_at = (unixepoch() + (60 * (SELECT period_minutes FROM limits l WHERE l.id = NEW.limit_id)))
      ,denied = 0
  WHERE id = NEW.id;
END;

This runs after the INSERT statement in our final interface query. If there wasn’t a counter for this operation and visitor, we initialize the counter, flag, and first reset timestamp.

For any INSERTs after this, we need to maintain the flag and reset the counter if necessary using a second trigger:

CREATE TRIGGER IF NOT EXISTS update_visitor_limit_counter_breaker
BEFORE INSERT
ON visitor_limits
BEGIN
  -- trip breaker for non-expired
  UPDATE visitor_limits
    SET denied = visitor_limits.count >= (SELECT maximum FROM limits l WHERE l.id = NEW.limit_id)
  WHERE visitor_limits.visitor_id = NEW.visitor_id
    AND visitor_limits.limit_id = NEW.limit_id;

  -- or reset if expired
  UPDATE visitor_limits
    SET
      count = 0
      ,resets_at = unixepoch() + (60 * (SELECT period_minutes FROM limits l WHERE l.id = NEW.limit_id))
      ,denied = 0
  WHERE visitor_limits.visitor_id = NEW.visitor_id
    AND visitor_limits.limit_id = NEW.limit_id
    AND unixepoch() > visitor_limits.resets_at;
END;

This runs before the INSERT to make sure the denied flag that is returned is up to date. In our example, the 6th message should be denied, not the 7th.

Testing

That’s it. We can test this now by running the same interface query repeatedly:

INSERT INTO visitor_limits(visitor_id, limit_id)
VALUES (1, 1)
ON CONFLICT DO UPDATE SET count = count + 1
RETURNING denied, resets_at;

-- send 6 messages in < 2 minutes:

-- Run 1 returns: 0, NULL
-- Run 2, 3, 4, 5 return: 0, 1680441289
-- Run 6 returns: 1, 1680441289

-- ... wait for 2 minutes ...

-- Run 7 returns: 0, 1680441422

As the limit is reached, the counter will continue to increment; which can be leveraged to gain insights into the behavior of limited users. A minor overage is to be expected, but a drastic surge in requests beyond the set limit could signify a bug or the presence of a malicious bot.