Skip to content

Support SQLite file dumps#20

Merged
Brayden merged 4 commits intomainfrom
bwilmoth/sqlite-dump
Oct 11, 2024
Merged

Support SQLite file dumps#20
Brayden merged 4 commits intomainfrom
bwilmoth/sqlite-dump

Conversation

@Brayden
Copy link
Member

@Brayden Brayden commented Oct 11, 2024

Purpose

A request came in for database instances to be able to pull a dump of their database SQLite file to store locally. This contribution intends to allow a user to hit the /dump route on their database and receive a .sql file downloaded.

Tasks

  • Allow users to download a .sql file dump of their database schema & contents

Verify

cURL:

curl --location 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/dump' \
--header 'Authorization: Bearer ABC123' \
--output database_dump.sql

Response:

SQLite format 3�
-- Table: sqlite_sequence
CREATE TABLE sqlite_sequence(name,seq);

INSERT INTO sqlite_sequence VALUES ('users', 5);
INSERT INTO sqlite_sequence VALUES ('orders', 5);


-- Table: users
CREATE TABLE users (user_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL);

INSERT INTO users VALUES (1, 'Alice', '[email protected]');
INSERT INTO users VALUES (2, 'Bob', '[email protected]');
INSERT INTO users VALUES (3, 'Charlie', '[email protected]');


-- Table: orders
CREATE TABLE orders (order_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, order_date TEXT NOT NULL, amount REAL NOT NULL, FOREIGN KEY (user_id) REFERENCES users (user_id));

INSERT INTO orders VALUES (1, 1, '2024-10-01', 50.75);
INSERT INTO orders VALUES (2, 1, '2024-10-03', 30);
INSERT INTO orders VALUES (3, 2, '2024-10-05', 99.99);
INSERT INTO orders VALUES (4, 3, '2024-10-06', 20.49);
INSERT INTO orders VALUES (5, 1, '2024-12-01', 50.75);

Before

After

@Brayden Brayden self-assigned this Oct 11, 2024
@Brayden Brayden added the enhancement New feature or request label Oct 11, 2024
@stritt
Copy link
Member

stritt commented Oct 11, 2024

can we have a load route as well? what if I want to turn my local sqlite file into something accessible from the web?

@Brayden
Copy link
Member Author

Brayden commented Oct 11, 2024

can we have a load route as well? what if I want to turn my local sqlite file into something accessible from the web?

Great idea. I won't bundle it into this PR but we should make a new issue for it. I'll add it to the Feature Roadmap on the README as well!

@Brayden Brayden merged commit 34e2b6a into main Oct 11, 2024
@Brayden Brayden deleted the bwilmoth/sqlite-dump branch October 11, 2024 13:33
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

enhancement New feature or request

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants