Sign In

Best Practices

This document highlights what we believe are best practices when developing applications with AppSynergy.

Naming Conventions

Consistent naming patterns make databases much easier to work with – especially as they become larger and more complex.

Table Names

We recommend that you name your tables in the singular and in CamelCase (i.e. Invoice rather than Invoices and InvoiceItem rather than InvoiceItems).

Column Names

We recommend that you name your columns in CamelCase and sparingly use the underscore character (i.e. Invoice_ID is recommended whereas InvoiceId is not, however InvoiceDate is recommended whereas Invoice_Date is not).

Trigger Names

We recommend that you use the default AppSynergy naming scheme for your triggers (e.g. Invoice_AFTER_UPDATE should be the name for an AFTER UPDATE trigger on the Invoice table).

Stored Procedure Names

When naming a stored procedure we recommend that you use a naming convention like this: TableName_MyAction.

Stored Function Names

When naming a stored function we recommend that you use a naming convention like this: TableName_GetSomething. The "Get" helps signify that the function will return a value.

Primary Keys

We recommend that you use an AutoKey column for the primary key on your tables except in special circumstances like the following:

We recommend that you never use Composite Primary Keys (these are Primary Keys composed of multiple columns). If you are an advanced database developer, we suggest you instead use an AutoKey for the primary key and then add an additional UNIQUE INDEX over the other column(s) that are required to be unique. Notwithstanding this rule, there are a handful of edge cases where a composite primary key is best, but those examples are beyond the scope of this documentation. You can add a unique index via Tools > SQL Console with a command like the following:

CREATE UNIQUE INDEX index_name ON table_name (col_name_1, col_name_2)

Generating AutoKey Values

AppSynergy automatically generates AutoKey values when records are added via the UI. However, if you need to insert records via a database trigger or stored routine you will need to call the parasql_next_val() function to generate the primary key:

INSERT INTO Customer (Customer_ID, Name) VALUES (parasql_next_val('Customer'), 'Bill Smith');

NOTE: You cannot simply leave an AutoKey column blank and have the system generate it (as you would with an AUTO_INCREMENT column). Unlike an AUTO_INCREMENT column, an AutoKey column allows "reservations" of values for use within a modern web UI.

UI Design

Auto Publish

Once your application is in use by other people, we strongly recommend turning Auto Publish OFF. See File > App Settings for details.

Be aware that if you modify a database table that an application uses, an AutoPublish will be forced automatically on that application.

Modifying Tables

For a variety of reasons listed below, we recommend that you do not modify tables that are in use by a production application except to add a new column to the table or add an index to an existing column.

Any modification to a production database table should be carefully considered:

In benchmarking MariaDB 10.6 in December 2022 using a table with 1 million rows and 50 columns we found the following:

Manual Schema Changes

If you create or modify database schema outside of the AppSynergy user interface, beware of the following:

NOTE: Do not make excess copies of your application(s). Each extra copy of an application must be opened and modified every time a table in your database is modified. Extra copies of your application will slow down the table modification process, potentially enough to cause it to timeout.

Security: Two-Factor Authentication

AppSynergy uses Google Accounts for authentication and SSO; we can also support SAML 2.0 if needed. The Google Account can be a Gmail account but should be a Google Account based on your existing business email address (you can create one here). In all cases the Google Account will support two-factor via SMS (least secure), the Google Authenticator app on your smartphone (more secure), or a hardware security key that supports "FIDO U2F" like those from Yubico (most secure). We strongly recommend that AppSynergy users with Administrator privileges use some form of two-factor authentication on their account.

Temporarily Enabling the General Log

If you need to see a log of all database activity you can do so by enabling the general log. Because this will log all connections, queries, data modifications, etc. it can generate very large log files very quickly if left enabled. We suggest you enable it, do your research, then disable it. Enabling the general log requires root access, so you will need to enable it and query the log via a third-party tool like MySQL Workbench.

To enable the general log:

SET GLOBAL general_log = 1;

To query the general log data:

SELECT * FROM mysql.general_log;

To disable the general log when you are done:

SET GLOBAL general_log = 0;

Importing A Database Using Command Line Tools

Please refer to the AppSynergy Data Migration Guide

Exporting A Database Using Command Line Tools

AppSynergy automatically performs nightly backups of your entire database. You can also create a backup at any time on demand to your own local computer.

The recommended way to create a local backup of your database is as follows:

mariadb-dump --host=dbs-myserver.parasql.com --user=root --password=rootPwd --ssl-verify-server-cert --triggers --routines --events --dump-history --master-data --single-transaction db12345 > mybackup.sql

The above command will ensure all code and data is backed up, including any history records from those tables with their audit trail enabled.

The options --master-data --single-transaction ensure that a global read lock is acquired on all tables at the beginning of the backup to ensure that a consistent snapshot of the entire database is achieved without blocking ongoing read and write activity.

Connecting To Your Database via MySQL Workbench

NOTE: AppSynergy has an integrated SQL Console which eliminates much of the need for MySQL Workbench. See Tools > SQL Console for details.

You can connect directly to your AppSynergy database with MySQL Workbench. Workbench allows you to issue any SQL command as root, which has slightly more permissions than an AppSynergy Administrator.