6 ms·

Related side note/question. I was on a project recently where postgres was used with a NodeJS JavaScript server app. It seems a bit backwards. Like you have the

Related side note/question. I was on a project recently where postgres was used with a NodeJS JavaScript server app. It seems a bit backwards. Like you have the flexibility of a loosely typed server app but all the annoyance of a RDBMS. It just made development a nightmare. The 'strictness' if the system was just backwards. Surely NoSQL + typescript is better for development (and potentially for performance). Thoughts?

maxmunzel 6y ago
I’d be interested, which use case of yours would be restricted by Postgres‘ strict typing. All my experiences were wonderful, especially its performance and powerful features like rule rewriting. Also PostgREST is brilliant.
- how_gauche 6y ago
  I've done this before (postgREST) for internal apps and it's amazing. You can POST a JSON file to the front end from a shell script with curl and have it show up on a Grafana dashboard immediately. I've used this in the past for setting up automated quality tests for machine learning pipelines -- the pipeline runs automatically and you get the results in a central place, so you can see how your quality metrics are trending over time. Given that postgREST exists, I don't see any reason to talk to postgres directly from a JS app.
Sebb767 6y ago
As someone who works on a business app using CouchDB+Typescript, I can tell you that (a) you will also hit the edge cases of NoSQL, like transactions and joins, and it will not be that awesome either and (b) at some point you'll realize that you'll need to do schema validation still. Frontend bugs, possibly malicious clients and protected attributes (i.e. 'disabled' should only be set by an admin) will force you to write a lot of validation anyway. 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.
voiper1 6y ago
But even mongodb isn't really loose typing. `db.collection('test').find({year:2020})` only returns rows with an integer value of 2020 and not a string value of "2020"...
nkozyra 6y ago
I think it's a good compromise. You're accepting a lot of looseness and fault-tolerance on the application side, but you have guaranteed safety of your underlying data. 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.
- smashah 6y ago
  I forgot to mention this was at a startup on ground level. Regular data backups always soothes my nerves. Eventually it makes sense to have type/db strictness on both app and db side however it just felt like wading through quicksand with a small team where we should be sprinting and db structure is fluid and ever changing
  - aidos 6y ago
    When you have the tooling in place, it’s relatively easy to remodel your data in Postgres. I use alembic (python), so most of the schema changes are handled for me - and then I need to write any updates to fill in the gaps. It doesn’t matter what you’re using though, if you’re restructuring data, you need to handle the changes yourself to get from one consistent state to another. Postgres allows you to do this so you you don’t end up in some horrible halfway house where you’ve kinda migrated, but kinda not because you missed something. DB level constraints are the best, especially when you have a bunch of legacy data to worry about.
2mol 6y ago
Yeah, my thought is that I've had a great time just dumping most data into a `jsonb` column. You can then extract fields into their own columns over time, as you learn more about your domain. JSON support is pretty excellent.
- smashah 6y ago
  That's exactly what we decided on. All unstructured data into jsonb column until the db structure is set in stone. Definitely a life saver
yokaze 6y ago
I see it the other way around. Code is fluid, data persists. So I would expect more rigor in the design of the schema then anywhere in the code. And hopefully with some fore-sight, you won't have to change it every other day. Migrating the data requires also some reflection. No amount of work of coding will fix your data, if it is a mountain of inconsistent garbage.
- smashah 6y ago
  I guess with experience comes the ability to correctly design the schema from day one. The project was just getting off the ground so schema was evolving constantly. If only there was such a thing as pgconfig.json which allows me to configure how strict I want the db to be - a sliding scale between NoSQL to SQL.
  - yokaze 6y ago
    No one gets the schema right from the beginning. Waterfall is out of fashion for a reason. And there are schema migration frameworks out there for that purpose. 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.
    - lowwave 6y ago
      I strongly disagree with this. Getting the schema right, reduces code writing and code maintenance.
  - neurostimulant 6y ago
    This is already solved in most server-side frameworks by using a schema migration tool. Heck, even wordpress has an easy way to automatically update a table's schema. The development of these tools in nodejs land are probably stalled due to nodejs community's obsession with nosql databases, but I bet there are several solutions available right now to handle this task.
smoe 6y ago
I have written a lot of server side code in dynamically typed languages (mostly Python) with a variety of database systems (I started my career in the mid 2000s on ZODB, an object-oriented database). 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.
throw1234651234 6y ago
As someone who loves JavaScript and used MongoDB in a serious, professional setting - you are eventually doing more work with Mongo to keep data in sync than with any relational database. 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.
- smashah 6y ago
  > our product is garbage and you need a "quick" db for prototyping. 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.
golergka 6y ago
This is my primary stack now, except for Typescript instead of Javascript, and I absolutely love it. 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.
- smashah 6y ago
  thanks for the pgtyped recommendation! I guess I was just frustrated with the constant moaning from the DB side when schemas were constantly being reconsidered. JSONb to the rescue though!
dragonwriter 6y ago
> The 'strictness' if the system was just backwards. 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.
- smashah 6y ago
  This is true. I had experience in every technology in the stack except Postgres (did a lot of TSQL work back in the day but never SQL with NodeJS). 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.
