SQLAlchemy: The BEST SQL Database Library in Python
HTML-код
- Опубликовано: 16 май 2024
- Ever heard of SQLAlchemy and thought it sounded like a medieval potion? Well, it's not! 🧙♂️🔮 SQLAlchemy combines the robustness of SQL with Python's flexibility, making database management not just easier, but kinda fun too! In this video, I’ll take a closer look at this awesome tool.
Try out Oso for free here: oso.1stcollab.com/arjancodes_3
🔥 GitHub Repository: git.arjan.codes/2024/sqlalchemy
💻 ArjanCodes Blog: www.arjancodes.com/blog
✍🏻 Take a quiz on this topic: www.learntail.com/quiz/zewcrd
Try Learntail for FREE ➡️ www.learntail.com/
🎓 Courses:
The Software Designer Mindset: www.arjancodes.com/mindset
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!
Social channels:
💬 Discord: discord.arjan.codes
🐦Twitter: / arjancodes
🌍LinkedIn: / arjancodes
🕵Facebook: / arjancodes
📱Instagram: / arjancodes
♪ Tiktok: / arjancodes
👀 Code reviewers:
- Yoriz
- Ryan Laursen
- Dale Hagglund
- Kit Hygh
- Alexander Milden
- Bean
🎥 Video edited by Mark Bacskai: / bacskaimark
🔖 Chapters:
0:00 Intro
1:30 Functional Approach
5:04 Object-Oriented Approach
8:35 Relationships
10:26 Oso
12:18 Relationships part 2
16:02 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!
Try out Oso for free here: oso.1stcollab.com/arjancodes_3
The documentation of sqlalghemy is a pain in the ass
Way to tackle it is train LLM on sqlalchemy then ask questions.
@@sarveshsawant7232 you got it Buddy. But now I'm seeing that I'm not the only one that thinks that
true that bro, sqlalchemy doc is a pain in the ass X 2, i guess is one of the worst with gcp python libraries. the worst about sqlalchemy docs is the lack of api standardisation, you can find thousand ways to create a session or to make a query, so may times docs and people in forums mix them, so you try to make it work and crash miserably, everything is very obscure about. not to mention migrations with alembic that are a pain in the ass too, but things like sqlalchemy model definition are ok
@@sarveshsawant7232 or use peewee, the one with the best api
"SQLAlchemy mixes SQL with questionable medieval practices" I think is the best description of the library to date!
I'm glad you liked it! :)
When following the Domain Driven Design principals make sure your SQLAlchemy models don't become your domain models. Keep the infrastructure separated from your business logic. When you know how to write an SQL query, I find it easier to just use an SQL query builder instead of an ORM. When the record is mapped to a dataclass, it's easy to build your domain objects with a mapper.
I use an ORM to abstract my database tables, but when it comes to executing the query, I simply let the ORM create the query string and execute it using an engine connection
I think this would be something good have as a video of it's own. An example refactoring to explicit ORM mapping with SA table objects, and discussing the advantages in terms of decoupling (and disadvantages in terms of adding abstraction complexity) would be great.
I have been using sqlalchemy core (no OR/M) for a couple of years. Version 2 is awesome! The API is almost identical to the OR/M API. Coupled with autoload schema discovery and fluent API to construct queries and DML statements leads to very short and maintainable Repository implementations.
Note that usage of OR/M should be justified on a case by case basis; and not just assumed. My starting assumption is to NOT use an OR/M.
OR/M's are NOT as simple as they seem on the surface and can create more architectural problems than they solve. Note that is true in general and is not specific to sqlalchemy or Python. I have fought with OR/M's with Java, .NET, and C++ - as well as Python and JavaScript. Same story every time. Just avoid them.
Note in the many projects I have led we do not allow any embedded SQL. It just creates tons of work later when the product needs to move vendors - e.g., Oracle or MS SQL Server to Postgres SQL. It also unnecessarily complicates local testing with sqlite.
Great overview as usual! Thanks Arjan. I just want to share my pain learning SQLAlchemy while building a production saas app with SQLModel. Especially mentioned CASCADE option, M2M relations etc. Mostly because both SQLModel and underlying SQLAlchemy evolved significantly during last years.
Thank you so much Arjan..I can't wait for your next exciting videos😃❤
I'm glad you like the content!
Arjen, when you specify the primary_key=True, you dont need to say index=True and unique=True since that is implicit in primary keys.
Could you make a video FastAPI + SQLModel ?
Yes! I just found SQLModel by searching for "pydantic sqlalchemy" and it looks like a wonderful way to unify these two worlds. an SQLModel instance is both a pydantic and sqlalchemy model .. at the same time!
Can't wait to start playing with that and bridge the gap between these libraries :)
You just made this video with me just being tasked to create a flask graphql API with sqlalchemy. Thx a lot.😂
You are in a lot of trouble my friend.
This is a great video! I've dabbled in sqlalchemy a bit but for most projects I work on I just write the SQL queries. If I need multiple queries or long queries, I store them as f-strings in a separate py file and import them as needed which helps keep my main file clean.
SQLAlchemy provide really flexible mechanism to communicate with database safely. It is not recommended to use f-strings directly from a security point of view. If there is a user input that is used to build queries in your app it may lead to sql-injection attack. It is strongly recommended to escape user input before using.
@@BenderAnt this is a good point! There isn’t any user input in these cases. It’s just pipelines transferring data from one db to another.
Oh, boy, Did I have fun with this python package.
engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False, "foreign_keys": True})
I would 100% use it again.
I like SQLAlchemy. But for big/complex projects, I would use Django so that many features are already included such as database migrations, graphic interface to manage the database contents.
If you work with SA, for migrations you're supposed to use alembic. For graphic interface there is SQLAlchemy Admin.
@@saitaro alembic isnt bad
I know both SQLAlchemy and Django ORM take different approaches. But personally I liked Django ORM more than SQLAlchemy.
If you have a big/complex project, don't use Django, the ORM will hold you back when it comes to doing any kind of complex query, and is generally not great because it's so far from SQL - you kinda have to learn the ORM language and what SQL that translates into which is just an annoying extra step to think about. I usually end up using raw SQL unless I'm doing single table queries in Django.
I'd say Django is really good if your app is functionally very simple (i.e. REST APIs that are basically just SELECT *) it has a lot of built-in niceties to support that stuff, but as soon as you have some complex DB logic you'll run into the flaws in Django's abstractions.
Never really been a fan of the argument that Django having an ORM built in is a reason to use it, just add one line to whatever dependency file you're using and you're done.
Django's great for getting stuff running quickly for sure but for complex projects I'd probably look elsewhere
Oh, and because Django's ORM has no way to pass in a connection (as far as I'm aware) and just implicitly handles all that stuff, you can't DI it for testing which is really annoying - definitely another reason you wouldn't want it for complex projects where you will want to put a load of tests in
Thank you. Great presentation. Of course it's up to developer to choose approach. Medieval would be to keep database structure as separate area managed by db tools and access to data as another area this time in programming language by creating kinda DAO layer under which might be a set of sql statements :) But sqlAlchemy is absolutely wonderful.
Great video! I was investigating using SQLAlchemy with Alembic in one of my projects, and this video came just in time to show me how to simply use SQLAlchemy! I would love to see the combo SQLAlchemy + Alembic in one of your videos too.
Alembic is one of the best things about Sqlalchemy and deserves a video as well
SQLAlchemy really awesome library for FastAPI at least, but its documentation is so hard to read and understand for me! Thank you for this video, Arjan. I have found some interesting things about the library in the video I didn't know before 🙂
I find plain SQL more readable. I develop SQL applications since 30 years and my eyes are bleeding when I look at ORM queries.
Problem is only the different flavour and features when you have to switch the database.
On Authorization: Can you make a video about casbin?
Do you type SQL in strings within your Python? Doesn't it bother you not having language server, syntax highlighting?
I don't understand how, i keep making a new project, i'm looking into something, and you ALWAYS release a video about it
thanks
I have been training my psychic powers, glad to know they're working!
In middle ages I would be Python developer working with SquealAlchemy.
SQLAlchemy is great. I use it everyday for small ETL jobs.
would love to see a deep dive into using oso
Hello tanks for all the information!! I has wondering if you can also make a video about the use of SqlAlchemy in DDD and all the levels it can have, like absolute DDD and use mapping imperative, or assume that the entity ill be a model and use SqlAlchemy with inherance of abstract classes.
Thanks a lot, for all your work.
P.S.: SqlAlchemy need to change their documentation, it has so potential but is really difficult to learn all of potential .
Random thought process inspired by the intro, based on superficial info from the interwebs: For the pigeon to be able to deliver a letter to you, you would have to raise them and distribute them in order for them to find their way back, because you can't just tell them to fly from A to B. So transport per road would be more likely. In the middle ages a courier on horseback with the option to change horses was able to make about 80km per day. But that was way to expensive for average people, so they'd most likely would entrust their letters to some traders with wagons. They were able to travel about 30 km per day. Using modern roads, it's about 500km from my nearest city to Amsterdam, that's about 17 days of travel time. Long story short, I would have been burned as a witch in the middle ages 😅
Hi Arjen, I experience relatively slow performance (minutes to insert just a couple of thousand rows) when using the execute statement. Is this typical for SQLalchemy or is it more likely the cause can be found somewhere else ?
What about peewee vs. sqlalchemy ?
Nice video as always. However imho Django orm is easier to use with more of standard practices and way better documented.
Just what I was looking for….❤
Glad it was helpful!
The only thing I am feeling bit hard about sql-alchemy is that, maintaining circular M2M relationship between models. Even some tips from documentation will not work in some circular M2M cases.
I’m not sure in bill g projects we will creating table using sqlalchemy I would be interested how it connects to ora le database and access existing tables. More interesting would be to write a generic insert, update and delete statements for all tables. It will speed up development
you should talk on imperative mapping
very nice overview. thx.
Glad to hear you liked it!
Hi Arjan! This might fall outside the purview of your channel goals, but I've been hitting a wall trying to move dfs to redshift 😢 Google made it so user friendly, but I'm not even sure what to do beyond googling permissions errors over and over in aws 😅
What about SQLModel?
That's SQLAlchemy but with syntax high lighting.
SQLModel combines Pydantic schema models with SQLAlchemy ORM models, something you could do on your own as well. I tested SQLModel and in my opinion it wasn't yet production ready, lots of documentation is still missing.
SQLAlchemy is excellent, not only because the API is great but also because the docs are excellent. There are a couple things I wish were better like DDL; when I need to drop down to raw SQL, I wish I had some good templating language or the ability to use `.sql` files with variables somehow, because raw SQL in large strings is very annoying and difficult to debug.
Also, alembic, the migration tool is really lagging behind (for example in the multi-tenancy case) and I hope more features will come along (I know, write your own PR's and all that).
That being said, I love SQLAlchemy and the abstractions for DML are pretty amazing and I love how backward compatibility is taken seriously.
I think the typing part of the lib needs improvement. So does the documentation. When I used SQLAlchemy, I used mappers to decouple domain from infrastructure, however, when I did this, I feel like using raw SQL or SQL builder would be a better approach, because, at the end, I was fetching the data using the ORM and instantiating my domain models "manually" (I was using pydantic v1 and doesn't integrates very well with the ORM)
SQLAlchemy is a "data mapper" ORM. IMO, that tends to help with maintainability, especially if the data source has a life independent of the objects being mapped to it. Some of the alternatives (especially Django's ORM) use what's been called an "active record" approach. I think that can help with development velocity, but hurts maintainability if you want to access the data through anything other than the ORM.
Calling one approach the BEST can't be universally right, I don't think... it depends what you need.
SQLAlchemy is not just ORM. Best part of SQLAlchemy is separated layered architecture(like onion):
- Core (DDL, Connections, pooling)"imperative" - best part for me because abstracts out actual SQL DB (mysql, postgres, sqlite, ..)
- ORM(sessions, models)"declarative" - this part is useful for small projects without complicated queries - focus on easy to use
My experience:
Other frameworks do just ORM and hide Core. SQLAlchemy let makes core totally transparent and intuitive if you KNOW SQL and how it works. Because I work on performance focused code I restrict to use only Core part. Core layer does not change much so it is much more maintainable than using whole ORM. Core also solves problem of DBApi compatibility because it masks that if new version of DBApi connector comes out.
Your opinion makes sense if you never wanted to do something complicated and want to keep code minimal, and you do not need to optimize each complicated query. Great example of ORM intended usage is web store where u got like 10/sec requests on server from 100 visitors in hour.
Core is more practical for performance use cases, like interactive live service system with 1000/sec requests and 100 000 users per hour.
cant you get some more code syntax highliting in vsc?
He can. I don't follow why he doesn't use it. Oh well.
SLQLchemy lacks database migrations out of box like in Django ORM or Piccolo ORM, GORM, AcrtiveRecord and other ORMs'. It needs a third party library: Alembic.
Oso might just be the missing component for our open source library. Thanks for this advertisement, they sometimes are utterly relevant !
You’re welcome! I do my best to pick partners for sponsorships that I think actually offer a cool product.
@@ArjanCodes We actually did chose Fief, which is fully integrated with FastAPI. I suggest you to give it a look, it is awesome (and open source, by François Voron)
i don’t understand why your code is almost completely uncolorized lately
The latest versions of SQLAlchemy have good improvements making the library modern and easy to use.
I recommend that you use it in your projects to avoid raw SQL.
It's terrible to have tons of raw SQLs in the code.
What's wrong with raw SQL? With SQLAlchemy you need to learn how to use the ORM and you still need to know how to write raw SQL for the logic that is not available in SQLAlchemy. Use the Repository pattern to hide the raw SQL from your domain logic. With the Repository pattern the infrastructure (the database) is hidden for the domain logic. The repository classes are the only objects that will contain SQL statements.
I've been using sqlalchemy at work for about 5 years now and I really love it. But I also think that the documentation is not perfect. For someone new to sqlalchemy, it's harder than it needs to be.
I would have been body guard.
In the middle ages I would be a cobol programmer....or...was that even earlier...
Argh-- I love/hate SQLAlchemy. It makes my life both easier and harder, every day!
In the Middle Ages, I would probably be a hostler or an ostler. That’s because I am quite good with horses.
A different meaning organ player suddenly makes sense to me... I have interest in music, and I always wanted to be pathomorphologist.
I am Jewish, so my job in the middle ages would be... Usery, I hope (the other options are way worse than "banking")
Ha!
I would probably die in my first battle if I born in middle ages.
The problem with SQLalchemy is that it does a lot of "magic" that is extremely hard to debug if something does not do exactly what was expected. It's a little too black boxy to justify itself if you are targeting just one or two DB systems. Typically the trade off to support more DBs but with a lot of extra testing really doesn't work out.
Well, they wouldn't call a simple thing "alchemy" 😅
Thank you.....mafia is only way forward....joking..
Sqlalchemy is the reason why i am staying with django.
DuckDB with python is much faster
I would personally much prefer a written tutorial -- sick of RUclips adverts too.
I understand ads can be annoying - they do help me cover my costs for producing these videos. If you prefer reading instead, I do have a blog: www.arjancodes.com/blog. Otherwise, try Medium - I regularly read articles there myself.
I can't imagine a worse SQL library! 🤣
In fact, I still can't see a reason to use SQL Alchemy 2+, seems a downgrade from v1.0
I've always found django's orm simpler/quicker to use, but yeah sqlalchemy is pretty cool as well
SQLAlchemy never made any sense to me whatsoever. Every data science org I've even been a part of generates queries for python using f-strings (or something similar), and just uses typehinting to make sure that schemas are consistent/get stuck to.
I like Peewee...
I think being a Dutch "Trader" and "getting" spices from the other end of the world would also be considered "killing people for a living". So, I hope you wouldn't be trader - probably simply a teacher.
Horrible library, stay away far from it!
I really like sqlAlchemy and pydantic. I am a little sad that they don’t always play nice together. Would it be possible to make a video about how to properly handle using sqlAlchemy and pydantic, in the Domain Driven Design context pointed out by @frankybraem below?
Same question. My company Pydantic as a basis. And writes dirty db schema and sql. Wanna adopt ORM into new projects (not web projects) but it seems hard to blend pydantic and orm.
@@coladockwouldn't SQLModel (SQLAlchemy + Pydantic) work for you? It's not just FastAPI addon, it should work as standalone ORM.
@@vlntsolo not exactly. It does not allow for strict domain driven design as it by definition couples the business model with the choice of SQL alchemy
You could use the repository pattern with SQLAlchemy or SQLModel. Keep the database models outside of your domain objects and use a mapper to create domain objects from the database models. I also don't use Pydantic for the domain objects. I do use it to validate input from the user. If you need an external library for your domain, use an interface or wrap it with a value object. I do the latter for the pendulum module for example.
15:00 "One of the nice thing is that you can add behaviour to your tables, which is normally not available..." You don't do postgresql much, do you? 😆