Why Does Bitcoinlib Need a Database Backend?

I am a noob in Bitcoin and trying to do some basic programming like implement a watch only wallet or initiating a transfer using the python bitcoinlib library.

From some reading of the documentation, it seems the library needs a back-end SQL database, although it creates the database transparently if needed.

So my question is, why? As in, I believed (correct me if I am wrong), all relevant transactions data since the genesis block are in the the Blockchain? And that is not really tabular/relational data suitable for a SQL interface? What's the role of the additional database?

Also, if I want to send sats, or get the balance of an XPUB, should not the library go query the main blockchain? Or does the SQL database acts as the data store? Why so?

Databases specialize in data storage. That's a hard problem that requires a lot of work. Each application could solve this on their own, or they could combine efforts and solve the problem together.

What specifically are those problems for applications? Scaling, read/write speed, disk storage and efficiency, indexing, compression, and fast queries rank among the top issues.

So Bitcoin had two options:

1. reinvent the wheel for the specialized data storage needs or
2. use a generalized abstraction that's already good at it.

That's why Bitcoin uses a backend database (LevelDB). That database is better at doing specific data storage tasks than a naive Bitcoin implementation could do without an enormous amount of extra work.

Also, LevelDB isn't a SQL database. SQL is just one category of database, there are many that specialize in different applications. LevelDB's README is a great explainer of what's useful about it.