HN Explorer

Show HN: Prisma Python – A fully typed ORM for Python (github.com)

130 points by probablyrobert ↗ HN
I created this ORM to fill a gap in the Python ecosystem. Due to the nature of typing in Python there are no other Python ORMs that can provide correct type hints. Prisma Python manages to work around this by auto-generating python types.

Aside from static type checking, providing type hints means that you will get autocomplete suggestions for you which for me is the killer feature for this ORM (see the GIF in the README for an example).

It's also built on top of Prisma, a next-generation ORM for TypeScript which means that the core query building and connection handling has been battle tested, getting around a potential concern with adopting a new ORM.

Prisma Python also supports PostgreSQL, SQLite, MongoDB, MariaDB and more!

90 comments

[ 3.3 ms ] story [ 183 ms ] thread
[deleted]
(comment deleted)
melony
Might want to explain in the README that Prisma's core is written in Rust. You don't need to install an entire JS stack to use this.
BozeWolf
Yes! I was looking for this as well.
bckr
This looks really cool. I might have to try it for my current project.

Question: The Readme says there's room for massive improvement in performance. What's the performance like right now? Any benchmarks?

probablyrobert
I haven't written benchmarks yet but it is something I am working on. The reason that performance is mentioned in the README is that I haven't spent time on improving it yet.

The biggest blockers in terms of performance are:

- Communication with the internal Rust engine is performed over HTTP instead of FFI.

- You cannot select a subset of fields (every scalar field in a model is selected)

- You cannot skip pydantic validation which while fast is unnecessary in some cases

With that said Prisma Python should be reasonably fast but I would expect it to be on the lower end of the spectrum compared to other Python ORMs. I have ran some load tests locally and creating 500,000 records took about 90 seconds.

anentropic
What is the Rust engine doing here?

How come GitHub doesn't highlight any Rust code in the project repo?

probablyrobert
Because Prisma Python currently interfaces with the Rust engine over HTTP (I am looking into changing this) and the Rust engines can be found here:

https://github.com/prisma/prisma-engines

bb88
Given the state of PyPi these days and third party packages, I would just prefer to have pure python, even if it is slower.

It means I don't have to worry about things breaking on cross compilation between mac/windows/linux.

joshribakoff
Prisma uses rust for two main reasons from what I understand. 1) to keep the engine portable and maximize code reuse, to make projects like this Python adapter possible for example 2) under the hood Primsa does not use joins. It does a waterfall of queries (but not n+1), then stitches the data together in memory, which happens in the rust engine for performance reasons
WaitWaitWha
Nowhere is the acronym ORM explained, let alone how it can benefit Python programmers.
probablyrobert
ORM stands for Object Relational Mapper and is essentially a wrapper over raw SQL queries to provide an easier to use interface.
tomrod
To add to this answer, an ORM is useful to deploy queries across multiple database vendors (e.g. Oracle, Postgres, SQLite) without re-writing queries. When used correctly it sanitizes SQL as well as supports with typing, as this library assist with.

Though to add to another comment, this is pretty basic in the CRUD and front-end world, and learning how to learn when confronted with unfamiliar information is a very useful life skill.

WaitWaitWha
Thank you. I heard of ORM, My comment was mostly to enhance the readme.
occz
Presumably a lot of Python-developers already know what an ORM is and how they can benefit from it, in particular given how long SQLAlchemy has been around.

The rest can presumably hit up their favourite search engine and type in 'ORM', hit 'Search' and learn quite quickly what it is.

[deleted]
(comment deleted)
Havoc
> The rest can presumably hit up their favourite search engine and type in 'ORM'

Thats missing the point quite badly. You don't want anything in the opening sentence of you site triggering users navigating away / switching to another browser tab

lytefm
This looks promising. Using prisma both in TypeScript app backends and in Python data pipelines would make switching between the two without much cognitive overhead much easier.

I'll probably oppose introducing a different Python ORM at work until Prisma Python reaches 1.0

probablyrobert
Thanks :)

Yeah that is understandable, it is being successfully used in production but it is safer to use a more stable ORM for mission critical products.

