Hacker News new | past | comments | ask | show | jobs | submit login
Show HN: Prisma Python – A fully typed ORM for Python (github.com/robertcraigie)
130 points by probablyrobert on Feb 21, 2022 | hide | past | favorite | 87 comments
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!




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!


Are there any ORMs that do that currently? That sounds awesome


If you're wanting to automatically generate the Prisma Schema then all you have to do is create the schema file, define the datasource connection and then run `prisma db pull`


Indeed! https://www.prisma.io/docs/concepts/components/introspection


See F# and it's type providers.


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".


MikroORM can also auto generate schema from an existing database


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.


Yes! I was looking for this as well.


Good point, I'll update it, thanks!


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%'


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.


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.


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.


Now try filtering on a relation!


I find the relational API very easy and intuitive to work with. What do you not like about it?


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


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.


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.


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 :)


How do you suggest to improve it?


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


Thanks for the suggestion, I've created an issue to track this as I do agree that `ANY` conveys the operation more clearly.

https://github.com/RobertCraigie/prisma-client-py/issues/293


I think the parent is saying that the SQL example is simpler and easier to read.


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.


Write SQL.


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/


Yes it is similar to SQLModel however as SQLModel is based off of SQLAlchemy, the query API is not typed. That is the difference.


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


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.


AFAIK SQLAlchemy 2.0 (coming soon?) will use a new system for full typing.


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.


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.


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.


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


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/...


Nice, sharing the models across Codebases but only doing the migrations on one side is definitely what I'd be looking for.


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.


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!


Big fan of queries being written in SQL and yielding pure, properly structured business objects.

An example of this approach is PureORM[0]

[0]https://github.com/craigmichaelmartin/pure-orm


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.


> 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.


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


> 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).


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.)


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.


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"
    ```


Somehow in all my searching the other day I missed that, thanks!


Python also offers structural typing, which is both dynamic and mypy-compatible.


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


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:
    ...


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.


Yes it is rather unfortunate, Python's typing system doesn't support dynamically creating types like you can in TypeScript :/


> dynamically creating types

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


Maybes mapped types are an example? https://www.typescriptlang.org/docs/handbook/2/mapped-types....


Yes mapped types is a good example, Pick is also a good example:

https://www.typescriptlang.org/docs/handbook/utility-types.h...


Ah, I read "dynamic" as implying something at runtime. I agree that these are extremely useful.


You are correct, we use code generation to define the query API types.


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.


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.


How does Prisma compare to EdgeDB? Pros and cons? I have limited experiences with DBs and ORMs, but EdgeDB looks very interesting.


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


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)


Nice! I opened a feature request for it already and shared with our Python community.


Thank you for sharing! It is much appreciated!


Also, the ticket I opened was promptly replied to and politely closed. This is actually an issue already open under this ticket:

https://github.com/prisma/prisma/issues/2879


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?


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.


What is the Rust engine doing here?

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


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


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


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.


No need to worry, the current behaviour will be preserved :)

The method used to interface with the internal engine will be able to be configured in the schema: https://www.prisma.io/docs/reference/api-reference/prisma-sc...


@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.


Thank you! I hope so too, it would be incredibly helpful.


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 :-)


fully typed? it used dicts for queryinb - should maybe look at sqlalchemy for comparison


Yes it is fully typed because of TypedDicts, https://docs.python.org/3/library/typing.html#typing.TypedDi...

SQLAlchemy on the other hand provides very little (if any) type hints for their query API.


[deleted]


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


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


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.


Nowhere is the acronym ORM explained, let alone how it can benefit Python programmers.


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.


> 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


ORM stands for Object Relational Mapper and is essentially a wrapper over raw SQL queries to provide an easier to use interface.


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.


Thank you. I heard of ORM, My comment was mostly to enhance the readme.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: