[Feature] Need robust in memory solution for lite weight unit testing

[DustinMoriarty]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

dbt-sqlite is in need of a maintainer (codeforkjeff/dbt-sqlite#48). While most production use cases would not use sqlite, having an in memory database solution is crucial for any form of local unit testing and development using dbt, especially for software that extends dbt-core itself.

Usually, when unit testing code involving DBT, the actual SQL and SQL backend is trivial. Sometimes interfaces can be mocked entirely. However, a lot of times there are local integration tests which test multiple components together yet are not intended to really test the actual SQL very much and are instead intended to make sure that DBT commands like run, compile, list, etc. work with the software. I suggest that DBT Labs really should step in to assist with the stability of the dbt-sqlite project.

In addition, for docs and demos on DBT functionality, an in memory solution that can be installed with python requirements only is much more portable and easy for people to implement. Demo setup can be a few lines of pip install.

Describe alternatives you've considered

We used to use dbt-sqlite for local testing of our systems pertaining to DBT itself. However, we now have to use more complex solutions with an actual database because dbt-sqlite is out of date. This means managing credentials and allowing IO in places where we usually avoid this type of thing. In some of these cases we don't actually allow traffic to or from the public internet at all because those systems are made for building code and we block all public internet traffic in our build system due to supply chain security concerns. No one wants to have to install PostgreSQL in a transient container just to run unit tests on code that has very little to do with an actual database.

Who will this benefit?

This will benifet dbt-labs and it's customers because it will be easier to locally demo, develop and test DBT commands.

Are you interested in contributing this feature?

yes

Anything else?

I think that the community can help to maintain dbt-sqlite. However, DBT Labs should step in to lead maintenance so that we can count on a stable project moving forward.

[dbeatty10]

Thanks for reaching out @DustinMoriarty.

Is your ask here that dbt Labs takes over maintainership of the dbt-sqlite adapter?

[DustinMoriarty]

Thanks for reaching out @DustinMoriarty.

Is your ask here that dbt Labs takes over maintainership of the dbt-sqlite adapter?

@dbeatty10 : Yes that would be the most likely solution that I can see if the current maintainer is interested, which sounds pretty likely.

[Fleid]

@DustinMoriarty have you considered using DuckDB the same way you are using SQLite now?

[dbeatty10]

After discussing further with @Fleid and @amychen1776, we don't have the capacity to take on additional adapters at this time, so I'm going to close this as not planned.

It looks like there is some traction in codeforkjeff/dbt-sqlite#48 for folks that are interested in maintaining dbt-sqlite.

An alternative for users looking for a dbt adapter that supports in-memory usage can take a look at dbt-duckdb which is actively maintained.