Developing a SQLite backend for Mapless
Published: 2023-10-10 14:20:35


![Mapless unit tests coverage for the SQLite backend](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6y5c7zykh44xtdnagne8.png)
Earlier this month I was asking in the community for interest on moving forward the development of a SQLite backend for [Mapless](https://github.com/sebastianconcept/Mapless) and it was generally well received. People understands that is good to have another persistence option in Smalltalk specially if it's based on a backend that is both mature and well accepted, not just among smalltalkers but in the whole industry.

[SQLite](https://www.sqlite.org/) alone will be a good fit specially with new small projects, MVPs or [micro-SaaS](https://www.microns.io/blog/best-micro-saas-ideas) and used behind Mapless, the SQLite features will be reachable with its comfortable API and its schemaless experience in the smalltalk side.

Two attractive reasons for adoption.

And with Mapless, there is a plus: state portability. A secondary benefit of having a `MaplessSQLiteRepository` is that your data can easily be migrated from one backend to another and since running SQLite is cheap, this has important economic consequences. It could make feasible something that previously wasn't. 

Think of the margins for a project that can start with a persistence solution that doesn't require the costs of setup, DevOps and operations of an additional database server. Instead, it will be the same server you're using for the application as SQLite only needs a library in the system and lean usage of CPU and RAM. 

Once your product, either a MVP, micro-SaaS or full SaaS has the network traffic that requires an upgrade, with Mapless you'll be enabled to easily develop migrations to `MaplessPostgreSQLRepository` or `MaplessMongoDBRepository` with almost no impact on your application. All that while the economics of that migration and setup are covered by the growth of your business.

So, for using SQLite with Mapless, the good news is that I'm almost having all its unit tests green. 

Currently two unit tests that are failing. These were designed when developing the [PostgreSQL](https://www.postgresql.org/) support and using its feature of querying inside the  JSON column type. 

Speaking of which, I'm still thinking what's the best approach for that since SQLite has 2 ways to use JSON:

1. JSON1 extension and 
2. using the BLOB type

If you have an educated opinion on what's the best path for this, reach me by email, the [Pharo Discord server](https://discord.com/invite/QewZMZa) or [LinkedIn](https://www.linkedin.com/in/sebastiansastre/) I'd love to hear it.

### Bonus benchmarks
Here are some benchmarks it on my 2,5 GHz Quad-Core Intel Core i7:
```
Benchmarking Mapless on SQLite...

Saved 1000 instances of MaplessDummyPerson in: 166 ms (~6008 saves per second)
Read 1000 instances of MaplessDummyPerson in: 159 ms (~6279 reads per second)
Saved 10000 instances of MaplessDummyPerson in: 1755 ms (~5699 saves per second)
Read 10000 instances of MaplessDummyPerson in: 1590 ms (~6290 reads per second)
Saved 1000 instances of MaplessDummyPerson and MaplessDummyUser in: 367 ms (~2722 saves per second)
Read 1000 instances of MaplessDummyPerson and MaplessDummyUser in: 188 ms (~5311 reads per second)
Saved 10000 instances of MaplessDummyPerson and MaplessDummyUser in: 3721 ms (~2687 saves per second)
Read 10000 instances of MaplessDummyPerson and MaplessDummyUser in: 1818 ms (~5500 reads per second)
```