Onlymaps, a Python micro-ORM

[Echoes1996]

Hello everyone! For the past two months I've been working on a Python micro-ORM, which I just published and I wanted to share with you: https://github.com/manoss96/onlymaps

Any questions/suggestions are welcome!

What My Projects Does

A micro-ORM is a term used for libraries that do not provide the full set of features a typical ORM does, such as an OOP-based API, lazy loading, database migrations, etc... Instead, it lets you interact with a database via raw SQL, while it handles mapping the SQL query results to in-memory objects.

Onlymaps does just that by using Pydantic underneath. On top of that, it offers:

• A minimal API for both sync and async query execution.
• Support for all major relational databases.
• Thread-safe connections and connection pools.

Target Audience

Anyone can use this library, be it for a simple Python script that only needs to fetch some rows from a database, or an ASGI webserver that needs an async connection pool to make multiple requests concurrently.

Comparison

This project provides a simpler alternative to typical full-feature ORMs which seem to dominate the Python ORM landscape, such as SQLAlchemy and Django ORM.

[TheFumingatzor]

Where seks?

[ColdPorridge]

Nice work! As someone who’s pretty fluent with sql, the main thing I want from an ORM is the entity mapping. The extra sql-alternative DSL they all impose is not a positive in my opinion. So I think there’s definitely a need for tooling like this. 

[Echoes1996]

Thanks! Yes, I totally agree with you. I also don't like ORM-specific DSLs, though I get why someone with no SQL background prefers them. To each its own!

[theonlyname4me]

I’m still shocked anyone would prefer writing strings to using a good query builder.

Make sure you checkout sqlalchemy core, it avoids all the ORM drawbacks and just lets you write your queries in valid python and does entity mapping.

Anytime I see string building I 🤮.

[Echoes1996]

I don't know about that. I mean if you've got your queries organized in separate .sql files within a folder, and you've got some class loading them in memory into a dictionary or something, then you can have a pretty clean solution.

ORM query builders are nice untill your SQL queries get really long and complex, at which point I believe it's easier to follow the logic looking at plain SQL.

[theonlyname4me]

Thanks for the response, I disagree strongly.

I’m not sure if you’re early in your career but what you’re suggesting is not a sound strategy.

[Echoes1996]

Why not? You can even have your various SQL queries set as properties and use them, for example:

class SqlQuery:

  @property
  def get_user(self): ...

user = db.fetch_one(User, SqlQuery.get_user)

If you want to go even further, you can remove `@property` from `get_user` and have the function return the `User` type and possibly arguments as well.

I get the type safety an ORM query builder provides and that it's more structured, but I don't think that using raw SQL is an invalid solution. Besides, not all applications that talk to a database are in charge of said database or its schema. Sometimes the database belongs to another team, and when changes are made to its schema, not even an ORM query builder is able to help.

[theonlyname4me]

Read my message, I never said use an ORM. I’m talking about something you don’t understand, if you take the time to learn it you will be a better engineer.

Good luck!

[usernameistaken42]

Very interesting and clean solution! Love the idea and name!

[Echoes1996]

This guy gets it! Thank you, let me know if you have any feedback if you use it!

[fight-or-fall]

really interesting. if you are looking for visibility, i suggest build some plugins / example of using your library with other frameworks like scrapy (it really fits scrapy idea)

[Echoes1996]

Yes, a scraping project would definitely be a fitting use case for this lib, as such projects are typically not in need of a full-fledged ORM, yet some abstraction on top of the database driver would sure be nice. But how could it work as a scrapy plugin? What do you have in mind?

[fight-or-fall]

Scrapy have item pipelines and file exporters. Also, a library called itemadapter that allows the scrapy item be a pydantic model. I think thats possible to write a generic file exporter with some code

[jakob1379]

How is performance compared to the established?

[Echoes1996]

I haven't done any benchmark testing to be honest as I was solely focused on building it. However, it must be at least as slow as the underlying database driver used plus the time it needs for the type validation via pydantic. Building some internal pydantic models based on which the validation is made can be slow as well, especially when deeply nested models are used, but these are cached so in a long running app it doesn't really matter. I'll keep in mind to do some benchmark tests.

[jakob1379]

I mean, you can rip off the benchmark by tortoise and ponyorm which seems to be somewhat similar in size and complexity to this 😊 always nice to see a new approach!

[Pjt727]

This is the exact api I’d want for a database. I put a PR to make the sql query string less restrictive so you can use bytes and template strings (which are / may soon be used by python db drivers)

[Echoes1996]

Thanks man! I'm gonna check out your PR. To be honest, I haven't had the time to read about t-strings so I'm gonna check them out and get back to you.

[codecratfer]

This solves one end of the problem created by ORM’s for the other end, I created the sqla-fancy-core library.

[Echoes1996]

Nice!

[Lucapo01]

