Raw SQL, SQL Query Builder, or ORM?

Подписаться 238 тыс.

Просмотров 95 тыс.

50% 1

Not sure if you should use Raw SQL, a SQL query builder, or an ORM for your next project? In this video, I’ll explore the pros and cons of each option, allowing you to make an informed decision based on your specific needs. Choosing the wrong technology for your project can be a costly mistake, so it's crucial to understand these tools.
Code example on GitHub: github.com/ArjanCodes/2023-orm
✍🏻 Take a quiz on this topic: www.learntail.com/quiz/syflxl
🚀 Next-Level Python Skillshare Class: skl.sh/3ZQkUEN
👷 Join the FREE Code Diagnosis Workshop to help you review code more effectively using my 3-Factor Diagnosis Framework: www.arjancodes.com/diagnosis
💻 ArjanCodes Blog: www.arjancodes.com/blog
🎓 Courses:
The Software Designer Mindset: www.arjancodes.com/mindset
The Software Designer Mindset Team Packages: www.arjancodes.com/sas
The Software Architect Mindset: Pre-register now! www.arjancodes.com/architect
Next Level Python: Become a Python Expert: www.arjancodes.com/next-level...
The 30-Day Design Challenge: www.arjancodes.com/30ddc
🛒 GEAR & RECOMMENDED BOOKS: kit.co/arjancodes.👍 If you enjoyed this content, give this video a like. If you want to watch more of my upcoming videos, consider subscribing to my channel!
💬 Discord: discord.arjan.codes
🐦Twitter: / arjancodes
🌍LinkedIn: / arjancodes
🕵Facebook: / arjancodes
📱Instagram: / arjancodes
♪ Tiktok: / arjancodes
👀 Code reviewers:
- Yoriz
- Ryan Laursen
- James Dooley
- Dale Hagglund
🎥 Video edited by Mark Bacskai: / bacskaimark
💻 Code example by Henrique Branco: / henriqueajnb
🔖 Chapters:
0:00 Intro
1:18 Code example
2:09 Main options
2:44 raw SQL
7:12 ORM
12:49 SQL query builder
14:30 Final thoughts
16:01 Outro
#arjancodes #softwaredesign #python
DISCLAIMER - The links in this description might be affiliate links. If you purchase a product or service through one of those links, I may receive a small commission. There is no additional charge to you. Thanks for supporting my channel so I can continue to provide you with free content each week!

Опубликовано:

22 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 328

@ArjanCodes 8 месяцев назад

👷 Join the FREE Code Diagnosis Workshop to help you review code more effectively using my 3-Factor Diagnosis Framework: www.arjancodes.com/diagnosis

@williamburdett9867 Год назад

I'm a data engineer. I almost always use raw sql because I can more explicitly optimize queries

@mamneo2 Год назад

Incroyable.

@piyushagarwal8330 Год назад

Same, for my de work, it's better to write it once and use it multiple times with better efficiency for specific job

@alexandryu5072 Год назад

DS/DA same

@dansplain2393 Год назад

I don’t even optimise the queries. I can just run them in my head faster.

@ryanbowns1517 Год назад

Data Scientist here, Same I Prefer to use raw sql. I find Case statements within a window statement to be extremely useful

@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 Год назад

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 Год назад

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 Год назад

Just noobs curious, don't you worry about a security such as a SQL injection?

@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 Год назад

+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 Год назад

My databases are typically complex. Never found abstractions around the SQL helped me. Just going raw SQL

@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.

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

@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.

@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 Год назад

Thank you!

@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.

@miquelgonzalezrossello3940 Год назад

Thanks for the video Arjan! It's always advisable to close the connection when you're finished querying.

@MedievalChips Год назад

I've been struggling with this dilemma for quite some time now. Glad you made a video about it !

@ArjanCodes Год назад

Glad it was helpful!

@amitnakash1642 Год назад

Love the channel Arjan! So grateful for your content! keep going!

@bryan_hiebert 11 месяцев назад

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.

