Hosting guide: deployments, databases and migrations

Your hosted app consists of three separate things: application files (pages and resources), configuration (settings), and database contents (stored records). Uploading a new version changes files; it does not automatically replace or empty the database. Migrations are numbered SQL files that update the database structure or its data, for example by adding a table or a column.

This guide explains how to upload and update an app hosted on datapage.app. File transfers use SFTP, a secure way to copy files between your computer and your hosting account. Use the connection details supplied for your account. For a first connection, see the file transfer tutorial.

1. What the hosting service provides

DataPage runs SQLPage for your application and makes it available at the web address supplied when your account is set up. Connections to the website are encrypted using HTTPS. You upload your application using SFTP (port 22), for example with FileZilla. This account lets you manage files; it does not let you run commands on the server or connect a database tool directly from your computer. Contact support if you need database access.

PostgreSQL is the standard database supplied with hosting. Stored records live in that database and survive application restarts. Some accounts use SQLite instead. Your database_url in /sqlpage_config/sqlpage.yaml identifies the database your app uses; contact support if you are unsure. You can also connect to a compatible database hosted elsewhere.

Sections 2–4 explain files, logs, uploads, and migrations for all accounts. Then read section 5 for PostgreSQL or section 6 for SQLite for database access, backups, and resets. Section 7 is a release checklist for everyone.

The public site and the SFTP login have different purposes. Your application must handle its users’ sign-in, permissions, and access to private documents. Keep the login details used to upload files separate from the accounts used to sign in to your application.

2. The folders you see over SFTP

The paths below start at the main folder you see after connecting with your file transfer software.

Path Purpose What to upload
/website/ Application pages and files intended for visitors The contents of your application folder: SQL pages, CSS, JavaScript, images, and other intended resources
/sqlpage_config/sqlpage.yaml Hosted SQLPage settings, including the database connection Keep the supplied settings; change only the settings you intend to update
/sqlpage_config/migrations/ Database migration scripts Numbered .sql migration files compatible with the selected database
/sqlpage_config/templates/ Custom SQLPage component templates, if used The contents of your local SQLPage templates directory
/logs/ Recent SQLPage messages and errors, in sqlpage.log Use for troubleshooting; do not include in deployment cleanup

These folders are shared by PostgreSQL and SQLite accounts. Database storage and backup instructions are covered separately in sections 5 and 6.

Everything placed in /website/ should be treated as potentially accessible through the application. Keep database files, passwords, backups, .env files containing settings or secrets, and private project files outside it. Application code must protect confidential documents even when they are stored in the database.

Where to put the files from your project

If your project, for example a Git repository downloaded from GitHub, has app/ and sqlpage/ folders:

Folder on your computer             Folder in your hosting account
app/index.sql                       /website/index.sql
app/assets/style.css                /website/assets/style.css
sqlpage/migrations/004_change.sql    /sqlpage_config/migrations/004_change.sql
sqlpage/templates/my_component.handlebars
                                    /sqlpage_config/templates/my_component.handlebars

Transfer the contents of app/, not the app directory itself, unless you intentionally want an /app/ URL prefix. Preserve subdirectories inside the application.

Do not copy the whole repository into /website/. Automated tests and their sample data, the .git folder, Docker setup files, programs used to run the app on your computer, local database copies, and development settings are not part of a normal upload.

A local sqlpage/sqlpage.json often selects the database used on your computer, sets the address for running the app locally, or uses web_root: "app" to select the folder containing its pages. It is not a replacement for the hosted /sqlpage_config/sqlpage.yaml. Likewise, /website/sqlpage/migrations/ is not the hosted migration directory, and /website/sqlpage/templates/ is not the hosted template directory.

File and folder permissions

Permissions control who can read, change, or open files and folders. Your SFTP account can upload and manage files inside /website/ and /sqlpage_config/. The main folder and /logs/ are managed by the hosting service: you can open them, but cannot upload to the main folder or change or delete logs. A “permission denied” message in these locations is expected.

Normally, keep the permissions supplied when files are created or uploaded. If your transfer software preserves permissions from your computer, make sure your hosting account can read the uploaded files and open their folders; it also needs write permission wherever the app saves files. SQL pages do not need the file permission called “execute”; for folders, that permission means being able to open and access their contents. Avoid granting everyone write access (often shown as 777). If an upload or the app reports “permission denied” inside an application folder, check its permissions in your transfer software or contact support.

File permissions do not decide which website visitors can see a document. Keep private files outside /website/ and use application access controls for confidential content.

