Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Table designer

The panels of APIs & Logic → Instance API (DB API) make and change the tables of every database API Maker connects to, and keep their schemas in step.

The four panels

APIs & Logic → Instance API (DB API) has four panels, left to right : instances, databases, tables, APIs. Pick one in each and the next panel fills.

Panel What it holds
Instances A connection to a database server : MongoDB, MySQL (TiDB, Percona), MariaDB, SQL Server, PostgreSQL, Oracle. Add New takes the database type, a unique Instance Name (used in every URL and in code, it can not change later), the Connection String picked among the keys of the secret and Max Pool Connections ; Oracle adds the username, the password and the privilege. Saving tests the connection first : an instance which can not connect is not saved. Compact Instance (in the menu of a MongoDB instance) reclaims space on every database of the instance, one after the other ; the admin, config and local databases of MongoDB are left alone.
Databases The databases the connection sees, with their settings. Add New makes a database on every kind of server, with what its kind takes : the character set and the collation (MySQL, MariaDB), the owner, the template, the encoding and the locale (PostgreSQL), the collation (SQL Server), the password, the tablespace and the rights of its user (Oracle, where a database is a user) or its first collection (MongoDB) ; the statements are shown before they run. Edit renames a database or gives it other options (below) ; Delete shows the statements, asks for the name and can remove the schemas of its tables too. Bulk Schema Creation generates the schema of every table at once ; Compact Database reclaims space on MongoDB.
Tables The tables or collections, with their schema, their indexes and their settings. + opens the table designer ; Run a script its editor of scripts ; Edit opens the designer on the table chosen, every field filled (below) ; Delete shows the statement, asks for the name and can remove the schema of the table too. A sync mark appears when a table and its schema differ : click to settle it.
APIs The operations of the table, each with its settings and Test API, which opens the API testing page on it.
  • Connection String Sandbox : another string for the code which runs in the sandbox, when the sandbox reaches the database through another address. Empty, the main one is used.
  • Is Multi Tenant Structure Instance and Connection String Multi Tenant make a multi-tenant instance ; Select tenant then loads the databases of one tenant.

The table designer

Designer makes one table, column by column, with the types of its database : every type MySQL, MariaDB, PostgreSQL, SQL Server, Oracle or MongoDB has, each with what it takes (a length, MAX, a precision and a scale, the values of an ENUM). A column is a part of the primary key, not NULL, unique, numbered by the database (AUTO_INCREMENT, an identity), has a default value or expression, a comment, a collation, a check, or is computed. The table has its indexes, foreign keys, checks and options (the engine and character set of MySQL, a tablespace, UNLOGGED). A collection of MongoDB checks its fields with $jsonSchema (type, required, limits, pattern, values), and can be capped, a time series or clustered. Quick add puts in the columns most tables have : a key, a UUID key, created and updated times, an email.

The server writes the script of the design while it is designed, on the right, and says what its database would refuse at the field it is about. Create table runs that script ; Edit as script takes it to the editor.

Script runs any number of statements : tables made at once, with their keys, procedures, data. Statements are split the way the client of the database splits them : at ;, at a line GO (SQL Server), DELIMITER $$ (MySQL, MariaDB), a line / after a block of PL/SQL (Oracle) ; on MongoDB db.createCollection(…), db.users.createIndex(…), insertOne, insertMany, db.runCommand(…). Each statement says what became of it : its time, its rows, what the database said, and an error at its line. Stop on error leaves out the statements after one which failed ; All or nothing (PostgreSQL, SQL Server) runs the script in one transaction, so a script which fails changes nothing. A script which drops, truncates or deletes asks first. Drafts of the designer and of the editor are kept in the browser.

Editing a table there is

Edit reads the table from its database : its columns with their types, sizes, defaults, identities and computed expressions, its primary key, unique keys, indexes, foreign keys, checks, comments and options (on MongoDB the fields of its validator, its indexes and its options). Change anything the designer shows : rename the table or a column, change a type, add or remove columns, keys, indexes and checks. Each column says what becomes of it (new, edited, was its old name) ; a column removed is listed under the columns, with Put back. Reset goes back to the table as it is.

The SQL tab lists the changes and the statements which make them, written the way each database takes them :

  • MySQL and MariaDB : one ALTER TABLE, which also puts columns in their new order (AFTER) ; RENAME TABLE ; foreign keys removed first and added last.
  • PostgreSQL and SQL Server : a statement per change, all in one transaction : the whole is made or nothing. SQL Server takes away the defaults, keys and indexes of a column before it changes the column, and makes them again.
  • Oracle : MODIFY with only what changed.
  • MongoDB : collMod with the new validator, renameCollection, and the documents follow their fields : a field renamed is renamed ($rename), a field removed is removed ($unset) in every document.

A column computed from a column whose type changes is made again around it. A change which can lose data, or fail on the rows there are (a column removed, a narrower type, NOT NULL without a default), is marked, and Apply changes asks first. If the table changed on the server since it was opened, the designer says so before anything runs. What the designer does not show (an index of expressions, a check which is not enforced, a validator written by hand) is listed at the top and left as it is.

The schema follows its table

Once a table is changed, its schema is changed with it : the designer shows the code of the schema as it is and as it will be, side by side. Only what the table changed is changed : a property added, removed or renamed with its column, and inside a property only __type, isPrimaryKey, isAutoIncrementByDB, validations.required and validations.maxLength. Your own validations, conversions and comments stay. Update the schema saves it ; Remember my choice updates schemas without asking from then on, or never. A table made in the designer is offered a new schema.

The list of tables marks a table whose schema says something else than the table : a column without a property, a property without a column, another type, required or not, another length, key or numbering. A click lists the differences, field by field, and settles them either way :

  • The schema follows the table : the code of the schema is changed for the fields chosen, shown before it is saved.
  • The table follows the schema : the designer opens on the table with the columns the schema asks for filled in, and shows the statements before they run.

A schema whose table is gone is marked too, and can be removed.

Recorded for every environment

What the designer, the editor of scripts, Delete, the form of a database and the index forms do can be recorded into a migration script : the pill REC in their footers says where the change goes (a script of the account, a new one, or Don't record), and the list of tables shows it in its header. The statements which ran and changed something are added to the script ; every environment which deploys the project runs it, and makes each change once, in its order.

Editing a database

Edit reads a database with its options. PostgreSQL and SQL Server rename a database (ALTER DATABASE … RENAME / MODIFY NAME) ; MySQL and MariaDB make the new one and move every table into it (RENAME TABLE) ; MongoDB moves every collection (renameCollection). MySQL and MariaDB change the character set and the collation, PostgreSQL the owner, SQL Server the collation, Oracle the password, the tablespace and the quota of the user. The schemas of the tables follow a database renamed. Delete removes a database, closing the connections to it first when asked (pg_terminate_backend of its sessions on every PostgreSQL version, SINGLE_USER, CASCADE of an Oracle user).