Where to actually create the SQL metadata's tables?

The “Introduction” page (https://www.encode.io/databases/) uses direct SQL queries to create the tables, whereas the “Database Queries” page seems to assume that the tables already exist when querying with SQLAlchemy.

The official SQLAlchemy Core explains to use metadata.create_all(engine): https://docs.sqlalchemy.org/en/13/core/tutorial.html#define-and-create-tables, but that’s not available with databases package since there is no engine.

What is the recommended way to ensure the tables exist?

For what it’s worth, I’m trying to use it with Starlette, which didn’t have any examples of creating the tables either.

1 Like

There’s an example in the databases documentation that uses the class-based syntax:
https://www.encode.io/databases/database_queries/

Hope that’s what you’re looking for.

Unfortunately that article does not include creating the tables themselves. It defines the schema with the metadata, after which it assumes the tables exist. Which is not the case, since defining the schema doesn’t execute any “CREATE TABLE” commands yet.

@Mahi, that library uses SQLAlchemy and all examples assumes it.
You’d better to consult the SA docs: https://docs.sqlalchemy.org/en/13/core/metadata.html#creating-and-dropping-database-tables

Quick tip:
you can create tables.py file and put all schema definitions in it. Then you can use them from your code.

One solid approach is to use alembic.

1 Like

@alex-oleshkevich all SQLAlchemy docs clearly recommend one to use metadata.create_all(engine). The problem is that I don’t have an engine object when working with databases

@Mahi create one:

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),
)

database = Database('postgresql://localhost/example')
engine = sqlalchemy.create_engine(str(database.url))
metadata.create_all(engine) 

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

I haven’t ran this code but it should work :wink:

@Mahi Have the same doubts as you. Whether databases.Database could be implemented compatibility with sa engine, so that we can metadata.create_all(database), no need create engine = sqlalchemy.create_engine(str(database.url)),

My solution to the issue was to implement utility that consumes Database object and returns the connection string that SQLAlchemy can consume. That way I was able to use Alembic for my migrations. I’ve also used Click for management commands, so I could run migrations via my command.