📘
db is an extension after 0.9 releases
By the help of community feedback, db is now a glim framework extension. Therefore, it is required to install glim-extensions package.

Glim framework has an extension to support rdb abstraction by its SQLAlchemy integration. SQLAlchemy is great a rdb abstraction kit with connection layer & object relational layer abstractions. In glim, the DB API won't require Orm features if it's not enabled. However, ORM is dependent on DB API naturally. It is highly suggested to read an introduction to sqlalchemy before going further.

So, let's make configuration!

Configuration & Connection Aliasing

After the 0.9.x releases, glim framework now supports sqlalchemy as an extension. Therefore, it is required to put db conifguration to the extensions key in config. An rdb configuration is quite simple in glim framework. Simply add these lines to enable db api & orm components;

# app/config/development.py
{
    # ...
  	'extensions': {
  
      'db': {
          'default': {
              'driver': 'mysql',
              'host': 'localhost',
              'schema': 'test',
              'user': 'root',
              'password': '',
        			'orm': True
          }
      },
    }
}

You might notice that we need a "default" key in this configuration. The reason why default exists is that glim can provide multiple database connections. The "default" key is here instructing glim to make database calls without having to give schema name. You can add more database connections by adding more keys inside the "db" dictionary;

# app/config/development.py
{
    # ...
    'extensions': {
        'db': {
            'default': {
                'driver': 'mysql',
              	'host': 'localhost',
              	'schema': 'test',
              	'user': 'root',
              	'password': '',
        				'orm': True
          	},
      			'analytics': {
        				'driver': 'mysql',
							  'host': 'localhost',
					 			'schema': 'analytics',
					 			'user': 'root',
					 			'password': '',
        				'orm': False
        		}
      	},
    }
}

Now, we have an aliased database connection called "analytics". An aliased connection could be reached in DB API like the following;

from glim_extensions.db import Database as DB
# returns the connection
DB.connection('analytics') # returns the "analytics" connection.
DB.connection() # returns the default connection

Defining Models & Mapping

Glim has a Model class inside glim.db module. The Model class is simply an alias of declarative_base() in SQLAlchemy. Defining a simple User model would be the following;

# app/models.py
from glim_extensions.db import Model
from sqlalchemy import Column, Integer, String

class User(Model):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    fullname = Column(String(255))
    password = Column(String(255))

This model maps to a users database table given the properties. As you noticed, the table name is mapped using __tablename__ variable. In other words, this table is mapped as if there exists a table with the following sql;

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `fullname` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Defining Relations

In glim, you can define model relations with SQLAlchemy's relationship() function. This section provides model relation examples.

One to One

SQLAlchemy defines one to one relations by the following statement;

"One To One is essentially a bidirectional relationship with a scalar attribute on both sides. To achieve this, the uselist flag indicates the placement of a scalar attribute instead of a collection on the “many” side of the relationship."

Consider a system where users having a phone number. In glim, you can define 2 tables namely "User" and "Phone" and provide a one to one relationship with the following;

# models.py
from glim_extentions.db import Model
from sqlalchemy import Column, Integer, String, Text, ForeignKey
from sqlalchemy.orm import relationship, backref

class User(Model):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    fullname = Column(String(255))
    password = Column(String(255))
    phone = relationship("Phone", uselist=False, backref="phones")

class Phone(Model):
		__tablename__ = 'phones'
		id = Column(Integer, primary_key=True)
		user_id = Column(Integer, ForeignKey('users.id'))
		number = Column(String(12))

One to Many

SQLAlchemy defines one to many relationships by the following statement;

"A one to many relationship places a foreign key on the child table referencing the parent. relationship() is then specified on the parent, as referencing a collection of items represented by the child"

Consider a blog system where blog posts. This relation can be done in glim by the following;

# models.py
from glim_extentions.db import Model
from sqlalchemy import Column, Integer, String, Text, ForeignKey
from sqlalchemy.orm import relationship, backref

class Post(Model):
		__tablename__ = 'posts'
		id = Column(Integer, primary_key=True)
		comments = relationship("Comment")

class Comment(Model):
		__tablename__ = 'comments'
		id = Column(Integer, primary_key=True)
		post_id = Column(Integer, ForeignKey('posts.id'))
		text = Column(Text(300))

You can achieve bidirectional relationship in Post using backref statement;

class Post(Model):
		__tablename__ = 'posts'
		id = Column(Integer, primary_key=True)
		comments = relationship("Comment", backref='comments')

This change will also provide a "reverse" type of one to many relationship namely "many to one".

Many to One

SQLAlchemy defines many to one relationships by the following statement;

"Many to one places a foreign key in the parent table referencing the child. relationship() is declared on the parent, where a new scalar-holding attribute will be created"

