3 ms·

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 migra

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?

stemcc 10y ago
You can easily have schema-less with Postgres's jsonb data type.
- hartator 10y ago
  Not really. Postgres ORMs are not meant to do schema-less and tables still need to be created.
  - stickfigure 10y ago
    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.
  - StreamBright 10y ago
    You are implying you need an ORM for the simple key-value (jsonb) data access. I don't think you would benefit much.
  - manigandham 10y ago
    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-types 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/ http://jasperfx.github.io/marten/documentation/documents/
JohnDotAwesome 10y ago
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 :)
cwp 10y ago
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.

stemcc 10y ago

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

hartator 10y ago
Not really. Postgres ORMs are not meant to do schema-less and tables still need to be created.
- stickfigure 10y ago
  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.
- StreamBright 10y ago
  You are implying you need an ORM for the simple key-value (jsonb) data access. I don't think you would benefit much.
- manigandham 10y ago
  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-types 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/ http://jasperfx.github.io/marten/documentation/documents/

JohnDotAwesome 10y ago

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 :)

cwp 10y ago

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.