Any particular resources you recommend to learn it?

sqlite.org's documentation for the C api [1] and its explanation of what kind of SQL it supports [2] are both very good.

[1] http://sqlite.org/cintro.html

[2] http://sqlite.org/lang.html

If I may, python sqlite interface is pretty great too.

https://docs.python.org/3.4/library/sqlite3.html#sqlite-and-...

The sqlite3 module that comes with python is terrible when it comes to errors. For example, if OperationalError is raised, it could be any of the following SQLite errors:

SQLITE_ERROR, SQLITE_PERM, SQLITE_ABORT, SQLITE_BUSY, SQLITE_LOCKED, SQLITE_READONLY, SQLITE_INTERRUPT, SQLITE_IOERR, SQLITE_FULL, SQLITE_CANTOPEN, SQLITE_PROTOCOL, SQLITE_EMPTY, SQLITE_SCHEMA

How do you know which? The only thing you can do is convert the exception to a string and compare error messages. That's absurd.

How about IntegrityError? That's either SQLITE_CONSTRAINT or SQLITE_MISMATCH. Which one? Compare strings again.

If you need to use SQLite with python, apsw actually converts errors to discrete exceptions.

(I am the APSW author). The reason why the sqlite3 module (aka pysqlite) behaves the way it does is because of DBAPI (Python standard database API). That tries to make all databases look and behave the same. pysqlite could do more - eg the SQLite error code could be an attribute on the exception, but the code base is very rarely updated.

Wanting a Python wrapper for SQLite that celebrated SQLite's features, functionality and semantics rather than paper them over is why I started APSW a decade ago. It also has considerably better testing and diagnostics, and is thread safe.

http://rogerbinns.github.io/apsw/pysqlite.html

Thank you for your work on APSW, it's a fantastic library. I'm the author of the peewee ORM[1] and I've included an APSW backend[2] for folks who want to use peewee with APSW. Being able to write extension modules in Python is such a cool feature! I also made a demo that exposes Redis as a SQLite table[3].

[1] http://docs.peewee-orm.com/

[2] http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#a...

[3] https://github.com/coleifer/peewee/blob/master/examples/redi...

Hmm I liked the python types embedding and never really worried about dealing with errors that way that much but you're right that should be improved. Thanks for the suggestion.

If you’re not familiar with RDBMS, general introductions to SQL and relational algebra should be useful. Back in uni our textbook was Fundamentals of Database Systems[1]. (It’s okay, but there are probably better books out there.)

[1] http://www.pearsonhighered.com/educator/product/Fundamentals...