@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!

@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.

@kosmonautofficial296 Год назад

Thank you! I was just learning sql and getting in sql alchemy and was wondering this!

@ArjanCodes Год назад

Great to hear!

@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.

@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 Год назад

Have a look at SQLModel from FastAPI creator Sebastián Ramírez.

@lawrencedoliveiro9104 Год назад

ORMs don’t make any sense to me. The code doesn’t end up any simpler.

@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 Год назад

@@lawrencedoliveiro9104 xD

@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 😊

@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 Год назад

Honestly using pandas "pd.read_sql_query()" function will give you a lovely dataframe while still allowing you to write raw queries.

@midicine2114 Год назад

Can you speak to the advantages and disadvantages between data mapper pattern vs active record?

@marioluizbernardinelli2659 Год назад

Hi, Arjan. Nice video. I have a suggestion for an another video: SQL and NoSQL comparison. Think about this. Thank you.

@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 Год назад

NoSql are dead, use memcached to cache, redis for pub/sub or queue... Put everything else in a table, inside a sql db

@DS-tj2tu Год назад

Thank you!

@cyber4joy Год назад

I always prefer to use raw SQL, and I have been doing SQL for 30-plus years.

@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 Год назад

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...

@123344556kk 7 месяцев назад

I was looking for a query builder and your video helped a lot in giving me a solution. Thank you.

@ArjanCodes 7 месяцев назад

Glad it was helpful!

@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.

@estebanmejia9989 Год назад

man your vides are awesome, thank you

@ArjanCodes Год назад

I appreciate that!

@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 Год назад

Yeah was about to comment this, we use the core (or sql expression as we call it) and its a nice middleground.

@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 Год назад

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 Год назад

@@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 Год назад

@@zoladkow dapper is the least shitty application layer integration of an SQL server I've used.

@Jishnu_Nath Год назад

Dapper, query builder, stored proc, ORM, raw SQL

@davidl3383 Год назад

Exactly when I start to study a fastapi project made with sqlachemy ORM.😊. You help me a lot !! I think if project is simple ORM seems to be cool. Query builder looks like very cool also, easy and helpfull when your are a catastrophe like me in SQL 😀. Merci

@markpaterson2260 Год назад

I would be interested in your views on if and when to move these kind of queries to stored procs and just use procs instead. I understand there are benefits to that, but I'm not sure what the downsides might be beyond having an extra place that you need to keep track of changes to your logic when iterating on a particular feature.

@julienyt1600 Год назад

SQLModel, combine validation with pydantic and sqlalchemy In the same code.

@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 Год назад

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 6 месяцев назад

Even better is to create views and functions in sql database.

@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 3 месяца назад

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

@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 Год назад

I dont know about the rest but you are able to manage indexes in orm

@zammea Год назад

@@dawidp749 indeed you can. Why argument was that SQL and DB administration is a good skill to have.

@shantanukaushikmathoholic 4 месяца назад

Very helpful discussion. Thanks for covering these topics.

@ArjanCodes 4 месяца назад

I'm glad the content has been useful :)

@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.

@qsykip Год назад

You should also do a video about Alembic. It's developed by the creators of SQLAlchemy and it integrates so well with it.

@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 10 месяцев назад

But for writing quick Crud it is fine.

@magfal 10 месяцев назад

@@robertmazurowski5974 even then I wouldn't use it for bulk actions such as importing 1000s of rows at the same time.

@robertmazurowski5974 10 месяцев назад

@@magfal if your a data analysts don't use ORM. What web app would do 1000s of rows?

@magfal 10 месяцев назад

@@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.

@radupopa6642 10 месяцев назад

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.

@TheGtmail Год назад

Raw SQL definitely! Reducing the dependencies.

@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.

@rood486 Год назад

Great video uncle Arjan.

@dorb1337 Год назад

Great video :) as always

@ArjanCodes Год назад

Thanks!

@mrmuranga Год назад

useful..thanks

