Feature Request: Engine Support for sqlite

[mydata-ag]

The title pretty much sums it up: It would be great to have sqlite as an engine.

I checked the list of supported engines and couldn't find it: https://sqlmesh.readthedocs.io/en/latest/integrations/overview/

As sqlgot already supports sqlite I believe the implementation should be similar to the duckdb engine/adapter.

I think SQLMesh really fills a gap in the market and I'm happy to contribute here with a little bit of guidance.

Best, Matt

[eakmanrq]

@mydata-ag Is there something you are wanting from sqlite that is not covered by DuckDB?

[mydata-ag]

It's less about feature parity but more about interoperability and convenience.

• We use SQLite as a generic storage format (produced by edge devices), that is consumed by customers and 3rd parties.
• SQLite has a wide range of viewer/tool support, whereas Tad still feels a little rough around the edges

I'm well aware that these might be considered edge case. I believe SQLite is such a widespread database/storage format and would open up a lot of good use cases in combination with SQLMesh.

[eakmanrq]

Thanks for the info @mydata-ag. I think the lowest effort/highest return way of achieving this will be by adding full support for DuckDB's attach syntax which would let you attach a SQLite database: https://github.com/TobikoData/sqlmesh/issues/2336

If you would like to contribute native SQLite support you could reference this PR on what it takes to add an engine adapter: https://github.com/TobikoData/sqlmesh/pull/1579/files

Join our slack if you want to discuss some details if you want to try adding it. I would recommend considering adding the full duckdb attach support as an alternative because it could achieve the result you want with much less work and benefiting more users than just SQLite users.