Writing a Python SQL engine from scratch

> The main reason why I ended up building a SQL engine was...just for entertainment. It's been fun learning about all the things

I also wrote an SQL parser [1] for SQLite schema. This was mainly for fun, but also to support the specificities of SQLite schema. I was not happy with the result of other parsers (including sqlglot).

[1] https://github.com/coast-team/sqlschm

This is very interesting work.

I've been debating writing a SQLite-compatible parser/generator for purposes of manipulating arbitrary queries in AST form.

The biggest reason would be to quickly enumerate statistics about any given query. This would allow for us to run reports about which tables are used from certain areas, how often related tables are joined, etc. This would make refactor decisions substantially easier, since most of our business logic is defined as SQL queries now. Any given install of our product could have well over 10k SQL queries to deal with.

Adding the generator bit would also give us an ability to automatically rewrite queries as needed. This would predominantly be used to standardize the text formatting of any given query, but would also be extremely useful for things like renaming tables or columns.

We (bit.io) use SQLGlot and love it! We use it in our open-source sqlite->postgres tool, pgsqlite:https://github.com/bitdotioinc/pgsqlite, and in our general SQL parser where we can translate between dialects (https://docs.bit.io/docs/query-translation):

  #!translate:sqlite
  select \* from [foo]

That will translate the given sql (which is in SQLite syntax) into PostgreSQL SQL and then run that query.

Also, Toby & team may be the most responsive open-source maintainers I've ever seen. Open issues are often closed within a day. It's awesome.

OK I'll admit: https://news.ycombinator.com/item?id=34189422 is not a real pure Python SQL engine, this one is.

I love that you linked to all the places in code for each section. Helps folks jump into the code.

Good work.

Here are my use cases of a SQL parser at application level.

    - enumerate, add, delete conditions from WHERE clause.
    - change ORDER BY.
    - paging, LIMIT.
    - turn an aggregate query into crosstab query.

Very cool! A while ago I wrote a B+ tree in Python[0] to learn how databases work under the hood. I left it there but it seems that this project could allow me to quickly add full SQL support for it.

[0] https://github.com/NicolasLM/bplustree

Sqlglot is fantastic, and a super readable codebase to learn from! Thanks @captaintobs.

With the comparisons to calcite, I was curious if you've considered implementing sqlglot in a native language too? Something compiling down to a small wasm extension would make it accessible to web apps.

sqlglot is awesome! Thanks for the contribution Toby!

Nice there is something like this, pitty its in python.