@gothicanatomist Год назад

I sometimes wonder if you can read my mind. Not for the first time, as I'm in the middle of wrestling with something python - boom, you drop a video on that exact topic! Thank you so much for this great overview.

@ArjanCodes Год назад

That's awesome!

@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 8 месяцев назад

Can you explain to me what mirroring is and how we achieve it in sqlalchemy ?

@danielmcpherson9062 6 месяцев назад

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

@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 Год назад

You could have AI write the migration these days.

@N8ontv 8 месяцев назад

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.

@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!!!

@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.

@thekokoserver Год назад

Personally i prefer ORM, because they it gives more interface to work with and make my work faster

@jolieriskin4446 Год назад

Peewee ORM. I like the flexibility of designing and using it with sqlite3 locally and not having to worry about whatever is going to be the production back-end. I usually set it up so i can swap back and forth between production and my local environment.

@salaahamin6558 Год назад

One thing worth mentiong regarding ORMs. It's very easy to fall into the trap of creating the N+1 problem if joins are not added correctly. This is particularly apparently in Django ORM.

@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)?

@alexforget 11 месяцев назад

Django ORM, simple and integrated. Save so much work on most small/medium projects. - Free admin interface (already debugged) with security, users, login, etc. - Form conected to your models, etc. When it’s not up to the task, you drop to SQL for the few queries where you need something special

@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.

@LookNumber9 Год назад

Useful information. Thank you! I'm curious what you think about SQLModel.

@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.

@marcelvanLare Год назад

As a database dev i like the option of a database procedure with the sql there returning a ref cursor.

@vxsniffer Год назад

much depends on your background, for instance, I don't expect many ORM users in data science field, where raw SQL is key tool itself... I also prefer raw SQL coz, in my eyes, additional abstraction layer introduces more complexity and risk of errors... from the other hand ORM seems natural in web world... diversity is beautiful ;-) very nice video Arjan, thank you!

@bilbo1337 Год назад

Would be good to mention n+1 queries with ORMs

@rrwoodyt Год назад

SQLAlchemy is nice for *most* work, though it seems to get in the way when I want to execute some really complicated query. And beware of mixing raw SQL updates with orm activity, since the SQLAlchemy object cache doesn't automagically know what you've done if you alter data directly via a session.execute( ).

@GuyWicks Год назад

If you're building a database heavy application (a CRM or reporting service) then it's worth investing in a proper DBA designed database and schema - it will allow you and the DBA to optimise and independently update the database for performance. That means using views over the raw tables, and using native SQL queries, and building an independent data layer. You do miss out on the IDE helper features against the raw database tables. But you them move this data into native classes anyway and gain all that back in the logical data layer above the physical data layer. However, if you're DB backend is not too heavy and complicated, then use an ORM to 'spin up a data store' and get moving quickly - and allow you to focus on the end-user functionality. DB migrations are never easy - and trying to be DB independent is usually a poor choice.

@adrianzuur5589 Год назад

Thanks Arjan, interesting video, as always. A question: do these options make any difference in terms of trying to set up and maintain a "clean architecture" for an application? From reading online, I got the idea that ORMs can help you interface between inner layers, which handle business logic, from outer level "details" like the specific database you use. But, watching your video, I wonder if you can't also do that decoupling with raw SQL or query builders. Maybe using raw SQL makes changes to the DBMS more cumbersome to manage? But, at the same time, if you are at an early phase in your project, and your database data models are still not stable, does ORM actually help? It seems like ORMs help you decouple from the DBMS, but actually increase coupling to how relations are defined inside the database... Does that make any sense? Anyway, excuse me for the digression, and thanks again.

@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.

@IBMboy Год назад

My strategy is: first ORM, and query builder only if required. Some query builders offer raw statements functions which come with string sanitization to avoid sql injections.

@hY-ug8vn Год назад

what do you think of sqlmodel?

@MH-oc4de Год назад

