# red-postgres
Piccolo Postgres integration for Red-DiscordBot, although it could be used with any dpy bot as an easy wrapper for making postgres with cogs more modular.
[](https://pypi.org/project/red-postgres/)
[](https://pypi.org/project/red-postgres/)
# Install
```python
pip install red-postgres
```
# File structure for using with cogs
```
cog-folder/
├── db/
│ ├── migrations/
│ ├── piccolo_conf.py
│ ├── piccolo_app.py
│ ├── tables.py
├── __init__.py
├── cog.py
```
# Cog Usage
```python
import asyncio
from pathlib import Path
from piccolo.engine.postgres import PostgresEngine
from redbot.core import commands
from redbot.core.bot import Red
from red_postgres import register_cog
from .db.tables import MyTable
class PiccoloTemplate(commands.Cog):
def __init__(self, bot: Red, *args, **kwargs):
super().__init__(*args, **kwargs)
self.bot = bot
self.db: PostgresEngine = None
async def cog_load(self):
asyncio.create_task(self.setup())
async def setup(self):
await self.bot.wait_until_red_ready()
config = await self.bot.get_shared_api_tokens("postgres")
self.db = await register_cog(self, config, [MyTable])
async def cog_unload(self):
if self.db:
self.db.pool.terminate()
```
The config for piccolo should have the following keys:
```json
{
"database": "postgres", # Replace with your maintenance database
"host": "127.0.0.1", # Replace with your host
"port": "5432", # Replace with your port
"user": "postgres", # Replace with your user
"password": "postgres" # Replace with your password
}
```
> Note: database name in your config should normally be the default "postgres", this library will automatically handle connecting your cogs to their own database
The register method connects to the database specified in config, creates the a new database with the name of the registering cog, registers any tables, runs any migrations, sets the new engine object to all tables, and returns the raw engine object.
- The name of the database will be the the name of the cog's folder, not the name of the main cog.py file
You can then use your piccolo table methods like so:
```python
count = await MyTable.count()
or
objects = await MyTable.objects().where(MyTable.text == "Hello World")
```
The engine associated with your tables after registering the cog is connected to the database named the same as the cog that registered them, thus using this integration with multiple cogs will not interfere, as each cog will create its own database.
- _If your cog's folder name is `MyCog` then the database will be named `mycog`_
# Piccolo Configuration Files
Your piccolo configuration files must be setup like so. This is really only used for migrations.
- _When migrations are run, the os environment variables are mocked in subprocess, so there should be no conflicts_
### piccolo_conf.py
```python
import os
from piccolo.conf.apps import AppRegistry
from piccolo.engine.postgres import PostgresEngine
DB = PostgresEngine(
config={
"database": os.environ.get("POSTGRES_DATABASE"),
"user": os.environ.get("POSTGRES_USER"),
"password": os.environ.get("POSTGRES_PASSWORD"),
"host": os.environ.get("POSTGRES_HOST"),
"port": os.environ.get("POSTGRES_PORT"),
}
)
APP_REGISTRY = AppRegistry(apps=["db.piccolo_app"])
```
### piccolo_app.py
```python
import os
from piccolo.conf.apps import AppConfig, table_finder
CURRENT_DIRECTORY = os.path.dirname(os.path.abspath(__file__))
APP_CONFIG = AppConfig(
app_name="cogname", # Replace with your cog name
table_classes=table_finder(["db.tables"]),
migrations_folder_path=os.path.join(CURRENT_DIRECTORY, "migrations"),
)
```
for `table_classes` add in the list of tables you're using
# Local development and making migrations
Handing migrations is up to you, but one way to do it is to make migrations locally like so:
First make an `.env` file in the root of your cog's folder.
```env
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
POSTGRES_DATABASE=postgres
```
Then create a `build.py` file in your cog folder.
```python
import asyncio
import os
from pathlib import Path
from dotenv import load_dotenv
from engine import engine
load_dotenv()
config = {
"user": os.environ.get("POSTGRES_USER"),
"password": os.environ.get("POSTGRES_PASSWORD"),
"database": os.environ.get("POSTGRES_DATABASE"),
"host": os.environ.get("POSTGRES_HOST"),
"port": os.environ.get("POSTGRES_PORT"),
}
root = Path(__file__).parent
async def main():
created = await engine.ensure_database_exists(root, config)
print(f"Database created: {created}")
description = input("Enter a description for the migration: ")
print(await engine.create_migrations(root, config, True, description.replace('"', "")))
print(await engine.run_migrations(root, config, True))
if __name__ == "__main__":
asyncio.run(main())
```
You would have a similar file in the root of each of your cog folders, here you would create the migrations to include in your cog folder for users to run when they load up the cog.
Raw data
{
"_id": null,
"home_page": "https://github.com/vertyco/red-postgres",
"name": "red-postgres",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": "piccolo, postgres, red, discord, bot, database, async, asyncpg, orm",
"author": "Vertyco",
"author_email": "alex.c.goble@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/00/40/541f60caef4f407960822d15e878b99e31d7f3eed4d72d3cd9d7614f72ad/red-postgres-0.5.1.tar.gz",
"platform": null,
"description": "# red-postgres\r\n\r\nPiccolo Postgres integration for Red-DiscordBot, although it could be used with any dpy bot as an easy wrapper for making postgres with cogs more modular.\r\n\r\n[](https://pypi.org/project/red-postgres/)\r\n[](https://pypi.org/project/red-postgres/)\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n# Install\r\n\r\n```python\r\npip install red-postgres\r\n```\r\n\r\n# File structure for using with cogs\r\n\r\n```\r\ncog-folder/\r\n \u251c\u2500\u2500 db/\r\n \u2502 \u251c\u2500\u2500 migrations/\r\n \u2502 \u251c\u2500\u2500 piccolo_conf.py\r\n \u2502 \u251c\u2500\u2500 piccolo_app.py\r\n \u2502 \u251c\u2500\u2500 tables.py\r\n \u251c\u2500\u2500 __init__.py\r\n \u251c\u2500\u2500 cog.py\r\n```\r\n\r\n\r\n\r\n# Cog Usage\r\n\r\n```python\r\nimport asyncio\r\nfrom pathlib import Path\r\n\r\nfrom piccolo.engine.postgres import PostgresEngine\r\nfrom redbot.core import commands\r\nfrom redbot.core.bot import Red\r\n\r\nfrom red_postgres import register_cog\r\nfrom .db.tables import MyTable\r\n\r\n\r\nclass PiccoloTemplate(commands.Cog):\r\n def __init__(self, bot: Red, *args, **kwargs):\r\n super().__init__(*args, **kwargs)\r\n self.bot = bot\r\n self.db: PostgresEngine = None\r\n\r\n async def cog_load(self):\r\n asyncio.create_task(self.setup())\r\n\r\n async def setup(self):\r\n await self.bot.wait_until_red_ready()\r\n config = await self.bot.get_shared_api_tokens(\"postgres\")\r\n self.db = await register_cog(self, config, [MyTable])\r\n\r\n async def cog_unload(self):\r\n if self.db:\r\n self.db.pool.terminate()\r\n```\r\n\r\nThe config for piccolo should have the following keys:\r\n\r\n```json\r\n{\r\n \"database\": \"postgres\", # Replace with your maintenance database\r\n \"host\": \"127.0.0.1\", # Replace with your host\r\n \"port\": \"5432\", # Replace with your port\r\n \"user\": \"postgres\", # Replace with your user\r\n \"password\": \"postgres\" # Replace with your password\r\n}\r\n```\r\n\r\n> Note: database name in your config should normally be the default \"postgres\", this library will automatically handle connecting your cogs to their own database\r\n\r\nThe register method connects to the database specified in config, creates the a new database with the name of the registering cog, registers any tables, runs any migrations, sets the new engine object to all tables, and returns the raw engine object.\r\n\r\n- The name of the database will be the the name of the cog's folder, not the name of the main cog.py file\r\n\r\nYou can then use your piccolo table methods like so:\r\n\r\n```python\r\ncount = await MyTable.count()\r\nor\r\nobjects = await MyTable.objects().where(MyTable.text == \"Hello World\")\r\n```\r\n\r\nThe engine associated with your tables after registering the cog is connected to the database named the same as the cog that registered them, thus using this integration with multiple cogs will not interfere, as each cog will create its own database.\r\n\r\n- _If your cog's folder name is `MyCog` then the database will be named `mycog`_\r\n\r\n# Piccolo Configuration Files\r\n\r\nYour piccolo configuration files must be setup like so. This is really only used for migrations.\r\n\r\n- _When migrations are run, the os environment variables are mocked in subprocess, so there should be no conflicts_\r\n\r\n### piccolo_conf.py\r\n\r\n```python\r\nimport os\r\n\r\nfrom piccolo.conf.apps import AppRegistry\r\nfrom piccolo.engine.postgres import PostgresEngine\r\n\r\nDB = PostgresEngine(\r\n config={\r\n \"database\": os.environ.get(\"POSTGRES_DATABASE\"),\r\n \"user\": os.environ.get(\"POSTGRES_USER\"),\r\n \"password\": os.environ.get(\"POSTGRES_PASSWORD\"),\r\n \"host\": os.environ.get(\"POSTGRES_HOST\"),\r\n \"port\": os.environ.get(\"POSTGRES_PORT\"),\r\n }\r\n)\r\n\r\n\r\nAPP_REGISTRY = AppRegistry(apps=[\"db.piccolo_app\"])\r\n```\r\n\r\n### piccolo_app.py\r\n\r\n```python\r\nimport os\r\n\r\nfrom piccolo.conf.apps import AppConfig, table_finder\r\n\r\nCURRENT_DIRECTORY = os.path.dirname(os.path.abspath(__file__))\r\n\r\nAPP_CONFIG = AppConfig(\r\n app_name=\"cogname\", # Replace with your cog name\r\n table_classes=table_finder([\"db.tables\"]),\r\n migrations_folder_path=os.path.join(CURRENT_DIRECTORY, \"migrations\"),\r\n)\r\n```\r\n\r\nfor `table_classes` add in the list of tables you're using\r\n\r\n# Local development and making migrations\r\n\r\nHanding migrations is up to you, but one way to do it is to make migrations locally like so:\r\n\r\nFirst make an `.env` file in the root of your cog's folder.\r\n\r\n```env\r\nPOSTGRES_HOST=localhost\r\nPOSTGRES_PORT=5432\r\nPOSTGRES_USER=postgres\r\nPOSTGRES_PASSWORD=postgres\r\nPOSTGRES_DATABASE=postgres\r\n```\r\n\r\nThen create a `build.py` file in your cog folder.\r\n\r\n```python\r\nimport asyncio\r\nimport os\r\nfrom pathlib import Path\r\nfrom dotenv import load_dotenv\r\nfrom engine import engine\r\n\r\nload_dotenv()\r\n\r\nconfig = {\r\n \"user\": os.environ.get(\"POSTGRES_USER\"),\r\n \"password\": os.environ.get(\"POSTGRES_PASSWORD\"),\r\n \"database\": os.environ.get(\"POSTGRES_DATABASE\"),\r\n \"host\": os.environ.get(\"POSTGRES_HOST\"),\r\n \"port\": os.environ.get(\"POSTGRES_PORT\"),\r\n}\r\n\r\nroot = Path(__file__).parent\r\n\r\n\r\nasync def main():\r\n created = await engine.ensure_database_exists(root, config)\r\n print(f\"Database created: {created}\")\r\n description = input(\"Enter a description for the migration: \")\r\n print(await engine.create_migrations(root, config, True, description.replace('\"', \"\")))\r\n print(await engine.run_migrations(root, config, True))\r\n\r\n\r\nif __name__ == \"__main__\":\r\n asyncio.run(main())\r\n\r\n```\r\n\r\nYou would have a similar file in the root of each of your cog folders, here you would create the migrations to include in your cog folder for users to run when they load up the cog.\r\n",
"bugtrack_url": null,
"license": null,
"summary": "Piccolo Postgres integration for Red",
"version": "0.5.1",
"project_urls": {
"Bug Tracker": "https://github.com/vertyco/red-postgres/issues",
"Changelog": "https://github.com/vertyco/red-postgres/blob/main/CHANGELOG.md",
"Homepage": "https://github.com/vertyco/red-postgres"
},
"split_keywords": [
"piccolo",
" postgres",
" red",
" discord",
" bot",
" database",
" async",
" asyncpg",
" orm"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "0040541f60caef4f407960822d15e878b99e31d7f3eed4d72d3cd9d7614f72ad",
"md5": "5ff30453e87e5b776b07e731e2b076a4",
"sha256": "d1c8d99cc9032ac814a2d3f5b969df160a995d662837e0ae4fed3392b33eb523"
},
"downloads": -1,
"filename": "red-postgres-0.5.1.tar.gz",
"has_sig": false,
"md5_digest": "5ff30453e87e5b776b07e731e2b076a4",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 8979,
"upload_time": "2024-11-17T20:03:12",
"upload_time_iso_8601": "2024-11-17T20:03:12.654177Z",
"url": "https://files.pythonhosted.org/packages/00/40/541f60caef4f407960822d15e878b99e31d7f3eed4d72d3cd9d7614f72ad/red-postgres-0.5.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-11-17 20:03:12",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "vertyco",
"github_project": "red-postgres",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "red-postgres"
}
JSON
