SQLAlchemy: The BEST SQL Database Library in Python

[@ArjanCodes]

Try out Oso for free here: oso.1stcollab.com/arjancodes_3

[@UNgineering]

"SQLAlchemy mixes SQL with questionable medieval practices" I think is the best description of the library to date!

[@ArjanCodes]

I'm glad you liked it! :)

[@WlisesR]

🤣🤣

[@StefanDiby]

😂😂awesome

[@99btyler]

🤣

[@hendrikmartina3552]

The documentation of sqlalghemy is a pain in the ass

[@sarveshsawant7232]

Way to tackle it is train LLM on sqlalchemy then ask questions.

[@hendrikmartina3552]

@@sarveshsawant7232 you got it Buddy. But now I'm seeing that I'm not the only one that thinks that

[@PedroLeonRiveraWilches]

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

[@PedroLeonRiveraWilches]

@@sarveshsawant7232 or use peewee, the one with the best api

[@AislanDiego00]

@@sarveshsawant7232 Very well said.... what a hell it was

[@FrankyBraem]

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.

[@YusufTyer]

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

[@markhetherington8718]

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.

[@vlntsolo]

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.

[@klmcwhirter]

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.

[@bryanmurasira9682]

You just made this video with me just being tasked to create a flask graphql API with sqlalchemy. Thx a lot.😂

[@iankaranja7765]

You are in a lot of trouble my friend.

[@pacersgo]

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.

[@saitaro]

If you work with SA, for migrations you're supposed to use alembic. For graphic interface there is SQLAlchemy Admin.

[@ravenecho2410]

​@@saitaro alembic isnt bad

[@aashayamballi]

I know both SQLAlchemy and Django ORM take different approaches. But personally I liked Django ORM more than SQLAlchemy.

[@Ghost-tv3kd]

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

[@Ghost-tv3kd]

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

[@sk45293]

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.

[@BenderAnt]

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.

[@sk45293]

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

[@hugofenosoa7280]

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

[@ArjanCodes]

I have been training my psychic powers, glad to know they're working!

[@pedrg19]

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.

[@diot]

Alembic is one of the best things about Sqlalchemy and deserves a video as well

[@__mostafa__]

Thank you so much Arjan..I can't wait for your next exciting videos😃❤

[@ArjanCodes]

I'm glad you like the content!

[@mathyeu2]

Could you make a video FastAPI + SQLModel ?

[@_DRMR_]

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

[@castor3435]

kzbin.info/www/bejne/fYCxqpdmeLqlhsU

[@raphaelprates1500]

Another great video! Thanks, Arjan! I've studying SQL Alchemy + Flask. As a lot of guys already said in the comments, the documentation of SQL Alchemy is not so friendly for noobs!!

[@ArjanCodes]

I'm happy to hear you enjoyed it! Thank you for the comment, Raphael!

[@razdingz]

bro ur chan is best for python stuffs

[@luricci8471]

i used ORM in Django, which i didn't really understand at first as a SQL guy. Now with my joined young Software ingenieur we developed a big project with docker and APIs and have now a very flexible backend. I can all beginners suggest to learn and start with ORM.

[@PedigreeGuitars]

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.

[@twitchizle]

Skill issue

[@udaym4204]

in fastapi production tips should we use guivicorn with uvicorn in fastapi

[@austinkuo6623]

@arjanCodes, could you also have a course talk about what's the difference using pyODBC and SQLAlchemy? I feel I could just using pyODBC do everything directly. For example, connect to SQL server, execute query, fetch data.

[@auroragb]

One problem with me using the declarative method of accessing data instead of SQL is it's really unclear if it will create the table if I already have it and if I don't include columns not used by my queries if it will work. Also, if a key is a primary key in db, what happens if I don't declare it in code. Things like this, makes me continue to build SQL in code using pyodbc to connect to db instead of SQL alchemy

[@asv5769]

Bi Arjan, I‘m a new subscriber, now watching your videos one after another. I notice you use VScode, and I wonder how did you manage to make vscode to predict your typing, like shown at 8:00 when adding second_user? Is this some kind of extension or similar? Maybe there is video somwhere explaining this, but it looks lika a magic.

[@ArjanCodes]

Hi, welcome here 😎. What you’re seeing is the GitHub Copilot extension.

[@AloisMahdal]

In middle ages I would be Python developer working with SquealAlchemy.

[@SuperHardik12345]

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.

[@jake115577]

Why is UserAuth using Column and not Mapped?

[@MrVernuk]

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 🙂

[@acdiwalinaiks5828]

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

[@zbyszeks3657]

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.

[@MicheleHjorleifsson]

What about peewee vs. sqlalchemy ?

