Skip to main content

GINO Is Not ORM - a Python ORM on asyncpg and SQLAlchemy core.

Project description


*We are working on a huge refactor in #59, which will deprecate most current APIs and be more close to SQLAlchemy. This update is expected to be done within December 2017 as version 0.6.0.* Documentation Status Updates Gitter chat

GINO - GINO Is Not ORM - is an extremely simple Python ORM, using SQLAlchemy core to define table models, and asyncpg to interact with database.

There’s been a lot of words about ORM a.k.a. Object-relational mapping - good or bad - as well as a lot of ORM libraries in Python. It is crucial to pick a most suitable one for your project, and for your team. GINO tries to stay in the middle between ORM and non-ORM, offering an extremely simple option.

GINO operates database rows with “plain old Python objects” - they are just normal Python objects, a rollback doesn’t magically change their values. Any database operations are explicit - it is crystal clear what is done underneath each GINO API. There are no dirty models, no sessions, no magic. You have concrete control to the database, through a convenient object interface. That’s it.

GINO depends on asyncpg, which means it works only for PostgreSQL and asyncio, which means Python 3 is required - actually 3.6 required for now. Based on SQLAlchemy, gate to its ecosystem is open - feel free to use e.g. Alembic to manage your schema changes. And we specially designed a few candies for the Sanic server.


A piece of code is worth a thousand words:

import asyncio
from gino import Gino, enable_task_local

db = Gino()

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer(), primary_key=True)
    nickname = db.Column(db.Unicode(), default='noname')

async def main():
    await db.create_pool('postgresql://localhost/gino')

    # Create object, `id` is assigned by database
    u1 = await User.create(nickname='fantix')
    print(, u1.nickname)  # 1 fantix

    # Retrieve the same row, as a different object
    u2 = await User.get(
    print(u2.nickname)  # fantix

    # Update affects only database row and the operating object
    await u2.update(nickname='daisy').apply()
    print(u2.nickname)  # daisy
    print(u1.nickname)  # fantix

    # Returns all user objects with "d" in their nicknames
    users = await User.query.where(User.nickname.contains('d')).gino.all()

    # Find one user object, None if not found
    user = await User.query.where(User.nickname == 'daisy').gino.first()

    # Execute complex statement and return command status
    status = await User.update.values(
        nickname='No.' + db.cast(, db.Unicode),
    ).where( > 10,

    # Iterate over the results of a large query in a transaction as required
    async with db.transaction():
        async for u in User.query.order_by(
            print(, u.nickname)

loop = asyncio.get_event_loop()

The code explains a lot, but not everything. Let’s go through again briefly.

Declare Models

Each model maps to a database table. To define a model, you’ll need a Gino object first, usually as a global variable named db. It is actually an extended instance of sqlalchemy.MetaData, which can be used in Alembic for example. By inheriting from db.Model, you can define database tables in a declarative way as shown above:

db = Gino()

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer(), primary_key=True)
    nickname = db.Column(db.Unicode(), default='noname')

Note that __tablename__ is required, GINO suggests singular for model names, and plural for table names. After declaration, access to SQLAlchemy columns is available on class level, allowing vanilla SQLAlchemy programming like this:

import sqlalchemy as sa[User.nickname]).where( > 10)

But on object level, model objects are just normal objects in memory. The only connection to database happens when you explicitly calls a GINO API, user.delete for example. Otherwise, any changes made to the object stay in memory only. That said, different objects are isolated from each other, even if they all map to the same database row - modifying one doesn’t affect another.

Speaking of mapping, GINO automatically detects the primary keys and uses them to identify the correct row in database. This is no magic, it is only a WHERE clause automatically added to the UPDATE statement when calling the user.update().apply method, or during User.get retrieval.

u = await User.get(1)                      # SELECT * FROM users WHERE id = 1
await u.update(nickname='fantix').apply()  # UPDATE users SET ... WHERE id = 1 = 2                                   # No SQL here!!
await u.update(nickname='fantix').apply()  # UPDATE users SET ... WHERE id = 2

Under the hood, model values are stored in a dict named __values__. And the columns you defined are wrapped with special attribute objects, which deliver the __values__ to you on object level, or as column objects on class level.

Bind Database

Though optional, GINO can bind to an asyncpg database connection or pool to make life easier. The most obvious way is to create a database pool with GINO.

pool = await db.create_pool('postgresql://localhost/gino')

Once created, the pool is automatically bound to the db object, therefore to all the models too. To unplug the database, just close the pool. This API is identical to the one from asyncpg, so can it be used as a context manager too:

async with db.create_pool('postgresql://localhost/gino') as pool:
    # play with pool

Otherwise, you will need to manually do the binding:

import asyncpg

pool = await asyncpg.create_pool('postgresql://localhost/gino')
db = Gino(pool)

# or
db = Gino()
db.bind = pool

It is theoretically possible to bind to a connection object, but this scenario is not normally well tested. And as stated in the beginning, it is possible to use GINO without binding to a database. In such case, you should manually pass asyncpg pool or connection object to GINO APIs as the bind keyword argument:

import asyncpg
conn = await asyncpg.connect('postgresql://localhost/gino')
user = await User.get(3, bind=conn)

At last, GINO can be used to only define models and translate SQLAlchemy queries into SQL with its builtin asyncpg dialect:

query, params = db.compile(User.query.where( == 3))
row = await conn.fetchval(query, *params)

Execute Queries

There are several levels of API available for use in GINO. On model objects:

await user.update(nickname='fantix').apply()
await user.delete()

Please note, update without apply only update the object in memory, while apply flush the changes to database.

On model class level, to operate objects:

user = await User.create(nickname='fantix')
user = await User.get(9)

On model class level, to generate queries:

query = User.query.where( > 10)
query ='id', 'nickname')
query = User.update.values(nickname='fantix').where( = 6)
query = User.delete.where( = 7)