Many to One relationships can be achieved with backref attribute. Consider a system where users can join a group. In this system, users are limited to join to only one group.

# models.py
from glim_extentions.db import Model
from sqlalchemy import Column, Integer, String, Text, ForeignKey
from sqlalchemy.orm import relationship, backref

class User(Model):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    group_id = Column(Integer, ForeignKey('group.id'))
    group = relationship("Group")

class Group(Model):
		__tablename__ = 'group'
		id = Column(Integer, primary_key=True)

You can achieve bidirectional relationship in User using backref statement;

class User(Model):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    group_id = Column(Integer, ForeignKey('group.id'))
    group = relationship("Group", backref="users")

Many to Many

SQLAlchemy defines many to many relationships by the following statement;

"Many to Many adds an association table between two classes. The association table is indicated by the secondary argument to relationship(). Usually, the Table uses the MetaData object associated with the declarative base class, so that the ForeignKey directives can locate the remote tables with which to link"

Consider a system where blog posts have tags. This can be done by the following;

# models.py
from glim_extentions.db import Model
from sqlalchemy import Column, Integer, String, Text, ForeignKey
from sqlalchemy.orm import relationship, backref

class Post(Model):
		__tablename__ = 'posts'
		id = Column(Integer, primary_key=True)
  	tags = relationship('Tag', secondary=tags)

tags = Table('tags',
	Column('tag_id', Integer, ForeignKey('tag.id'),
	Column('post_id', Integer, ForeignKey('post_id'))))

class Tag(Model):
		id = Column(Integer, primary_key=True)

A bidirectional relationship could be established using backref in the Post model by the following;

class Post(Model):
		__tablename__ = 'posts'
  	id = Columtn(Integer, primary_key=True)
  	tags = relationship('Tag', secondary=tags, backref='tags')

Querying

After defining releations and model mappings, querying is the most important concept to learn. In glim, there are many ways to achieve querying. You can even write raw sql queries using the DB API. This section explains the orm querying part only.

Switching between connections

In glim, it is said above that you can do connection aliasing in the "default-analytics" connection. You can switch to "analytics" database connection by the following;

# app/config/<env>.py
config = { 
		'extensions': {
    		'db': {
            'default': {
                'driver': 'mysql',
              	'host': 'localhost',
              	'schema': 'test',
              	'user': 'root',
              	'password': '',
        				'orm': True
          	},
      			'analytics': {
        				'driver': 'mysql',
							  'host': 'localhost',
					 			'schema': 'analytics',
					 			'user': 'root',
					 			'password': '',
        				'orm': False
        		}
      	},
    }
}

from glim_extentions.db import Orm

Orm.connection('analytics').blah() # performs operations on the analytics database
Orm.blah() # performs operations on the default conncetion

In this example we can infer that Orm object holds the active session object of SQLAlchemy.

The query() function

The query() function takes model classes or model column names and configures itself. You can use method chaining here by the following;

from glim_extensions.db import Orm

# query from a class
Orm.query(User).filter_by(name='Aras').all()

# querying from a class with only one row
Orm.query(User).filter_by(name='Aras').first()

# query with multiple classes
Orm.query(User, Phone).join('phone').filter_by(name='Aras').all()

# query specific columns
Orm.query(User.name, User.id).all()

You can get much more information in the Querying section of SQLAlchemy.

Adding new items with orm session

You can create new models and persist in the database using add() & commit() function.

# services.py
from glim_extensions.db import Orm
from app.models import User

def add_users(user1_name, user2_name):
		user1 = User(name=user1_name)
  	user2 = User(name=user2_name)
  	Orm.add(user1)
  	Orm.add(user2)
  	Orm.commit() # write changes to the database

You can also add the users as a list by the following

Orm.add_all([user1, user2])

🚧
Disabled autocommit
Currently, it is required to write commit() statements to persist models to database. Although it is highly suggested to make these database changes transactional, the autocommit mode will be enabled. This issue will be addressed on the future releases.

Deleting Items using orm session

You can filter and delete tuples by the following;

from glim_extensions.db import Orm

Orm.query(User).filter(id=7).delete()

Using DB API

The DB API includes the engine instance of active connection. You can use SQL Expression Language that SQLAlchemy provides. It is highly recommended to read this documentation first.

Running SQL Expression on models

The documentation above showed how to define models. SQL Expression language requires Table object to run chained sql statements. Consider an example of a system holding users in a model. The following example shows a simple select statemtent on defined model;

# models.py
from glim_extensions.db import Model
from sqlalchemy import Column, Integer, String, Text, ForeignKey
from sqlalchemy.orm import relationship, backref

class User(Model):
		__tablename__ = 'users'
  	id = Column(Integer, primary_key=True)
  	name = Column(String(250))

