Database Management System: A Simple SQL Implementation Using Python

This repository contains a simple SQL Database Management System (DBMS) implemented in Python. The primary objective of the project is to simulate basic functionalities of a SQL database like parsing SQL queries, managing schema metadata, and performing CRUD (Create, Read, Update, Delete) operations.

Features

• SQL Query Parsing
• Schema Management
• CRUD (Create, Read, Update, Delete) Operations
• Error Handling

Environment

python==3.9
lark==1.1.5

Storage is backed by Python's built-in dbm module, so there are no native libraries to install (this replaces the original BerkeleyDB backend). This is what makes the project run identically on macOS, Linux, and Windows.

Run natively

python -m venv venv
source venv/bin/activate        # Windows: venv\Scripts\activate
pip install -r requirements.txt
python run.py

Run in Docker (recommended for a consistent, cross-platform setup)

The repo ships a Dockerfile, docker-compose.yml, and Makefile so everyone runs the same environment regardless of host OS.

Quick-start (Makefile targets)

make build      # build the image (once, or after changing requirements.txt / Dockerfile)
make run        # start the interactive SQL REPL
make shell      # open a bash shell inside the container
make test       # pipe a few statements through the engine as a smoke test
make reset      # wipe the database volume and start from a clean slate

Two run modes

1. Interactive SQL REPL (run.py)

make run
# or directly:
docker compose run --rm app python run.py

You will see a DB_2023-12345> prompt where you can type SQL statements.

Running SQL from a file

# Execute a .sql file directly (non-interactive)
python run.py path/to/script.sql

# Or inside the REPL
DB_2023-12345> \source path/to/script.sql

File execution supports:

• -- and # line comments, plus /* */ block comments
• Multiple statements separated by semicolons
• Continue-on-error behavior (logs each error and proceeds to the next statement)

2. Web UI (web_ui.py — Flask app on port 5000)

docker compose up        # start the web UI (Ctrl+C to stop)
docker compose up -d     # start detached

Open http://localhost:5000 in your browser.

How the container is wired up

• Source code is mounted as a volume (./app), so edits you make on the host are reflected instantly inside the container — no rebuild needed between iterations.
• Database files live in a named Docker volume (dbdata) mounted at /app/DB. This keeps the binary dbm files out of git and avoids host/container file-format clashes.
• Port 5000 is exposed for the web UI.

VS Code / Cursor dev container

Users of VS Code or Cursor can open the project in the pre-configured dev container:

1. Open the Command Palette (Ctrl+Shift+P).
2. Select "Dev Containers: Reopen in Container".
3. The editor, terminal, and any AI agents all run inside the container — dependencies are pre-installed, so python run.py just works with zero host setup.

See .devcontainer/devcontainer.json for the container configuration.

Note: table and column identifiers must start with a letter and contain only letters and underscores — names like t2 are rejected by the grammar as a syntax error. Use account, students, etc.

Sample I/O

DB_2023-12345> create table account 
(
account_number int not null, 
branch_name char(15)
); 
DB_2023-12345> 'account' table is created

DB_2023-12345> drop table account;
DB_2023-12345> 'account' table is dropped

DB_2023-12345> explain account;
-----------------------------------------------------------------
table_name [account]
column_name		    type		  null		key
account_number		char(10)	N		    PRI
branch_name		    char(15)	N		    FOR
balance			      int		    Y  
-----------------------------------------------------------------

DB_2023-12345> show tables
------------------------
branch
customer
loan
borrower
account
depositor
------------------------

DB_2023-12345> insert into account values(9732, 'Perryridge');
DB_2023-12345> The row is inserted

DB_2023-12345> delete from account where branch_name = 'Perryridge';
DB_2023-12345> 5 row(s) are deleted