I mainly use the ORM (sqlalchemy) because I'm dealing with a very complex and extensive schema. It works well for development, in that it makes it easier/cleaner to write code. However, I've noticed that for large databases, using the ORM when retrieving objects (rows) is slow (by a lot!) compared to raw sql queries. I guess the overhead cost of creating python objects from the raw queries starts to add up. That's the one downside I've noticed with sqlalchemy.

@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.

@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?

@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.

@Prakhar79 Год назад

Great content Arjan and team! Can you make something for DynamoDB? Do you recommend any libraries for that?

@cfn3107 Год назад

raw sql and prepared statements

@AlexeiCheban Год назад

You can add --sql in start line and vscode will be show pretty sql query

@marcopaolovaleriovezzoli5776 7 месяцев назад

I love SQL, It Is really a great DSL once I learn It properly. I read also a couple of great Brooks from Joe Chelko which are really informative. While I also appreciate query builders I actually use ORMs in production, because migration are vitali in a project life cycle. Nice video

@ArjanCodes 7 месяцев назад

Thank you, Marco! Glad you enjoyed the video :)

@dimitro.cardellini Год назад

ORM can lead to abstraction leaks when its models appear everywhere in the application, which brings an additional pain if domain structure differs from storage structure. So, I'm using ORM to manage migration and using almost raw-sql to interact with DB

@bn_ln Год назад

Curious to know from those using ORMs if mapped columns are common

@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#

@dirkschannel5817 8 месяцев назад

It depends :-) When starting from scratch with a smaller web project, an ORM is great. When adding to a bigger application, where parts are developed in different languages with a central database raw SQL often is the only option. I often saw, that complex queries where moved to stored procedures in the database and for another layer of possible validation, sanitation and security.

@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.

@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 Год назад

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 Год назад

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 Год назад

@@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 Год назад

@@ipadista Same reasoning applies to ORMs.

@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

@franklehmann3449 Год назад

SQLmodel for me. I really wanted to like SQLAlquemy but we never became friends. Second favorite: Raw dogging the DB wit plain SQL.

@d0cx Год назад

I didn't know something like a query builder existed. I've never really been a fan of SQLAlchemy as it just seems so overly complicated. I've used Peewee a little bit, and it seems much cleaner and more Pythonic.

@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.

@walid7189 4 месяца назад

lets say I updated my orm database structure, how do I get it to upgrade and make the required changes in the database ?

@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.

@theinternetcommoner 3 месяца назад

raw SQL is my personal favorite

@JasperMandos Год назад

Je plant heeft wat sql injectie nodig Arjan, hij wordt geel. 😂 Bedankt voor de goede afwegingen.

@ArjanCodes Год назад

😂😂😂

@TonyBokas 2 месяца назад

Once you enter a project that uses ORM, you realize you have entered hell.

@Mesquita2987 Год назад

Are there best standards for testing database calls?

@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?

@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.

@henrikolsen5 8 месяцев назад

I would like input on how to construct hierarchical data structures when joining relation tables. Say I have a one-to-many relationship and would like the many-part to be a list on level down of the parent it belongs to. How to best get an SQL-result into this structure in Python (automatically, or close to it)?

@nicolaspillot5789 3 месяца назад

Could you please alaborate on the difficulty (or it's absence) to evolve/regress the application schema and code between releases ?

@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.

@ssarkar2996 Год назад

Can you use SQL analytical functions with ORM?

@lawrencedoliveiro9104 Год назад

2:55 Have you looked at apsw? I prefer that to the standard Python sqlite module.

@seb_is_class8107 5 месяцев назад

When you said that you're joke was weird I knew that it deserved a like. - From a fellow weirdo.

@ArjanCodes 5 месяцев назад

Ahaha! Thank you for the support, Seb!

@TheMrSnuSnu Год назад

If there are multiple dataset that needs to be merged (which is often the case since you use relational tables to optimise a database), I always stick with Raw SQL. I also find time zone manipulation a huge pain in any scripting language whilst in SQL it typically a 1 line change.