Hacker News Reader

Comment by hartator | Hacker News Reader

I am considering more and more a move back from MongoDB to PostgreSQL. I will be missing being schema less so much though. Migrations - particularly Rails migrations - left a bad taste in my mouth. Anyone did the move recently and what are their feelings?

View on HN

I did just that move when I realized that I was doing a lot of work so impose schemas on my "schemaless" data and another bunch of work to implement joins in my application.

I found the best way to do migrations is with vanilla SQL. I wrote a little tool to read migrations from SQL files in a directory, send them to the server and keep track of which ones have already been applied. Simple and easy.

The big benefit of migration is that your app code doesn't have to deal with every possible schema that you've ever used; it can rely on the data being uniform.

I'm very happy with the switch; wouldn't go back to Mongo for anything.

Yeah, all the things that need flexible schema can go in a jsonb column. While querying on JSON has gotten less painful, it's still a bit of a chore. But I've found that I rarely need to do that. Or if I do, I just denormalize a bit and put those fields in a regular ol' column.

We did the move maybe 4-5 years ago? At least in the JavaScript world, this makes your life so so much better.

Of course, you still have to handle migrations, but at least you have transactions :)

You can easily have schema-less with Postgres's jsonb data type.

Not really. Postgres ORMs are not meant to do schema-less and tables still need to be created.

You're right that ORMs aren't really set up to use jsonb but it can be done. I've had pretty good success with a kind of "hybrid" putting most relational data in regular columns (with FK constraints) and adding flexible data in jsonb. The main trick is understanding how to build custom column types in whatever ORM you actually use.

I can imagine a future with specialized ORMs designed around jsonb, but the current state of the art is probably not as bad as you think.

In the .NET world, ServiceStack ORMLite has custom Postgres type attributes to use the jsonb and other fields automatically.

http://docs.servicestack.net/releases/v4.5.6#postgresql-data...

There is also Marten which is an ORM that offers a full document database interface all backed by Postgres JSON columns.

http://jasperfx.github.io/marten/documentation/documents/

You are implying you need an ORM for the simple key-value (jsonb) data access. I don't think you would benefit much.

Reply on news.ycombinator.com