smashah 6y ago
I don't understand why I've been downvoted to -3 here. I'm sure if I just showed off how much I hate technology and live in a cave then I'd get lots of upvotes. Hacker news is wild.
lowwave 6y ago
Why would people even use NoSQL when they really know how to use postgresql. It has JSON native build in. 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.

maxmunzel 6y ago

I’d be interested, which use case of yours would be restricted by Postgres‘ strict typing. All my experiences were wonderful, especially its performance and powerful features like rule rewriting. Also PostgREST is brilliant.

how_gauche 6y ago
I've done this before (postgREST) for internal apps and it's amazing. You can POST a JSON file to the front end from a shell script with curl and have it show up on a Grafana dashboard immediately. I've used this in the past for setting up automated quality tests for machine learning pipelines -- the pipeline runs automatically and you get the results in a central place, so you can see how your quality metrics are trending over time. Given that postgREST exists, I don't see any reason to talk to postgres directly from a JS app.

Sebb767 6y ago

As someone who works on a business app using CouchDB+Typescript, I can tell you that (a) you will also hit the edge cases of NoSQL, like transactions and joins, and it will not be that awesome either and (b) at some point you'll realize that you'll need to do schema validation still. Frontend bugs, possibly malicious clients and protected attributes (i.e. 'disabled' should only be set by an admin) will force you to write a lot of validation anyway. 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.

voiper1 6y ago

But even mongodb isn't really loose typing. `db.collection('test').find({year:2020})` only returns rows with an integer value of 2020 and not a string value of "2020"...

nkozyra 6y ago

I think it's a good compromise. You're accepting a lot of looseness and fault-tolerance on the application side, but you have guaranteed safety of your underlying data. 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.

smashah 6y ago
I forgot to mention this was at a startup on ground level. Regular data backups always soothes my nerves. Eventually it makes sense to have type/db strictness on both app and db side however it just felt like wading through quicksand with a small team where we should be sprinting and db structure is fluid and ever changing
- aidos 6y ago
  When you have the tooling in place, it’s relatively easy to remodel your data in Postgres. I use alembic (python), so most of the schema changes are handled for me - and then I need to write any updates to fill in the gaps. It doesn’t matter what you’re using though, if you’re restructuring data, you need to handle the changes yourself to get from one consistent state to another. Postgres allows you to do this so you you don’t end up in some horrible halfway house where you’ve kinda migrated, but kinda not because you missed something. DB level constraints are the best, especially when you have a bunch of legacy data to worry about.

2mol 6y ago

Yeah, my thought is that I've had a great time just dumping most data into a `jsonb` column. You can then extract fields into their own columns over time, as you learn more about your domain. JSON support is pretty excellent.

smashah 6y ago
That's exactly what we decided on. All unstructured data into jsonb column until the db structure is set in stone. Definitely a life saver

yokaze 6y ago

I see it the other way around. Code is fluid, data persists. So I would expect more rigor in the design of the schema then anywhere in the code. And hopefully with some fore-sight, you won't have to change it every other day. Migrating the data requires also some reflection. No amount of work of coding will fix your data, if it is a mountain of inconsistent garbage.

smashah 6y ago
I guess with experience comes the ability to correctly design the schema from day one. The project was just getting off the ground so schema was evolving constantly. If only there was such a thing as pgconfig.json which allows me to configure how strict I want the db to be - a sliding scale between NoSQL to SQL.
- yokaze 6y ago
  No one gets the schema right from the beginning. Waterfall is out of fashion for a reason. And there are schema migration frameworks out there for that purpose. 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.
  - lowwave 6y ago
    I strongly disagree with this. Getting the schema right, reduces code writing and code maintenance.
- neurostimulant 6y ago
  This is already solved in most server-side frameworks by using a schema migration tool. Heck, even wordpress has an easy way to automatically update a table's schema. The development of these tools in nodejs land are probably stalled due to nodejs community's obsession with nosql databases, but I bet there are several solutions available right now to handle this task.

smoe 6y ago

I have written a lot of server side code in dynamically typed languages (mostly Python) with a variety of database systems (I started my career in the mid 2000s on ZODB, an object-oriented database). 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.

throw1234651234 6y ago

As someone who loves JavaScript and used MongoDB in a serious, professional setting - you are eventually doing more work with Mongo to keep data in sync than with any relational database. 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.

smashah 6y ago
> our product is garbage and you need a "quick" db for prototyping. 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.

golergka 6y ago

This is my primary stack now, except for Typescript instead of Javascript, and I absolutely love it. 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.

smashah 6y ago
thanks for the pgtyped recommendation! I guess I was just frustrated with the constant moaning from the DB side when schemas were constantly being reconsidered. JSONb to the rescue though!

dragonwriter 6y ago

> The 'strictness' if the system was just backwards. 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.

smashah 6y ago
This is true. I had experience in every technology in the stack except Postgres (did a lot of TSQL work back in the day but never SQL with NodeJS). 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.

smashah 6y ago

I don't understand why I've been downvoted to -3 here. I'm sure if I just showed off how much I hate technology and live in a cave then I'd get lots of upvotes. Hacker news is wild.

lowwave 6y ago

Why would people even use NoSQL when they really know how to use postgresql. It has JSON native build in. 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.