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
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
1is attempting to perform an operation limited by limit
- If the limit is not yet reached, the current counter is incremented
- If the limit is reached,
1(SQLite has no booleans), and
resets_atis defined as the next UTC unix epoch timestamp after which the limit resets.
resets_atis 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.
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;
nameuniquely identifies the limit, this should be the limited operation, e.g.
maximumis 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_minutesdefines 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);
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.
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.
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.
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.