News for hackers. Stuff that matters.
Re:New Features Coming in PostgreSQL 10 by hartator on Saturday April 08, 2017 @11:43AM (#14067445)
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?
Re:New Features Coming in PostgreSQL 10 by cwp on Saturday April 08, 2017 @06:53PM (#14069503)
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.

Re:New Features Coming in PostgreSQL 10 by JohnDotAwesome on Saturday April 08, 2017 @12:29PM (#14067690)
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 :)

Re:New Features Coming in PostgreSQL 10 by stemcc on Saturday April 08, 2017 @11:51AM (#14067488)
You can easily have schema-less with Postgres's jsonb data type.
Re:New Features Coming in PostgreSQL 10 by hartator on Saturday April 08, 2017 @12:04PM (#14067552)
Not really. Postgres ORMs are not meant to do schema-less and tables still need to be created.
Re:New Features Coming in PostgreSQL 10 by stickfigure on Saturday April 08, 2017 @12:23PM (#14067659)
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.

Re:New Features Coming in PostgreSQL 10 by manigandham on Monday April 10, 2017 @01:14AM (#14076218)
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/

Re:New Features Coming in PostgreSQL 10 by StreamBright on Saturday April 08, 2017 @02:49PM (#14068335)
You are implying you need an ORM for the simple key-value (jsonb) data access. I don't think you would benefit much.