On query level, GINO adds an extension gino to run query in place:

users = await query.gino.all()
user = await query.gino.first()
user_id = await query.gino.scalar()

These query APIs are simply delegates to the concrete ones on the Gino object:

users = await gino.all(query)
user = await gino.first(query)
user_id = await gino.scalar(query)

If the database pool is created by db.create_pool, then such APIs are also available on the pool object and connection objects:

async with db.create_pool('...') as pool:
    users = await pool.all(query)
    user = await pool.first(query)
    user_id = await pool.scalar(query)

    async with pool.acquire() as conn:
        users = await conn.all(query)
        user = await conn.first(query)
        user_id = await conn.scalar(query)

Transaction and Context

In normal cases when db is bound to a pool, you can start a transaction through db directly:

async with db.transaction() as (conn, tx):
    # play within a transaction

As you can see from the unpacked arguments, db.transaction() acquired a connection and started a transaction in one go. It is identical to do it separately:

async with db.acquire() as conn:
    async with conn.transaction() as tx:
        # play within a transaction

There is an alternative to do this without async with, but this may be changed in next version, as discussed in #59. Also, tx is always None for now.

Because GINO offers query APIs on not only connections but also model classes and objects and even query objects, it would be too much trouble passing connection object around when dealing with transactions. Therefore GINO offers an optional feature to automatically manage connection objects, by enabling a builtin task local hack before any tasks are created:

from gino import enable_task_local

This switch creates a local storage for each coroutine, where db.acquire() shall store the connection object. Hence executions within the acquire context will be able to make use of the same connection right in the local storage. Furthermore, nested db.acquire() will simply return the same connection. This allows db.transaction() to be nested in the same way that asyncpg conn.transaction() does it - to use database save points.

async with db.transaction() as (conn1, tx1):      # BEGIN
    async with db.transaction() as (conn2, tx2):  # SAVEPOINT ...
        assert conn1 == conn2

If nested transactions or reused connections are not expected, you can explicitly use db.acquire(reuse=False) or db.transaction(reuse=False) to borrow new connections from the pool. Non-reused connections are stacked, they will be returned to the pool in the reversed order as they were borrowed. Local storage covers between different tasks that are awaited in a chain, it is theoretically safe in most cases. However it is still some sort of a hack, but it would be like this before Python officially supports task local storage in PEP 550.

