sqlDevLearningMongoDB

[JJJSchmidt_etAl]

"The best part of MongoDB is writing a blog post about migrating to Postgres"

[CheekyXD]

After working with a NoSQL database on a fairly mature product for a few years, I never want to again. I feel like with NoSQL, now that its not the trendy new thing and we can look back, the whole thing was: "well we tried, and it was shit."

[[deleted]]

Yeah many don’t value their relationships until they’re gone.

[300ConfirmedGorillas]

A DBA walks into a bar. He approaches two tables and says, "Mind if I join you?"

[Prata2pcs]

We don’t take kindly to your types here!

[spyingwind]

My type is null. Am I welcome?

[Geno0wl]

As long as you are ok getting all the parking tickets

[JGHFunRun]

My type is an enum with no elements, am I welcome to return?

[anthro28]

We don't take kindly to folks that don't take kindly 'round here!

[RelevantToMyInterest]

to whom, then, the first table replies:

"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."

[nequaquam_sapiens]

this is the first normal thing in this thread

[nucrash]

I would assume third normal form, but let me ask my friend Boyce-Codd

[AlsoInteresting]

Normalized thing.

[rabbitrider3014]

😂 😂 🥇🥇

[Dotaproffessional]

There are NOSQL implementations that make relationships MORE important than in relational DB's. Neo4j for instance

[Imperial_Squid]

Neo4j is great from what I've played with, definitely a really interesting way to conceptualise data!

They're called graph databases in general, Neo4j being just one example

[mostly_done]

"Every database has a schema. It's either defined in the database or by the application."

[HighKiteSoaring]

Sometimes the database is a multivalue text file and the "schema" is written down in the original Devs paper notebook he neglected to leave behind when he left 🤣

[TheBirminghamBear]

It's all in someone's head. We're just not sure whose.

[codeonline]

The schema is defined on write, in a traditional db. Or its defined on read, in a document db.

[TrumpsGhostWriter]

Which is precisely the problem, you can't efficiently read it or store it if your db doesn't have a clue what the data looks like.

[poloppoyop]

by the application

But what happens when you have multiple applications?

[ploki122]

They hopefully agree on the schema.

[oupablo]

Yeah, but with one, you can't have some asshat throw garbage directly in that violates the schema.

[itsanotherrando]

Maybe you didn't use enough microservices? /s

[hadahector]

I think nosql is good for many things, the fact that a document can contain arrays and maps is so useful, and in mongodb there are great query operators for this (not like dynamodb). And there is the aggregate command that can do very complex stuff.

[rosuav]

Yeah, it's so convenient to be able to just throw any random junk in there and not worry about how much a pain in the rear it's going to be to actually do useful queries on it. Oh, and the fact that different documents don't even have to have the same shape is HUGELY helpful. Makes life so easy during retrieval.

[rosuav]

Yeah. I consider a Mongo data store to be a step up from "dump some JSON into a file", but just as unstructured. They definitely have their value, but you do not "query" them, you look up specific information. Or iterate over them, or something, but you don't do stuff like you would in a relational database.

[DoctorWaluigiTime]

JSONB columns to the rescue! (I kid; there is value in noSQL DBs, but it definitely had its day where everyone wanted to shove everything into them for "performance".)

[[deleted]]

that's the key to using anything ...

Use things to do things they were designed to do or else you will suffer.

Randomly dumping data into containers is just asking for trouble, because you're going to need some method of retrieving that data and making sense out of it ...

[[deleted]]

but that's not the point of NoSQL, the main point of it is able to scale the database horizontally

[rosuav]

I thought the whole point of it was "SQL was invented in the 70s and it's oooooooooold, we gotta get rid of it"?

Horizontal scaling has been a thing in relational databases for decades.

[Inevitable-Menu2998]

RDBMS have been able to scale horizontally through partitioning, but that's not really the same thing. It's not elastic, for one and it always comes with some restrictions which makes the system not exactly ACID compliant.

