The two most common types used for primary keys in database tables are integers and UUIDs (sometimes referred to as GUIDs).
There are a number of tradeoffs to make when deciding whether to use integers vs. UUIDs, including:
- UUIDs don’t reveal anything about the number of records in a table
- UUIDs are practically impossible for an adversary to guess (though you shouldn’t rely solely on that for security!)
- UUIDs are harder to communicate/remember
- UUIDs may result in worse performance for certain access patterns due to the random ordering
You’ll have to decide based on your application which is right for you, but if you want to use UUIDs/GUIDs for your primary keys, there are some difficulties to navigate.
Challenges using UUID-valued primary keys with sqlalchemy¶
Python has support for UUIDs in the standard library, and most relational databases have good support for them as well.
However, if you want a database-agnostic or database-driver-agnostic type, you may run into challenges.
In particular, the postgres-compatible UUID type provided by sqlalchemy (
will not work with other databases, and it also doesn’t come with a way to set a server-default, meaning that
you’ll always need to take responsibility for generating an ID in your application code.
Even worse, if you try to use the postgres-compatible UUID type simultaneously with both
sqlalchemy and the
encode/databases package, you may run into issues where queries using one require you to set
when declaring the column, and the other requires you to declare the table using
Fortunately, sqlalchemy provides a
backend-agnostic implementation of GUID type
that uses the postgres-specific UUID type when possible, and more carefully parses the result to ensure
uuid.UUID isn’t called on something that is already a
uuid.UUID (which raises an error).
For convenience, this package includes this
GUID type, along with conveniences for setting up server defaults
for primary keys of this type.
You can create a sqlalchemy table with a GUID as a primary key using the declarative API like this:
import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base from fastapi_utils.guid_type import GUID Base = declarative_base() class User(Base): __tablename__ = "user" id = sa.Column(GUID, primary_key=True) name = sa.Column(sa.String, nullable=False) related_id = sa.Column(GUID) # a nullable, related field
If you want to add a server default, it will no longer be backend-agnostic, but
you can use
import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base from fastapi_utils.guid_type import GUID, GUID_SERVER_DEFAULT_POSTGRESQL Base = declarative_base() class User(Base): __tablename__ = "user" id = sa.Column( GUID, primary_key=True, server_default=GUID_SERVER_DEFAULT_POSTGRESQL ) name = sa.Column(sa.String, nullable=False) related_id = sa.Column(GUID)
(Behind the scenes, this is essentially just setting the server-side default to
Note this will only work if you have installed the
in your postgres instance. If the user you connect with has the right privileges, this can be done
by calling the
import sqlalchemy as sa from fastapi_utils.guid_type import setup_guids_postgresql database_uri = "postgresql://user:password@db:5432/app" engine = sa.create_engine(database_uri) setup_guids_postgresql(engine)
If you are comfortable having no server default for your primary key column, you can still
make use of an application-side default (so that
sqlalchemy will generate a default value when you
create new records):
import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base from fastapi_utils.guid_type import GUID, GUID_DEFAULT_SQLITE Base = declarative_base() class User(Base): __tablename__ = "user" id = sa.Column(GUID, primary_key=True, default=GUID_DEFAULT_SQLITE) name = sa.Column(sa.String, nullable=False) related_id = sa.Column(GUID)
GUID_DEFAULT_SQLITE is just an alias for the standard library
which could be used in its place.