CODEWITHSUNDEEP
pythonsqldatabasepostgresqlasyncpg
Simona Simeonova
Simona Simeonova

Reputation: 13

asyncpg - Connect to server and create database if not exists

Is there a way to create a database in a PostgreSQL server using asyncpg like you can in Hibernate, using:

jdbc:mysql://localhost:3306/db_name?createDatabaseIfNotExist=true

flags in the uri. Is there a way to connect to the server without connecting to a db and execute sql statements?

Upvotes: 1

Views: 7307

Answers (1)

Elvis Pranskevichus
Elvis Pranskevichus

Reputation: 1274

There is no shortcut for this in asyncpg, but it's easy to implement in your code. Every PostgreSQL cluster has a template1 database, which can be used to issue a CREATE DATABASE statement if the original database you are connecting to does not exist. CREATE DATABASE usually requires special privileges, so you need to use a superuser account when connecting to template1 (usually postgres).

Example:

import asyncio
import asyncpg

async def connect_create_if_not_exists(user, database):
    try:
        conn = await asyncpg.connect(user=user, database=database)
    except asyncpg.InvalidCatalogNameError:
        # Database does not exist, create it.
        sys_conn = await asyncpg.connect(
            database='template1',
            user='postgres'
        )
        await sys_conn.execute(
            f'CREATE DATABASE "{database}" OWNER "{user}"'
        )
        await sys_conn.close()

        # Connect to the newly created database.
        conn = await asyncpg.connect(user=user, database=database)

    return conn


asyncio.get_event_loop().run_until_complete(
    connect_create_if_not_exists(user='elvis', database='new-database')
)

Upvotes: 7

Related Questions