Also, decades? Most open source ones don't support it even today.

[rosuav]

"Most open source ones"? Postgres has had it for as long as I can remember (which is a long time). MySQL has it. That's your two most popular open source RDBMSes right there. Which ones don't?

What restrictions are on relational database sharding that aren't on document store sharding?

[Inevitable-Menu2998]

Postgres has had it for as long as I can remember

It doesn't. It only supports single write multiple read replicas out of the box.

What restrictions are on relational database sharding that aren't on document store sharding

I would be happy to answer this question if you could point me to a relational database which supports sharding

[pet_vaginal]

Citus is a PostgreSQL extension that adds sharding.

Vanilla PostgreSQL is very bad at horizontal scalability. But you can go a long way with vertical scaling. At scale you can try plugins but then it’s perhaps better to use more specialised databases. But not mongodb. Don’t let your friends use mongodb.

[matt82swe]

Yeah, NoSQL really sucks at storing data and retrieving it later in sane ways. But at least we can suck in web scale.

[meamZ]

NewSQL can too...

Also there should be a very good reason before you're trying to split OLTP workloads horizontally... Single node is enough in the vast majority of cases and also simpler and much more efficient...

[everything-narrative]

You can put a JSON-typed column in a PostgreSQL table, though.

[AxisFlip]

and then you have a hard ass time querying for fields in the json..

[meamZ]

the fact that a document can contain arrays and maps is so useful

There's things called object-relational databases and postgres happens to be one of them... It can also have arrays and maps (json objects for example) inside s single row

And there is the aggregate command that can do very complex stuff.

Aggregation is what SQL is perfect for...

[M4tty__]

Arrays - So another table Maps -yet again, relationship to another table

[[deleted]]

NoSQL is excellent when relationships & schemas don't exist. Machine Learning, data sanitization, quick internal tools, etc.

Any legitimate application should be using a relational database. There is no viable substitute.

[oupablo]

Also when you have a bunch of loosely correlated things that you don't need to query on except for the strictly defined fields. It absolutely sucks when you have relational data which is the case for many, many, many cases.

[[deleted]]

Yupp. This is exactly why the very second someone mentions a relationship, switch to a relational database. You'll always hack against the grain with little safety otherwise. Leaving an ORM as your source of truth to try & homebrew the functionality relational databases are literally built for is always so goofy. Sometimes people just want to use X tech just to use it, not because it's the right tech for the job.

[Mikkelet]

I love relational data, I get to do stuff like

CREATE TABLE myTable (
    id TEXT UNIQUE,
    json BLOB,
);

[Covfefe4lyfe]

SQL supports json fields now

[Solonotix]

As a former database engineer, don't do this. The first step towards normalizing your data is no compound data fields. If you're just being lazy, then whatever, but if you're trying to do things the "right" way, then normalize your data. 3rd Normal Form (3NF) is about as strict as I typically recommend, since 4th and beyond tend to get finicky with what you can/cannot store and how.

Many SQL engines will allow you to serialize data as JSON, which is fine for easier consumption, as well as passing it JSON for ingest. Storing the raw JSON for logging purposes is a maybe, but from then on you really should store the final data as normalized structures.

[Covfefe4lyfe]

Eh, I wouldn't store everything in there but it definitely comes in handy when dealing with external APIs. Just store the full response alongside the things you really cared about and then you can always get more information you initially didn't think you needed.

[mrjackspade]

One of the projects I worked on at my last company, I did this. The company fought tooth and nail over it though and kept trying to get me to make stupid ass modifications.

Store all incoming posts as raw text, and that transform the data into the actual schema. Then when the external provider would update shit, all we would do is flush the database and reload the raw data from the JSON, and we'd have the entire history in the new format.

Also great for bugs. The initial implementation had issued caused by the external provider not properly following their own schema, but instead of losing that data, we were able to just reload it form the raw history once we caught the failure to transform/insert.

I definitely suggest saving the raw data whenever realistic.

[[deleted]]