If you don't want to have to duplicate your model definitions you could write a custom Prisma Generator to generate models for a different Python ORM.

https://prisma-client-py.readthedocs.io/en/stable/reference/...

lytefm
Nice, sharing the models across Codebases but only doing the migrations on one side is definitely what I'd be looking for.
samwillis
> by auto-generating python types

I'm assuming you are using a code generator? My understanding is that as MyPy is a static analyser there is no way to automatically create types at runtime (which is actually super annoying, particularly for a language as dynamic as Python).

probablyrobert
You are correct, we use code generation to define the query API types.
dragonwriter
You can create Python types at runtime, and Python’s runtime type checking features predate it's static analyzers. Unfortunately, the additional kinds of objects used for typechecking in the static analyzers (beyond those which are also runtime types) don’t work with runtime type checking, nor do static type declarations (even using types that are also runtime types.) (And, obviously, AOT static analyzers can’t make use of types that don’t exist when they run.)
probablyrobert
Yes it is rather unfortunate, Python's typing system doesn't support dynamically creating types like you can in TypeScript :/
dllthomas
> dynamically creating types

Could you explain what you mean by this? I'm not clear on what typescript behavior this describes.

samwillis
Exactly, it’a brilliant that they designed a system where the annotations themselves can be dynamic and read at runtime, it has so much potential for interesting things. But MyPy completely hobbles it by preventing all the dynamic potential, even if MyPy had an escape hatch to say ignore anything derived from this type as it’s not possible to describe statically that would be better than nothing.

It unfortunately leaves you with a choice of either taking advantage of dynamic annotations or using MyPy, but not both.

And as the sibling comment says, typescript did it so much better.

probablyrobert
There is actually an escape hatch you can use, although it is a bit clunky:

    ```py
    from typing import TYPE_CHECKING

    if TYPE_CHECKING:
        Foo = "expression that mypy can understand"
    else:
        Foo = "dynamic expression"
    ```
samwillis
Somehow in all my searching the other day I missed that, thanks!
BerislavLopac
Python also offers structural typing, which is both dynamic and mypy-compatible.
cabalamat
I have also written a python ORM, Bozen[1]. In Bozen you define a table like this:

    class Book(MonDoc):
        title = StrField()
        yearPublished = IntField()
Is it possible to give run-time types to `title` and `yearPublished`, in a way that works with mypy and the Python typing ecosystem? If so, is there a tutorial on this, as I'd like to add the feature to my ORM.

1: https://github.com/cabalamat/frambozenapp

probablyrobert
The most obvious way would be to simply annotate the fields, e.g.

  class Book(MonDoc):
    title: str = StrField()
    yearPublished: int = IntField()
If this means too much redundant information for you then you could try defining the fields to return the corresponding python type e.g.

  def IntField() -> int:
    ...
cabalamat
I could certainly annotate the fields like you suggest. But that goes against the spirit of Bozen which is DRY.

When I define something as a `StrField` I'm already saying it's a string, so I shouldn't have to say it again elsewhere. It would be nice if Python has an API such that I can write code within my `StrField` class that tells python that `title` is a `str`, but to the best of my understanding this doesn't exist.

cabalamat
> Prisma, a next-generation ORM for TypeScript which means that the core query building and connection handling has been battle tested, getting around a potential concern with adopting a new ORM.

Can I suggest you put this at the top of your README.md file, as I had never heard of Prisma before and it would have helped.

> Prisma Python

Is your program called Prisma Python or Prisma Client Python? You seem to be inconsistent with naming.

probablyrobert
Thanks for the feedback, I will add that.

> Prisma Python

I originally decided to go with Prisma Client Python however I found this to be too verbose and have recently used Prisma Python. I do need to decide on one and stick with that. Thanks for pointing that out

klft
Seems to solve the same problem as SQLModel.

From [1]: "SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It is designed to be intuitive, easy to use, highly compatible, and robust.

SQLModel is based on Python type annotations, and powered by Pydantic and SQLAlchemy."

[1] https://sqlmodel.tiangolo.com/

