Integrated database UI db and table view?

Hi all.

New to Wappler I just want to know if it is possible to view database tables and do row editing, creating etc. of the db in wappler. I suspect I know the answer: you have to use an external database tool, but would be awesome if i could do it within Wappler?

I currently use Navicat for Mac, but would be faster if i could do it all from within Wappler.

Cheers!

1 Like

This is something we are working on right now :slight_smile: - we call it the Database Creator!

Hope to present it soon!

4 Likes

Hi Steven!

Great question… I hear rumours this will come, but it hasn’t arrived yet.

The journey of database creation has been interesting for me… in Bubble you do lots of clicking, and I found myself creating a parallel document in a spreadsheet of the fields and their possible values which was constantly in need of maintenance.

Now in Wappler, all my table definitions are in a collection of .sql files, where I document them with comments, so everything is in one place and always up to date. Those files can also create a core database content to use for testing too… along with lots of stored procedures which do much of the heavy background work that used to be done by the “Backend Workflows” in Bubble… things like creating data structures when users sign up.

With the database design this way, as I develop and the database structure changes, I can choose sometimes to just blow the whole structure away and replace it by running the .sql files… and if I make a new database somewhere else as I evaluate different hosting solutions I can create the new database and be running with it in a few minutes.

So for me, the lack of point and click database creation has moved me to a more flexible and consistent place.

Clearly as I go to production there won’t be any more “blow the whole structure away” going on, and then I will need to move to tools like gh-ost for database updates, which I have yet to learn.

I hope that helps!

Best wishes,
Antony.

Example from one of my .sql files:

-----------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS lookups (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL);
-----------------------------------------------------------------------------------------
ALTER TABLE lookups ADD COLUMN subscriber_user INT UNSIGNED;
ALTER TABLE lookups ADD FOREIGN KEY (subscriber_user) REFERENCES users(id);
ALTER TABLE lookups ADD COLUMN parent_lookup INT UNSIGNED DEFAULT 0;      -- e.g. for Expense Sub Category, this is the Expense Main Category.

ALTER TABLE lookups ADD COLUMN colour CHAR(7)DEFAULT "       ";           -- #123456
ALTER TABLE lookups ADD COLUMN lang_code CHAR(5) DEFAULT "en-GB";         

ALTER TABLE lookups ADD COLUMN lookup_name VARCHAR(128) DEFAULT "";       -- e.g. Male, Female, North, South, Card Payment...
ALTER TABLE lookups ADD COLUMN lookup_type TINYINT UNSIGNED DEFAULT 0;    -- 1 = Activity Tag, 2 = Activity Type
                                                                          -- 10 = Contact Tag, 11 = Region,  12 = Gender, 13 = Age Range
									  -- 20 = Payment Method, 21 = Expense Payment Method, 22 = Expense Main Category, 23 = Expense Sub Category
                                                                          -- 30 = Price Type, 31 = Price Group, 32 = Optional Extra Price Group
                                                                          -- 40 = Mailing Message Collection, 41 = Reusable Text Collection, 42 = Image collection, 43 = Attachment collection
                                                                          -- 50 = Question Topic, 51 = Question Group, 52 = Response Group
                                                                          -- (50 and 52 found in questions and responses)
                                                                          -- 100 = Background Colours, 101 = Item Colours
                                                                          -- 110 = Question Types (n = number, N = Number Selection ...)
                                                                          -- 111 = Answer Types (n = number, d = dates...
ALTER TABLE lookups ADD COLUMN description VARCHAR(255) DEFAULT "";       -- Internal Description
ALTER TABLE lookups ADD COLUMN user_message VARCHAR(255) DEFAULT "";      -- Message user sees such as "Please pay into account XYZ"

-- Deletion / Hidden
ALTER TABLE lookups ADD COLUMN is_hidden BOOLEAN DEFAULT 0;               -- Won't be selectable but will still appear next to an item.

-- Indices
CREATE INDEX lookups_lookup_type     ON lookups(lookup_type);
CREATE INDEX lookups_lang_code       ON lookups(lang_code);
CREATE INDEX lookups_parent_lookup   ON lookups(parent_lookup);
CREATE INDEX lookups_is_a_test       ON lookups(is_a_test);
CREATE INDEX lookups_is_a_demo       ON lookups(is_a_demo);
CREATE INDEX lookups_is_hidden       ON lookups(is_hidden);
CREATE INDEX lookups_lookup_name     ON lookups(lookup_name);
4 Likes

ahhhhh I just ran around my home office screaming, that would be an absolute game changer!

3 Likes

@anthony thanks yep that makes sense and useful, especially with stored procedures on signup I am sure I will need to get my head into that space sooner than later.

1 Like