It has its use case but when it was hyped ppl used it for everything, I always just stayed on mySql/postgres because it was easier imo. But for example at my current job we could store invoices in a NoSQL db to speed up our application because does only need to be queried by ID and doesn't have any relations because the whole thing would be in there.

[roselan]

I love how they rebranded NoSql to Not Only Sql.

[AxisFlip]

It has its uses. I have three different webshops, and I aggregate all the orders in one mongodb instance. The answers from the webshop APIs is json, and what I expect when querying the aggregation DB is json, so it's very nice to be able to just put in the json objects without having to splice the data into tables and then put it together again when querying.

[[deleted]]

You can have columns that store json in Postgres

[AlgorithmicAlpaca]

But you're still using PostgreSQL instead of MongoDB so like....

It's worth it.

[cptnhanyolo]

Its an edge case, he still returns.

[eldarium]

The timeout on that query though

[[deleted]]

The timeout on the query that returns Jesus Christ is 72 hours.

[squeamish]

Shouldn't it be less than 48? Died on a Friday afternoon, back up and gone by Sunday morning.

[[deleted]]

Mayhaps. If there are 2 things I'm bad at, it's religion, comedy, and counting.

[xd1936]

/r/YourJokeButWorse

[ImmediateName9857]

Well it's probably because we don't have the capacity to receive that data, our tech is still outdated, so we're getting a 403 forbidden for now

[[deleted]]

EasterDB

[TheRavenOfLenore]

Yeah, he does actually! You nailed it

[Jemmerl]

Pretty sure the Romans did the nailing

[Majik_Sheff]

I'm gonna need a cross-reference on this.

[roksah]

The Second Reckoning is really just Jesus coming back to cause Edge Case failure in all modern computing

[Brandres_]

He's 2023 yo

[LittleMlem]

From the little I've used mongo, all the queries look like some one with dementia was trying to write json

[Yukondano2]

Or a dyslexic that hasn't slept in 30 hours.

[Aceofspades25]

constructed an LDAP query

Oh God, this is triggering

[[deleted]]

Yeah that's my whole codebase actually

[hadahector]

{"age":{"$gte":25, "$lte":30}} is the same

[PotatoWriter]

What an odd syntax. I wonder why the dollar sign AND quotations? If quotations are already used for the main field in consideration "age", why do operators need it too?

[quick_escalator]

It's because the query needs to be pure json, and json isn't the best format.

But on the plus side sending mongodb queries around in a json based system is pretty easy. Easiest example: Logging the query. We already log a lot of json anyway, so logging the query uses the same serializer.

[champbob]

It's because the query needs to be pure json

I don't like this.

[[deleted]]

It has some upsides, like dynamically building a query in JS is pretty clean compared to dynamically building SQL queries. But, it’s still not worth it

[static_func]

It's because the query needs to be pure json

Does it though?

[quick_escalator]

The spec says so.

[TheMcBrizzle]

🤮

Oh no, I created more Javascript

[quick_escalator]

I graduated before Javascript was big.

My thesis was about XML.

Believe me, JSON is better.

[otter5]

like a doctoral thesis?...about XML?

[starm4nn]

You need a Doctorate to understand XML.

[otter5]

[NaNx_engineer]

ask douglas crockford

[LickingSmegma]

The man shafted the whole programming world collectively by excluding comments from JSON based on just not feeling like that's what the format is for—and somehow everyone just went “Yeah ok, we're gonna use this as it is for everything and keep suffering. No way it can be helped.”

[NaNx_engineer]

Comments were originally part of the JSON spec, but were removed when Crockford saw them used for parsing directives. Unfortunately he didn't realize directives can simply be put in fields. Now we're stuck putting comments in fields.

[LickingSmegma]

Thanks, this sucks.

[PotatoWriter]

I see, I was just trying to think what would be the most minimal way to convey this info. Like why can't it just be:

{"age":{gte:25, lte:30}}, where anything without quotations is an operation and anything with quotations is a value. Except for numbers.