probablyrobert
Yes it is similar to SQLModel however as SQLModel is based off of SQLAlchemy, the query API is not typed. That is the difference.
klft
My understanding is that this is exactly what SQLModel adds on top of SQLAlchemy.

statement = select(Hero).where(Hero.age > 32).limit(3)

But I am only halfway through the tutorial so I might be wrong.

HN discussion of SQLModel here [1]

[1] https://news.ycombinator.com/item?id=28294534

probablyrobert
My understanding of SQLModel is that it brings the benefits of both Pydantic and SQLAlchemy. The benefit of merging Pydantic with SQLAlchemy is that you can then use the database models directly in FastAPI route definitions.
O5vYtytb
AFAIK SQLAlchemy 2.0 (coming soon?) will use a new system for full typing.
probablyrobert
Yes I had heard about that but due to the nature of Python typing, they can't actually type the query API properly without falling back to Any, negating the benefits of type checking.
BozeWolf
The creator of sqlmodel seems to be very busy with fastapi and other projects. Only so much you can do in a day, so progress is slowed down by that.

Prisma looks nice. Is it a solo project mostly? Or is there a company paying for your time to spend on this ORM? To me it is very important that such an important module as an ORM will have support for the lifetime of an application. Or at least when the app is still being developed.

probablyrobert
Unfortunately this is a solo project, however I am in communication with the Prisma team and the core engine that Prisma Python uses is backed by Prisma.
djstein
@propbablyrobert great job on this! I hope you can get buy-in from the Prisma team for support. This would be great for their moat.
probablyrobert
Thank you! I hope so too, it would be incredibly helpful.
sorenbs
This is really cool!

We (Prisma) would love to support this project in any way we can. You can find my email in bio if you are interested in a call :-)

leetrout
A comment from a previous Prisma post

> under the hood @prisma/client was spinning up it's own GraphQL server that it would send requests to in order to generate SQL to send to postgres[1]

So is this the same approach you are taking with the HTTP API?

[1] https://news.ycombinator.com/item?id=26889543

probablyrobert
Yes I am using the same approach however I am working on using native FFI bindings which is what the TypeScript client is using now.

https://github.com/RobertCraigie/prisma-client-py/pull/165

leetrout
That's great.

I think it makes it such an easy onramp to integrate with something by having HTTP based APIs (or really, gRPC, even) even if it is lower performance compared to native libraries.

Looking forward to trying this.

crucialfelix
I use Prisma typescript daily and love it. It's fast to write complex queries and readable joins. The type hints are well documented and custom generated for your models and relationships.

It would be great if Prisma could support yet more languages! It's a great product.

That said, I would not have two backends sharing the same database, even if one is the master that runs migrations. A component should only have one reason to change.

probablyrobert
Yeah, sharing the same database between two backends would not be a good idea. That said you do not have to use the TypeScript client at the same time as the Python client, they are independent of each other.
joshribakoff
I use Prisma and it’s far from perfect but what I love about it is you can introspect a legacy database and generate a Prisma schema, and use the database as the source of truth. You’re not tied to a 1:1 relationship between classes and tables, and the correctness of the types do not depend on tediously annotating code correctly. Excited to see more languages supported!
post-it
Are there any ORMs that do that currently? That sounds awesome
camdenreslink
Entity Framework allows you to do that (if you are coding for .NET). It is the best ORM I've encountered.

The option to do it is called "Code First from Database".

dpacmittal
MikroORM can also auto generate schema from an existing database
hutrdvnj
Is it just me who thinks that plain SQL has won the SQL vs ORM battle? I mean why should I learn how to use X different ORM syntaxes in Y different languages and in addition to that lose full control over the actual query.
probablyrobert
Use whatever works for you! I personally felt the same until I learned of Prisma. The main benefits for me are type checking and autocomplete.

Autocomplete is the big one as it lessens the learning curve immensely. You no longer have to search through documentation to find a relevant method, you simply have to trigger autocomplete and it'll show you what you can do!

NAHWheatCracker
That's how I've personally felt since I first encountered ORMs a decade ago at a data intensive startup where ORMs were a burden. Yet I keep running into ORMs.

