Raw SQL, SQL Query Builder, or ORM?

  Рет қаралды 106,841

ArjanCodes

ArjanCodes

Күн бұрын

Пікірлер: 337
@ArjanCodes
@ArjanCodes Жыл бұрын
👷 Join the FREE Code Diagnosis Workshop to help you review code more effectively using my 3-Factor Diagnosis Framework: www.arjancodes.com/diagnosis
@AggyLovesGames
@AggyLovesGames Жыл бұрын
I'm a data engineer. I almost always use raw sql because I can more explicitly optimize queries
@mamneo2
@mamneo2 Жыл бұрын
Incroyable.
@piyushagarwal8330
@piyushagarwal8330 Жыл бұрын
Same, for my de work, it's better to write it once and use it multiple times with better efficiency for specific job
@aleksander3000
@aleksander3000 Жыл бұрын
DS/DA same
@dansplain2393
@dansplain2393 Жыл бұрын
I don’t even optimise the queries. I can just run them in my head faster.
@ryanbowns1517
@ryanbowns1517 Жыл бұрын
Data Scientist here, Same I Prefer to use raw sql. I find Case statements within a window statement to be extremely useful
@kurt7020
@kurt7020 Жыл бұрын
Almost always raw sql. Here's why: - I know exactly what I'm getting. - The query can be run completely independent from my app. - The query can be shared between multiple apps. - I can always find a DBA who can read raw sql. - I never have have to debug an abstraction for corner cases. - Every single advanced feature of my database is supported and works exactly as documented. - If I use a different language on a new project, my SQL knowledge is still valid.
@valcron-1000
@valcron-1000 Жыл бұрын
Amen. Also, if you're using a language with compile time code execution there might be libraries available that check that your SQL code is valid
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
Another thing is, there are no language-independent ORMs. I do some stuff in PHP also, so I have to learn SQL anyway, I might as well do it properly.
@taehyun-lee
@taehyun-lee Жыл бұрын
Just noobs curious, don't you worry about a security such as a SQL injection?
@taehyun-lee
@taehyun-lee Жыл бұрын
I heard that it would be better to control the security part easily with using ORM while programmers need to think lots of things with the raw SQL for the security.
@ThanatosCG
@ThanatosCG Жыл бұрын
+1. "- The query can be run completely independent from my app." is what really took the cake for me. It allows for food system abstraction where the database creation and its usage are well separated.
@hirolau
@hirolau Жыл бұрын
My databases are typically complex. Never found abstractions around the SQL helped me. Just going raw SQL
@hirolau
@hirolau Жыл бұрын
With that said I have now got the task to build a DAL which generates a consistent way of extracting data from 15 tables. Sql-alchemy core has actually helped me a lot creating some of the dynamic parts of the queries.
@strikef7976
@strikef7976 Жыл бұрын
Most of the tech debt I’ve come across has been caused by orm generated queries. It gets to the point where at scale you just have to use raw sql or you’ll be dealing with contention. Soft deletes, inserts are all a nightmare via ORMs. There are instances where an ORM will come in handy, such as building something quick, but it’s important to know the caveats of doing so; mainly that you’ll be hiring a sql person to refactor your ORM queries as you scale.
@bla7091
@bla7091 4 ай бұрын
I'm in the raw SQL camp. Only one feature makes me doubt it sometimes: Data validation that can be built into the ORM through pydantic. If not for that, I wouldn't quite see the point in abstracting away the SQL.
@miquelgonzalezrossello3940
@miquelgonzalezrossello3940 Жыл бұрын
Thanks for the video Arjan! It's always advisable to close the connection when you're finished querying.
@FlisB
@FlisB Жыл бұрын
I like SQLalchemy, but I think raw SQL is a very useful skill to master, it is been around for ages and it will be around in the future as well.
@rrestituti
@rrestituti Жыл бұрын
I'm a data cientist and I use raw SQL. Now I am writing a internet app for my wife's company and deciding which approach I must use. And with your video I decided: raw SQL. ORM can't use all the potencials that a SGDB could give. Thanks for the video.
@g.c955
@g.c955 Жыл бұрын
Unless you know SQL well, using the other two would be a mistake. But to be honest, ORM is something you'll regret down the road when you scale. Raw SQL is what I recommend, it'll scale well and it's something you need to learn anyway.
@wp_panther
@wp_panther Жыл бұрын
Subscribed for the smoke-signal, sign-language, and Binary Clown-based SQL queries. glad I finally found a channel paying attention to the bleeding edge of clownbit accessibility 🙏
@ArjanCodes
@ArjanCodes Жыл бұрын
Thank you!
@manonthedollar
@manonthedollar Жыл бұрын
Really great topic! I have experimented with ORMs but I just keep going back to raw SQL. I know exactly what's going on in my queries, I can get really nitty-gritty with optimizations, and it works with any language. It certainly can get rocky "manually" handling the mapping between python objects and their representations in a database, but there's no perfect solution for that I guess.
@GrattonB
@GrattonB Жыл бұрын
Honestly using pandas "pd.read_sql_query()" function will give you a lovely dataframe while still allowing you to write raw queries.
@SaniSensei
@SaniSensei Жыл бұрын
Note that, if you use e.g. Mapped[str] = mapped_column(...) - the nullability is also derived from the Mapped type. Mapped[str] would be NOT NULL in your cases, and you need either Mapped[Optional[str]] or Mapped[str | None] to allow NULL (or I believe set nullable=True explicitly)
@Part-Time-Larry
@Part-Time-Larry Жыл бұрын
I've only ever used an ORM when I first started out writing c# with entity framework. It was really good to be honest, but I moved to nodejs and for whatever reason I decided to go raw SQL and honestly haven't noticed any productivity loss. Thinking in SQL is actually very easy. It's algebraic after all.
@SeRoShadow
@SeRoShadow Жыл бұрын
I use raw SQL as functions with typed obj params. Using some custom helper functions, the SQL is compiled and executed. I dont use ORMs or "?" args though since I find them unreliable.
@radupopa6642
@radupopa6642 Жыл бұрын
I've always preferred the raw SQL. I need to know it anyway, to find my way through the database, review and validate the data, etc. My approach, most of the time, is to design and test the SQL on its own, in a SQL editor. After that I integrate it in the client code, with the confidence that the SQL works.
@vhphan19
@vhphan19 Жыл бұрын
I almost always use raw SQL. And when I do, I use the Jetbrains IDE, such as Pycharm Professional (not community version). It has such great support for database which amongst other things: 1) SQL syntax highlighting and SQL code completion from within the .py file 2) Ability to run SQL query from any SQL text string in the .py file directly to the query console 3) Provide ability to jump to SQL table or views definitions from python code... I do love VS Code. But as much as I love VS code, when it comes to any Python code that I need to use raw SQL, Jetbrains IDEs just blows VS Code out of the water. But of course, the con is it is not free :-)
@markippo
@markippo Жыл бұрын
DataGrip... The only good thing that I could say about it... It's JetBrains - it looks like JetBrains, integrates with JetBrains - that's all. It's laggy, underperforming, full of bugs (it cannot even parse Sql Server structure properly). Putting it behind a pay wall is a big "no no" for me. Dbeaver community is free and does its job far more better...
@bernhardkrickl5197
@bernhardkrickl5197 Жыл бұрын
The main problem with ORM is when you fall into the trap of stopping to think about and harness the power of the relational database model. You limit your possibilities and don't even notice. And I'm not talking about complicated queries here. Eg. with an ORM you can easily load big, deeply nested data from the DB into an Object. This is nice but can produce dramatic performance issues. This is not hypothetical. Been there, done that, didn't like the t-shirt.
@TonyBokas
@TonyBokas 9 ай бұрын
Once you enter a project that uses ORM, you realize you have entered hell.
@edgeeffect
@edgeeffect Жыл бұрын
A lot of people assume that the sole purpose of an ORM is to provide an abstraction over SQL. But the important thing about an ORM is that it maps result sets into objects. ORMs often (always?) do provide that abstraction layer over SQL but where we're often dealing with APIs that are expected to return result sets in JSON, it's the object mapping that's their most important function. Some ORMs take that "Object Mapping" idea a little to literally and only map data into "strict OOP" objects with some kind of class definition but often all we actually need is for the result set to be returned in a collection of nested dictionaries.
@vlntsolo
@vlntsolo Жыл бұрын
Have a look at SQLModel from FastAPI creator Sebastián Ramírez.
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
ORMs don’t make any sense to me. The code doesn’t end up any simpler.
@stoched
@stoched Жыл бұрын
Yes this is correct. The main benefit is mapping them to objects. The full on builder pattern stuff for sql really comes into play in a massive project and large databases with complicated relations. For the most part you only need a micro ORM
@dawidp749
@dawidp749 Жыл бұрын
@@lawrencedoliveiro9104 xD
@ER-oj8mo
@ER-oj8mo Жыл бұрын
@@lawrencedoliveiro9104 just wait til you’re writing queries that require data from 5+ tables, complex triggers, very specific repetitive logic, etc. SQLAlchemy 2.0’s ORM module is amazing 😊
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
8:02 DBMSes themselves usually provide some kind of introspection facilities, though this varies a lot. For example, SQLite provides enough information that you should be able to programmatically derive the entire database schema without having to parse actual SQL, while MySQL/MariaDB is a bit more limited. The question is: do ORMs make use of this? Doesn’t seem like it.
@igordemetriusalencar5861
@igordemetriusalencar5861 Жыл бұрын
Awesome video, as always but more I lean Python more I Like R functional way to work with data. I use raw SQL always.
@cyber4joy
@cyber4joy Жыл бұрын
I always prefer to use raw SQL, and I have been doing SQL for 30-plus years.
@AlexeiCheban
@AlexeiCheban Жыл бұрын
You can add --sql in start line and vscode will be show pretty sql query
@TannerBarcelos
@TannerBarcelos Жыл бұрын
While I like ORMs because they get you going fast on projects when you aren’t really interested in how the underlying queries are written / work, I’m in the camp of using raw sql and the native drivers for the languages I use (Java, Python or TS). It’s great because you can continuously practice your sql skills while writing app code, you can get full control over the sql and functionality and you can optimize right within the implementation. Plus writing sql is fun.
@bryan_hiebert
@bryan_hiebert Жыл бұрын
I love taking a break, watching your channel and reading the comments. I think my brain grows every time.😂. I really like the implicit handling of transactions using ORM with SQLAlchemy. I think it just makes the code cleaner when working with tables that are unlikely to change. But when doing machine learning or exploratory data analysis the flexibility of SQL would certainly be the way to go.
@CatMeowMeow
@CatMeowMeow 5 ай бұрын
I use raw sql statements because my ide connects to my database and gives me auto completion on sql statements anyways. Although, always having to convert lists of tuples into something usable is a bit of a pain.
@theinternetcommoner
@theinternetcommoner 10 ай бұрын
raw SQL is my personal favorite
@N8ontv
@N8ontv Жыл бұрын
I prefer raw queries. They allow for better optimization, and the SQL engine you use is far less likely to change than the programming language you access it from. ORMs kinda bear additional overhead in that you need to define your schema twice(in you mappers and in your migration scripts), while the other two only need it defined in one place. Also, no matter which you pick you're still learning and executing all of the same SQL concepts. I've kinda landed in the camp that the only scenarios in which an ORM is being used effectively are also scenarios where you don't actually need an ORM.
@CharlieSmithv2
@CharlieSmithv2 Жыл бұрын
Would be interested in a part 2 where you give your thoughts on which to use given the application use case. My understanding is an ORM can help with a multi-user application like a Web frontend, where throttling to the DB might be necessary. In either event, it seems you need to know the SQL query you need (obviously) so would be a stretch to go above and beyond unless for performance or security. Thank you!
@Ziggity
@Ziggity Жыл бұрын
Excellent content as always. Worth mentioning that ORMs might perform worse in some cases. And it's still very important to know SQL because you know.... Sometimes the abstraction magic doesn't work and you gotta look under the hood.
@magfal
@magfal Жыл бұрын
The some is a bad choice of word. The absolute majority of anything beyond the absolute basics is what my experience tells me. I've lost count of the times I've replaced ORM code with a few well reasoned lines of SQL and improved performance by 3 orders of magnitude.
@zoladkow
@zoladkow Жыл бұрын
@@magfal yup, the more abstraction an ORM has, then anything more complex than basic CRUD gets absurdly complex if not plain simply impossible... luckily there are workarounds like using custom native SQL then just mapping the results, but then you have a mix of structured and freetext anyway...
@magfal
@magfal Жыл бұрын
@@zoladkow dapper is the least shitty application layer integration of an SQL server I've used.
@newmonengineering
@newmonengineering Жыл бұрын
Definitely at least know how to use Raw SQL if needed. As a Database Admin I can say, there are times when the generated SQL from any option other than RAW is less than optimal. One thing i do is look for SQL statements that use a ton of resources and suggest ways to make them better. An example i had a few years ago, the sql sent to the database by some auto generated tool was 7 pages printed out. I printed the SQL and used a pen and highlighter and rewrote the SQL to 3 pages. The one i wrote ran in about 1 second, the one the app sent ran for 2+ hours. I gave the developer the new sql statement and he was confused as to how to fix his application. Eventually we found another developer to help him fix it. This kind of thing happens on large complex systems all the time. Its nit really an issue with small simple queries but at least know how to do this, you never know when it may be needed.
@zammea
@zammea Жыл бұрын
Hey Arjan, I started learning programing with Python, curently I work as SQL Developer and more spesificaly Transact-SQL (Microsoft SQL Server). I loved the power of ORM when I was learing to code with Python and Django. However now after I worked for so long with raw SQL I can honestly say there are some stuff that the database does that ORM can't, for example, reating indexes, stored procedures, optimising execuition palans and so on. Overall I believe even with ORM you can leverage it even more when you give a go learing sql database and how it work. Thanks for the video it was really cool to see.
@dawidp749
@dawidp749 Жыл бұрын
I dont know about the rest but you are able to manage indexes in orm
@zammea
@zammea Жыл бұрын
@@dawidp749 indeed you can. Why argument was that SQL and DB administration is a good skill to have.
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
12:50 I deal with that in one of two ways: * For some projects, there will be a schema file containing the requisite CREATE TABLE statements, included in the source tree and version-controlled along with everything else. * For other projects, particularly ones using SQLite, the program itself will automatically create the necessary tables each time it is asked to create a new database, so the CREATE TABLE statements will be in the program source code.
@TomTrval
@TomTrval Жыл бұрын
Hi Arjan thx for fine video. SQLAlchemy has architecture is onion layered, so you can use Query Builder in sqlalchemy it is called Core. I usually use it with custom python slot-ed dataclasses. I like the core does not change so often as ORM layer and provides: - query flexibility (you can always fall back to text query) - good protection against vulnerabilities - tables definition and create_all metadata ( DataDefinitionLanguage )
@nutbusted27
@nutbusted27 Жыл бұрын
Yeah was about to comment this, we use the core (or sql expression as we call it) and its a nice middleground.
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
6:16 Just a note that splitting things out into separate files will slow down loading, compared to having it all in the same file.
@GuyWicks
@GuyWicks Жыл бұрын
Have you measured this? FWIW, the OS is VERY good at caching recently opened files, and it's not as if you're having to wait for spinning disks anymore. So the overhead is really fractional, when compared to the time taken in the SQL layer anyway.
@mariuszp1649
@mariuszp1649 Жыл бұрын
Even better is to create views and functions in sql database.
@fernandino1909
@fernandino1909 Жыл бұрын
I am trying to learn sqlalchemy but that's really hard. Hey I just found out about something called codon. You could do a video on that, the differences and the multithreading. That looks dope. Love your content!!!
@amitnakash1642
@amitnakash1642 Жыл бұрын
Love the channel Arjan! So grateful for your content! keep going!
@DevlogBill
@DevlogBill Жыл бұрын
Hi Arjan, about a year ago I was learning SQL with PostgreSQL and eventually MySQL. My first programming language was SQL before jumping onto python. My SQL is a bit rusty now I am learning Django's ORM and I must say personally I prefer raw SQL versus the ORM. The ORM doesn't feel as powerful as SQL from MySQL or POSTGRESQL. Now the question is, how can I use a SQL database on my Django project versus using an ORM? I would prefer to open up the MySQL and creating the database, tables and rows myself versus using an ORM for my project, but I haven't figured it out yet. Thanks for the video.
@NunoFerreiraX
@NunoFerreiraX 17 сағат бұрын
Raw SQL. I have over 35 years experience. There are lots of reasons why ORM and builders are bad. I'll enumerate a few. * Readability * Context * Transactional control * Concurrency control ORM and builders might be good for CRUD or reporting, but for serious coding they're a liability.
@KLM1107
@KLM1107 Жыл бұрын
I'd definitely stick with an ORM. For databases which are changing in the development phase you can use mirroring to have the ORM pull it's shape directly from the tables it's accessing. Also, if you do run into a very tricky corner case where you need to construct a raw SQL query you can send that with the ORM, or you could create a view in the database itself and point the ORM at that.
@sachinfulsunge9977
@sachinfulsunge9977 Жыл бұрын
Can you explain to me what mirroring is and how we achieve it in sqlalchemy ?
@danielmcpherson9062
@danielmcpherson9062 Жыл бұрын
@@sachinfulsunge9977when you use an ORM, you know how you have to specify the tables and the columns you have in your database? Well ORMs can do that for you - they look at the database and create classes that “mirror” the tables and columns. I don’t know about SQLAlchemy, but if you ever use Prisma, then you would run: prisma db pull (You may need to add “npx” or whatever package manager you’re using before the command)
@dschledermann
@dschledermann Ай бұрын
My database is using SQL, so I'm using SQL when talking to it. I've too often been in a situation where I knew exactly what to do in SQL, but the dumb, annoying, stupid, broken, limited or otherwise deficient query builder or ORM prevented me in doing what I wanted. i have a colleague who absolutely love these query crutches and his code is always more lines of code, it can't executed outside the program for debugging and sometimes it's actually complicated to see what the thing is doing. I hate it so much. For the love of all that is good and holy, please, please use SQL when you're talking to a SQL server.
@bn_ln
@bn_ln Жыл бұрын
Curious to know from those using ORMs if mapped columns are common
@innstikk
@innstikk Жыл бұрын
I like Sqlalchemy as it gives you ORM and Core so you can choose to do SQL with Core if you need it for something more complex than is practical with the ORM.
@marioluizbernardinelli2659
@marioluizbernardinelli2659 Жыл бұрын
Hi, Arjan. Nice video. I have a suggestion for an another video: SQL and NoSQL comparison. Think about this. Thank you.
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
I don’t think there’s such a thing as “NoSQL” any more. Even the non-relational DBMSes are adopting forms of SQL for their query languages.
@davidec3240
@davidec3240 Жыл бұрын
NoSql are dead, use memcached to cache, redis for pub/sub or queue... Put everything else in a table, inside a sql db
@123344556kk
@123344556kk Жыл бұрын
I was looking for a query builder and your video helped a lot in giving me a solution. Thank you.
@ArjanCodes
@ArjanCodes Жыл бұрын
Glad it was helpful!
@ranjansingh9972
@ranjansingh9972 Жыл бұрын
I guess what I'm curious about is what is the right way to persist Python objects to DB so that your applications state is sensibly captured? And then on the flip-side, how should application objects stored in DB be sensibly retrieved?
@MedievalChips
@MedievalChips Жыл бұрын
I've been struggling with this dilemma for quite some time now. Glad you made a video about it !
@ArjanCodes
@ArjanCodes Жыл бұрын
Glad it was helpful!
@ssarkar2996
@ssarkar2996 Жыл бұрын
Can you use SQL analytical functions with ORM?
@Prakhar79
@Prakhar79 Жыл бұрын
Great content Arjan and team! Can you make something for DynamoDB? Do you recommend any libraries for that?
@yomajo
@yomajo Жыл бұрын
I don't understand difference between: id:int = Column(Integer(), primary_key=True, nullable=False) id:Mapped[int] = mapped_column(Integer(), primary_key=True, nullable=False) chatgpt is vague here. Anyone?
@originalmianos
@originalmianos Жыл бұрын
30 years of writing SQL. An ORM such as sqlalchemy gives you the ability to compose SQL in parts. I usually write the sql and then build the sqlalchemy. This is not really 'object relational mapping' but more of an abstraction. A lot of the time my query is using the metadata and does not even have a class for the table.
@user-qr4jf4tv2x
@user-qr4jf4tv2x Жыл бұрын
if you are new learn SQL skip the BS of ORM the only safe way i find ORM is for basic queries that don't join , if you start putting queries in for loop in your application its time to learn proper sql or just skip the BS and learn SQL
@cfn3107
@cfn3107 Жыл бұрын
raw sql and prepared statements
@JosephMcMurray1984
@JosephMcMurray1984 Жыл бұрын
Why I prefer raw sql over ORM's is because the things you learn from raw sql can translate over quite easily to other sql type languages. Whereas, with ORM's, going from one ORM to another is not as simple. This is even more of an issue if you work with multiple programming languages on the backend as they will have their own ORM's you need to learn as well. Raw SQL does not have this issue. Other advantages are sometimes the queries it generates are not the most efficient. Even some of the methods you use don't do what you expect them to. Prisma was one that messed me up. ORM's may also not have all the features that the sql language provides due to its complexity, maturity in the ecosystem or simply because the feature itself is very new and not yet supported.
@ZaItan1
@ZaItan1 Жыл бұрын
As a data analytics engineer, I personally use raw SQL most often to keep queries portable between apps and people, and to make optimization easier. Something which always bothers me: SQL is great, but it's sometimes not as testable as something like PySpark Dataframe API or functions built on ORM. There is value in specifying a query plan in chunks that may be isolated and re-used...though it doesn't always work out as nicely. SQL tends to demand more integration testing, which is fine too.
@BrianStDenis-pj1tq
@BrianStDenis-pj1tq Жыл бұрын
The fancier of a helper layer you use, like an ORM being fancier than query builder, the greater the risk that some security or other lacking feature bites you in the future. I see many older code bases having trouble due to unsupported or difficult to upgrade helper layers.
@oprea1991
@oprea1991 Жыл бұрын
Can you do sql Injection with the case you shown at the beginning? I think sqlite3 supports one operation per cursor execution. I don’t see how you can do sql injection in that query.
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
People seem to be very afraid of the various forms of injection attacks. In fact, they are quite easy to avoid.
@NathanHedglin
@NathanHedglin Жыл бұрын
​@@lawrencedoliveiro9104 and yet I constantly see SQL injection project code.
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
@@NathanHedglin So do I ... mainly in PHP code.
@oprea1991
@oprea1991 Жыл бұрын
For the sql string in the video, I do no see how you can insert sql injection. It will just crash.
@kkdpsudpsu
@kkdpsudpsu Жыл бұрын
@@oprea1991 If the code was running in sql -> and the limit value given by the user was say "10;drop table users;" it would run the sql query generated and then run the second query
@MatsRolfson
@MatsRolfson Жыл бұрын
Arjan, you could have demonstrated the built in SQLalchemy query language, using native python syntax to filter, group and so on to demonstrate that you don't have to learn sql at all. I think i the biggest strength of SQLAlchemy similiar to hibernate/nhibernate in java/C#
@shantanukaushikmathoholic
@shantanukaushikmathoholic 10 ай бұрын
Very helpful discussion. Thanks for covering these topics.
@ArjanCodes
@ArjanCodes 10 ай бұрын
I'm glad the content has been useful :)
@dorb1337
@dorb1337 Жыл бұрын
Great video :) as always
@ArjanCodes
@ArjanCodes Жыл бұрын
Thanks!
@anonanon2031
@anonanon2031 Жыл бұрын
Raw SQL is my current choice
@echernyavskiy
@echernyavskiy Жыл бұрын
Didn't think the code using SQLAlchemy would look so ugly. I've been using Peewee, and the code is usually clear and concise.
@nathanbrown2640
@nathanbrown2640 Жыл бұрын
I often use a hybrid of ORM and raw SQL: Some more complex parts of a query I might write as raw SQL, then pass them into the ORM alongside more standard things like selecting normal columns and making reasonably simple joins. I do sometimes use the SQLAlchemy `with_hint` method to force certain indexes to be used. One very important thing to note about SQLAlchemy is that it has a `Core` and an `ORM` - I know SQLAlchemy 2.0 changes some of this up, but we are still on 1.x. The `Core` side compiles statements, executes them and returns simple tuples of the raw data. The `ORM` side can allow you to fetch an entire row of the table, loaded as a Python Class, meaning you can have your own custom methods defined on that class for calculated properties used later, or for common functions to update fields on the row all at once. The ORM query can be much bigger (it will fetch all fields on a table), but can be powerful if you want are try to be Object-Oriented - you effectively treat each row of a table as an Object. Though, again, for many situations where speed of queries is important, you often just want to load the limited data you need and keep it quick. (That said, SQLAlchemy have 'Bundles' which allow you to subclass the main Class for a Table and limit down the columns loaded - SOOOO helpful for speeding up some of my queries loading a half-dozen columns of a 30+ column table) The SQL Injection protection is massive for a webapp if we are using customer input in the query (especially searches!) and resorting to raw SQL, even in part, needs that little more careful attention to prevent possibilities.
@mahmoudhosny8881
@mahmoudhosny8881 10 ай бұрын
I am not really experienced with this stuff, can I ask you for a bit more detail? Wouldn't that make things just a bit more confusing overall rather than just using one method? Or is this for a particular use case? Trying to decide what I should go for for my first web app. Also I am probably going to be using Django's ORM if I do, as I will use it as a framework, so if you have any thoughts on that too that'd be great. Thanks :)
@pacersgo
@pacersgo Жыл бұрын
Migration is important for me. With Django it is included, and for sqlalchemy you can use alembic. Therefore I would use ORM for sure.
@ZaItan1
@ZaItan1 Жыл бұрын
A really nice feature I've seen experienced teams develop on top of ORM is some way to automate writing (or refreshing) those data classes by pre-reading tables in the database once. Seems like a natural addition to an ORM library.
@public_static
@public_static Жыл бұрын
Given that I mainly inquire about metadata related topics such as listing views, tables, columns, and data types using various database providers (both cloud-based and on-premises), what would be the optimal choice for me (if it even makes any difference)?
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
4:16 Fun fact: the awkward “PostgreSQL” name arises from the fact that the original Postgres DBMS had its own query language, called QUEL. This was in the early days of relational DBMSes, when SQL was still gathering momentum but hadn’t quite become totally dominant yet. So when they finally gave in and adopted SQL as a query language, they had to indicate this by grafting this new thing on the end of their name, with slightly unfortunate results.
@lunalect
@lunalect Жыл бұрын
Eh that’s half right. They were called Ingres, and when they adopted SQL were born anew called themselves Postgres.
@mehdi-vl5nn
@mehdi-vl5nn Жыл бұрын
What is lost is the use of SQLite itself. For other DBMSs, they provide a driver which is usually a dynamic C library. This library may be written differently depending on the different standards for defining APIs or it may be a custom API for the database itself, such as ODBC, etc. Eventually, this API can be used with a protocol, for example, when connecting to a certain database, you need to send this URL and the username and password. But for SQLite, interestingly, the driver itself is part of the database
@AlexV6
@AlexV6 3 ай бұрын
I don't know much about SQL, but I think you have a misconception about how SQLite works.
@tarunhasija5156
@tarunhasija5156 Жыл бұрын
Hi Arjan can you please share the code repository link along with Database download link. Thanks
@ArjanCodes
@ArjanCodes Жыл бұрын
Thanks for noticing! I'll add them in the description.
@DataPastor
@DataPastor Жыл бұрын
Isn’t the primary use case for ORMs is that you develop an OOP app, and the ORM can load and save your object’s state to the database and vice versa, so that you don’t have to write the mappings manually?
@thomashamilton564
@thomashamilton564 Жыл бұрын
Your raw SQL had a LEFT join, but it looked like in SQLAlchemy you just used .join - are these the same? Or is that specific to the setup of your model classes?
@deepikameenakshisundaram1748
@deepikameenakshisundaram1748 Жыл бұрын
I have odd issue, in my application one of procedure purpose will return data from previous record. This procedure never had any issues running on windows xp and sql 2000 server. But same procedure running on windows 10 and sql 2014 returns null character intermittently even though the previous record has valid data. Any suggestions how to identify the root for this issue? Tools,method, etc?
@mrmuranga
@mrmuranga Жыл бұрын
useful..thanks
@midicine2114
@midicine2114 Жыл бұрын
Can you speak to the advantages and disadvantages between data mapper pattern vs active record?
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
5:05 If it was a “%d” format specifier and you used printf-style formatting, then it cannot be substituted with anything other than a decimal integer. Problem solved.
@BrittoRaul
@BrittoRaul Жыл бұрын
I prefer to work directly with raw SQL defined in files. This way is independent of language, gives more flexibility to tuning queries, and everyone can read and understand queries without learning a new framework,
@kosmonautofficial296
@kosmonautofficial296 Жыл бұрын
Thank you! I was just learning sql and getting in sql alchemy and was wondering this!
@ArjanCodes
@ArjanCodes Жыл бұрын
Great to hear!
@raj.svc.google911
@raj.svc.google911 Жыл бұрын
Please talk about stored procedures. For mature schemas that contain deeply derived tables, I believe SPs to be arguably the best option for secure, efficient & idempotent inserts/updates.
@bogdanp1351
@bogdanp1351 Жыл бұрын
Great video! Another point to touch on are database migrations. With SQLAlchemy you can use Alembic to auto-generate your migration files based on your Python DB models. It's not perfect, but it does 90% of the job. With Raw SQL or SQL query builder you have to write migrations by hand. Would you still use Alembic to manage these migrations or something else?
@voxelfusion9894
@voxelfusion9894 Жыл бұрын
You could have AI write the migration these days.
@estebanmejia9989
@estebanmejia9989 Жыл бұрын
man your vides are awesome, thank you
@ArjanCodes
@ArjanCodes Жыл бұрын
I appreciate that!
@magfal
@magfal Жыл бұрын
Most ORMs are limited by supporting multiple databases since it forces usage of the lowest common denominator. In the case of postgres that means you leave 80% of the potential behind.
@robertmazurowski5974
@robertmazurowski5974 Жыл бұрын
But for writing quick Crud it is fine.
@magfal
@magfal Жыл бұрын
@@robertmazurowski5974 even then I wouldn't use it for bulk actions such as importing 1000s of rows at the same time.
@robertmazurowski5974
@robertmazurowski5974 Жыл бұрын
@@magfal if your a data analysts don't use ORM. What web app would do 1000s of rows?
@magfal
@magfal Жыл бұрын
@@robertmazurowski5974 if you ingest a list of customer or a list of products it's a bulk action. Accepting an Excel through a web interface, validating and importing from it is a good user experience for some people.
@spitsmuis4772
@spitsmuis4772 Жыл бұрын
Isn't support for migrations also an advantage for ORMs? (Never had to deal with them before, so just wondering)
@NathanHedglin
@NathanHedglin Жыл бұрын
Yeah there are alternatives to that too. ORMs are nice because everything just works together.
@Septumsempra8818
@Septumsempra8818 Жыл бұрын
What's the best for building a database that constantly has new columns being added to some of the tables? I currently have a flimsy solution that's driving me maaaaaad
@ArjanCodes
@ArjanCodes Жыл бұрын
Ideally you should design the database so that the columns don't have to change that often. If that's not possible, you could look into a NoSQL database as that puts less constraints on the structure of your data.
@Septumsempra8818
@Septumsempra8818 Жыл бұрын
@ArjanCodes hmmm. You're not the 1st to suggest the Nosql route. I guess I'm just hesitant of the learning curve, but it has to be done. Thank you
@SP-db6sh
@SP-db6sh Жыл бұрын
If we use pandas or polar with SQL db with a custom context manager... Then pd.read_sql(), df.to_sql() .. Does this simpler way... Sql is not that simple way to do complex queries, when pandas do nested filtering with simple pipe method after using pd.read_sql()
@sandeshgowdru8869
@sandeshgowdru8869 Жыл бұрын
I think ORM's are better choice, I like the part where I can see what's going on with my dB and how it is structured. I do can get irritated when I want to change the structure of the tables or database I have created dynamic system in Python which generates with generates schema and alters the schema automatically instead of hard punching them in the program
@rood486
@rood486 Жыл бұрын
Great video uncle Arjan.
@y4lnux
@y4lnux Жыл бұрын
Raw SQL, ORM are amazing if your DB is simple, whenever you need to delegate processing to sql engines I will always use Raw SQL
@CarlosMorenoV
@CarlosMorenoV Жыл бұрын
Nice viewo Arjan!. Well, I prefer the raw SQL approach. Jajaja, I have been checking sqlalchemy this year, and it looks really cool as well. Maybe I will use it in the future. I did not know anything about the Query Builder option, it is like a intermediate alternative between ORMS and RAW, great.
@CottonInDerTube
@CottonInDerTube Жыл бұрын
ORM, fallback on (own) queryBuilder, fallback on plain SQL. ORM is IMO more to let the "next guy" working on the entity - so he|she does not have to care what the tbl name is, or w/e. Also: ORM should have ::getData(string key = null) and ::setData(mixed key, mixed value = null) to be able to use new columns that are not implemented yet.
@loutrea
@loutrea Жыл бұрын
There is a point with ORM I really don't like is that most of them are following the Active Record pattern. That is to say it couples data to model (most of the projects I've seen, ORM classes became domain models, and that's a pain). Raw SQL or query builders allow much more decoupling. Note that SQLAlchemy can work as an Active Record, a Data Mapper and a Query Builder!
@manuelstausberg8923
@manuelstausberg8923 Жыл бұрын
Could you explain why the Active Record Pattern is bad / what pains you had with it?
@loutrea
@loutrea Жыл бұрын
@@manuelstausberg8923 Active Record is also known as "anemic domain model antipattern". It is ok to use it when business logic is simple, when all you have to perform with your model are CRUD operations. When the business logic begins to be complex, uncoupling the models from the data is essential.
@MatsRolfson
@MatsRolfson Жыл бұрын
Exactly, if you like the repository pattern, you can use it. by the way AR pattern is not that bad. someone saying it is a antipattern they are just plain wrong
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
Cursor creation and extraction of results is such a common pattern that I like to wrap it up in a generator function, e.g. def db_iter(conn, cmd, values = None, mapfn = lambda x : x) : for item in conn.cursor().execute(cmd, values) : yield mapfn(item) ♯end for ♯end db_iter What is the point of the mapfn arg? When returning a given list of field_names, you can turn the result into a dict with something as simple as mapfn = lambda row : dict(zip(field_names, row))
@danielschmider5069
@danielschmider5069 Жыл бұрын
You dont really need to define your schemas in code, you can also load them from the database's metadata. I feel like saying "You NEED to write your schema in your code!" will turn away a lot of people from an ORM. Having the database itself as the source of truth makes a lot of things much simpler, because a change on the database will always be reflected in your code automatically. This is very much relevant to the point you're making at 12:30. Basically, you import the schema from the database to your code instead.
@willemm9356
@willemm9356 Жыл бұрын
The problem I've seen with ORMs is that developers get a bit lazy, and/or don't know exactly what some database accesses are doing, and end up writing code that completely mullers the database performance, like by triggering complete table scans multiple times, or just pulling in giant tables entirely when all they needed were a few rows and columns. And then they go "we need a faster database engine and/or more processing power", which is especially frustrating when the old code they are "modernizing" was lightnig fast. I've seen some experimental projects where you can write some kind of orm/api/stored-proc interface, where you don't define a mapping to tables directly, but to these stored procs which are custom to exactly what the programmer wants to query. But I don't think any of those ever got off the ground.
@freacas
@freacas Жыл бұрын
ORM has much more features than shown. It's like comparing Django, FastAPI and build-in HTTPServer. All will handle requests, but you need to spend weeks to understand what the trade-offs between them are. Same with ORM. Control is always the price you pay when picking an abstraction, but we should be fully aware what the get instead. My default is ORM and if I need control or optimization, I drop raw SQL here and there.
@arkanderr
@arkanderr Жыл бұрын
I prefer ORM for simple CRUD and raw SQL for more complex things.
@dirkschannel5817
@dirkschannel5817 Жыл бұрын
It’s personal preference. I prefer writing SQL. Well, I do SQL for 25 years, so it’s muscle memory for me. But I do understand people who prefer ORMs. In big projects I use stored procedures, materialized views, etc. I recently learned that some people think this is bad practice. Well, in the end, a software must do it’s job and the customer must be satisfied.
@astronemir
@astronemir Жыл бұрын
If you’re using raw sql, please use DBT!
@za_wavbit
@za_wavbit 6 ай бұрын
Raw SQL is great. Query builders are okay. ORMs are okay if they're stable and have broad community support, and let you drop down to raw SQL. Just don't build your own ORM, if you do I'll hunt you down and show you all the sql injection bugs I'm currently fixing in my company's in-house ORM. You'll have nightmares for months.
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
How would you handle a level break? Here’s a fairly simple two-column example, which lists the moons for each planet on a separate line. The planet name only appears for the first moon, and the last one is followed by a count line for that planet. out⋅write(" ") out⋅write("PlanetMoon ") items_iter = db_iter \ ( db, "select planets⋅name, moons⋅name" " from planets left join moons on planets⋅id = moons⋅parent" " order by planets⋅name, moons⋅name" ) cur_planet = None while True : item = next(items_iter, None) if item == None or cur_planet != None and item[0] != cur_planet : if cur_planet != None : out⋅write \ ( "Count:%d " % count ) cur_planet = None ♯end if if item == None : break ♯end if out⋅write("") if cur_planet == None : ♯ first moon for next planet cur_planet = item[0] count = 0 out⋅write("" + html⋅escape(item[0]) + "") else : ♯ second or subsequent moon, don’t repeat planet name out⋅write(" ") ♯end if out⋅write("") if item[1] != None : ♯ there is a moon out⋅write(html⋅escape(item[1])) count += 1 else : ♯ that’s no moon out⋅write("(none)") ♯end if out⋅write(" ") ♯end while out⋅write(" ")
@ipadista
@ipadista Жыл бұрын
I would partially disagree about the disadvantages of ORMs in the design phase. If you change your DB structure in the ORM model, you will instantly get notifications about code that needs fixing via the IDE. If you hand code the DB, you will have to fix any now dysfunctional SQL queries spread out in the code manually, easily leading to missed issues. A now slightly incorrect query might still give a result, and could easily be missed, leading to really hard-to-find bugs.
@rikschaaf
@rikschaaf Жыл бұрын
Also, if the database is there to facilitate your application, rather than the application being there to facilitate a(n existing) database, you can usually let the ORM libraries generate your database from the ORM model. This is especially useful for in-memory databases or at the very least databases that don't require long-term storage that would necessitate migration strategies.
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
I do hand-coded database schemas and queries. And I often make changes to them, with the need for corresponding changes to the code. “Missed issues” will manifest themselves with SQL errors, so they won’t stay “missed”.
@ipadista
@ipadista Жыл бұрын
@@lawrencedoliveiro9104 If they result in malformed SQL, then sure, that would throw errors and be impossible to miss. However, you can easily have subtle issues, like changing something in the schema that still allows the old query to succeed syntactically, but might be slightly invalid causing errors eventually, if what is expected to be a long is now an int, and so on. Another potential issue would be if you read from multiple tables, and a field you thought was an ID suitable for a join, now is something else, giving you a syntactically correct result, however being incorrect data
@lawrencedoliveiro9104
@lawrencedoliveiro9104 Жыл бұрын
@@ipadista Same reasoning applies to ORMs.
@ipadista
@ipadista Жыл бұрын
@@lawrencedoliveiro9104 well during the design phase your constantly dropping the DB each round, so a fair assumption would be that the DB precisely matches the ORMs idea of it, but sure I guess the ORM type checking could go astray. So ok both approaches are valid, but doing all the sql manually looses out on built in type checking and code completion, so in most cases loses out in the rapid development department. But ok it does give you full control
This Is Why Python Data Classes Are Awesome
22:19
ArjanCodes
Рет қаралды 821 М.
What is an ORM and what does it do?
8:49
Code With Bubb
Рет қаралды 3,7 М.
小丑教训坏蛋 #小丑 #天使 #shorts
00:49
好人小丑
Рет қаралды 54 МЛН
Don’t Choose The Wrong Box 😱
00:41
Topper Guild
Рет қаралды 62 МЛН
BAYGUYSTAN | 1 СЕРИЯ | bayGUYS
36:55
bayGUYS
Рет қаралды 1,9 МЛН
To Brawl AND BEYOND!
00:51
Brawl Stars
Рет қаралды 17 МЛН
DONT USE AN ORM | Prime Reacts
25:46
ThePrimeTime
Рет қаралды 245 М.
The Ultimate Guide to Writing Classes in Python
25:39
ArjanCodes
Рет қаралды 122 М.
Avoid These BAD Practices in Python OOP
24:42
ArjanCodes
Рет қаралды 81 М.
Functions vs Classes: When to Use Which and Why?
10:49
ArjanCodes
Рет қаралды 171 М.
SQLModel + FastAPI: Say Goodbye to Repetitive Database Code
19:50
I tried 8 different Postgres ORMs
9:46
Beyond Fireship
Рет қаралды 443 М.
Migration Lesson: Don't Use Prisma | Prime Reacts
29:16
ThePrimeTime
Рет қаралды 164 М.
Solving one of PostgreSQL's biggest weaknesses.
17:12
Dreams of Code
Рет қаралды 222 М.
SQLAlchemy Turns Python Objects Into Database Entries
22:23
NeuralNine
Рет қаралды 187 М.
小丑教训坏蛋 #小丑 #天使 #shorts
00:49
好人小丑
Рет қаралды 54 МЛН