Surely NoSQL + typescript is better for development (and potentially for performance).
Thoughts?
No amount of work of coding will fix your data, if it is a mountain of inconsistent garbage.
If you care for the data you store, you'll have to take care of your data. ACID and the "rigidness" of schemata are tools for that, and to keep the complexity in check. It is much easier than accruing technical debt by having unstructured data and having later to figure out to make heads and tails of what you and your colleagues did at some arbitrary earlier time. (Not that crappy SQL designs don't have that problem).
If you don't care for your data, as you are in the beginning, why don't you create the DB then from scratch? You can use various editors to create the schemata from tools you are more comfortable with.
Don't work against the tool, and find the slider in your head to adjust your way of working.
It definitely allows for faster iteration, though. You can deploy a prototype or change very quickly without caring about this, which is pretty nice. But you need to keep track of that and pay the debt if you use the prototype.
Overall, I think it would not give or take much if we'd be using PostgreSQL+some JSON column for general data instead of CouchDB. You just need to know your stack and its drawbacks and work with them.
At least in my experience the "philosophical" language-database pairing always ended up being much less important than the database own strength and weaknesses in regards to the problem being solved.
For me Postgres is the best default choice if you don't understand that well where your project/product is going (Not saying it isn't a good choice later on as well). Out of the box it handles pretty much every use case I encountered at least good enough for a long time without adding supplemental data stores or ugly hacks.
For me the difference in development barely matters. Locally schema changes are pretty much friction-less with the right tooling, and in production the problem is usually not changing the schema, but the existing data. The latter being a pain in any tech I have used so far, because often it is not even an engineering challenge but a matter of business decisions.
All my experiences were wonderful, especially its performance and powerful features like rule rewriting.
Also PostgREST is brilliant.
Given that postgREST exists, I don't see any reason to talk to postgres directly from a JS app.
Most systems do not need no-SQL. The only reason to use no-sql is when you are allowing users to create completely dynamic form schemas and JSON and when your product is garbage and you need a "quick" db for prototyping.
That's not to mention that when your product grows and you are selling to "serious" corporations, they ask you to integrate with third-party data visualization / analysis software. They claim to support MongoDB/No-SQL in their marketing material, but virtually none of them do, and you are mapping back to SQL. That's when the cost of using no-SQL really hits, several years down the line.
Yep that's the stage the project was at.
Integrating the DB with grafana was a breeze though so that's definitely a benefit. I've personally never tried to hook up a MongoDB to Grafana, wonder if it's any easier.
First, the ideological thing: flexibility is an awful thing for user's data and logical relationships within it. The fact that with RDBMS you have to define your schema and follow it, makes many classes of erros that would have been corrupted data at operations to being exceptions at development: PostgreSQL just won't let you shoot yourself in the foot like that. And with pgtyped, which I'm growing to love, you don't even have to write any boilerplate: just write plain straightforward SQL, run verification against your test database, and get all of your types for free.
But that's not even the most important part; what I absolutely love about SQL is my ability to delegate huge amounts of work from my app server to my db server, saving a lot of latency and CPU. It may not be that critical for things when you just update one row in the 'users' table, but when you have O(n) updates and up, it's just great to be able to do things directly in the database.
And in rare occasions where you absolutely have to use non-structured data (or data with a lot of different data types that don't deserve their own columns), jsonb is useful and pretty damn fast if you think throught the schema and make use of the GIN indexes.
`db.collection('test').find({year:2020})` only returns rows with an integer value of 2020 and not a string value of "2020"...
What scares me more than anything else is lost / compromised / invalid data. That's hard to fix. The frontend code is straightforward to fix, relatively speaking.
Postgresql and NodeJS is the best! being using it for years. Of course you got learn SQL or some kind of ORM to make it worth you while.
IMO, the strictness was in the right place (the source of truth), what was probably backwards is the team’s relative skill and tooling for adapting the solution. Which might make it wrong for the team, if it is otherwise right for the situation.
After getting used to it it wasn't so bad but the unclear error messages, abstractions and lack of types really made it a steep learning curve.
If the codebase was in typescript it would've saved a lot of time trying to sleuth out the documentation for an abstracted piece of code.
You can then extract fields into their own columns over time, as you learn more about your domain. JSON support is pretty excellent.