syntaqlite 0.6: SQLite dot commands and pyodide - Lalit Maganti

Since my original launch post for syntaqlite,<br>I&rsquo;ve been quietly working away on it in the background. A lot of the work has<br>been fixing correctness bugs which I discovered as I integrated it into<br>production as the parser for PerfettoSQL in the Perfetto trace processor: as I<br>wrote previously, this has been my dream for over 8 years so it&rsquo;s amazing to see<br>it finally realized.<br>Just today, I released<br>syntaqlite 0.6,<br>the most interesting release since the original launch, so I wanted to talk<br>about what&rsquo;s new.<br>The biggest step forward for real world applicability is that we now support<br>SQLite dot commands:<br>.print 'running foo now';<br>.read foo.sql;

.print 'running bar now';<br>.read bar.sql;

select * from baz;<br>-- ^ baz will error if it's not part of the schema!

SQLite scripts are very common in the wild and in the past we would simply error<br>on dot commands like .read and .print, causing spurious warnings and an<br>inability to format files like this properly. Now, these lines will be silently<br>ignored while still parsing, formatting and validating the SQL inside!<br>In followup releases, I plan on making syntaqlite more aware of the semantics<br>of these dot commands, especially .read: we should be able to do things like<br>verify the file exists or to use the contents of those files for populating<br>schemas etc.<br>Along with this, the release of<br>Pyodide 314.0 last week means<br>that we can now publish Python wheels to PyPI for Pyodide: I&rsquo;ve taken advantage<br>of that to immediately add support for it. So you can now go to the<br>Pyodide playground and run the<br>following:<br>import micropip<br>await micropip.install('syntaqlite')

import syntaqlite<br>sq = syntaqlite.Syntaqlite()

# Formatting<br>print(sq.format_sql('select 1', keyword_case='upper'))<br># SELECT 1;

# Analysis<br>schema = syntaqlite.Schema(<br>ddl="CREATE TABLE users (id INT, name TEXT, email TEXT, active INT)")<br>print(sq.analyze("SELECT nme FROM users WHERE active = 1", schema,<br>output=syntaqlite.AnalysisOutput.TEXT,<br>render_options=syntaqlite.RenderOptions(source_name="query.sql")))<br># warning: unknown column 'nme'<br># --> query.sql:1:8<br># |<br># 1 | SELECT nme FROM users WHERE active = 1<br># | ^~~<br># = help: did you mean 'name'?

This is another step on the journey towards a 1.0 release which I can now see<br>appearing on the horizon!

If you enjoyed this post, consider subscribing to my newsletter or following via RSS. You can also share it on Hacker News or Lobsters.

# 21:53 / #sqlite #databases