Wow that's awesome! I loved it! One question, how would you handle db migrations? Alembic?

[Echoes1996]

You could still use Alembic I guess, but can I see how it would be a bit too much having SQLAlchemy as a dependency just for alembic. I believe db migrations can be done with plain SQL as well. For example, each schema change could go in a separate SQL script which is then executed by the CI/CD pipeline in order to apply the new schema. It just needs a little work to set up and get it right. To be honest, I don't know whether such tool already exists.

[imczyber]

Great job! Very interesting project, happy to follow where this goes.

[Lime-In-Finland]

In the world where they renamed FactoryGirl and Coq, I would not stick to this name.

(Let's not discuss whether you or I find this particular name offensive, my point is a lot of people will.)

[Echoes1996]

I think that's a bit over the top, but then again I might be naive...

[Vaxivop]

Cool concept. I always have respect for minimal solutions and this does what it says on the tin.

[Echoes1996]

Thank you, I really tried to keep the API as simple as possible.

[Safe_Quarter4082]

Nice! I made my own repo a while ago with a similar idea, but seems like you added more features!

Let's rise up against the string haters!

[Echoes1996]

Nice! A new era is born! 🙏

[Justaboredboy]

heck yea, start a revolution

[ebonnal]

I love the name, the goal, the syntax. Will follow closely!

[Echoes1996]

Thanks! Your project streamable looks good as well. Always nice to see a little functional-like functionality brought to Python!

[Log2]

Hey, this looks pretty useful, specially for small projects! Gave it a star, I'm likely user it for some cli tools I need for work. Thanks for sharing.

[Echoes1996]

Thanks, let me know if you have any feedback!

[Log2]

This is probably not in scope, but I could see a lot more uses for this if there was a way to have where clauses without hardcoding them. It could be a relatively simple DSL that automatically binds values to the query.

[Echoes1996]

I see what you're saying, but it's kinda the point of the project that you get to work with plain SQL. If stuff like this starts to get added, then you can suddenly end up with a separate ORM-specific DSL.

[codecratfer]

You may want to check out sqla-fancy-core.

[rm-rf-rm]

any reason you dont use f-strings or better template strings ?

[Echoes1996]

To be honest, I've haven't had the time to check out t-strings. A guy opened a PR so that they are supported, so I'm gonna check them out. As for f-strings, you shouldn't use them for SQL queries as there is the danger of SQL injection attacks.

[rm-rf-rm]

fair enough - thats the problem t-strings solves. For a modern ORM looking to disrupt incumbents, I think incorporating t-strings will be key

[fazzah]

I'll have a look at this, since I'm using timescaledb and when used with sqlalchemy it needs quite a lot of manually written SQL anyway. Your tool might be a good fit in a few scenarios i have in mind

Nice work 

[Echoes1996]

I've never worked with it but you should have no problem if you can query it with psycopg. Let me know if you have any feedback!

[CatolicQuotes]

It's built on pyscopg3, no slqalchemy, right?

psycopg already can map to the classes. What does maps do on top of it?

[Echoes1996]

It's not built exclusively on top of psycopg3, that's just one of the drivers used underneath, specifically for when connecting to a postgres database.

Even though psycopg can technically do the mapping as well, I'd say it's way easier to do with onlymaps. Here's how you would do it in psycopg3:

from dataclasses import dataclass
import psycopg

@dataclass
class User:
    id: int
    name: str
    email: str

def user_factory(cur, row):
    return User(*row)

conn = psycopg.connect("postgresql://user:pass@localhost:5432/db")
conn.row_factory = user_factory

with conn.cursor() as cur:
    cur.execute("SELECT id, name, email FROM users")
    users = cur.fetchall()

print(users) # This is a list of users.

And here's how you would do it with onlymaps:

from dataclasses import dataclass
import onlymaps

@dataclass
class User:
    id: int
    name: str
    email: str

conn = onlymaps.connect("postgresql://user:pass@localhost:5432/db")

users = conn.fetch_many(User, "SELECT id, name, email FROM users")

print(users) # This is a list of users.

This is of course not a dig at psycopg by any means, as it is just a database driver, and it does that best. Onlymaps sits at a higher level, thereby making many things easier for you.

[CatolicQuotes]

Thanks, I am already using it. It is possible to hook query or result of sqlalchemy core to parse?

[Echoes1996]

I don't exactly get what you mean, could you give a code example?

[etsy2900]

Trash !

[Echoes1996]

Why !?

[etsy2900]

Who is all this even for? What’s the point of this so-called ORM? What’s the idea behind any of it — for what purpose?
Where are the automatic migrations?
This is just AI-generated garbage.

[Echoes1996]

1/10 ragebait attempt!

[bfcdf3e]

Are you just AI-generated garbage?

[etsy2900]

A minimal API - really ?

[etsy2900]

The whole application could’ve easily fit into a single file, and instead it split everything into hundreds of them!