DB_2023-12345> select * from account; 
+----------------+-------------+---------+ 
| ACCOUNT_NUMBER | BRANCH_NAME | BALANCE | 
+----------------+-------------+---------+
| A-101
| A-102
| A-201
| A-215
| A-217
| A-222
| A-305
+----------------+-------------+---------+
DB_2023-12345> select customer_name, borrower.loan_number, amount from borrower, loan where borrower.loan_number = loan.loan_number and branch_name = 'Perryridge'; 
+---------------+-------------+--------+
| CUSTOMER_NAME | LOAN_NUMBER | AMOUNT | 
+---------------+-------------+--------+
| Adams | L-16 | 1300 | 
| Hayes | L-15 | 1500 | 
+---------------+-------------+--------+

Core Modules

• grammar.lark: Defines SQL grammar in EBNF (Extended Backus-Naur Form). Using the Lark API, this file serves as the basis for parsing SQL queries into AST (Abstract Syntax Trees).

• sql_transformer.py: Inherits from Lark's Transformer class to handle the AST generated by the parser. It processes and returns tables, records, and columns selected in the query.

• db_model.py: Defines the data structures for schemas and records (each represented by Table and Record classes). It also contains a DB class which acts as a wrapper for manipulating dbm key/value stores. Metadata of schemas is stored in MetaDB, which inherits from the DB class.

• dbms.py: Handles SQL statements such as CREATE TABLE, DROP TABLE, EXPLAIN/DESCRIBE/DESC, SHOW TABLES, INSERT, DELETE, SELECT through a DBMS class.

• messages.py: Defines exception classes for logging and error messages that indicate whether the SQL command was executed successfully by the DBMS class.

• utils.py: Defines function mappings for unknown variables and logical operations in SQL, as well as for parsed comparison/null operators. It also includes functions for validating data types, including date data types.

• run.py: Splits the query sequence into multiple statements and performs actions for each query. It imports the Lark class from the Lark library and generates a parser based on the grammar defined in the grammar.lark file. It also imports SQLTransformer to interpret the AST generated by the parser and extract the necessary data. The corresponding handling functions for SQL statements are called from the DBMS instance, and the result or error message is output.

Implementation Details

• grammar.lark

  • Adds null data type to the INSERT statement to allow null values.

• sql_transformer.py

  • The transformer navigates the AST in a bottom-up manner, collecting and categorizing data into queries, tables, and record information as it traverses the nodes. The result is returned in the form of a dictionary.
  • Input table and column names are converted to lowercase.
  • Type casting is done for int values as Python's int and for null values as Python's None.
  • It also handles the where clause in SQL by parsing predicates, Boolean factors, and Boolean terms, saving operators and operands in a dictionary, which eventually becomes a nested dictionary.

• db_model.py

  • Uses a separate DB file to store and manage schema metadata (Metadata schema) and employs a one DB-one schema approach where a single DB file contains all records for one table. The reason for this is that dbm stores data in a key-value pair format within a single DB. When table keys and record keys are mixed within the same DB, inefficiencies can occur when trying to search for just one of them. Therefore, a MetaDB instance solely for managing metadata is continuously managed within the DBMS, and a new DB is created or opened for managing individual tables when necessary.
  • The MetaDB class stores table names as keys and Table instances as values in a dbm store, while the DB class stores the primary key or a randomly generated UUID (if no primary key exists) as key and Record instance as value.
  • Both Table and Record classes manage information about what tables or records they are referenced by and what columns or record values they are referencing. This allows quick integrity checks during operations like DROP TABLE, INSERT, DELETE.

• dbms.py

  • Manages a MetaDB instance continuously within one DBMS instance, fetching table metadata as needed.
  • Handles referential integrity during INSERT and DELETE
  • Executes SELECT by taking cartesian products of records from the involved tables and filters them based on WHERE clauses.

• utils.py

  • Enables flexible handling of operators, irrespective of the number of operands.

• run.py

  • Reads and processes queries until an "exit" command is encountered.
  • In case of syntax errors, it prints an error message and stops processing any remaining queries.

---

This project was done as part of Spring 2023 Database M1522.001800 course of Seoul National University.