Luckily, they are better implemented at my current job. I still think it gets in the way quite often, but I recognize that it enables some outsized returns on effort. Entire REST APIs can be generated with things like Spring Data. SqlAlchemy can be pretty slick in Python.

I still write raw SQL on my personal projects, but I recognize that I have to spend a lot of time doing that.

inshadows
How is this DSL acceptable?

    posts = await client.post.find_many(
        where={
            'OR': [
                {'title': {'contains': 'prisma'}},
                {'content': {'contains': 'prisma'}},
            ]
        }
    )
SQL for comparison:

    ... where title like '%prisma%' or content like '%prisma%'
lucas_codes
Now try filtering on a relation!
probablyrobert
I find the relational API very easy and intuitive to work with. What do you not like about it?
lucas_codes
So do I - the parent is choosing to compare the most basic example which didn't show the benefits of using an orm over raw sql
probablyrobert
Ah sorry I misread your comment, I thought you were agreeing and saying that filtering by a relational field is also unacceptable DSL.

Thank you :)

subsection1h
This is one of the things I've never understood about ORMs. They're not helpful for simple queries, but they're also not helpful for the most complex queries. When I've used ORMs in past (e.g., Active Record), we regularly needed to bypass the ORM to write raw SQL for complex queries in order to get the best performance.
mypalmike
With Python and SqlAlchemy, I was surprised how intuitive some rather complex queries were to write and read, and by the quality of the generated SQL.
probablyrobert
How do you suggest to improve it?
jjice
I think the parent is saying that the SQL example is simpler and easier to read.
vonseel
Yeah, but he’s completely skipping over the fact that you’re going to lose some of the readability of short SQL statements switching to an ORM with methods, types/classes, functions, etc., but you can gain in maintainability of code.
IanCal
Not the original author, but "or" at the start then a list may be better called "any". That (to me) would read more clearly. Particularly if you were passing in the contents, '"or": var' would make me think "var or what?" But '"any": var' seems more obvious.

Has a clear link to the python "any" as well

dgb23
Haven’t used prisma but I think their query builder type checks (not just the results) which make composing and writing them quite a bit more convenient than the example shows.
mypalmike
Try making an apples to apples comparison.

You neglected to write the SELECT, which would typically not be as simple as "SELECT * from post".

You neglected to write the code to iterate over the results, which is trivial in the ORM version. In the SQL version it requires cursors and the error-prone manual mapping of positional data to named data.

inshadows
Bullshit. I don't need to use cursor with Python's sqlite3 module if I use the shorthand API exported by the module. I can write abstractions in Python. But I don't need to write shitty abstractions like the one for filtering.
messo
How does Prisma compare to EdgeDB? Pros and cons? I have limited experiences with DBs and ORMs, but EdgeDB looks very interesting.
probablyrobert
I am not very familiar with EdgeDB but it is quite different to Prisma. EdgeDB is a database while Prisma is a database client. EdgeDB do provide their own clients to interface with the EdgeDB database but with Prisma you can connect to many different databases, e.g. PostgreSQL, SQLite, MongoDB etc
RedCrowbar
That's correct. Prisma is an ORM with all the pros and cons of being one. EdgeDB, on the other hand, is a brand new graph-relational database server, built on PostgreSQL.

ORMs have the ability to work with multiple RDBMS implementations, but for that they trade away expressive power and efficiency. Prisma is fairly slow, especially when your query is fetching multiple relationships, because it does multiple DB roundtrips to fetch parts of the result and reconstitutes it on the client. ORM APIs are generally very limiting, as there is no general way of doing server-side computation (e.g. do a comparison on a substring of a string property or simply do arithmetic).

EdgeDB does not have these problems, because its query language, EdgeQL, is designed to be efficiently embeddable into a programming language without any loss of expressive power or performance. At this moment we have a fully-featured TypeScript/JavaScript builder [1], with Python and Go coming soon.

[1] https://www.edgedb.com/docs/clients/01_js/index#the-query-bu...

(Full disclosure: I work on EdgeDB)