Setting up

Redbrix account registration

To get started with Redbrix, you need to create an account, which you can do via the Redbrix registration page. You can create an account with an email address or you can log in with Google or GitHub.

Connecting your database

After logging in to your Redbrix dashboard, you will be prompted to connect your database. Redbrix supports MySQL, MariaDB and PostgreSQL databases (support for MS SQL Server is planned for the future).

Enter your connection details, specifically:

• Host: The address of your database server (a hostname or IP address).
• Database type: MySQL, MariaDB or PostgreSQL.
• Port: The port number your database server is listening on. This is filled in for you when you choose the database type (3306 for MySQL and MariaDB, 5432 for PostgreSQL); change it if your server uses a different one.
• Database Name: The name of the specific database you want to connect to.
• Schema (PostgreSQL only): A PostgreSQL database can hold several schemas, and Redbrix works with one at a time. Click "Find schemas" to choose from the ones your login can use, or leave it empty and Redbrix will choose for you: public if it has tables, otherwise the only other schema that does.
• Username: Your database username.
• Password: Your database password.
• Require SSL/TLS: Ticked by default, so Redbrix refuses to connect unless the connection is encrypted. Untick it only for a server that does not support encryption, which Redbrix will tell you if it finds. Unticked, a PostgreSQL connection still uses encryption whenever the server offers it; a MySQL or MariaDB connection does not.

Once you have entered the required information, click the "Connect" button. Redbrix will attempt to establish a connection to your database. If the connection is successful, you will be taken to the main dashboard where you can start exploring your database and using Redbrix features.

Allowing external access

If you are hosting your database on a third-party provider, you may need to configure your database server to allow external connections and ensure that your firewall settings permit access from Redbrix's IP addresses. You should allow connections from the following IP addresses, on your database's port (3306 for MySQL and MariaDB, 5432 for PostgreSQL):

Address          Subnet Mask         Port
13.134.60.54     255.255.255.255     3306 (or 5432 for PostgreSQL)

or, if your provider allows CIDR notation, you can use:

13.134.60.54/32

A PostgreSQL server also checks its own list of who may connect, in its pg_hba.conf file. See Connection problems for the line to add.

Golden rules for database schema design

In order for Redbrix to provide the best possible experience, there are a few golden rules to keep in mind when designing your database schema:

Use a compatible database version

Redbrix supports MySQL 5.7 and later, though 8.0 or later is recommended. For MariaDB, 10.3 is the minimum, though 12.0 or later is recommended. For PostgreSQL, 13 is the minimum.

Your mileage will vary with older versions. The basics should work fine but you should expect some rough edges and missing features.

Support for MS SQL Server is planned for the future, but there is no ETA at this time.

Use standard data types

Stick to your database's standard data types for your columns. This ensures that Redbrix can properly interpret and display your data.

Redbrix interprets most data types and displays them in the application in the following formats:

• varchar, char, int, smallint etc. are displayed in single-line text fields on edit forms.
• date, timestamp fields are displayed in single-line text fields with an accompanying date picker.
• blob (MySQL and MariaDB) and bytea (PostgreSQL) fields are displayed as file upload inputs and their contents rendered accordingly on record details panels.
• tinyint(1) (MySQL and MariaDB) and boolean (PostgreSQL) fields are displayed as checkboxes.
• enum fields, including PostgreSQL enum types, are displayed as drop-down lists.
• text fields on MySQL and MariaDB are displayed as rich-text editors. On PostgreSQL, where text is the everyday type for any string, a text field is a single line unless its name suggests longer content, such as notes, description or comments.
• PostgreSQL types with no direct equivalent, such as uuid, json, arrays and intervals, are displayed and edited as text.

Define clear relationships

You should define your foreign key relationships explicitly. You can check a table's foreign key definitions by running the following SQL query on MySQL or MariaDB:

SHOW CREATE TABLE your_table_name;

or, on PostgreSQL, this command in psql:

\d your_table_name

You will see the defined foreign keys towards the end of the output. If there are any missing, it is highly recommended that you create them using the following SQL command, which works on all three:

ALTER TABLE child_table_name
ADD CONSTRAINT fk_name FOREIGN KEY (child_column) REFERENCES parent_table(parent_column);

Or, if you're happy with a default constraint name, just

ALTER TABLE child_table_name
ADD FOREIGN KEY (child_column) REFERENCES parent_table(parent_column);

Properly defined relationships allow Redbrix to provide powerful features like automatic relationship mapping and intuitive data navigation.

Include primary keys

Ensure that each of your tables has a primary key defined. This allows Redbrix to uniquely identify records and provide features like editing and deleting records directly from the UI.

Avoid reserved keywords

Try to avoid using your database's reserved keywords as table or column names, as this can lead to issues with query execution and may require additional escaping.

User permissions

Configuration permissions

Redbrix decides who sees the configuration tools (e.g. custom labels and title fields) from the database login's own permissions — there's nothing extra to configure inside Redbrix.

Admins

A login that can also change the database's structure. They additionally see the configuration tools.

• On MySQL and MariaDB, a login with structural rights (such as ALTER) on the database.
• On PostgreSQL, a login that owns the schema or the database, or is a member of the role that does.

You do not need a server-wide superuser (MySQL's "root" or PostgreSQL's "postgres") for this, and you shouldn't use one for everyday work. Create an ordinary login and give it these rights on just the one database you use with Redbrix.

Regular users

A login that can read, and optionally add/edit/delete, data. They get the full data experience; the configuration tools are simply hidden.

Data permissions

The permissions assigned to the database user with which you connect to Redbrix will determine which functions are accessible within the application.

You can assign these permissions to different users to control their access to various functions within the application. For example, if you want to create a user who can view and edit records but not delete them or create new ones, you can run the following statements on MySQL or MariaDB:

CREATE USER 'your_user'@'%' IDENTIFIED BY 'your_password';
GRANT SELECT, UPDATE ON your_database.* TO 'your_user'@'%';

or on PostgreSQL, where a login also needs to be allowed to connect to the database and to use its schema:

CREATE ROLE your_user LOGIN PASSWORD 'your_password';
GRANT CONNECT ON DATABASE your_database TO your_user;
GRANT USAGE ON SCHEMA public TO your_user;
GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO your_user;

On PostgreSQL, a login that can add records (INSERT) to tables numbered with serial columns also needs to use their sequences:

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO your_user;

ON ALL TABLES covers the tables that exist when you run it. To cover tables created later as well, run this as the login that creates them:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE ON TABLES TO your_user;

It is worth reading up on user management and permissions to understand fully the implications of these commands, particularly section 15.7.1 Account Management Statements in the MySQL documentation, or the GRANT page in the PostgreSQL documentation.