Sanic Support

To integrate with Sanic, a few configurations needs to be set in app.config (with default value though):

  • DB_HOST: if not set, localhost

  • DB_PORT: if not set, 5432

  • DB_USER: if not set, postgres

  • DB_PASSWORD: if not set, empty string

  • DB_DATABASE: if not set, postgres

  • DB_POOL_MIN_SIZE: if not set, 5

  • DB_POOL_MAX_SIZE: if not set, 10

An example:

from sanic import Sanic
from gino.ext.sanic import Gino

app = Sanic()
app.config.DB_HOST = 'localhost'
app.config.DB_USER = 'postgres'

db = Gino()

After db.init_app, a connection pool with configured settings shall be created and bound to db when Sanic server is started, and closed on stop. Furthermore, a lazy connection context is created on each request, and released on response. That is to say, within Sanic request handlers, you can directly access db by e.g. User.get(1), everything else is settled: database pool is created on server start, connection is lazily borrowed from pool on the first database access and shared within the rest of the same request handler, and automatically returned to the pool on response.

Please be noted that, in the async world, await may block unpredictably for a long time. When this world is crossing RDBMS pools and transactions, it is a very dangerous bite for performance, even causing disasters sometimes. Therefore we recommend, during the time enjoying fast development, do pay special attention to the scope of transactions and borrowed connections, make sure that transactions are closed as soon as possible, and connections are not taken for unnecessarily long time. As for the Sanic support, if you want to release the concrete connection in the request context before response is reached, just do it like this:

await request['connection'].release()

Or if you prefer not to use the contextual lazy connection in certain handlers, prefer explicitly manage the connection lifetime, you can always borrow a new connection by setting reuse=False:

async with db.acquire(reuse=False):
    # new connection context is created

Or if you prefer not to use the builtin request-scoped lazy connection at all, you can simply turn it off:


JSON Property

PostgreSQL started to support native JSON type since 9.2, and became more feature complete in 9.4. JSON is ideal to store varying key-value data. GINO offers objective support for this scenario, requiring PostgreSQL 9.5 for now.

from gino import Gino

db = Gino()

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer(), primary_key=True)
    profile = db.Column(db.JSONB())
    nickname = db.StringProperty(default='noname')
    age = db.IntegerProperty()

nickname and age look just like normal columns, but they are actually key-value pairs in the profile column. profile is the default column name for JSON properties, you can specify a different name by offering the argument column_name when defining a JSON property. Actually multiple JSON columns are allowed, storing different JSON properties as needed. Also, both JSON and JSONB can be used, depending on your choice. For example:

from gino import Gino

db = Gino()

class Article(db.Model):
    __tablename__ = 'articles'

    id = db.Column(db.Integer(), primary_key=True)

    profile = db.Column(db.JSONB())
    author = db.StringProperty(default='noname')
    pub_index = db.IntegerProperty()

    values = db.Column(db.JSON())
    read_count = db.IntegerProperty(default=0, column_name='values')
    last_update = db.DateTimeProperty(column_name='values')

JSON properties work like normal columns too:

# Create with JSON property values
u = await User.create(age=18)

# Default value is immediately available
u.nickname = 'Name: ' + u.nickname
# identical to: u.update(nickname='Name' + u.nickname)

# Updating only age, accept clause:
await u.update(age=User.age + 2).apply()


There are a few tasks in GitHub issues marked as help wanted. Please feel free to take any of them and pull requests are greatly welcome.

To run tests:

python test


Credit goes to all contributors listed or not listed in the AUTHORS file. This project is inspired by asyncpgsa, peewee-async and asyncorm. asyncpg and SQLAlchemy as the dependencies did most of the heavy lifting. This package was created with Cookiecutter and the audreyr/cookiecutter-pypackage project template.


0.5.7 (2017-11-24)

