Plume is a Ruby library for working with SQLite elegantly. Presently, this means that it provides a pure Ruby implementation of SQLite's lexer/tokenizer as well as a 100% compatible parser. This is useful for introspecting the structure of SQL queries, and for building tools that work with SQL queries - like working out which tables and columns a query is going to read.
Plume needs Ruby 3.2 or later. Install the gem and add to the application's Gemfile by executing:
bundle add plumeIf bundler is not being used to manage dependencies, install the gem by executing:
gem install plumeEvery statement in SQLite's grammar parses, and the parser is checked against the SQL in SQLite's own test suite - both the SQL written in its test files and the SQL they build as they run. Of the 26,977 fixture files in that corpus, Plume parses 26,707 and rejects 266 that SQLite rejects too. The 4 left are deliberate: 3 that Plume parses but SQLite refuses for reasons other than syntax, and 1 expression nested 1,000 deep, past what Plume's stack allows.
Parse SQL with Plume.parse, which returns one node per statement:
sql = <<~SQL
CREATE TEMP TABLE IF NOT EXISTS `schema0`."tb0" (
c0 INTEGER PRIMARY KEY DESC ON CONFLICT ABORT AUTOINCREMENT,
c1 INT NOT NULL ON CONFLICT ROLLBACK,
c2 TEXT UNIQUE ON CONFLICT IGNORE,
c3 BLOB CHECK ( c3 > 0),
c4 REAL DEFAULT (1.1 * 2.2),
c5 ANY COLLATE RTRIM,
c6 DECIMAL(4, 6) GENERATED ALWAYS AS (1.1 + 2.2) STORED,
c7 REFERENCES tb1(c1) ON DELETE SET NULL ON UPDATE CASCADE MATCH FULL DEFERRABLE INITIALLY DEFERRED,
PRIMARY KEY (c0, c1 AUTOINCREMENT) ON CONFLICT ABORT,
UNIQUE (c0, c1) ON CONFLICT ROLLBACK
CHECK (c0 > 0)
FOREIGN KEY (c0) REFERENCES tb1(c1) ON DELETE CASCADE ON UPDATE RESTRICT MATCH FULL DEFERRABLE INITIALLY DEFERRED
) WITHOUT ROWID;
SQL
ast = Plume.parse(sql).first # one node per statement
# => Plume::CreateTableStatement(...)(click to see full AST)
Plume::CreateTableStatement(
temporary = true,
if_not_exists = true,
table = Plume::TableName(
schema = "schema0",
table = "tb0",
),
columns = [
Plume::ColumnDefinition(
name = "c0",
type = Plume::ColumnType(
text = "INTEGER",
affinity = :INTEGER,
),
constraints = [
Plume::PrimaryKeyColumnConstraint(...),
],
),
Plume::ColumnDefinition(
name = "c1",
type = Plume::ColumnType(
text = "INT",
affinity = :INTEGER,
),
constraints = [
Plume::NotNullColumnConstraint(...),
],
),
Plume::ColumnDefinition(
name = "c2",
type = Plume::ColumnType(
text = "TEXT",
affinity = :TEXT,
),
constraints = [
Plume::UniqueColumnConstraint(...),
],
),
Plume::ColumnDefinition(
name = "c3",
type = Plume::ColumnType(
text = "BLOB",
affinity = :BLOB,
),
constraints = [
Plume::CheckColumnConstraint(...),
],
),
Plume::ColumnDefinition(
name = "c4",
type = Plume::ColumnType(
text = "REAL",
affinity = :REAL,
),
constraints = [
Plume::DefaultColumnConstraint(...),
],
),
Plume::ColumnDefinition(
name = "c5",
type = Plume::ColumnType(
text = "ANY",
affinity = :ANY,
),
constraints = [
Plume::CollateColumnConstraint(...),
],
),
Plume::ColumnDefinition(
name = "c6",
type = Plume::ColumnType(
text = "DECIMAL(4, 6)",
affinity = :ANY,
),
constraints = [
Plume::GeneratedAsColumnConstraint(...),
],
),
Plume::ColumnDefinition(
name = "c7",
constraints = [
Plume::ForeignKeyColumnConstraint(...),
],
),
],
constraints = [
Plume::PrimaryKeyTableConstraint(
autoincrement = true,
columns = [
Plume::IndexedColumn(...),
Plume::IndexedColumn(...),
],
conflict_clause = Plume::ConflictClause(resolution = :ABORT),
),
Plume::UniqueTableConstraint(
columns = [
Plume::IndexedColumn(...),
Plume::IndexedColumn(...),
],
conflict_clause = Plume::ConflictClause(resolution = :ROLLBACK),
),
Plume::CheckTableConstraint(
expression = Plume::BinaryExpression(
operator = :ABOVE,
left = Plume::ColumnName(...),
right = Plume::LiteralExpression(...),
),
),
Plume::ForeignKeyTableConstraint(
columns = [Plume::IndexedColumn(...)],
foreign_key_clause = Plume::ForeignKeyClause(
table = "tb1",
deferred = true,
columns = [...],
actions = [...],
match_clauses = [...],
),
),
],
options = [
Plume::WithoutRowidTableOption(),
],
strict = false,
without_row_id = true,
)Turn a tree back into SQL with to_sql. It prints from the nodes' values rather than copying the source, so trees built by hand or changed after parsing print too:
stmt = Plume.parse("select a from t where b = 1").first
stmt.body.condition = Plume::BinaryExpression.new(
operator: :ALL,
left: stmt.body.condition,
right: Plume::BinaryExpression.new(operator: :EQUALS, left: Plume::ColumnName.new(column: "tenant_id"), right: Plume::Variable.new(name: "?")),
)
stmt.to_sql
# => "SELECT a FROM t WHERE (b = 1) AND (tenant_id = ?)"walk yields every node with its parent, and walk_with_slots also gives the prop the node sits in, plus its index when that prop is a list. transform builds a changed copy and leaves the original alone. Return a node from the block to keep it, or another node to put in its place:
stmt = Plume.parse("SELECT a FROM t WHERE b IN (1, 2) ORDER BY 1").first
stmt.transform { |node, parent| Plume::LiteralExpression === node && !(Plume::OrderingTerm === parent) ? Plume::Variable.new(name: "?") : node }.to_sql
# => "SELECT a FROM t WHERE b IN (?, ?) ORDER BY 1"Only the nodes on the way to a replacement are copied, and the rest is shared with the original. Neither method recurses, so trees as deep as SQLite allows are fine. This works on shareable trees as well.
Plume.split finds the statements in a script by their tokens alone, so it also works on SQL that doesn't parse. A trigger's body stays in one piece:
Plume.split("SELECT 1; CREATE TRIGGER t AFTER INSERT ON x BEGIN DELETE FROM y; END; SELEC oops")
# => ["SELECT 1", "CREATE TRIGGER t AFTER INSERT ON x BEGIN DELETE FROM y; END", "SELEC oops"]to_sql(pretty: true) lays the SQL out over lines. Each clause starts a line. A clause too long for max_line_length (80 by default) puts its items one per line, indented by indent_size (4), and a long AND or OR chain puts each operand on its own line. Subqueries are indented inside their parentheses. commas_start_of_line: true puts the commas at the start of the lines:
Plume.parse("select id, title from posts p join users u on u.id = p.user_id where u.active and p.draft = 0").first.to_sql(pretty: true)
# => SELECT id, title
# FROM posts AS p
# JOIN users AS u ON u.id = p.user_id
# WHERE u.active AND (p.draft = 0)Pretty SQL parses back into the same tree, which is checked across the whole fixture corpus.
Tokenize SQL with Plume.tokenize:
tokens = Plume.tokenize("SELECT * FROM users WHERE id = 1")
# => [:SELECT, :STAR, :FROM, :ID, :WHERE, :ID, :EQ, :INTEGER]with_values: true adds each token's text, and with_positions: true its byte range in the SQL - for highlighting, or for finding a token's place in the original:
Plume.tokenize("SELECT a FROM t", with_values: true, with_positions: true)
# => [[:SELECT, "SELECT", 0, 6], [:ID, "a", 7, 8], [:FROM, "FROM", 9, 13], [:ID, "t", 14, 15]]dump turns a tree into a plain hash that JSON can carry, and Plume.load turns that hash back into the same tree. Positions, keywords as written and the source all come through, so the loaded tree prints, locates and compares as the original did. That makes it possible to cache parsed trees, or to hand them to another process:
json = JSON.generate(Plume.parse("SELECT DISTINCT a FROM t").first.dump)
Plume.load(JSON.parse(json)).to_sql
# => "SELECT DISTINCT a FROM t"Neither dump nor load recurses, but JSON's own generator and parser do, so a very deep tree needs max_nesting: false and enough stack.
The lexer knows 183 token types: 153 keywords, and 30 more for punctuation, operators, literals and names. Words like rowid, strict and stored, which SQLite reads as plain names and checks by their text, are keywords here that fall back to names wherever SQLite would take a name.
The lexer knows 184 token types: 153 keywords, and 31 more for punctuation, operators, literals, names and comments. Words like rowid, strict and stored, which SQLite reads as plain names and checks by their text, are keywords here that fall back to names wherever SQLite would take a name.
Comments are tokens of their own (:COMMENT), which the parser skips the same way SQLite does. Plume.comments lists them with their byte ranges. tags reads sqlcommenter comments, including the ones Rails' query_log_tags adds, in its current format and the older key:value one:
sql = "SELECT * FROM posts /*controller='posts',action='index'*/"
Plume.comments(sql).first.tags # => {"controller" => "posts", "action" => "index"}to_sql(comments: true) keeps the comments directly before and after a statement, such as a header line or the sqlcommenter tags. Comments inside the statement are left out. A trailing -- comment is printed as /* */, so a semicolon after it doesn't become part of the comment:
Plume.parse("-- report
select a from t /*app='shop'*/").first.to_sql(comments: true)
# => "-- report
SELECT a FROM t /*app='shop'*/"Plume.parameters numbers a statement's bind parameters the way SQLite does - ? takes the next number, ?N takes N, and a name keeps the number it first got - so you know what to bind where before preparing anything:
parameters = Plume.parameters("SELECT * FROM posts WHERE user_id = :user AND (title = ? OR body = ?) LIMIT :user")
parameters.count # => 3
parameters.names # => [":user", nil, nil]
parameters.index(":user") # => 1
parameters.map(&:number) # => [1, 2, 3, 1]count, names and index answer what sqlite3_bind_parameter_count, _name and _index would. Each parameter also carries its node, and with it the location it was written at.
Plume.truncate(sql, max_length) shortens SQL to fit a log line or an error tracker. It replaces the parts you need least to recognise the query with ...: result columns, WHERE conditions, VALUES rows, CTE bodies, INSERT column lists and UPDATE assignments. The most deeply nested parts go first, and the longest first among those. Only if that is still not short enough is the end cut off:
sql = "WITH recent AS (SELECT id, title FROM posts WHERE created_at > ?) SELECT r.id, r.title, u.name FROM recent r JOIN users u ON u.id = r.user_id WHERE u.active"
Plume.truncate(sql, 100)
# => "WITH recent AS (...) SELECT ... FROM recent r JOIN users u ON u.id = r.user_id WHERE u.active"Plume.normalize replaces a query's constants with ? and leaves everything else as written, so the same query with different values comes out the same. That lets you group a log the way pg_stat_statements does, or keep values out of it:
Plume.normalize("SELECT * FROM posts /* app */ WHERE user_id = 42 AND title LIKE 'Re: %' ORDER BY 1 LIMIT 10")
# => "SELECT * FROM posts /* app */ WHERE user_id = ? AND title LIKE ? ORDER BY 1 LIMIT ?"Numbers (including their sign), strings and blobs are replaced. NULL, TRUE, FALSE and CURRENT_* are left alone, and so is an integer on its own in ORDER BY or GROUP BY, where SQLite reads it as a column position. Only SELECT, INSERT, UPDATE and DELETE are normalized. Pass a parsed statement to get a tree back with Variables in place of the constants. The tests bind the constants back into the normalized SQL and check that SQLite returns the same rows.
Plume.fingerprint goes one step further and gives the same short hash for every spelling of a query: whatever its values, parameter names, spacing, keyword and name case, or parentheses, and however many constants an IN list or VALUES holds. It is stable across processes and Ruby versions, so it can be stored:
Plume.fingerprint("SELECT a FROM t WHERE b IN (1, 2, 3)") # => "07b540b0c5ed57e1"
Plume.fingerprint("select A from T where b in (?)") # => "07b540b0c5ed57e1"Plume.reads works out which tables and columns a statement would read, looking through aliases, joins, subqueries, CTEs and views:
catalog = Plume::Catalog.from_sql(<<~SQL)
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INT, title TEXT);
SQL
reads = Plume.reads(<<~SQL, catalog: catalog)
WITH recent AS (SELECT * FROM posts ORDER BY id DESC LIMIT 10)
SELECT r.title, u.name FROM recent r JOIN users u ON u.id = r.user_id
WHERE u.email LIKE '%@example.com'
SQL
reads.tables # => #<Set: {"posts", "users"}>
reads.columns # => {"posts" => #<Set: {"id", "user_id", "title"}>, "users" => #<Set: {"id", "name", "email"}>}
reads.projected # columns whose values can end up in the result rows
reads.referenced # columns only used to filter, join, sort or group
reads.calls # => #<Set: {"like"}>reads.calls lists the functions the statement calls, including the ones inside the views it reads. It also covers what LIKE, GLOB, REGEXP, MATCH, ->, ->> and CURRENT_* call, because SQLite's authorizer counts those as calls too. Table-valued functions in FROM, like json_each, are listed separately in reads.functions.
reads.ctes lists the names the statement's WITH clauses define, and reads.aliases maps each table alias to what it stands for, spelled as written: {"r" => "recent", "u" => "users"} above.
Plume.reads also takes what you already parsed - a statement node, or the array Plume.parse returns - so the SQL is not parsed twice, and a tree you changed after parsing is analyzed as changed. So do Plume.writes and Plume.access below.
The catalog can also come from a live database with Plume::Catalog.from_sqlite(db), or be left out - then SELECT * is reported as a read of the column "*" and unqualified columns only resolve when there is a single table in scope.
Anything that cannot be attributed (an unknown table or column, an ambiguous name) raises Plume::Analysis::Error with a pointer into the SQL. Pass strict: false to get the partial result and a list in reads.unresolved instead. Each entry of reads.references carries the AST node it came from, so you can point at it too - see examples/read_guard.rb for a small column-level access check built on top.
The results are tested against SQLite's own authorizer callback, which is also the place to enforce access rules at runtime when you have a connection. Plume.reads is for when you want to vet SQL ahead of time, without a database, or with good error messages. It reports one thing the authorizer does not: the right-hand join columns of USING and NATURAL joins.
Plume.writes works out which tables and columns an INSERT, UPDATE or DELETE would change. It takes the same catalog: and strict: as Plume.reads:
writes = Plume.writes(<<~SQL, catalog: catalog)
INSERT INTO posts (user_id, title) VALUES (?, ?)
ON CONFLICT (id) DO UPDATE SET title = excluded.title
SQL
writes.tables # => #<Set: {"posts"}>
writes.columns # => {"posts" => #<Set: {"user_id", "title"}>}
writes.inserted # => {"posts" => #<Set: {"user_id", "title"}>}
writes.updated # => {"posts" => #<Set: {"title"}>}, from the DO UPDATEA DELETE removes whole rows, so it names a table and no columns:
writes = Plume.writes("DELETE FROM posts WHERE user_id = ?", catalog: catalog)
writes.deleted # => #<Set: {"posts"}>
writes.tables # => #<Set: {"posts"}>
writes.columns # => {}Around the edges:
- An
INSERTwithout a column list writes every column of the table except generated ones, or"*"without a catalog.DEFAULT VALUESwrites the table but names no columns. - Setting
rowid,oidor_rowid_writes"ROWID", even when anINTEGER PRIMARY KEYstands in for it - unless the table has a real column by that name. RETURNINGand CTEs only read. A CTE never stands in for the table being written, even when it has the same name.- Writing to a view reports the view. The triggers a statement fires are not looked into, so what an
INSTEAD OFtrigger or any other trigger writes is not reported, and neither are the rows aREPLACEdeletes to make room. Pass theCREATE TRIGGERitself to find out what a trigger does - see below. - A
SELECTwrites nothing. - Tables outside
mainare keyed with their schema, as in"aux.users".
With strict: false, a column the catalog does not have is left out and listed in writes.unresolved:
writes = Plume.writes("UPDATE posts SET title = ?, body = ? WHERE id = ?", catalog: catalog, strict: false)
writes.updated # => {"posts" => #<Set: {"title"}>}
writes.unresolved.map(&:name) # => ["body"]Plume.access gives you both the reads and the writes, from one pass over the statement:
access = Plume.access(<<~SQL, catalog: catalog)
UPDATE posts SET title = upper(u.name) FROM users u WHERE u.id = posts.user_id RETURNING posts.id
SQL
access.writes.updated # => {"posts" => #<Set: {"title"}>}
access.reads.projected # => {"users" => #<Set: {"name"}>, "posts" => #<Set: {"id"}>}
access.reads.referenced # => {"users" => #<Set: {"id"}>, "posts" => #<Set: {"user_id"}>}A value copied into a column being written counts as projected, just like one returned in the result rows.
Writes are checked against the authorizer as well, and must match it exactly - except for the columns of an INSERT, which the authorizer does not name.
Plume.access, reads and writes also take a CREATE TRIGGER. They report what its WHEN clause and body read, write and call each time the trigger fires. NEW and OLD stand for the row of the table the trigger is on, so reading new.total reads that table, as SQLite's authorizer reports it:
access = Plume.access(<<~SQL, catalog: catalog)
CREATE TRIGGER audit AFTER UPDATE ON posts WHEN new.title <> old.title BEGIN
UPDATE users SET name = upper(name) WHERE id = new.user_id;
END
SQL
access.reads.columns # => {"posts" => #<Set: {"title", "user_id"}>, "users" => #<Set: {"name", "id"}>}
access.writes.updated # => {"users" => #<Set: {"name"}>}Plume.access takes every other kind of statement too, and reports in actions what it does besides reading and writing rows. The names follow SQLite's authorizer action codes, and the actions are checked against it:
catalog = Plume::Catalog.from_sql(<<~SQL)
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT UNIQUE);
CREATE TRIGGER touch AFTER UPDATE ON posts BEGIN SELECT 1; END;
SQL
Plume.access("DROP TABLE posts", catalog:).actions.map { |a| [a.action, a.name, a.detail] }
# => [[:drop_table, "posts", nil], [:drop_trigger, "touch", "posts"]]
Plume.access("CREATE TEMP TABLE drafts (slug PRIMARY KEY)").actions.map { |a| [a.action, a.name] }
# => [[:create_temp_table, "drafts"], [:create_temp_index, "sqlite_autoindex_drafts_1"]]Each action has an action (:create_table, :drop_temp_index, :alter_table, :pragma, :transaction, :savepoint, :attach, :reindex and the rest), a name, a detail (the table an index or trigger is on, a pragma's value, the column ALTER TABLE drops) and a schema.
As with SQLite, a CREATE TABLE … AS reads what its SELECT reads, a CREATE INDEX reads the columns it indexes, and DROP TABLE deletes the table's rows. A CREATE VIEW reads nothing, because SQLite only checks a view's body when the view is used. EXPLAIN reports what the statement it explains would do. The catalog also keeps track of indexes and triggers, which is how a DROP INDEX knows its table.
reads, writes and access each take exactly one statement. Plume.summary takes any number of statements, such as a migration or a batch from a log, and merges what they touch:
summary = Plume.summary(<<~SQL)
CREATE TABLE archive (id INTEGER PRIMARY KEY, title TEXT);
INSERT INTO archive SELECT id, upper(title) FROM posts WHERE id < 100;
DELETE FROM posts WHERE id < 100;
SQL
summary.statement_types # => ["CreateTableStatement", "InsertStatement", "DeleteStatement"]
summary.tables # => #<Set: {"posts", "archive"}>
summary.tables(:delete) # => #<Set: {"posts"}>
summary.calls # => #<Set: {"upper"}>
summary.actions.map(&:action) # => [:create_table]
summary.accesses # => one Plume::Analysis::Access per statementIt takes the same catalog: and strict: arguments. Each statement sees the schema as the statements before it left it, so a script that creates a table and then fills it checks out against a catalog that has never heard of the table. The changes go to a copy of the catalog, and the one you pass in stays as it was. Catalog#apply(statement) makes the same changes to a catalog of your own.
Plume loads its parts lazily, and Ractors other than the main one cannot read the class-level state that leaves behind. Call Plume.eager_load! once from the main Ractor before starting any, and freeze the catalog with Ractor.make_shareable so it can be passed in:
Plume.eager_load!
catalog = Ractor.make_shareable(Plume::Catalog.from_sql(schema_sql))
ractors = queries.each_slice(100).map do |batch|
Ractor.new(batch, catalog) do |sqls, cat|
sqls.map { |sql| Plume.reads(sql, catalog: cat, strict: false).columns }
end
end
ractors.flat_map(&:value) # `take` before Ruby 4.0tokenize, parse, to_sql, walk, reads, writes and access all work inside a Ractor. Trees and results come back out like any other object, and a tree passed through Ractor.make_shareable can still be read, printed, walked and analyzed - it just can no longer be edited. After eager_load! the node classes cannot be given new props.
-
expression -
alter_table_stmt -
analyze_stmt -
attach_stmt -
begin_stmt -
commit_stmt -
create_table_stmt -
create_index_stmt -
create_trigger_stmt -
create_view_stmt -
create_virtual_table_stmt -
delete_stmt -
detach_stmt -
drop_index_stmt -
drop_table_stmt -
drop_trigger_stmt -
drop_view_stmt -
insert_stmt -
pragma_stmt -
reindex_stmt -
release_stmt -
rollback_stmt -
savepoint_stmt -
select_stmt -
update_stmt -
vacuum_stmt -
with_stmt
bin/benchmark runs every fixture that parses (26,707 files, 5.5 MB of SQL). On an Apple M5 Pro with Ruby 4.0:
| Step | Fixtures/s | MB/s | MB/s with YJIT |
|---|---|---|---|
Plume.tokenize |
~30,000 | 6.4 | 18.5 |
Plume.parse |
~2,900 | 0.62 | 0.66 |
Node#to_sql |
~12,000 | 2.5 | 2.3 |
Plume.reads (no catalog, SELECTs only) |
~2,200 | 0.38 | 0.46 |
The corpus leans on very large generated expressions; a typical application query parses in well under a millisecond. About half of the parse time goes to Literal's runtime type checks on every node property.
After checking out the repo, run bin/setup to install dependencies. Then, run rake test to run the tests. bin/fixture-report shows how much of SQLite's own test SQL parses (pass --write to record it as the new baseline the tests hold us to), and bin/benchmark times the whole pipeline. bin/trace-fixtures runs SQLite's Tcl test suite and adds a fixture for each shape of statement it finds that the corpus lacks, and for each one where Plume and SQLite disagree. You can also run bin/console for an interactive prompt that will allow you to experiment.
To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and the created tag, and push the .gem file to rubygems.org.
Bug reports and pull requests are welcome on GitHub at https://github.com/yippee-fun/plume. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the code of conduct.
The gem is available as open source under the terms of the MIT License.
Everyone interacting in the Plume project's codebases, issue trackers, chat rooms and mailing lists is expected to follow the code of conduct.