Connecting to the Database

---

How to Connect

pylite-orm uses DbConn to manage database connections and DbSession to manage database sessions.

It supports thread-local storage, ensuring that each thread has its own database connection. This is particularly useful for web development — maximizing SQLite's concurrency capabilities (one writer, multiple readers).

💡pylite-orm is not an asynchronous ORM, but a synchronous multi-threaded one.

Assuming your SQLite database already exists, here's how to proceed:

from pylite_orm import DbConn, DbSession

#Create a database connection
my_db = DbConn('mydb.db')

#Create a database session
dbs = DbSession(my_db)

#Use the session to perform CRUD operations
user = dbs.select(User).filter(User.id == 1).first()

#Close the database connection when done (optional; it will be automatically closed when the thread is reclaimed)
dbs.close()

If you prefer to use a transaction approach (highly recommended):

my_db = DbConn('mydb.db')
with DbSession(my_db) as dbs:
    user = dbs.select(User).filter(User.id == 1).first()

If your project uses multiple SQLite databases (which is quite possible), simply create a DbConn instance for each database, and then create sessions on the relevant instances to perform operations.

About Database Files

All examples in this documentation assume that the database file is located in the project's root folder, so the DbConn filename parameter has no path, just "mydb.db".

However, you may want to place the database file in a separate folder; in that case, the filename parameter needs to include the path, usually a relative path. If the file is located outside the project, you must use an absolute path.

The following are all possible:

my_db = DbConn('mydb.db')
my_db = DbConn('./mydb.db')
my_db = DbConn('db_folder/mydb.db')
my_db = DbConn('./db_folder/mydb.db')
my_db = DbConn('d:/db_center/sqlite/mydb.db')

About Sync vs. Async

pylite-orm is sync and multi-threaded.

Therefore, for development frameworks like FastAPI, it is recommended to define endpoints using def rather than async def.

But this is not an absolute rule. If you prefer async code, you can still use pylite-orm in asynchronous endpoints. Simply wrap it with asyncio.to_thread.

Example code:

import asyncio
from fastapi import FastAPI

app = FastAPI()

def get_user_by_id(id):
    my_db = DbConn('mydb.db')
    with DbSession(my_db) as dbs:
        user = dbs.select(User).filter(User.id == id).first()
    return user

@app.get('/userinfo/{id}')
async def get_user(id: int):
    user = await asyncio.to_thread(get_user_by_id, id)
    return user.asdict()

In the example above, connecting to the database and querying remain synchronous, but the endpoint can be called asynchronously and return a result.

💡You don't need to create a new connection for every database access. The my_db = DbConn('mydb.db') in the example is only to meet the minimum runnable requirement.

For desktop software or automation scripts, asynchronous code is rarely used; operations are typically performed on the main thread or separate threads. pylite-orm is naturally well-suited for this.

**A Side Note
Is asynchronous always faster than synchronous?
Not necessarily. The purpose of async code is to alleviate I/O concurrency, not to increase speed. 
In general scenarios, sync multi-threading is faster than single-threaded async code.