I still prefer SQLite for little things you know.

I still can't express enough how useful SQLite is, when i need a db for small
and experiments stuff, but for a while, i have taken it beyond that subconsciously.

It’s now a core part of my toolkit for tasks where simplicity and minimal overhead are crucial. What began as a preference for smaller, experimental projects has matured into a deliberate choice in production scenarios. This shift comes in the the rise of managed services that address its traditional limitations.

I have come across a couple of
SQLite managed services
in a while now and i believe it's eventually going to get better.

This has opened up new possibilities for developers who appreciate the simplicity and lightweight nature of SQLite but need more robust performance for production environments. With the growing ecosystem around SQLite, from serverless platforms like Cloudflare D1 to promising newcomers like Turso’s libSQL, there’s a clear trend towards making SQLite more scalable and resilient, often these services deploy SQLite in edge environments, providing low-latency access and reducing the distance between users and data.

In production, i am usually soaked with PostgreSQL and MongoDB depending on
requirements and deadline, but these days using SQLite often in production code
has become my norm and as far i'm using a reputable managed service.

For me, Cloudflare D1 just works fine and super cheap,turso libSQL looks solid
but i haven't dig into it yet.

SQLite vs. PostgreSQL: Just to differentiate

Just to differentiate between these data tools, while both have distinct design philosophies and use cases. we should evaluates their differences in features, performance, and suitability for various applications.

Data Modeling Considerations

When choosing between SQLite and PostgreSQL, consider your data modeling requirements. SQLite is suitable for simple data models, while PostgreSQL supports complex data relationships and schema changes, ~i need to stress this part~.

SQLite vs. PostgreSQL: A Comparative Analysis

Features

Feature	SQLite	PostgreSQL
Data Types	Limited	Extensive
Indexing	B-tree, Hash	B-tree, Hash, GiST, SP-GiST
Transactions	ACID-compliant	ACID-compliant
Concurrency	File-level locking	Row-level locking
Scalability	Limited	Horizontal partitioning
SQL Support	Partial SQL-92	Full SQL-92, SQL:2011

It's reasonable to consider community support and extensibility when comparing SQLite and PostgreSQL. PostgreSQL benefits from a large ecosystem of plugins and extensions that enhance its capabilities in analytics, geospatial data, and beyond. On the other hand, SQLite's simplicity allows for faster iterations, making it perfect for agile development where rapid prototyping is key. The choice ultimately depends on the project's complexity, scalability needs, and how much control over database management is desired.

Use Cases

Use Case	SQLite	PostgreSQL
Embedded Systems	Ideal	Not suitable
Mobile Applications	Suitable	Not ideal
Web Applications	Limited	Suitable
Enterprise Systems	Not recommended	Recommended

Yeah Postgres is a lioness, regardless using a managed SQLite DB offers reliability and security needed for production, plus simplicity.

Some of the traditional constraints of using SQLite in small to medium scale web and enterprise applications are fading away. For example, managed services provide automated backups, high availability, and even multi-region deployments, allowing SQLite to be a viable option for certain production workloads.

Benefits i enjoy using Managed services

Benefit	Description
Scalability and cost-effectiveness	Automatic scaling and cost optimization
Simplified application development	Reduced administrative burden
Enhanced performance and reliability	Optimized database performance
Integrated security features	Built-in security controls
edge deployments	managed edge deployments

Managed services often come with monitoring tools and performance metrics that offer valuable insights into database usage, helping optimize performance over time. For projects that require real-time data processing at the edge, using SQLite in this way is proving to be a cost-effective solution, blending well with serverless architectures.

Challenges i face using managed services.

Challenge	Description
Cold start latency	Initial function invocation delay
latency	network delay
Vendor lock-in	Norms
can't fit large data use case	SQLite itself is not scalable

Network latency in edge deployments can be minimized, Additionally, while vendor lock-in is a challenge, understanding each service’s offerings and limitations can help mitigate the risks and create a more adaptable database strategy.

Mitigating

To overcome the challenges of managed services one could:

• Implement caching mechanisms to reduce latency.
• Design applications with scalability in mind.
• Monitor vendor lock-in risks and plan for migration.
• Optimize data storage and retrieval strategies.

last week i needed to make my code reusable, So i worked a query builder that fits my needs for:

1. using cloudflare D1 via api, (yeah i gotta escape using workers bindings 👀)
2. using bun SQLite locally (yeah it's kinda faster)
3. just generate the query and let me do the rest (💆)
4. in typescript and super lightweight? yeah!!! (💃)

If you want to check it out see here.

follow me for more contents as well.