# services.py
from app.models import User
from glim.db import Database as DB
from sqlalchemy.sql import select

def select_all_users():
		s = select([User.__table__])
  	result = DB.execute(s)
  	for row in result:
		    print row

You can notice that sql expression language can be run by using __table__ property of a model. You can fetch one row using fetchone() function;

# services.py
from glim_extensions.db import Database as DB
from app.models import User

# get rows using defined column names
def select_first_user():
		s = select([User.__table__])
  	result = DB.execute(s)
  	row = result.fetchone()
  	print("name:", row['name'], "; fullname:", row['fullname'])
  
# get rows using column name in __table__
def select_first_user2():
  	s = select([User.__table__])
  	result = DB.execute(s)
  	row = result.fetchone()
  	print("name:", row[User.__table__.c.name],
        	"; fullname:", row[User.__table__.c.fullname])

Insert Expressions

Consider the example of the user model above. You can achieve insert expressions by running Model.__table__ statement by the following;

# services.py
from app.models import User
from glim_extensions.db import Database as DB

def insert_user(name):
		ins = User.__table__
  						.insert().values(name='Aras')
 		result = DB.execute(ins)
  	return result

Select Expressions

You can run selects by chaining statements;

from glim_extensions.db import Database as DB
from app.models import User, Address

users = User.__table__
addresses = Address.__table__

# an example of chaining multiple tables
s = select([users, addresses])
			.where(users.c.id == addresses.c.user_id)
  
# an example of chaining more and or_ statements
s = select([users.c.fullname + ", " + addresses.c.email_address])
			.where(users.c.id == addresses.c.user_id)
  		.where(users.c.name.between('m', 'z'))
    	.where(
       	or_(
        	addresses.c.email_address.like('%@gmail.com')
         	addresses.c.email_address.like('%@yandex.com')
        ) 
      )
      
rows = DB.execute(s).fetchall()

Order By, Group By

from sqlalchemy import func, desc
from glim_extensions.db import Database as DB
from app.models import Address

addresses = Address.__table__

stmt = select([
	addresses.c.user_id,
	func.count(addresses.c.id).label('num_addresses')]).\
	order_by("num_addresses")

DB.execute(stmt).fetchall()

stmt = select([
				addresses.c.user_id,
				func.count(addresses.c.id).label('num_addresses')]).\
				order_by(desc("num_addresses"))
  
DB.execute(stmt).fetchall()

Update Expressions

You can run update statements using update() function by the following;

from glim_extensions.db import Database as DB
from app.models import User

users = User.__table__

stmt = users.update().\
				where(users.c.name == 'Aras').\
				values(fullname="Fullname: " + users.c.name)

result = DB.execute(stmt)

Delete Expressions

You can run delete statements using delete() function by the following;

from glim_extensions.db import Database as DB
from app.models import Address

addresses = Address.__table__
DB.execute(addresses.delete())

Raw Queries

There are many ways of running raw sql queries

from sqlalchemy.sql import text
from glim_extensions.db import Database as DB

s = text(
		"SELECT users.fullname || ', ' || addresses.email_address AS title "
		"FROM users, addresses "
		"WHERE users.id = addresses.user_id "
		"AND users.name BETWEEN :x AND :y "
		"AND (addresses.email_address LIKE :e1 "
		"OR addresses.email_address LIKE :e2)")

DB.execute(s, x='m', y='z',
           e1='%@gmail.com',
           e2='%@yandex.com')
	.fetchall()
  
sql = "INSERT INTO users (full_name, title)" \
      "VALUES ('%s','%s')" % (full_name, title))"

result = DB.execute(sql)
print result

Migrations

Migrations are currenlty available by RDB Migrations extension. You can check the docs about database migrations there.

Database & ORM

📘
db is an extension after 0.9 releases

Configuration & Connection Aliasing

Defining Models & Mapping

Defining Relations

One to One

One to Many

Many to One

Many to Many

Querying

Switching between connections

The query() function

Adding new items with orm session

🚧
Disabled autocommit

Deleting Items using orm session

Using DB API

Running SQL Expression on models

Insert Expressions

Select Expressions

Order By, Group By

Update Expressions

Delete Expressions

Raw Queries

Migrations

📘db is an extension after 0.9 releases

Configuration & Connection Aliasing

Defining Models & Mapping

Defining Relations

One to One

One to Many

Many to One

Many to Many

Querying

Switching between connections

The query() function

Adding new items with orm session

🚧Disabled autocommit

Deleting Items using orm session

Using DB API

Running SQL Expression on models

Insert Expressions

Select Expressions

Order By, Group By

Update Expressions

Delete Expressions

Raw Queries

Migrations

📘
db is an extension after 0.9 releases

🚧
Disabled autocommit