Things I learned after getting users
basementcommunity.bearblog.dev
basementcommunity.bearblog.dev
I appreciate this honesty. Listen to this old man's advise: learn SQL properly. It's not that hard. Focus on it for a few weeks intensely and you've mastered it for life. Then just write SQL directly.
I've had weekends ruined troubleshooting my "highly productive ORM layer" that nuked a production database. Whilst functionally speaking my ORM code was in no way incorrect. I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls.
You can then become proficient in trying to reason and predict about what your ORM calls do in the actual database, but when you're several joins deep, this becomes near impossible. At which point you become the ORM, and might as well just write SQL.
Typically a single statement to get the job done for any query.
And the only team capable of zero-downtime schema changes uses a minimal DSL to SQL lib.
My experience here is quite limited, so I'm inclined to defer to yours. With that caveat, I find that ORMs play more nicely with my IDE (e.g. I can rename a column name and automatically update all call sites) and often raise early warnings if their declarative classes are out of sync with the underlying table schema.
[0] Django, SQLAlchemy, Active Record, Piccolo, Tortoise, SQLModel, peewee
[0]: https://hackage.haskell.org/package/persistent-2.14.5.0/docs...
Normally I’d prefer testing more isolated units, but if it’s your own data access, just write the integration tests. They’re at least implicitly part of your “unit” anyway.
That doesn’t sound like ORM… More like an N+1 problem. Eager-loading makes N+1 more likely with ORMs, but it’s easy to avoid when you know what to look for.
ORMs are designed to reduce querying, not increase it a thousand-fold :)
Even the notion of eager vs implied-lazy loading suggests N+1, just spread out over time. Granted that might be optimal for a whole lot of use cases! But it’s definitely not optimal for a use case where you need a join upfront and your ORM does it in memory.
Also granted many ORMs can handle this in a lot of general cases if you know how to use them, and know their limitations. But they’re inherently highly dynamic, and inevitably deoptimize for some cases where just querying the database directly will be much more effective. That’s not even an admonishment to “learn SQL” as is the common retort, it’s just the generic “abstractions are leaky and sometimes it’s better to bail out a layer or more”.
But we do always need a clean way to handle CRUD between apps and DBs, that ideally doesn't require custom calls for each and every data view. Here's the thing: Probably 90% of CRUD can be handled with generic updates and inserts. Raw JSON reads can be passed back to the client... let the client know how to cast or structure those to complex data types. The rest, that the server has to manipulate, can be cast / structured using the same classes the client uses, if in node. The actual meat of really complex reads or really optimized writes should always be exceptional and done by hand.
E.G: django "select_related"
Not to mention they will will have ways to use raw SQL through the lib infrastructure, which is still better than querying SQL manually, and give you full control of the query.
Also wordpress shows that you can write manual SQL and still have terrible performances because your application is badly structured, which an ORM helps with.
In the end, I don't think there is less trap with raw SQL, the traps are just different. It's not a matter of "better".
The raison d'etre for a micro ORM is to capture the results of that SQL query into a simple, flat collection of objects in a way that has a better UX than your framework's default behavior.
Micro ORMs are the perfect middle ground for debates like these.
- sensible ways of composing queries beyond direct string concatenation
- safe parametrization
- return results into usable data structures (not objects)
None of these things carry the problems of ORMs. They don’t impose a paradigm that isn’t SQL. They simply let you interface with SQL in an ergonomic manner.
Exactly the same is true of ORMs. I find most of the people who advocate "just use SQL" have a bizarre aversion to applying the same learning effort to their ORM.
> I've had weekends ruined troubleshooting my "highly productive ORM layer" that nuked a production database. Whilst functionally speaking my ORM code was in no way incorrect. I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls.
That kind of problem happens with regular SQL all the time, and the tools to test/investigate it are less available.
SQL is a lingua franca while an ORM is specific to a stack.
If you know that you will always, say, be using SQLAlchemy on Python, then learning it well might be a good investment, but I don't find learning multiple ORMs to be a good time investment over just learning SQL. Most every stack has good, lightweight query builders.
Oh, sure, instead of just learning SQL I'll just learn SQL, the ORM, the ORM's weird edge case features that actually support the SQL I want, the undocumented ORM internals that prevent the good query from actually being generated, and then I'll commit a patch to the open source project to fix the undocumented internals and shepherd a custom dependency for 6 months while it gets into a numbered release. Then I'll do it all over again when I switch stacks and have to learn a completely new ORM.
Or I could just use SQL.
ORMs are much less bad than SQL at that, IME. If the ORM generated a particular query there is usually documentation for why, often an option you can change. If the SQL engine decided not to use the right index for this query... tough, there's literally nothing you can do.
I'm glad you found an ORM you like, but it seems we have very different experiences with ORMs.
MySQL Index Hints: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
MariaDB Index Hints: https://mariadb.com/kb/en/use-index/
PostgreSQL: No. More information: https://stackoverflow.com/questions/309786/how-do-i-force-po...
SQL Server INDEX hint: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...
Oracle INDEX Hint: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_e...
Of course, knowing exactly what is wrong and what hint you need to use (whether the issue is the index in particular or something else) is also a bit of work to figure out.
I recall working on a project that had Oracle as the RDBMS of choice and there was this one query that took approx. 45 minutes to execute, which wasn't acceptable. Merely a SELECT that JOINed a few tables and checked the data with some EXISTS constructions and such. If memory serves me right, looking through the AWR reports and adding a single NO_EXPAND hint made the query execute in not 45 minutes but around 3 seconds.
Of course, ORMs don't necessarily solve the aforementioned types of problems either, since it's still SQL statements being executed under the hood and figuring out how to add hints to those might be a bit more challenging. I've had cases where you need to drop down to native query level when using ORMs for particular queries, because there were no elegant ways of doing it otherwise (apart from creating a DB view and mapping against that in some cases, for read only access).
I think both using ORMs and using SQL directly suck, just in their own unique ways. That said, I wouldn't outright dismiss either: depending on what you're doing, one or the other is going to be a good enough tool for the job. You occasionally also see some pretty interesting projects in the ORM space, which is nice.
For example, jOOQ allows you to use a type safe fluent API for constructing your queries: https://www.jooq.org/
Oh and MyBatis let's you generate your own SQL dynamically, which is an interesting approach: https://mybatis.org/mybatis-3/dynamic-sql.html
Learning straight SQL is far more transferable across databases than learning an ORM.
There are reasons to have ORMs, for e.g. to keep a large code base consistent. It's useful for keeping relatively simple queries consistent across an ERP system, stuff like that.
However, learning SQL is going to have a far higher payoff across your career.
There were other problems with the ORM approach -- mostly that it ran tens or hundreds of extra queries for relatively straightforward SQL queries. But the software was only used by a few people at a time, so the performance wasn't that big of a deal.
Not my experience. For example a recursive/tree query usually has one way to do it in an ORM, but three or four different ways to express it in SQL depending on the database, some of which seem like they're wilfully screwing you over (one popular database requires you to only write "WITH" and won't recognise "WITH RECURSIVE"; another requires you to write "WITH RECURSIVE", even though the error message it gives you when you only write "WITH" tells you that it knew exactly what you meant).
Well, no, it doesn't. I told you to learn SQL properly. When you do, you know what a query plan is. Which is something you'd rarely need if you know how to design a database and its indices.
All of this would be covered in every beginner's book.
Meanwhile if you'd applied the same "learning it properly" standard to your ORM you would never have had the problem you described.
> Listen to this old man's advise: learn SQL properly. It's not that hard.
I couldn't agree more with the first half, or disagree more with the second :) Once you're operating at scale, it takes a lot of fine-tuning, know-how, experimentation, and reading docs to get things moving efficiently.
In terms of schemas, where you put your indexes matters a TON. Indexes make reads faster and writes slower (to oversimplify).
Figuring out where to break out a separate table vs keep everything in one table is also a common issue.
Also, with lateral joins and windows in mysql 8 you can really have control over whether you prefer your execution plan to loop or scan. Which takes away most of the rest of the argument for appside processing.
* your database is the core of your app. Abstraction implies that you don't care about the details of your data store, which eventually will lead to two major problems:
1. When things are slow you won't be able to debug it, because you're abstracted away your database and don't understand what queries your ORM is using to do things, and
2. when your ORM migration fails you won't know what to do, because you don't understand your database. In general this happens with every ORM product, ever, because you didn't put in a constraint initially because you didn't know what that meant and then you did later, which caused the migration to fail because your constraint is being violated. Or the migration fails because the tool uses some attribute and not others for drift detection, so it tries to delete your table/database because it's detected drift.
* the way you deal with data in code and in a database are different. Code iterates over objects. If you just use your ORM naively you'll end up doing some ridiculous number of selects because in code it looks like you're just iterating over objects...when in reality the ORM is doing a select/join for each one of those objects. And if you start getting more complicated there are structures that are just harder to do with ORMs...because you have to map what you're trying to do in SQL to the way your ORM works. At that point why not ditch your ORM?
SQL isn't really that hard...but thinking about how do queries is harder than you would expect. It's almost an order of magnitude faster to do joins in the database than to join stuff in-code...but that also assumes that your schema isn't screwed up, it has indexes, etc.
Lastly, a lot of ORMs don't put indexes on columns for some reason, which kills performance. You'd think that would be a detail that would be abstracted away for you at the ORM level, but it isn't. I mean it knows what you're using to select data, so it should auto-create indexes for you, right?
Curious, how does it know what you're using to select data?
I'll give you an example: prisma has a "find/findMany." As part of the build process it should examine your usage and put indexes in the column that you're using in find/findMany
Indexes are an implementation detail - but to use RDBMSs successfully you need indexes. ORMs try to hide those implementation details, but without those kinds of details you get bad performance.
Prisma just fixed another problem it had with transaction isolation levels. To even understand the problem you need to understand SQL, how databases work, and how each database implements that. It's another important implementation detail that's abstracted away...until it bites you in the ass.
Prisma also finally allowed you to use database-specific types instead of just using varchar(191), which was its default for strings.
I'm picking on prisma because I happen to be using it right now. There are good things about it, like the way it does migrations and handles schema changes. But there are times its abstractions get in the way, like when you're trying to do aggregated group by (which is easier in SQL than it is in prisma). And its migrations often fail, and to understand why you really need to understand SQL.
Hibernate was worse, because it provides a transparent object-level backing store.
This is not as easy as it sounds, though. Reliably inspecting source code is hard, and definitely not in the scope of an ORM. It also can't catch dynamically generated queries (think user configurable filters). It might be a good idea for a third party product that analyses your code and suggest adding indexes to models.
You know who knows best about your queries? It's the RDBMS. There are tools that can add/suggest indexes by searching your database logs for slow running queries.
Ad1: People do debug ORMs, actually. Including queries effectivity.
Hibernate and Prisma migrations will delete tables if you allow them to.
Ad1 -> I'm sure they do...but usually they call in a DBA, who basically says "this is a disaster" then recommends that they refactor everything because their database schema is fucked up because it's based on some weird object hierarchy instead of RDBMS principle.
Fifth, spring has nothing to do with database whatsoever. Sixth, every production code I have ever seen used flyway.
I've been on projects where the team has re-invented an ORM organically, and I've never seen it go well. Likewise with projects that are ORM 'purest', bending themselves over backwards to use an ORM for a query that can't easily be represented by whatever query syntax it has.
This is why I still think 'lite' weight ORMs are the best of both worlds since they usually have a pretty good experience for common/easy queries but then when things get tricky, it is best to just use SQL and map to an app language data structure for the results. I mostly use OrmLite [0] in dotnet and have found it has a balance that done well by me for years (note I now work at ServiceStack who built OrmLite).
[0] https://docs.servicestack.net/ormlite/ormlite-apis#query-exa...
site creator here - yeah this is the approach i'm taking now. the ORM is useful for sure and there's still a benefit to using it, but anything that needs to read from a few different tables, i'm definitely going with raw SQL
Writing queries is easy part. Maintaining that thing is the hard part.
Even if I'd run a database operation just on a local machine and it takes 100x more load than an optimized SQL query, I still care. It's craftsmanship.
Sounds like you may have a case of the abstraction disease. You keep reaching for abstractions that never accomplish the actual benefits of a truly solid abstraction.
You can have database schemas, and you can have software schemas, and they don't necessarily need to coincide as long as your database schema allows efficient queries. The rest of your relational-manager-logic can be embedded in the post-processing methods you apply to the query results.
[1]: https://guides.rubyonrails.org/v3.2/active_record_querying.h...
it's sad that people like this exist in the world. what could possibly motivate someone to spend their time doing this?
It seems like parasocial relationships can swing both ways. You know how some fans develop a creepy, obsessive sort of love for creators? Well, the same goes for hatred. They feel slighted by that person that doesn't know them, and they retaliate from behind their keyboard.
[1]https://en.wikipedia.org/wiki/Bartle_taxonomy_of_player_type...
Probably being between the ages of 10-14 years old. Bartle Killer-explorer?
It also left me wondering why that person would spend an hour or two each day for several days in a row, filling out online forms. What’s the motivation?
My suspicion is that the person doing it works at the company and was trying to mess with their systems. But I’ll never know for sure.
First time I bumped into this was in the DAOC MMORPG. Some players were deliberately annoying others, to the point of "wasting" their online time doing stupid shit that annoyed people. It really shocked me.
Thing is, there's lots of griefers in any online game. Which means there's lots of people out there in the real world who would do this if they could get away with it. Anonymity allows them to do this online with very little repercussion, so it shows how many there really are. But now every time I do an interview, or look at a rental, I'm thinking "is this person a griefer? are they going to enjoy making my life a misery?"
> (That's life)
> And as funny as it may seem
> Some people get their kicks
> Stomping on a dream
> But I don't let it, let it get me down
> Cause this fine old world, it keeps spinnin' around
- That’s Life
By Dean Kay and Kelly Gordon. Most famously recorded by Frank Sinatra.
safety is the hard part of ugc products but is left to figure out after scale and ossification.
But some quick references... One approach is to avoid algorithmic surfacing of UGC outside of one's own network (or secondary connections etc), which makes discovery harder (must be compensated in other ways). Twitter may explore similar ideas with pluggable algorithms (though I don't trust them). Another approach is to constrain UGC: eg a music/audio community which has barriers to sharing new audio outside your network, but which freely allows remixing and promoting remixed audio of known good audio without as much safety control over the remixes because the operations allowed on the "good" source material make it difficult to subvert. This kind of idea is at the core of a product concept, not an additional layer to tack on later. I believe that finding these kind of cheaper ways of managing UGC and lending discovery to UGC can be a huge competitive edge.
To me all position:fixed elements (headers, footers, this back-to-top button, etc) feel like a kind of annoying dirt on the screen. Their absence is a big part of why I love the web 1.0 aesthetic.
I'll start collecting data on its use, because people on the orangey site (including me) tend to have opinions that don't represent the average user.
I think it's a really good feature, but the Wikipedia implementation needs work.
1. CMS sites are constant maintenance, as most are an endless supply of issues. However, some have content caching to reduce the SQL workload.
2. Delayed registration with CAPTCHA and a brief explanation of why you are there. Quiet banning IP filter applied to list to boot pending users who enter emails that bonce or fail to authenticate.
3. Firewall blacklist areas of the world where you don't do business (better yet, whitelist the ISPs in the regions you do business), blacklist proxy/tor/spam IP ranges, add port tripwires, and setup rate limited traffic per IP (see slow loris mitigation methods if you are not using nginx).
4. add peer site content blocker for forum spammers/bots i.e. share exploit probes preemptively with the rest of the net.
5. add email filter for mention of bitcoin/BTC, and black-hole the entire IP block if in an irrelevant region.
6. lookup same-origin enforcement for your web-server, add Subresource Integrity Hash to your core, and re-scale/watermark/scrub all media to protect users from themselves.
7. fail2ban rules for common site security scanners, known exploit attempts, and common email scams.
You owe nonpaying users nothing, so the collateral cost of blanket bans is $0 in hostile regions. Remote traffic monitoring is also recommended if you have a game engine running.
On day 2 we can look at how BTC tumblers/launderers fund most of these issues, and whether it is OK to also preemptively blanket-ban most cloud/hosting providers (costs under 7% of your users in most cases). Remember, adversaries will often pretend to be from wherever they wish to inflict harm, and time does not have an associated cost in the 3rd world.
Have a gloriously wonderful day =)
Specifics preclude the multifaceted nature of the policy.
https://www.youtube.com/watch?v=cJMwBwFj5nQ
Happy computing =)
This is basically every game or internet forum that acquires even a little popularity: there will be some (few) people who just wanna ruin everything, and I'm always surprised by how many people are surprised by this even when they're the technically literate sort.
For example, some Japanese fighting game devs still try to count disconnects during a match as different from losses for someone's record. One guess as to what this encourages as far as player behavior goes.
⸻
1. Basically a set of really obvious questions, like “Who wrote Hamlet?” and what’s “Shakespeare’s first name?” that any writer (for whom the site is targeted) should be able to answer.
Given that ChatGPT exists now, I assume these questions will need to be replaced with something harder to automate.
If you cache the answers you're probably looking at 10 queries or so until the site admin gives up on that idea and tries something different
Years ago I heard of a simple anti-spam technique where you add extra form fields to a web form. Then use CSS to make those fields invisible. Put a check in your backend where if you see any content in those form fields, you respond with 200 OK but ignore the request.
The programmer in me can immediately think of 10 ways to get around that - the most obvious being to fill in spam using a real web browser, automated via webdriver or something. But apparently that one trick removed ~95% of spam on their site.
Sir Francis Bacon :smirk:
hey now, not all writers who discuss things in English are necessarily familiar with the anglo literary tradition
(it's probably a higher overlap than average, and shouldn't be too hard for them to search, but be careful throwing the assumption around)
So true. My products have improved greatly from listening to (some!) user feedback.
If you're going to solicit feedback, just dump it in an ideas bucket, no need to reply, certainly don't funnel it through the support channel for bugs/questions.
From a user’s perspective there is typically a tipping point for a thing that becomes popular enough where user feedback becomes useless, superficial, lowest common denominator crap, which doesn’t understand the value prop, the quality standards and the implications of change vs stability.
I believe this type of feedback often bubbles up for similar reasons bikeshedding can become a problem, which is then perpetuating through social media.
At this point one needs a filter.
Would gating access with Google Sign-in, or Facebook sign-in, etc, be sufficient for rate limiting bad actors?
Beyond VPNs, I've even seen attackers leverage residential IP networks which makes VPN detection ineffective as well [1]. If you ever need a more permanent identifier to ban users on, consider using a device/browser fingerprinting tool [2]. It helps avoid the whack-a-mole issue of more sophisticated attackers churning IPs/emails/user agents/etc.
[1] https://brightdata.com/proxy-types/residential-proxies [2] https://stytch.com/products/device-fingerprinting (I'm admittedly biased towards our solution as I work at Stytch)
1 check your fingerprint details here: https://coveryourtracks.eff.org/
If you are good about squashing errors you can make it very far on the free plan. Plus they have some burst detection built in. Just make sure that "expected" errors aren't just ignored in the UI, stop emitting them in the app itself so they don't count towards your quota (and it keeps your logs tidy).
I haven't been using their tracing or anything because their Rust SDK doesn't seem to support it despite claiming that it does (or I have set it up wrong).
also for errors from the BE and FE
Blocklist if you absolutely have to.
"denylist" is an abomination.
Oh I see, a goon.