This is an emergency fix for 0.5.6.

  • Fixed broken lazy connection (Contributed by Ádám Barancsuk in #114)

  • Added Model.outerjoin

0.5.6 (2017-11-23)

  • Changed to use unnamed statement when possible (#80 #90)

  • Added more example (Contributed by Kentoseth in #109)

  • Added Model.join and made Model selectable (Contributed by Ádám Barancsuk in #112 #113)

0.5.5 (2017-10-18)

  • Ensured clean connection if transaction acquire fails (Contributed by Vladimir Goncharov in #87)

  • Added ability to reset local storage (#84)

  • Fixed bug in JSON property update

  • Added update chaining feature

0.5.4 (2017-10-04)

  • Updated example (Contributed by Kinware in #75)

  • Added Model.insert (Contributed by Neal Wang in #63)

  • Fixed issue that non-lazy acquiring fails dirty (#79)

0.5.3 (2017-09-23)

  • Fixed no module named cutils error (Contributed by Vladimir Goncharov in #73)

0.5.2 (2017-09-10)

  • Added missing driver name on dialect (#67)

  • Fixed dialect to support native decimal type (#67)

0.5.1 (2017-09-09)

This is an emergency fix for 0.5.0.

  • Reverted the extension, back to pure Python (#60)

  • Used SQLAlchemy RowProxy

  • Added first_or_404

  • Fixed bug that GinoPool cannot be inherited

0.5.0 (2017-09-03)

This is also version 1.0 beta 1.

  • [Breaking] Internal refactor: extracted and isolated a few modules, partially rewritten

    • Extracted CRUD operations

    • Core operations are moved to dialect and execution context

    • Removed guess_model, switched to explicit execution options

    • Turned timeout parameter to an execution option

    • Extracted pool, connection and api from asyncpg_delegate

  • Added support for SQLAlchemy execution options, and a few custom options

  • [Breaking] Made return rows by default (#39)

  • Moved get_or_404 to extensions (#38)

  • Added iterator on model classes (#43)

  • Added Tornado extension (Contributed by Vladimir Goncharov)

  • Added Model.to_dict (#47)

  • Added an extension module to update asyncpg.Record with processed results

0.4.1 (2017-08-20)

  • Support select on model instance

0.4.0 (2017-08-15)

  • Made get_or_404 more friendly when Sanic is missing (Contributed by Neal Wang in #23 #31)

  • Delegated sqlalchemy.__all__ (Contributed by Neal Wang in #10 #33)

  • [Breaking] Rewrote JSON/JSONB support (#29)

  • Added lazy parameter on db.acquire (Contributed by Binghan Li in #32)

  • Added Sanic integration (Contributed by Binghan Li, Tony Wang in #30 #32 #34)

  • Fixed iterate API to be compatible with asyncpg (#32)

  • Unified exceptions

  • [Breaking] Changed update API (#29)

  • Bug fixes

0.3.0 (2017-08-07)

  • Supported __table_args__ (#12)

  • Introduced task local to manage connection in context (#19)

  • Added query.gino extension for in-place execution

  • Refreshed README (#3)

  • Adopted PEP 487 (Contributed by Tony Wang in #17 #27)

  • Used weakref on __model__ of table and query (Contributed by Tony Wang)

  • Delegated asyncpg timeout parameter (Contributed by Neal Wang in #16 #22)

0.2.3 (2017-08-04)

  • Supported any primary key (Contributed by Tony Wang in #11)

0.2.2 (2017-08-02)

  • Supported SQLAlchemy result processor

  • Added rich support on JSON/JSONB

  • Bug fixes

0.2.1 (2017-07-28)

  • Added update and delete API

0.2.0 (2017-07-28)

  • Changed API, no longer reuses asyncpg API

0.1.1 (2017-07-25)

  • Added db.bind

  • API changed: parameter conn renamed to optional bind

  • Delegated asyncpg Pool with db.create_pool

  • Internal enhancement and bug fixes

0.1.0 (2017-07-21)

  • First release on PyPI.

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

gino-0.5.7.tar.gz (44.7 kB view hashes)

Uploaded Source

Built Distribution

gino-0.5.7-py2.py3-none-any.whl (40.3 kB view hashes)

Uploaded Python 2 Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page