[@bombus1340]

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 😅

[@TheedonCritic]

I love the intro!

[@ArjanCodes]

Thanks!

[@MicheleHjorleifsson]

would love to see a deep dive into using oso

[@andybecker5001]

SQLAlchemy is great. I use it everyday for small ETL jobs.

[@tombischopink3343]

cant you get some more code syntax highliting in vsc?

[@NostraDavid2]

He can. I don't follow why he doesn't use it. Oh well.

[@alexenriquecrispim830]

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)

[@lefterispapoutsis9154]

I've always found django's orm simpler/quicker to use, but yeah sqlalchemy is pretty cool as well

[@Chris-yd2nr]

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.

[@fredericoisaac9074]

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 .

[@twentytwentyeight]

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 😅

[@jeroenverboom661]

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 ?

[@geoffbeier]

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.

[@TomTrval]

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.

[@ipelezikis]

Nice video as always. However imho Django orm is easier to use with more of standard practices and way better documented.

[@hipertracker]

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.

[@johnidouglasmarangon]

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.

[@FrankyBraem]

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.

[@radoeka]

What about SQLModel? That's SQLAlchemy but with syntax high lighting.

[@D_T244]

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.

[@mystisification]

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.

[@udaym4204]

make video on connection pooling pg bouncer and pure sql without any library

[@Lomronn]

Oso might just be the missing component for our open source library. Thanks for this advertisement, they sometimes are utterly relevant !

[@ArjanCodes]

You’re welcome! I do my best to pick partners for sponsorships that I think actually offer a cool product.

[@Lomronn]

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

[@abhaykatiyar3539]

you should talk on imperative mapping

[@bobakserioso6858]

i don’t understand why your code is almost completely uncolorized lately

[@lungenbrötchen]

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.

[@frd85]

very nice overview. thx.

[@ArjanCodes]

Glad to hear you liked it!

[@PedigreeGuitars]

Just what I was looking for….❤

[@ArjanCodes]

Glad it was helpful!

[@brainforest88]

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?

[@simonhaines7301]

Do you type SQL in strings within your Python? Doesn't it bother you not having language server, syntax highlighting?

[@christinemurray1444]

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.

[@fburton8]

In the Middle Ages, I would probably be a hostler or an ostler. That’s because I am quite good with horses.

[@TheSzybas]

A different meaning organ player suddenly makes sense to me... I have interest in music, and I always wanted to be pathomorphologist.

[@JonMartins]

Since I'm Brazilian and came from a family that goes from Africa and Portugal. I would probably have been anything between a Portuguese aristocrat and an African slave.

[@y2ksw1]

I would have been body guard.

[@TipanVerella]

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?

[@coladock]

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.

[@vlntsolo]

@@coladockwouldn't SQLModel (SQLAlchemy + Pydantic) work for you? It's not just FastAPI addon, it should work as standalone ORM.

[@TipanVerella]

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

[@FrankyBraem]

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.

[@rrwoodyt]

Argh-- I love/hate SQLAlchemy. It makes my life both easier and harder, every day!

[@obsidiansiriusblackheart]

I am Jewish, so my job in the middle ages would be... Usery, I hope (the other options are way worse than "banking")

[@inspiredbyrd]

In the middle ages I would be a cobol programmer....or...was that even earlier...

[@sophiophile]

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.

[@edgeeffect]

Um... I'm rather interested in "History of Science" and I'd just like to say that you're being a little unfair to alchemy by calling it "questionable" :)

[@epgui]

I prefer a very different approach: I use SQL directly, and use the PugSQL library as a very minimal interface between python and SQL. SQLAlchemy is frankly a monstrosity. Pretty much all ORMs are monstrosities IMO.

[@MikeChristopher-l6m]

Any system that allows direct access to tables is a gigantic security risk.

[@keepsecret7624]

I would personally much prefer a written tutorial -- sick of KZbin adverts too.

[@ArjanCodes]

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.

[@alengine3748]

Fresh water trenching

[@wexwexexort]

I would probably die in my first battle if I born in middle ages.

[@danieldarko6495]

Sqlalchemy is the reason why i am staying with django.

[@pycz]

Well, they wouldn't call a simple thing "alchemy" 😅

[@amandhol2313]

Haha, my job would prolly be a farmer, cuz its my generational occupation

[@NicolayGiraldo]

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

[@ravenecho2410]

Ha!

[@nityaincorta4539]

DuckDB with python is much faster

[@devilasks]

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.

[@hubstrangers3450]

Thank you.....mafia is only way forward....joking..

[@_Akhilleus_]

I like Peewee...

[@marcusg2000]

Horrible library, stay away far from it!