Reading your application logs

Connect with your SFTP software, open /logs/, and view or download sqlpage.log. Open the downloaded copy in a text editor. The hosted file is read-only and refreshes roughly every minute; refresh the folder listing and download a new copy after reproducing a problem. You do not need a command-line connection.

Entries include a timestamp and a message. They can show application starts and restarts, database connection or migration failures, warnings, and errors when SQLPage processes a page. Depending on the error, the message may include a page or migration filename and details from the database. This is a recent, size-limited troubleshooting history, not a complete record of every visitor, every database change, or all past activity.

To investigate a problem, note when it happened, then look near that time for errors and the first failure before any repeated messages. For an upload problem, check entries after the upload; for a page problem, reproduce it in your browser first. Timestamps include a time-zone offset, which may differ from your computer’s time. If the relevant messages are missing or you cannot read the file, contact support with the site address and approximate time.

Logs may contain parts of SQL statements, submitted values, or other sensitive application information. Share only the relevant excerpt with support and remove passwords, tokens, and personal data. Do not upload logs into /website/.

3. What happens when you upload files

Pages, images, styles, and scripts

SQLPage uses the files in /website/ to serve your app. New or replaced pages become available without a manual restart. Deleting a page removes the page provided by that file, although your app may have a rule to handle missing pages at the same address. Browsers sometimes keep older copies of styles, scripts, and images, so those changes may require a refresh.

Uploading several files does not replace the whole app in one step. While a group of files is being transferred, visitors can encounter a mixture of old and new pages, or incomplete files. Choose a suitable time for a short interruption for changes that need coordinated database and application updates. If visitors must not use the app during the update, ask support to arrange a pause while you transfer the files.

Uploading a new release does not remove obsolete files. Compare the deployed files with the new release and remove pages that are no longer needed, especially diagnostic pages and old files that process forms or actions. Keep documents uploaded by users and any other files created while the app is running. Before deleting all of /website/, identify anything you cannot restore from your project files.

SQLPage configuration and migrations

Changes in /sqlpage_config/ and its migration directory trigger an automatic application restart after a short delay. At startup SQLPage checks which migrations have already run and runs any new ones before making the app available. This can briefly interrupt access. Allow roughly a minute after a transfer, then check the site and logs; a longer migration can take more time.

Upload each migration completely before giving it its final .sql filename. A temporary filename without the .sql suffix, followed by an SFTP rename, avoids executing a partly uploaded script. The restart delay does not ensure that all files arrive together. If migrations rely on earlier ones, upload and rename them in order, starting with the lowest version number, or arrange with support to pause the app during the transfer.

Custom component templates control how your own components are displayed. Updating them is separate from updating the main settings file. If a template change is not visible, ask support for a restart. An optional /sqlpage_config/nginx.conf file for custom URL and caching rules is checked and applied separately; it does not run database migrations.

4. Database migrations: initial install versus upgrade

Migrations are SQL scripts with a numeric version followed by an underscore and a description, such as 001_initial_schema.sql or 004_add_account_status.sql. Versions determine execution order; gaps are allowed. Versions must be unique within your migration history. The names do not need the same number of leading zeroes, but use a consistent convention.

SQLPage records each completed migration in the database's _sqlx_migrations table, along with a checksum: a fingerprint of the file contents used to detect changes. Restarting the app does not rerun an unchanged, successfully applied migration. The history belongs to the database, not to the directory of files.

Keep migrations that have already run unchanged, including their version numbers and file contents. Even a formatting change can alter the checksum and prevent the app from starting. Renumbering an applied script can make it appear to be a new migration. Removing a script does not undo it; deleting history rows does not undo the schema or data changes either.

A new, empty database

Upload the full set of migrations intended for your hosted app, in order, including the files that create its initial tables. The tables, columns, and their relationships are called the database schema. Exclude test data and demo records unless you deliberately want them. A migration that creates only the schema does not create user accounts, administrator login details, or business records; set those up separately as your application requires.

An existing database you want to keep

  1. Obtain a complete, reliable database backup and save the existing application and configuration before deployment.
  2. Compare the database's applied migration history and current schema with the release. Ask support to inspect this if you cannot access the database directly.
  3. Retain the existing migration scripts. Add only the new migrations required for this database, using new versions above its existing history.
  4. Test the upgrade against a copy, including data preservation and application behavior, then coordinate the schema and page updates.

