{"id":4774,"library":"sqlalchemy-adapter","title":"SQLAlchemy Adapter for PyCasbin","description":"SQLAlchemy Adapter for PyCasbin enables Casbin, a powerful authorization library, to store and load policy rules from any SQLAlchemy-supported relational database. This includes popular databases like PostgreSQL, MySQL, SQLite, Oracle, Microsoft SQL Server, Firebird, and Sybase. The library is actively maintained, currently at version 1.9.0, and has a frequent release cadence, with multiple feature and bugfix releases in late 2025.","status":"active","version":"1.9.0","language":"en","source_language":"en","source_url":"https://github.com/officialpycasbin/sqlalchemy-adapter","tags":["casbin","pycasbin","authorization","rbac","access control","sqlalchemy","database"],"install":[{"cmd":"pip install sqlalchemy-adapter","lang":"bash","label":"Install current version"}],"dependencies":[{"reason":"Core authorization library that this adapter integrates with.","package":"pycasbin"},{"reason":"ORM and database toolkit used for database interactions.","package":"sqlalchemy"}],"imports":[{"note":"The library package name changed from `casbin-sqlalchemy-adapter` to `sqlalchemy-adapter`.","wrong":"from casbin_sqlalchemy_adapter import Adapter","symbol":"Adapter","correct":"from sqlalchemy_adapter import Adapter"}],"quickstart":{"code":"import os\nimport casbin\nimport tempfile\nfrom sqlalchemy_adapter import Adapter\n\n# Create a dummy model.conf for the Enforcer\nmodel_conf_content = '''\n[request_definition]\nr = sub, obj, act\n\n[policy_definition]\np = sub, obj, act\n\n[policy_effect]\ne = some(where (p.eft == allow))\n\n[matchers]\nm = r.sub == p.sub && r.obj == p.obj && r.act == p.act\n'''\n\n# Write the model content to a temporary file\nwith tempfile.NamedTemporaryFile(mode='w+', delete=False, suffix=\".conf\") as model_file:\n    model_file.write(model_conf_content)\n    model_path = model_file.name\n\n# Use an in-memory SQLite database for a simple, runnable example.\n# For persistent storage, provide a file path or a full database URL\n# e.g., 'sqlite:///test.db' or from an environment variable.\ndatabase_url = os.environ.get('SQLALCHEMY_DATABASE_URL', 'sqlite:///:memory:')\n\ntry:\n    # 1. Initialize the SQLAlchemy Adapter\n    # By default, create_table=True, so tables will be created if they don't exist.\n    adapter = Adapter(database_url)\n\n    # 2. Initialize Casbin Enforcer with the adapter\n    e = casbin.Enforcer(model_path, adapter)\n\n    # 3. Add policies\n    e.add_policy(\"alice\", \"data1\", \"read\")\n    e.add_policy(\"bob\", \"data2\", \"write\")\n\n    # 4. Save policies to the database (persists changes through the adapter)\n    e.save_policy()\n\n    print(\"Policies added and saved.\")\n\n    # 5. Enforce policies\n    print(f\"Alice can read data1: {e.enforce('alice', 'data1', 'read')}\") # Expected: True\n    print(f\"Bob can write data2: {e.enforce('bob', 'data2', 'write')}\") # Expected: True\n    print(f\"Alice can write data1: {e.enforce('alice', 'data1', 'write')}\") # Expected: False\n\n    # 6. Load policies (useful if Enforcer was re-initialized or policy was changed elsewhere)\n    e.load_policy()\n    print(f\"After reloading, Alice can read data1: {e.enforce('alice', 'data1', 'read')}\") # Expected: True\n\nexcept Exception as ex:\n    print(f\"An error occurred: {ex}\")\nfinally:\n    # Clean up the temporary model file\n    os.remove(model_path)\n\n","lang":"python","description":"This quickstart demonstrates how to initialize the `SQLAlchemyAdapter` and integrate it with `pycasbin.Enforcer`. It uses an in-memory SQLite database for a self-contained example. Policy rules are added, saved to the database via the adapter, and then enforced. Ensure you have `pycasbin` installed alongside `sqlalchemy-adapter`."},"warnings":[{"fix":"Upgrade to `pycasbin` and ensure your application code uses `from sqlalchemy_adapter import Adapter`.","message":"Version 1.5.0 switched its core dependency and internal implementation from the older `casbin` library to `pycasbin`. Users migrating from `casbin-sqlalchemy-adapter` or older versions of `sqlalchemy-adapter` that implicitly relied on `casbin` must now ensure `pycasbin` is installed and update their imports to `from sqlalchemy_adapter import Adapter`.","severity":"breaking","affected_versions":"<1.5.0"},{"fix":"If `create_table=False` is used, manually create the `casbin_rule` table and its schema before the adapter is used. For automatic table creation, omit `create_table` or set it to `True` (default behavior).","message":"When initializing the `Adapter` with `create_table=False`, the library will not automatically create the `casbin_rule` table. It is then the user's responsibility to ensure the necessary policy tables exist in the database, typically via a separate database migration system like Alembic.","severity":"gotcha","affected_versions":">=1.8.0"},{"fix":"Upgrade to version 1.9.0 or newer to resolve potential SQLAlchemy warnings regarding `CasbinRule` class definitions.","message":"Older versions of the adapter (prior to 1.9.0) might produce SQLAlchemy warnings related to duplicate `CasbinRule` class names if `db_class` was customized or specific SQLAlchemy configurations were in place. This has been addressed in v1.9.0.","severity":"gotcha","affected_versions":"<1.9.0"},{"fix":"Upgrade to version 1.7.0 or newer to prevent unintended global metadata state alterations when using custom `db_class` models.","message":"Prior to version 1.7.0, using a custom `db_class` with the adapter could inadvertently alter the global SQLAlchemy metadata state. This bug was fixed in v1.7.0.","severity":"gotcha","affected_versions":"<1.7.0"}],"env_vars":null,"last_verified":"2026-04-12T00:00:00.000Z","next_check":"2026-07-11T00:00:00.000Z"}