There are so many types of databases in the world, how to choose from them?
IMPORTANT
There is no one DB for all use cases, architects should always choose the right tool for the right task. Those in science and engineering shouldn’t worship a particular database as if it were the ultimate truth, like a fan or a religious believer.
Features
Make sure the database of choice has all the features you need now, and may need in the foreseeable future. Think about what potential features you will need in the future. For example, vector databases are very popular in recent years. You may not need it for now, but it would be a headache when you need it but it’s not available.
Scalability
Always plan ahead, estimate the amount of data there will be, and pick a scalable solution. For small projects, even Sqlite based DB like Pocketbase or Turso can be enough. For large projects, consider the cost, complexity and limits of horizontal scaling vs vertical scaling; also throughput of databases.
Client SDK
Client SDK is one of the most important aspects to look at when choosing a DB. No matter how performant a DB is, if the DX is bad, then you can’t take full advantage of it.
- More language support is preferred
- Support for ORMs or similar encapsulation is preferred
- Without ORMs, raw SQL queries can be a nightmare to work with and maintain. Developers will have to rely on large amount of unittests to make sure the behavior is correct.
- Type safety and autocomplete is preferred
- A good client sdk should help you complete the function you are calling, table name you are filling, provide type info on the returned data
- When schema updates, the client library should throw error/warnings to tell where to update
- A good sdk should help developers to write less tests on testing schema related code, but service logic
- It’s understandable that it can be hard to support this for some languages. This usually requires code generation from a given schema to provide types for statically typed/strongly typed languages. Prisma is a good example.
Maturity
New databases always sounds amazing, with all kinds of fancy new features. It’s a good idea to try them or even use it for a small side project, but be careful when choosing them for serious projects in production.
- New databases can have large design changes (syntax changes, API changes, client sdk changes)
- Unknown bugs
- Database is a complex field, there can be unknown bugs anywhere
- Mature databases like Postgres will definitely has less problems, and more solutions for existing problems
- Read tech blogs of other companies, see what they picked, what problems they have had, what DB they migrated to.
Vendor Lock-in
Always avoid proprietary databases as much as possible to avoid vendor lock-in. e.g. Firebase Always prefer open source databases so you can migrate freely among cloud services and have more room for price negotiation. When migration cost is too high, you lose the power to bargain.