create_table()

Create database tables with schema definitions, foreign key relationships, and cross-database compatibility.

Overview

The create_table() method creates database tables with type-safe schema definitions. It uses CREATE TABLE IF NOT EXISTS to prevent errors on duplicate creation and supports foreign key relationships across all supported databases.

Signature

create_table(table_name: str, schema: Dict[str, str]) -> None

Parameters

table_name

Type: str

Name of the table to create. Must be a valid identifier for the target database.

schema

Type: Dict[str, str]

Dictionary mapping column names to their types. Supported types:

  • "int" - Integer (becomes PRIMARY KEY AUTOINCREMENT for id columns)
  • "str" - String/VARCHAR
  • "float" - Floating point number
  • "bool" - Boolean
  • "int->table.column" - Foreign key reference

Returns

None - The method performs the table creation and returns nothing.

Examples

Basic Table Creation

Simple User Table
1from akron import Akron
2
3db = Akron("sqlite:///example.db")
4
5# Create a basic users table
6db.create_table("users", {
7 "id": "int", # Becomes PRIMARY KEY AUTOINCREMENT
8 "username": "str", # VARCHAR
9 "email": "str",
10 "age": "int",
11 "active": "bool"
12})
13
14print("Users table created successfully!")
Expected Output
Users table created successfully!

Generated SQL (SQLite):

Generated SQL
1CREATE TABLE IF NOT EXISTS users (
2 id INTEGER PRIMARY KEY AUTOINCREMENT,
3 username TEXT,
4 email TEXT,
5 age INTEGER,
6 active BOOLEAN
7)

Foreign Key Relationships

Use the syntax "int->table.column" to create foreign key relationships:

Tables with Foreign Keys
1from akron import Akron
2
3db = Akron("sqlite:///blog.db")
4
5# Create categories table first
6db.create_table("categories", {
7 "id": "int",
8 "name": "str",
9 "description": "str"
10})
11
12# Create posts table with foreign key to categories
13db.create_table("posts", {
14 "id": "int",
15 "title": "str",
16 "content": "str",
17 "category_id": "int->categories.id", # Foreign key
18 "published": "bool",
19 "created_at": "str"
20})
21
22# Create comments table with foreign keys to both posts and users
23db.create_table("comments", {
24 "id": "int",
25 "post_id": "int->posts.id", # Foreign key to posts
26 "user_id": "int->users.id", # Foreign key to users
27 "content": "str",
28 "created_at": "str"
29})
30
31print("Blog schema created with foreign key relationships!")
Expected Output
Blog schema created with foreign key relationships!

E-commerce Example

Complex schema with multiple relationships:

E-commerce Database Schema
1from akron import Akron
2
3db = Akron("mysql://user:password@localhost/ecommerce")
4
5# Create base tables
6db.create_table("customers", {
7 "id": "int",
8 "first_name": "str",
9 "last_name": "str",
10 "email": "str",
11 "phone": "str",
12 "created_at": "str"
13})
14
15db.create_table("products", {
16 "id": "int",
17 "name": "str",
18 "description": "str",
19 "price": "float",
20 "stock_quantity": "int",
21 "category": "str"
22})
23
24# Orders with customer reference
25db.create_table("orders", {
26 "id": "int",
27 "customer_id": "int->customers.id",
28 "total_amount": "float",
29 "status": "str",
30 "order_date": "str"
31})
32
33# Order items linking orders and products
34db.create_table("order_items", {
35 "id": "int",
36 "order_id": "int->orders.id",
37 "product_id": "int->products.id",
38 "quantity": "int",
39 "unit_price": "float"
40})
41
42print("E-commerce database schema created!")
Expected Output
E-commerce database schema created!

Database-Specific Behavior

🗄️ SQLite

Types are mapped to SQLite's flexible type system:

  • int → INTEGER (PRIMARY KEY AUTOINCREMENT for id columns)
  • str → TEXT
  • float → REAL
  • bool → BOOLEAN

🐬 MySQL

Types are mapped to MySQL's strict type system:

  • int → INT AUTO_INCREMENT PRIMARY KEY
  • str → VARCHAR(255)
  • float → FLOAT
  • bool → BOOLEAN

🐘 PostgreSQL

Types use PostgreSQL's robust type system:

  • int → SERIAL PRIMARY KEY
  • str → VARCHAR(255)
  • float → REAL
  • bool → BOOLEAN

🍃 MongoDB

MongoDB collections are created implicitly. Schema definitions are stored for reference but not enforced:

1# MongoDB - collection created when first document inserted
2db.create_table("users", {
3 "id": "int",
4 "name": "str",
5 "email": "str"
6})
7# Collection 'users' will be created on first insert

Error Handling

Handling Table Creation Errors
1from akron import Akron
2from akron.exceptions import AkronError, TableNotFoundError
3
4db = Akron("sqlite:///example.db")
5
6try:
7 # This will work fine
8 db.create_table("products", {
9 "id": "int",
10 "name": "str",
11 "price": "float"
12 })
13
14 # This might cause issues if categories table doesn't exist
15 db.create_table("product_reviews", {
16 "id": "int",
17 "product_id": "int->products.id", # Valid reference
18 "category_id": "int->categories.id", # Table doesn't exist yet!
19 "rating": "int"
20 })
21
22except AkronError as e:
23 print(f"Table creation failed: {e}")
24
25 # Create the missing table first
26 db.create_table("categories", {
27 "id": "int",
28 "name": "str"
29 })
30
31 # Now try again
32 db.create_table("product_reviews", {
33 "id": "int",
34 "product_id": "int->products.id",
35 "category_id": "int->categories.id",
36 "rating": "int"
37 })
Expected Output
Table creation failed: Foreign key constraint failed: categories
Table product_reviews created successfully!

CLI Usage

You can also create tables using the Akron CLI:

CLI Table Creation
1# Create a simple table
2akron create-table users --db sqlite:///example.db --schema '{"id": "int", "name": "str", "email": "str"}'
3
4# Create table with foreign keys
5akron create-table posts --db sqlite:///blog.db --schema '{"id": "int", "title": "str", "user_id": "int->users.id"}'
6
7# Different database types
8akron create-table products --db mysql://user:pass@localhost/shop --schema '{"id": "int", "name": "str", "price": "float"}'
Expected Output
Table users created.
Table posts created.
Table products created.

Best Practices

📋 Schema Design

  • Always include an "id" column as the primary key
  • Use descriptive column names (snake_case recommended)
  • Create referenced tables before tables with foreign keys
  • Consider adding created_at/updated_at timestamps

🔗 Foreign Keys

  • Foreign key format: "int->table_name.column_name"
  • Usually references the id column: "int->users.id"
  • Ensure referenced table exists before creating
  • Foreign keys are not supported in MongoDB

🧪 Development Workflow

  • Use migrations for production schema changes
  • Test schema with SQLite first, then migrate to production DB
  • Use IF NOT EXISTS behavior for safe re-runs
  • Document your schema relationships

Next Steps

After creating tables, you can start working with data:

Insert Data

Add records to your tables

→ learn about insert()

Query Data

Find and retrieve records

→ learn about find()