If the repository's initial migration creates tables that already exist, do not upload it as a new migration against that database. If a repository migration repeats changes you already applied under other numbers, do not merely renumber it: it would still repeat the same SQL. Compare the two histories to identify which changes are still needed. The migrations needed to update an existing database can differ from those needed to create a new one.

If a migration fails

Check the logs and identify the first failing migration. Do not repeatedly rename migrations or delete the history table to force startup. Whether a failed migration leaves any changes behind depends on the database system and the SQL used. Check what changed before trying again. Send support the filename, error message, and deployment time, without passwords or sign-in tokens.

See the SQLPage migration guide for more about SQLPage’s migration rules.

5. PostgreSQL accounts

This section applies to the standard PostgreSQL hosting service. Follow the shared file, log, and migration instructions above when updating your app.

Where your data lives and how to connect

PostgreSQL stores your records and migration history in the configured database, separately from your SFTP files. There is no active database file to download or replace through SFTP. Replacing or deleting /website/ does not clear the database, and uploading application files does not import database records.

Keep the supplied PostgreSQL database_url unless you intentionally switch databases. If that URL contains localhost, it means the server where your app runs, not your own computer. It is not an address you can use from your own computer. Your file transfer login details do not by themselves give you a connection to the database; ask support for the supported database access method for your account. Keep database connection details private.

Updating an app while keeping its data

Use PostgreSQL-compatible SQL pages and migrations. Follow section 4: keep applied migrations unchanged and add only the new migrations required for the existing database. Keep the hosted connection settings when uploading the new application version. Records remain in PostgreSQL unless your application or a migration changes or deletes them.

Backups, imports, and restores

Contact support to coordinate a database export, import, or restore. An SFTP download of your application and configuration saves those files; it does not back up PostgreSQL records. Arrange a complete database backup separately before changes that affect stored data or database structure. An import can replace or conflict with existing records and migration history, so explain what you want to preserve. A restore can lose changes made after the backup.

For a complete reset, contact support and state whether any current data must be preserved. Deleting application files or migration scripts does not reset PostgreSQL.

6. SQLite accounts

This section applies only to accounts configured to use SQLite. Follow the shared file, log, and migration instructions above when updating your app.

Where your data lives

SQLite stores both application data and migration history in the file selected by database_url, commonly /sqlpage_config/database.db. It persists through application restarts and page uploads. A local development database is a different database, even if its filename is the same. The hosted filename can differ: check database_url to identify the active file, and do not assume that a .db file found elsewhere is in use.

Updating an app while keeping its data

Do not overwrite the hosted database with a release or a local copy when updating code. That would replace accounts, documents, business records, and migration history as well as the schema.

Follow section 4 for migrations: keep applied scripts unchanged and add only the new ones needed for the existing database. Use SQLite-compatible SQL pages and migrations.

Backups and restores

For a complete, reliable backup, restore, or file replacement, arrange with support to pause the app or create a backup using SQLite’s backup tools. While the app is running, SQLite may keep recent changes in companion files, called journals or WAL files. Downloading only the .db file can leave those changes out. Do not delete or separately copy -wal, -shm, or journal files while the database is in use.

For a complete reset, contact support and state whether any current data must be preserved. Support can identify the active database and coordinate its safe replacement.

Moving from SQLite to PostgreSQL

These are different database systems. Moving an app requires adapting its tables, SQL pages, and migrations to PostgreSQL, and separately transferring any records you want to keep. For example, SQLite AUTOINCREMENT declarations are not PostgreSQL syntax. Uploading a .db file or changing database_url does not perform that conversion. Contact support to coordinate the transfer and decide how to handle migration history.

7. Before and after every release

Before deployment, identify which database system and database your app uses, decide whether the current data must survive, save the current release, and arrange a consistent database backup. Test the new version with the same database system. For external databases, arrange backups with their provider as well.

After deployment, check login, permissions, document access, and a typical task that both displays and saves data. Confirm that removed pages are no longer accessible and that no configuration or database file was uploaded into the public application folder. Some migrations deliberately sign users out, so users may need to sign in again.

Restoring old application files does not reverse database migrations. Returning to an earlier version may also require adjusting the database structure or restoring a database backup, and a restore can lose changes made after the backup. Hosting recovery backups do not replace a verified backup for your planned migration; contact support to find out which backups are available and arrange a restore.

For help, email contact@datapage.app with your site address, database system, relevant migration names, error, and approximate time. Never send database passwords, session tokens, or private documents in a troubleshooting screenshot.