There has been multiple efforts to get SQLite working on the IC to provide an “embedded” SQL compliant database engine in a canister dapp.
A forums search for sqlite will return a whole history of discussion around the idea, the need for it and actual implementations. The best starting point is reading this 2021 post
Important work has since been done by @sgaflv with his stable-fs implementation of a high IO throughput in-canister virtual filesystem backed by stable memory. Performance testing of an SALite db file on stable-fs is shown at the end of the head post here
More recently @lastmjs has posted about Demergent Lab’s plans to support SQLite and possibly PGlite (a wasm complied branch of PostgreSQL that runs in-browser) in an upcoming release of their Azle CDK when wasm module support it added to it.
PGlite would be the ideal in-canister database engine IMHO although the TCP connection protocol support would need to be replaced with an ICP based transport for inter-canister query comms (or do without it altogether). The maturity, extensible datatype support and ease of implementing extension to the pg engine is a compelling package. My team have used PostgreSQL in production web2 application operations since 2003 and I have never once regretted that decision. It is robust, flexible and incredibly well supported by its open source community.
I know in theory we “dont need traditional databases” on the IC, a position usually argued based on having persistent canister state for code+data and orthogonal memory support. Also SQL databases are sometimes seen as a “legacy technology”. But relational databases remain the dominant technology for transactional enterprise data management in the commercial world. Likewise the relational data model and relational calculus remains a crucial abstraction over the data object references we use to implement data structures in most modern programming languages. The problems with search and updating ever larger data structures using tree and graph models is what inspired E.F. Codd to design and propose the relational model of data management back in 1970. There is a very good article covering this history and the context of why this development was so important here Important Papers: Codd and the Relational Model