Build Internal DB

This is the database hosted internally by the platform to build apps faster, and manage data. Appizap database requires no setup and gives users a powerful user interface for managing their data.

Table Editor

This editor panel helps users to create tables and manage their schema.

Create new table

Users can add new table into the database.

Field	Description
Name	Provide a unique name for the table
Description	A brief description of the table and the purpose
RLS	Restrict access to the table by enabling Row level security

Manage columns - New table

This interface allows users to create/update/delete columns of the table.

The interface generates the id and created_at columns by default. Users can keep them or discard them according to the need, and create new columns using the Add column button. Click the Save button to commit the work.

Below is the list of information the user will enter when creating a new column and then save the column.

Field	Description
Name	Provide a unique name for the column
Type	Select the type of the column
Default Value	Set the default value for the column when a new row is inserted into the table
Primary	Check if the column has to be the primary column of the table

Add new columns to the table using the New Column button.

Input the required fields and click the Save button to add the new column.

Add the required columns to the table the same way, using the New Column button.

In this example - the other fields like 'email', 'countryresidence', 'primarynationality', and 'alternatenationality' columns are added.

The Options button at the end of each column allows the user to Edit the column and Delete.

Manage columns - Existing table

This presents the interface for users to manage the columns of an existing table - by clicking New Column Edit Column Delete Column

Below is the list of information the user will enter when creating a new column and then saving the column.

Field	Description
Name	Provide a unique name for the column
Description	Select the type of the column
Foreign Key Relation	This will open up the list of tables where the user wants to link this column as the foreign key, and then pick the column to which the link is complete. It will show an error msg if the data types of both columns do not match
Data Type	Choose the type of the column from the list
Default Value	Set the default value for the column when the record gets inserted into the table
Is Primary Key	Check if the column has to be the primary column of the table
Allow Nullable	Set if the column can allow nullable values if no value is provided when the record is saved
Is Unique	Enforce values in the column to be unique across rows
Check Constraint	Define the rule that will check the value saved in this column meets the criteria

Primary Key

One essential feature of a table is the inclusion of a "primary key," which serves as a distinct identifier for each individual row of data.

It is advisable to establish a Primary Key for each table within your database. Any column can be designated as the primary key, provided that it contains a unique value for each record.

Using a uuid type or a numbered identity column as the primary key is a commonly observed practice.

create table projects (
  id bigint generated always as identity primary key
);

We could also use generated by default as identity, which would allow us to insert our own unique values.

create table projects (
  id bigint generated by default as identity primary key
);

List of data types of the column

Each column in a database has a specific data type assigned to it. The database management tool offers a variety of pre-set data types for users to choose from, with the option to create custom types or utilize extensions if necessary.

The SQL editor allows users to work with any data type supported by Postgres. However, the Table Editor interface only displays a limited selection of data types in order to streamline the user experience for those who may not be as familiar with database management.

Refer the data types for more details

Data Type	Description
int2	Signed 2byte integer value. -32,768 to 32,767
int4	Signed 4byte integer value. -2,147,483,647 to 2,147,483,647
int8	Signed 8byte integer value. -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807
float4	Single precision floating point number. 23 bits 8 bits
float8	Double precision floating point number. 52 bits 11 bits
numeric	Exact numeric of selected precision
json	Json formatted text value
jsonb	Binary Json text
text	Variable length characters
varchar	Variable length characters
uuid	Universally unique identifier
date	Calendar date format
time	Time of day without timezone
timetz	Time of day with timezone
timestamp	Date and time without timezone
timestamptz	Date and time with timezone
bool	true or false

Sort tables

This is the option to sort the table and view - by Alphabetical or Entity Type

Search tables

This is the feature to search and filter down the tables on the list.

Filter columns

This lets the user narrow down the columns they want to view/update/delete.

SQL Editor

This editor helps the user to write and run queries using SQL language. There is a result window below the query window for viewing the execution results. This serves as the query panel where the user can operate with the tables and data using SQL syntax.

Joining tables with foreign keys

The origin of the term "Relational" in database management is rooted in the interconnected nature of data, which exhibits some form of relational structure. In the case of the example involving "projects," it may be related to a designated "category" for each project, such as "Infrastructure" or "Cloud." To facilitate this, we shall establish a distinct table known as "categories" and establish a connection with our existing movies table.

create table categories (
  id bigint generated always as identity primary key,
  name text -- category name
);

alter table projects
  add column category_id bigint references categories;

You can also create "many-to-many" relationships by creating a "join" table.

create table projects (
  id bigint generated by default as identity primary key,
  name text,
  description text
);

create table resources (
  id bigint generated by default as identity primary key,
  name text
);

create table performances (
  id bigint generated by default as identity primary key,
  project_id bigint not null references projects,
  resource_id bigint not null references resources
);

Schema Visualizer

This panel displays the tables and columns as a network of schema, including the foreign key links. This helps the user to visualize the overall DB schema of the app.

Tables are part of schemas, which serve as a structured method for organizing tables, typically implemented for security purposes.

If a schema is not specified during the table creation process, Postgres will default to creating the table in the public schema.

We can establish schemas to structure our tables. The creation of a private schema will be kept hidden from our application programming interface (API).

create schema private;
create table private.credits(
  id bigint generated by default as identity primary key,
  credit bigint not null,
  resource_id bigint not null references public.resources
);

Views

A view is a virtual table based on the result of a SELECT statement from one or more tables. It provides a way to simplify complex queries, hide data complexity, and provide security by restricting access to specific columns of a table.

For example, suppose you have a table named employees containing information about company employees. In that case, you could create a view named employee_names that includes only the first_name and last_name from the employees table. Here is the SQL statement:

CREATE VIEW employee_names AS
SELECT first_name, last_name
FROM employees;

Now, you can use the employee_names view in your SQL queries like this. This query will return all the rows from the employee_names view, which contains only the first_name, last_name columns from the employees table.

SELECT * FROM employee_names;

It's essential to remember that a view in SQL does not store data. Instead, it is just a SELECT statement executed each time you query the view. It means that the data in the view is always up-to-date with the data in the underlying table or tables. It is possible to update data via the view in certain circumstances, but not recommended. You should modify the data in the underlying table if you need to modify the data.

Security

As a standard practice, views are typically accessed based on the creator's authorization, known as the "security definer" setting. When a privileged role generates a view, individuals who access it will inherit the permissions assigned to that role. To implement row level security measures, the view should be defined with the "security invoker" modifier.

Views can restrict the amount and type of data presented to a user. Instead of allowing a user direct access to a set of tables, we provide them a view instead. We can prevent them from reading sensitive columns by excluding them from the underlying query.

Materialized View

A materialized view is a view that stores the results of the view’s query. Whenever you query the materialized view, it returns the data stored with the view itself. It doesn’t run the view’s query against the underlying tables.

CREATE MATERIALIZED VIEW viewname
[REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]]
[BUILD IMMEDIATE|BUILD DEFERRED]
AS
select_query;

A materialized view can combine all of that into a single result set that’s stored like a table.

Calculations can also be added to materialized views for any fields you may need, which can save time, and are often not stored in the database. For example, perhaps you want to store both the monthly and annual salary for an employee. Normally, you would only store one and calculate the other, but in a materialized view, you can store both.