Prisma

Prisma is a database ORM runtime that enables to query many different databases. It enables to create, read, update and delete data with a developer-friendly API. This runtime is included within the typegate and is compatible with the following databases:

• PostgreSQL
• MySQL
• MariaDB
• SQLite (however, SQLite is unlikely to be used with Metatype as it cannot be distributed easily)
• AWS Aurora
• AWS Aurora Serverless
• Microsoft SQL Server
• Azure SQL
• MongoDB
• CockroachDB

The exact supported version can be found on their documentation. Some databases may not have some specific behaviors and features support. The precise details are also available in this page.

Your first API

Getting random data is great, but having some input variables and meaningful output is even better. The random runtime is usually used to quickly draft and iterate on an API prototype. Once the interface feels efficient and well-designed, it's time to move another runtime backed by some storage.

Please share your feedback

You can raise issues, suggest improvements, ask questions and share your feedback using comments below ↓, using private messages with the "Help, Feedback & Roadmap" button on the right → or directly using the GitHub discussions.

Prisma runtime

Prisma is a "Next-generation Node.js and Typescript ORM" supporting PostgreSQL, MySQL/MariaDB, SQLite, MongoDB, CockroachDB and Microsoft SQL Server. It is one of the main runtimes provided by Metatype and doesn't require any additional installation.

Go ahead and update typegraph.py with the highlighted lines below:

Loading...

A few things to note on the changes:

1. You can import runtimes from typegraph.runtime.Y or typegraph.providers.X.runtimes.Y for non-core providers.
2. The config method allows specifying runtime specific attributes. In this case, id shall be automatically set and incremented by the database.
3. Types get generated names unless you manually specify them. You can find the exact names in the playground documentation. Here you want to have a human friendly name as it will also be the name of the table in your database.
4. Runtimes often come with some sugar syntax to generate types and avoid manipulating functions directly. A corresponding declaration would have looked like this:

In order to use the Prisma runtime, you need to add a new environment variable. Runtimes don't take raw secrets, but instead a secret key used to look up environment variables named under the format TG_[typegraph name]_[key]. You can either add it in your metatype.yml (recommended) or in your compose.yml.

$ cat metatype.yml
typegates:
  dev:
    # ..
    secrets:
      database
        POSTGRES_CONN: postgresql://postgres:password@postgres:5432/db

$ meta dev

And now, you can iteratively continue to improve your interface, running migrations and having data stored inside your database.

Usage

with TypeGraph("prisma-runtime-example") as g:
    db = PrismaRuntime("main_db", "DB_CONNECTION")

    user = t.struct(
        {
            "id": t.uuid().config("id", "auto"),
            "email": t.email(),
        }
    )

    g.expose(
        createUser=db.create(user).add_policy(public)
    )

Raw query

Generate a raw SQL query operation on the runtime

db = PrismaRuntime("my-app", "POSTGRES")
g.expose(
    countUsers=db.raw_query(
        "SELECT COUNT(*) as total FROM User",
        t.struct({}),
        t.list(t.struct({"total": t.integer()}))
    )
)

Generate a raw SQL query operation without return

db = PrismaRuntime("my-app", "POSTGRES")
g.expose(
    setActive=db.raw_execute(
        "UPDATE User SET active = TRUE WHERE id=${id}",
        t.struct({"id": t.uuid()}),
        effect=effects.update()
    ),
)

Models

Any t.struct that is passed to a generator of a PrismaRuntime defines a model. Models must have an ID field specified by the "id" config.

Here is the list of all the available configs for model fields:

Config	Effect
id	defines the field ID for the model (a.k.a. primary key)
auto	the value of this field can be auto generated; supported for t.integer() (auto-increment) and t.uuid()
unique	make this field unique among all instances of the model

Relationships

Relationship fields must be defined on both sides of the relationship. A relationship is always defined for t.struct types and t.optional or t.list of t.struct.

Relationships can also be defined implicitly using the link instance method of PrismaRuntime.

runtime = PrismaRuntime("example", "POSTGRES")

user = t.struct(
    {
        "id": t.uuid().config("id", "auto"),
        "email": t.email().config("unique"),
        "posts": t.list(g("Post")),
    }
).named("User")

post = t.struct(
    {
        "id": t.uuid().config("id", "auto"),
        "title": t.string(),
        "author": g("User"),
    }
).named("Post")

The PrismaRuntime supports two kinds of relationship between models.

One-to-one relationships

A one-to-one relationship must be in one of these two variants.

Cardinality	Field type in Model1	Field type in Model2
1..1 ↔ 0..1	g("Model2")	g("Model1").optional()
0..1 ↔ 0..1	g("Model2").optional()	g("Model1").optional()

For the optional (0..1 ↔ 0..1) one-to-one relationship, you need to indicate on which field/model the foreign key will be by:

• wrapping the type in a runtime.link(.) with fkey=True: runtime.link(g("Model2").optional(), fkey=True);
  
• or adding .config("unique"): g("Model2").optional().config("unique").

One-to-many relationships

A one-to-many relationship must be in one of these two variants.

Cardinality	Field type in Model1	Field type in Model2
1..1 ↔ 0..n	g("Model2")	t.list(g("Model1"))
0..1 ↔ 0..n	g("Model2").optional()	t.list(g("Model1"))

Many-to-many relationships

Many-to-many relationships must be modelled explicitly using a join model.

Link

Explicitly declare a relationship between models. The return value of this function shall be the type of a property of a t.struct that defines a model. If the other end of the relationship is also defined using link, both links must have the same name.

runtime = PrismaRuntime("example", "POSTGRES")

user = t.struct(
    {
        "id": t.uuid().config("id", "auto"),
        "email": t.email().config("unique"),
        "posts": runtime.link(t.list(g("Post")), "postAuthor"),
    }
).named("User")

post = t.struct(
    {
        "id": t.uuid().config("id", "auto"),
        "title": t.string(),
        "author": runtime.link(g("User"), "postAuthor"),
    }
).named("Post")

Generators

Generators are instance methods of PrismaRuntime that can be used to generate a t.func that represents a specific operation on a specific model of the runtime. They match to the model queries defined for the prisma client API. for the type of the input t.struct and the return type.

Example:

with TypeGraph("prisma-runtime-example") as g:
    db = PrismaRuntime("main_db", "DB_CONNECTION")

    user = t.struct(
        {
            "id": t.uuid().config("id", "auto"),
            "email": t.email(),
        }
    )

    g.expose(
        createUser=db.create(user).add_policy(public),
        findUser=db.find(user).add_policy(public),
        findManyUsers=db.find_many(user).add_policy(public),
    )

Here is a list of all available generators:

• find_unique
• find_first
• find_many
• create
• update
• upsert
• delete
• delete_many

Dealing with migrations

Migrations are basically blueprints that ensure that your local database and remote database both have the same schema. Migration files are generated as modifications are made in your typegraph as you deploy.

meta cli offers various ways to deal with failing migrations. In general, you will be required manually edit the SQL changes that made your migration fails, most of the time failing migrations are related to columns/tables that were removed, renamed or added.

However, if you wish to ignore failing migrations (eg. in a testing environment), you can use the --run-destructive-migrations flag, it will reset your database schema.