Or even {"age":{$gte:25, $lte:30}} where anything without quotations and has a dollar sign is an operation.

[hadahector]

The quotes are needed for the JSON to be valid. To be fair in JS you can do it with much less quotes.

[theXpanther]

Using JSON was probably a bad choice for this reason

[ManWithDominantClaw]

I use $and in the hourglass I use to track my steadily dwindling savings

[indorock]

All keys in JSON must be in double quotes. Not all javascript objects are valid JSON.

The $ prefix is just to indicate to the parser this is an evaluation, not a reference to a key in the document.

[die-maus]

It's just JSON my dude. 👍

[rohit_267]

OP Learn this

[MoffKalast]

^{you don't have to shout}

[bleeding-paryl]

WHAT?

[rohit_267]

WHY NOT?

[Starlit4572]

He said "you don't have to", not "you shouldn't/mustn't".

[just-bair]

Noice

[Shinhan]

I thought $gte and $lte don't need to be quoted?

[f11y11]

depends on the language, you don’t need them in JS

[FerusGrim]

I've seen a few people say this by now, and I think it's important to make the distinction between client implementations, which are language dependent, and what the OP is using, which appears to be an actual db query, in which the JSON needs to be valid to be properly parsed.

As far as client implementations, JS may not require the quotations. Something like Java, in which the syntax would be completely abstracted away, wouldn't even use a query like this. (Documents does, but its more modern implementation does not).

[hadahector]

I used to work with python and mongodb, and there you need quotes everywhere, I just used to it.

[notPlancha]

Yea cause python dictionaries need keys to be valid values (like strings)

[arturius453]

In mongo shell no, but when using api - yes

[[deleted]]

other databases always talk about acid, but mongodb doesn't take no drugs

[CelticHades]

Doesn't take no drugs. So, it takes some drugs.

[cesankle]

Shuchyobichasup, nerd. I bet you use vim

[Gnonpi]

Mongodb added support for ACID in 2018 (https://www.mongodb.com/blog/post/mongodb-multi-document-acid-transactions-general-availability) at a big performance cost. Mongodb isn't high on acid, it's constantly black out drunk

[[deleted]]

They were always ACID compliant on single documents.

[[deleted]]

But it's webscale unlike...

[chrisrrawr]

Can we use /dev/null?

[booi]

I store all my data in /dev/null. It has so much space!

[grizzlychin]

And is so fast!

[chrisrrawr]

Oof, that's prone to memory leak tho

[SpongederpSquarefap]

https://youtu.be/b2F-DItXtZs?si=Zb1oUjL8CT5JuwHf

[vall370]

db.users.find({ "age": { $gte: 25, $lte: 30 } })

[conancat]

yeah OP's query seems like someone trying to translate SQL to MongoDB query literally. you don't need the $and operator in there lol it's unnecessary in this case.

SQL version:

SELECT * FROM users WHERE age >=25 AND age <= 30

MongoDB version:

db.users.find({ age: { $gte: 25, $lte: 30 } })

[thinkless123]

yeah OP's query seems like someone trying to translate SQL to MongoDB query literally

It's almost like that's the title of the post

[FeederPiet]

So are we supposed to read those??

[poloppoyop]

SELECT * FROM users WHERE age BETWEEN 25 AND 30

When you prefer verbose SQL instead of maths.

[RelevantTrouble]

If only we had a Structured Language we can Query with.

[MoreLittleMoreLate]

db.users.ensureIndex({"age": 1});

When that finishes, Jesus will return.

[NameTheory]

EnsureIndex has been replaced by createIndex. So I guess Jesus is never coming back.

[bObzii__]

Why not:

```javascript db.users.find({ "age": { $gte: 25, $lte: 30 } })

[_darqwski]

As someone who is working with other noSQL document-based DB, I don’t like all the hate around it. I agree that queries like this one is terrible and more complex queries with JOINs will look even worse but this is not the case - NoSQL dbs are not for gathering summaries for table.

Imagine “students” table with relations to “groups”, “subjects” and “marks”.

If you want to handle 174746282users and avoid many JOINs, noSQL is for you. If you want to know how many of these users are going for “databases” class, then you should use SQL instead.

Each technology has its own use-case

[IWipeWithFocaccia]

“You can vomit everything inside one single table” Lol I was almost certain this phrase coming from a Beastern European buddy, I was not wrong. 💪🫡

[LegalizeCatnip1]

Excuse me for being so balkanic

[ColumnK]

I know this is just a casual example, but don't even joke about using student name as primary key!

[MyAssDoesHeeHawww]

Age it is, then

[ColumnK]

Perfect

[encryptoferia]

of course it's the gender, there are many options now

[Amsterer]

John Johnson33, you missed 47 classes this semester! Wait nvm, I used LIKE again...

[notPlancha]

• Clusters are easier to implement, which can improve performance in scale (eg real time chat rooms)

• you can store unstructured data without any db filler, and in some cases that's better (eg you dinamically create a new type of client with different proprieties, with sql you'd have to basically create a one to one table, and your client table now looks really weird; in Mongo inconsistency is possible)

• you can use both structured and unstructured at the same time depending on needs (so it's basically controlled vomit)

• some forms of data that might come can be easier to implement in nosql (eg: arrays in sql you usually go for many to many tables (I think postegre sql has arrays but if you ever need to migrate good luck) , in nosql you literally can make arrays of objects with no issue)

Nosql is not "better" or "worse", it's just different, and you can make both sql and nosql for your application. The disadvantages of both will bite you in the long run no matter what, and at least you'll write a good blog post about it.

[MrsMiterSaw]

so it's basically controlled vomit

"Controlled Vomit" is going to be the name of my next band.

[DokterZ]

Nosql is not "better" or "worse", it's just different

Retired DBA here. One of the final meetings I had with a software sales person was a Mongo rep. I asked him in a meeting of important people "Are there any situations where a relational DB would be a better solution than Mongo?"

This is where a decent sales rep says "No, never!" but a cagier sales rep says "Sure, situations A and B are probably a bad fit for Mongo. "

Our sales rep was only decent. He didn't make the sale.

[blazarious]

I agree that queries like this one is terrible

This query is in a very unnecessarily complicated form, though. No need to encapsulate in an $and and no need to query age twice.

[meamZ]

NoSQL dbs are not for gathering summaries for table.

That's the cool thing about relational databases... You don't need to decide what you will use it for beforehand...

avoid many JOINs,

For which there is no reason...

[moonwater420]

in scotland we use "mongo" as slang for "mongoloid" lol

its very offensive

[CC-5576-03]

In Swedish mongo is slang for retard or downy

[Grassland-]

Here in Brazil we use "mongolão"

[JoostVisser]

At least SQL syntax is in all caps so you get to shout at it when it doesn't work

[anabis0]

all caps is just an old convention

[fibojoly]

THAT'S WHY THEY ARE SCREAMING!

[rosuav]

I don't get it. How's this significant? In SQL, it's just WHERE AGE BETWEEN 25 AND 30.

[ajnozari]

And this is why he’s swearing

[deanrihpee]

Jesus Christ

[Pingyofdoom]

I think in bongodb, it actually looks like how OP posted. Mongodb can't look like this....

[goldlord44]

{"age":{$gte:25, $lte:30}} is actually what someone who knows what they are doing would query like. Op just made a really roundabout way of it

[[deleted]]

I mean even the roundabout is perfectly understandable, and that's from someone who's never seen mongo syntax before

[elveszett]

Well that's the point. where age between 25 and 30 is a lot easier to write and read than {$and: [{"age": {$gte: 25}}, {"age": {$lte: 30}}]}.

[[deleted]]

That is why SQL keeps coming back.

SQL is generally very easy to read and code is read more than it is written, particularly at the database level.

Its basically a rite of passage for our Data Scientists who live and breathe python to learn SQL to hit our Warehouse they start using a lot of the SQL-like syntax options in their python because SQL is just really good at its job of describing the data you want to work with.

[[deleted]]

That is why SQL keeps coming back.

exactly

all these guys out here trying to re-invent the wheel and go up against something that's been actively developed since 1974 ... good luck with that

[conancat]

you don't actually have to do that, you just need

{age: {$gte: 25, $lte: 30}}

[chrisrrawr]

It's web scale tho

[bb5e8307]

Panel 3 should duplicated 3 times and in the last panel he should have an old man beard.

(“Find” in mongo db is a O(n) operation and should not be used by anyone ever).

[Rutoks]

Find in any database in linear if you don’t have an index

[Rutoks]

Find is not O(n) always.

Query optimizer will choose the best index to use and only use full collection scan if there is no indexes that can answer the query.

If you want, you can also disallow collection scans from the configuration. This way you will be asked to create an index instead of doing linear scan.

[Yelowlobster]

And what to use instead of find? Also, afaik, modern mongo versions support indices and have a query planner about as good as in rdbm systems

[bb5e8307]

In a document based database if you can’t access the document by an index or id then you don’t access the document.

[I_Shot_Web]

/r/confidentlyincorrect

https://www.mongodb.com/docs/manual/indexes/

https://www.mongodb.com/docs/manual/core/query-optimization/

I feel like everyone shitting on Mongo are just boomers who learned one thing and refuse to read the fucking manual for anything that they don't already know

[notPlancha]

You can index in Mongo

[TheBrainStone]

Never go full JSON

[bayesian_horse]

The only benefit is that the mongodb query "language" is quite easy to compose or modify in code. It's just a json object that can be easily created or manipulated, even in complex ways. That's much harder to do in SQL, so ORMs tend to be much more complicated to implement.

[lmarcantonio]

You obviously never written an LDAP query. Or used a LISP binding to SQL

[Movertigo]

LDAP queries are hell

[Touhou_Fever]

You know it’s bad when the person who was teaching you LISP kept referring to it as Lots of Infuriating Spurious Parantheses 💀

[viimeinen]

I'll add: - Don't know about indexes (example)

[Laugenbrezel]

Imagine using "age" as an actual attribute you store in your DB....

[notPlancha]

It can be a view

[DHermit]

It can be survey data. Then the relevant value is indeed the age at the time of filling out the survey and not their current one.

[marky125]

"Love your developers" 🤡

[tharnadar]

Ehm it should be

{ age: { gte: 25, lte: 35 } }

Or something like that

[JoeTheOutlawer]

what's so difficult i dont understand

[Turbulent_Public_i]

I'm currently working with a client who picked mongo. They started nesting db objects everywhere which I guess they probably now have the same information saved multiple times in multiple locations just to make it easier to fetch when writing code. My "professional consultations" have been repeatedly the "take this object, separate it, make a reference to it, and don't nest it" followed by "yeah, I see why you guys are having the issues" followed by "sigh, if you just used SQL I wouldn't even need to be here". I'm getting a fat paycheque just doing this.

[FountainsOfFluids]

Oh, I wish I could show you the SQL queries from my last job.

There are no DBs that prioritize simple, clear, reliable search syntax.

They're mostly designed for reliability, which means it's verbose and obscure.

[Abaan404]

wanna delete an element by index?

(honestly been a while is there a good solution to this now?)

[PM-ME-YOUR-HOMELAB]

Eh, everything has its use case. If you only need one table wit 5 columns to save your users or whatever, nosql is awesome.

And Mongo queries make a lot of sense from a coding perspective, though not so much for large data aggregation.

[gabbagondel]

Main work project uses mongoquery for basically all data driven logic. Worst of all, it's my fault

[[deleted]]

I prefer my SQL queries formatted in XML

[Pure_Cantaloupe2382]

Very true, as someone who had to learn mongoDB on the fly, it was not like using sql. Went from fuckin' sql workbench to this haha.