In the realm of software development, the notion of database agnosticism has long been considered a best practice. The idea of writing code that can interface with any database type without modification sounds appealing, promising flexibility and future-proofing. However, this approach has significant downsides that are often overlooked. Today I finally decided to talk about this controversy approach.
Traditional "SQLAlchemy" Like Approach: The Drawbacks
The conventional method of maintaining database agnosticism typically involves the use of Object-Relational Mappers (ORMs) like SQLAlchemy. While ORMs abstract the complexities of database interactions, they introduce several issues:
- Time-Consuming Implementations: Implementing features like Row-Level Security (RLS) and Access Control Lists (ACLs) can be incredibly time-consuming. Although databases like Postgres and MongoDB offer built-in features for these, using them with ORMs often requires custom solutions.
- Integration Risks: Each layer of abstraction increases the risk of integration failures. Writing and maintaining REST APIs to interact with the database adds complexity and points of failure.
- Data Retrieval Challenges: If a failure occurs after significant processing, retrieving the result can be complicated. This is especially problematic when dealing with large datasets or complex queries.
- Lack of Real-time Capabilities: Traditional approaches do not inherently support real-time data processing, which can be an asset in modern applications.
The problem originated when databases stopped being simple storage spaces for data and started adding features. Most of these features are not transferable to different types of databases. This underscores the primary reason why computational units and data storage should be separated in the first place.
Therefore, if you have to redevelop those features, it is not a great usage of your time, nor for the company goals.
A New Proposition: Embrace the Specific or Go All-In with Pocketbase
Instead of adhering to the outdated principle of database agnosticism, developers should either choose a specific database and leverage its full capabilities or adopt a solution like Pocketbase, which offers robust out-of-the-box features.
Why Choose a Specific Database?
- Feature-Rich Environments: Databases like Postgres offer advanced features such as PostGIS for geographic information systems, full-text search, and complex data types. Using these features directly can significantly enhance application capabilities.
- Optimized Performance: Database-specific optimizations can lead to better performance. For instance, tuning Postgres for specific workloads can result in significant performance gains that are not achievable through a generic ORM layer.
- Reduced Complexity: By focusing on a single database, developers can reduce the complexity associated with maintaining multiple database interfaces, leading to simpler and more maintainable codebases.
Pocketbase: The Comprehensive Solution
Pocketbase provides a compelling alternative to the traditional approach. It integrates several critical features out of the box, addressing many of the challenges posed by database agnosticism:
- Built-in ACL and RLS: Pocketbase includes advanced security features such as ACL and RLS, eliminating the need for custom implementations.
- Real-time Data Processing: With real-time capabilities, Pocketbase allows developers to build applications that respond immediately to data changes, providing a better user experience.
- Simplified Development: Pocketbaseās comprehensive feature set reduces the need for additional tools and libraries, streamlining the development process, so you can focus on what matter: your product.
Letās talk about Pocketbase
Globally, the reason I love Pocketbase so much, is because for me, it is a mix between Firebase and Supabase. All the advantages, without the drawbacks.
Firebase
I used to be a big fan of Firebase, particularly the "realtime" feature. However, I quickly encountered the limitations of a NoSQL database, especially regarding schema management. Firebase Auth also presents a significant lock-in problem. Although it's possible to migrate users who authenticate with a password using bcrypt, moving those using third-party authentication services is more complicated. Firebase Functions can be slow to deploy, so it's often more practical to use Firebase Admin in Vercel API to perform some of Firebase Functions' features. Moreover, until recently, you needed two separate projects to have two databases. While some configuration operations like rules and indexes are manageable with the CLI, others are only available through the web console. As a result, if you forget to whitelist your production domain on the authorized origins to sign up, you can easily encounter a bug due to environmental differences that will simply result in nobody being able to sign up to your product your worked very hard onā¦
Supabase
I was initially drawn to Supabase, an open-source alternative to Firebase. However, just like with Firebase, the initial excitement didn't last. The self-hosting feature took longer to arrive than expected, which I found disappointing since I struggle with the apparent open-source representation of some companies that just pretend.
Furthermore, I had difficulties manipulating features like triggers and Postgis, and configuring RLS properly using a third-party provider like Firebase Auth. I spent three days trying to implement a feature that was supposed to work, and ended up writing an article about it on Medium detailing my experience.
In retrospect, my main issue with Supabase is that to use the interesting features of Postgres, it requires using different languages. The INNER joins are complex, and you still have to manage your migration system (Prisma and drizzle are both disappointing for me, but thatās maybe another story). On the positive side, Supabase offers less lock-in, and it's easy to see your database(s), dump/export them, even in JSON format through JetBrains IDE. The code completion is enhanced with the table names and their columns context, even in your client programming language.
Self-hosting is released for a while now and I tried it. It works like a charm, but I have to admit the architecture is quite complex:
ā¤ Pocketbase In The Game
First of all, let met remind you that Pocketbase is a program to use and manage a SQLite database. It means it can run locally on your computer (like the other DB type, except Firestore - I donāt count the emulator as viable), but also be self-hosted on any kind of desired server.
The reason I say this is because, after some second thought, when you use Pocketbase for example in a project where you have one database per devices, it changes everything in term of paradigm: while on Firebase you will be forced to be aware of the queries you make to avoid cost disaster, for Pocketbase, it simply does not apply at all š¤·āāļø If you are alone on your own database instance, you can do a SELECT *, without even having to think about it.
Now let me go ahead and tell you 10 things I love about Pocketbase.
- Super Easy To Setup
Probably easier than reading this article. You simply download the binary for your platform and unzip it in a pocketbase folder in your project root, add those 2 scripts in your NodeJS scripts package.json file:
"pocketbase-serve": "./pocketbase/pocketbase serve",
"pocketbase-generate-types": "pocketbase-typegen --db ./pocketbase/pb_data/data.db --out models/pocketbase-types.ts"
you run pocketbase-serve, and thatās it! Youāre good to go.
- Easy authentication setup
In Firebase, the inability to extend the Firebase Auth user is a limitation, forcing the creation of a user collection linked to it. Supabase overcomes this issue, as the user is simply represented by a table. For Pocketbase, it's the same; custom columns can be added, striking a perfect balance with not too many or too few options:
Of course, Pocketbase also support 3rd party authentication with easy setup:
Fine-grained token duration customization:
And a basic email system for the stuffs we really donāt want to spend time on.
- A super intuitive Super Admin interface
The tool provide the super admin interface that you already started to see with the possibility to add more:
Which give you access to few configuration, like for example an external S3, if you donāt want the hosting machine to full up with medias.
There are also the backups, a simple feature that Firebase failed to provide in a clear way, and that Supabase offer as a paying service for 20$ per month / database.
- Data storage is finally about data!
One of my personal favorite feature is this data export:
The info contains everything: the schema, the rules, the data. If you export the JSON and import it in another Pocketbase setup, it works exactly like you would image it does. Copying your prod data would never have been so easy!
- Designing your datamodel is very intuitive
We can find all the modern datatype one could expect, including JSON, but my 2 personal favorites are Relation and File and I will explain why.
File concept is abstracted (like in MongoDB) in the way that it just looks like a column in your database! Therefore regardless multiple or single file, you handle the size limit, mime type limitation, auto-resize thumb sizes directly in the column! This is really one of my favorite feature, because it means that File is not yet another concept (you can simply remove it from your brain and manage it like it was any other column) and another SDK to learn like Firebase Cloud Storage or Supabase Storage, while still being a real file hosted on S3 under the hood.
Relation allow you to connect (INNER JOIN) your collection to another one, but the crazy thing about pocketbase nice UX, is that this new collection target by the relation does not need to prior exist! You can simply create everything hierarchically, and itās really nice!
- Finally an intuitive ACL
Iām also very happy about the work they did on ACL. I was never a big fan of Firebase Rules, and Supabase RLS is very complex in my opinion. With Pocketbase API Rules, you have a comprehensive and simple language, with auto-completion that allow you to secure your data access with all the flexibility you need, like the request author payload, any column of the collection accessed for comparison, time macros, and much much more. Among the 3 systems, it is by far the most intuitive for me.
- A Useful SDK
One of the most crucial feature for me for an ORM, is the ability to āpopulateā, āexpandā relationship. I was very disappointed the day I discovered that drizzle does not do that (you have to do your own INNER JOIN manually) and pretend to be an ORM.
You simply configure the āexpandā parameter in the SDK and therefore get your full relational object, rather than a stupid numerical ID. But donāt worry, in order to create (POST) or update (PUT), you can simply pass this stupid numerical ID, or array of numerical ID, just like you would expect it to work š
Therefore, your models looks flat and stunning, regardless if there are files or relation underneath š¤©
- Learning curve? Hell no!
Like it wasnāt easy enough, the super admin UI provides you for each collection the complete CRUD code implementation adapted, you can simply copy paste it to get started.
- Admin & Logs
We often want to have info about what is going on on the database and/or the REST API. Pocketbase got you covered since point #1 with its built-in ālogsā feature.
You might wonder: what if I need to make a higher authorization request type? For instance, when I'm on a server receiving a webhook from Stripe, where is the Pocketbase Admin SDK? The answer is, it doesn't exist. It's the same SDK as the front; you simply need to log in as a super admin on your server or Vercel API, and use it exactly as you would on the front. Essentially, this means moving your TypeScript code from the front to the back or vice versa is effortless.
And since Pocketbase support realtime, it means you can do realtime on both the front end and on a monolith server, which replace the need for mecanisms like Firebase Cloud Functions node listening or Supabase Triggers, and does not require any new learning.
- What about migration?
It is not noticeable at first, but actually every single move and changes you make on the schema create a migration file in pocketbase folder, making the migration process Prisma-like, and when pushed in production the migration happen. The only difference with Prisma is that you can remove this requirement from your brain, since it is fully automatic and working.
- SHUT UP! TAKE MY MONEY
Sorry I canāt, Pocketbase is actually free, open-source, and self-hosted.
It takes 1 click and 1 min 30 to deploy on Railway here, or find the docker-compose.yml file here:
version: '3.8'
services:
pocketbase:
build:
context: .
container_name: pocketbase
restart: unless-stopped
ports:
- 8080:8080
volumes:
- "./pocketbase-data:/pb/pb_data"
Yeah, itās a tiny bit smaller than Supabase self-hosted docker file (but just a tiny):
Popular beliefs about Pocketbase
- I will have performances issues
- As per the documentation:
Even without optimizations, PocketBase can easily serve 10 000+ persistent realtime connections on a cheap $4 Hetzner CAX11 VPS (2vCPU, 4GB RAM).
Source: https://pocketbase.io/faq
Most of your projects will not even reach half of Pocketbase limitations.
- Yeah but what if I do, I cannot scale horizontally.
- Actually yes, you can. I never did it myself, but there is a great tutorial from Marmot on this page, with a lot of other cool goodies like pocketbase mobile version.
- Itās not a v1.0.0
- Yet, Iāve been using it for more than 8 months and itās incredibly stable. Itās open-source and self-hosted, thus, you will never be subjected to a forced update.
Conclusion
The traditional approach of maintaining database agnosticism is fraught with challenges and limitations. Developers should reconsider this paradigm, either by fully committing to a specific database to leverage its unique features or by adopting robust solutions like Pocketbase that provide essential features out of the box. This shift can lead to more efficient development processes, enhanced application capabilities, and ultimately, better performance and user experience, but also developer experience than can focus on building products and bringing value to customers, rather than focusing on architecture in early phase.
Cheers š»
