Documentation lacks guidance on creating database tables

[Lonami]

The Databases Introduction executes a raw SQL query like so:

query = """CREATE TABLE HighScores (id INTEGER PRIMARY KEY, name VARCHAR(100), score INTEGER)"""
await database.execute(query=query)

The next page, Database Queries instead uses SQLAlchemy Core like so:

import sqlalchemy


metadata = sqlalchemy.MetaData()

notes = sqlalchemy.Table(
    "notes",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("text", sqlalchemy.String(length=100)),
    sqlalchemy.Column("completed", sqlalchemy.Boolean),
)

...

from databases import Database

database = Database('postgresql://localhost/example')

# Establish the connection pool
await database.connect()

# Execute
query = notes.insert()
values = {"text": "example1", "completed": True}
await database.execute(query=query, values=values)

Nowhere actually shows how to create the tables. I think the documentation could do a better job at showing a full example that runs and works.

The documentation does link to SQLAlchemy core official tutorial, however this tutorial talks about "engines" which are nowhere to be found in the databases library, so it's not easy to find out how to actually create a table or adapt their examples. One may naively try to use the database as the engine, which fails with a rather cryptic "AttributeError: 'Database' object has no attribute '_run_visitor'" error.

I think the documentation could do a better job at explaining how one should map the concepts from the SQLAlchemy tutorial for use in this library.

Finally, the documentation does not seem to provide a reference section to learn all the methods available and look at their internal documentation without the need for Python's help(). I think the documentation should have a section with the reference documentation for all public types.

All of this is from the point of view who wants to use databases as their only interface to databases, and has no experience with sqlalchemy. Given that sqlalchemy is the recommended way to use it, I think the library should put some effort into explaining how the both libraries work together.

[tducret]

This comment helped me a lot

[aadibajpai]

I've also been wondering about this since a few days, before I found this issue.

I feel one way is to use the sync method like described in the comment^ which uses sqlalchemy create_all(), but I was wondering if it's just smarter to specify it in installation or as a comment to be ran on first run. I'm not sure I like the idea of having a sync call present in an async setup.

It would definitely be very useful to have a compatible implementation or similar, right from databases.

[artslob]

I think production-ready way to create tables is alembic package. Its official way to create database migration by sqlalchemy.

You can find example of how I did it in my pet project https://gitlab.com/artslob/cyberbox/-/tree/master/cyberbox. Basically you create metadata and Table objects from sqlalchemy, then create config for alembic, and in env.py include your target metadata. Also I strongly suggest to define naming convention as early as possible.

Metadata + tables + naming convention: https://gitlab.com/artslob/cyberbox/-/blob/master/cyberbox/orm.py Alembic migrations + config file: https://gitlab.com/artslob/cyberbox/-/tree/master/cyberbox/migrations

[artslob]

For tests its pretty good to use metadata.create_all, but in production code better way is yo use migrations.

[rockallite]

How about creating temporary tables in runtime?

Updated:

OK, I finally got it. Here are the steps to create a table in Databases:

1. Define your table the SQLAlchemy way.
2. Get the CREATE TABLE string:
3. Pass it to await database.execute(query=...), done.

Example code:

import sqlalchemy
from sqlalchemy.dialects import postgresql
from sqlalchemy.schema import CreateTable
from databases import Database

metadata = sqlalchemy.MetaData()

# Define the table
notes = sqlalchemy.Table(
    "notes",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("text", sqlalchemy.String(length=100)),
    sqlalchemy.Column("completed", sqlalchemy.Boolean),
)

dialect = postgresql.dialect()

# Get the CREATE TABLE statement
query = str(CreateTable(notes).compile(dialect=dialect))

database = Database('postgresql://localhost/example')

# Establish the connection pool
await database.connect()

# Execute the SQL statement
await database.execute(query=query)

References:

• How can I get the CREATE TABLE/ DROP TABLE output as a string?
• SQLAlchemy printing raw SQL from create()
• Databases - Quickstart

[Lonami]

Although tighter integration would be welcome (imagine a method to create a table that does what you did manually above), that seems like the way to go for now, thanks. Now all there's left is to update the documentation.

[jonathan-daniel]

This is what I do to create all tables based on @rockallite

dialect = sqlite.dialect()

for table in metadata.tables.values():
    query = str(CreateTable(table).compile(dialect=dialect))
    await database.execute(query=query)