Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Database Backends

Kinesis API supports multiple database backend options: the bundled Kinesis DB (default), SQLite, MySQL, and PostgreSQL. This guide explains how to choose between them and migrate data when switching backends.

Overview

Kinesis DB (Default)

Kinesis DB is a custom-built, ACID-compliant embedded database written in Rust specifically for Kinesis API. It offers:

  • Multiple storage engines (in-memory, on-disk, hybrid)
  • Advanced transaction isolation levels
  • Custom optimizations for Kinesis API workloads
  • No external dependencies

For detailed information about Kinesis DB features, see the Kinesis DB documentation.

SQLite

SQLite is a widely-used, battle-tested embedded database. Using SQLite may be preferable if you:

  • Need compatibility with external SQLite tools
  • Want to use a well-established database engine
  • Have existing SQLite expertise
  • Require integration with other systems that use SQLite
  • Prefer an embedded database without requiring a separate database server

MySQL

MySQL is a popular open-source relational database management system. Using MySQL may be preferable if you:

  • Already have a MySQL server infrastructure
  • Need to share the database with other applications
  • Require advanced replication and clustering features
  • Have existing MySQL expertise and tooling
  • Need enterprise-grade database support

PostgreSQL

PostgreSQL is an advanced open-source relational database known for its robustness and features. Using PostgreSQL may be preferable if you:

  • Need advanced SQL features and data types
  • Require strong data integrity and ACID compliance
  • Want extensibility and custom functions
  • Have existing PostgreSQL infrastructure
  • Need advanced indexing and query optimization

Configuring the Database Backend

You can choose which database backend to use by setting the DB_BACKEND environment variable in your .env file.

Using Kinesis DB (Default)

To use Kinesis DB, either omit the DB_BACKEND variable or set it to one of these values:

DB_BACKEND=kinesis_db
# or
DB_BACKEND=kinesisdb

If DB_BACKEND is not set or is empty, Kinesis DB will be used by default.

Using SQLite

To use SQLite, set the DB_BACKEND environment variable to sqlite:

DB_BACKEND=sqlite

Optionally, you can control the location of the .sqlite file using the DATABASE_URL environment variable:

DB_BACKEND=sqlite
DATABASE_URL=/app/data/my_database.sqlite

Default SQLite paths:

  • Docker installations: /app/data/db.sqlite
  • Local installations: data/db.sqlite (relative to the project root)

If you don’t specify DATABASE_URL, Kinesis API will use the default path for your installation type.

Using MySQL

To use MySQL, set the DB_BACKEND environment variable to mysql and provide a connection URI via DATABASE_URL:

DB_BACKEND=mysql
DATABASE_URL=mysql://username:password@host:port/database_name

Example:

DB_BACKEND=mysql
DATABASE_URL=mysql://kinesis_user:secure_password@localhost:3306/kinesis_db

The DATABASE_URL must include:

  • Username and password for authentication
  • Host and port of your MySQL server
  • Database name to use

Using PostgreSQL

To use PostgreSQL, set the DB_BACKEND environment variable to one of these values: postgresql, postgres, or pgsql:

DB_BACKEND=postgresql
DATABASE_URL=postgresql://username:password@host:port/database_name

Example:

DB_BACKEND=postgresql
DATABASE_URL=postgresql://kinesis_user:secure_password@localhost:5432/kinesis_db

The DATABASE_URL must include:

  • Username and password for authentication
  • Host and port of your PostgreSQL server
  • Database name to use

Resetting Sequences After Migration

⚠️ IMPORTANT: After migrating to PostgreSQL, you MUST manually reset the auto-increment sequences for all tables. This ensures that new records use correct ID values and prevents primary key conflicts.

Connect to your PostgreSQL database and run the following SQL commands:

SELECT setval(pg_get_serial_sequence('"user"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "user") + 1,false);
SELECT setval(pg_get_serial_sequence('"user_link"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "user_link") + 1,false);
SELECT setval(pg_get_serial_sequence('"ticket"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "ticket") + 1,false);
SELECT setval(pg_get_serial_sequence('"ticket_comment"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "ticket_comment") + 1,false);
SELECT setval(pg_get_serial_sequence('"project"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "project") + 1,false);
SELECT setval(pg_get_serial_sequence('"collection"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "collection") + 1,false);
SELECT setval(pg_get_serial_sequence('"custom_structure"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "custom_structure") + 1,false);
SELECT setval(pg_get_serial_sequence('"structure"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "structure") + 1,false);
SELECT setval(pg_get_serial_sequence('"config"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "config") + 1,false);
SELECT setval(pg_get_serial_sequence('"media"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "media") + 1,false);
SELECT setval(pg_get_serial_sequence('"event"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "event") + 1,false);
SELECT setval(pg_get_serial_sequence('"constraint"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "constraint") + 1,false);
SELECT setval(pg_get_serial_sequence('"constraint_property"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "constraint_property") + 1,false);
SELECT setval(pg_get_serial_sequence('"data"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "data") + 1,false);
SELECT setval(pg_get_serial_sequence('"data_pair"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "data_pair") + 1,false);
SELECT setval(pg_get_serial_sequence('"route"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "route") + 1,false);
SELECT setval(pg_get_serial_sequence('"personal_access_token"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "personal_access_token") + 1,false);
SELECT setval(pg_get_serial_sequence('"redirect"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "redirect") + 1,false);
SELECT setval(pg_get_serial_sequence('"snippet"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "snippet") + 1,false);
SELECT setval(pg_get_serial_sequence('"blog_post"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "blog_post") + 1,false);
SELECT setval(pg_get_serial_sequence('"blog_comment"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "blog_comment") + 1,false);
SELECT setval(pg_get_serial_sequence('"backup"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "backup") + 1,false);
SELECT setval(pg_get_serial_sequence('"backup_schedule"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "backup_schedule") + 1,false);
SELECT setval(pg_get_serial_sequence('"content_history"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "content_history") + 1,false);
SELECT setval(pg_get_serial_sequence('"component_user_metrics"', 'id'),(SELECT COALESCE(MAX(id), 0) FROM "component_user_metrics") + 1,false);

How to run these commands:

# Using psql command-line tool
psql -U kinesis_user -d kinesis_db -f reset_sequences.sql

# Or connect interactively and paste the commands
psql -U kinesis_user -d kinesis_db
# Then paste all the SELECT setval(...) commands

Why this is necessary:

When migrating data to PostgreSQL, the sequences that generate auto-incrementing IDs are not automatically updated to match the migrated data. Without resetting these sequences, PostgreSQL will start generating IDs from 1, which will conflict with existing records and cause insertion failures.

Switching Between Database Backends

⚠️ CRITICAL: When switching between database backends, you MUST migrate your data manually. Simply changing the DB_BACKEND environment variable will result in Kinesis API using an empty database, as different backends store data in different formats and locations.

Migration Process

Kinesis API provides two methods to transfer data between database backends:

  1. Web UI (Recommended): Use the migration interface on the login page
  2. API Endpoint: Call the migration endpoint directly via HTTP request

Both methods require the same initialization code (init_code) that you configured during setup.

  1. Back up your current data (see the Backups guide)

  2. Stop Kinesis API

  3. Update your .env file to set the target database backend:

    • To migrate to SQLite: Set DB_BACKEND=sqlite (optionally set DATABASE_URL)
    • To migrate to MySQL: Set DB_BACKEND=mysql and DATABASE_URL=mysql://...
    • To migrate to PostgreSQL: Set DB_BACKEND=postgresql and DATABASE_URL=postgresql://...

  4. Restart Kinesis API

  5. Navigate to the login page at http://your-domain-or-ip:8080/web/login

  6. Scroll down to find the “Initialize” and “Migrate Database” sections

  7. Click the navigation button to switch to the “Migrate Database” panel

    Migrate Button

  8. Enter your initialization code (default is code)

  9. Click the “Migrate” button

  10. Wait for the success confirmation message

  11. Verify that your data is accessible in the web interface

Method 2: Using the API Endpoint

The general process for migrating via API is:

  1. Ensure you know what database backend you’re currently using
  2. Back up your current data (see the Backups guide)
  3. Stop Kinesis API
  4. Update your .env file to configure the target database backend
  5. Restart Kinesis API
  6. Call the migration endpoint
  7. Verify that your data is accessible
Example: Migrating to SQLite
# 1. Stop Kinesis API
# 2. Update .env file:
DB_BACKEND=sqlite
DATABASE_URL=/app/data/db.sqlite  # Optional: specify custom path

# 3. Restart Kinesis API
# 4. Call migration endpoint:
curl "http://your-domain-or-ip:8080/db/migrate?code=your_init_code"
Example: Migrating to MySQL
# 1. Stop Kinesis API
# 2. Update .env file:
DB_BACKEND=mysql
DATABASE_URL=mysql://kinesis_user:password@localhost:3306/kinesis_db

# 3. Restart Kinesis API
# 4. Call migration endpoint:
curl "http://your-domain-or-ip:8080/db/migrate?code=your_init_code"
Example: Migrating to PostgreSQL
# 1. Stop Kinesis API
# 2. Update .env file:
DB_BACKEND=postgresql
DATABASE_URL=postgresql://kinesis_user:password@localhost:5432/kinesis_db

# 3. Restart Kinesis API
# 4. Call migration endpoint:
curl "http://your-domain-or-ip:8080/db/migrate?code=your_init_code"
Example: Migrating to Kinesis DB
# 1. Back up your current data
# 2. Stop Kinesis API
# 3. Restart Kinesis API (keep DB_BACKEND set to your current external database)
# 4. Call migration endpoint with inverted flag:
curl "http://your-domain-or-ip:8080/db/migrate?code=your_init_code&inverted=true"

# 5. After successful migration, you can optionally update .env to use Kinesis DB:
# DB_BACKEND=kinesis_db
# (or remove DB_BACKEND entirely)
# 6. Restart Kinesis API to use Kinesis DB as the active backend

Important Notes:

  • Keep DB_BACKEND set to your current external database (sqlite/mysql/postgresql) when calling the migration endpoint
  • The inverted=true parameter tells the system to read from the external database and write to Kinesis DB
  • Only change DB_BACKEND to kinesis_db AFTER the migration completes successfully
  • To migrate between external databases (e.g., SQLite to MySQL), you must first migrate to Kinesis DB, then migrate to the target database

Replace your_init_code with the init code configured in your setup (default is code).

Migration Endpoint Details

  • Endpoint: /db/migrate
  • Method: GET
  • Query Parameters:
    • code (required) - Your initialization code (same as used for /init)
    • inverted (optional) - Set to true to migrate back to Kinesis DB (default: false)
  • Behavior: Migrates all data between Kinesis DB and external database backends
    • Normal migration (inverted=false or omitted): Migrates FROM Kinesis DB TO the external database specified by DB_BACKEND (SQLite, MySQL, or PostgreSQL)
    • Inverted migration (inverted=true): Migrates FROM the external database specified by DB_BACKEND back TO Kinesis DB
    • Direct migration between external databases (e.g., SQLite to MySQL) is NOT supported - you must migrate to Kinesis DB first, then to the target external database

Important:

  • The DB_BACKEND environment variable should be set to the external database backend (SQLite, MySQL, or PostgreSQL) for both migration directions
  • For normal migration: Migrates from Kinesis DB to the external database specified in DB_BACKEND
  • For inverted migration: Migrates from the external database specified in DB_BACKEND back to Kinesis DB
  • The migration will delete all data from the target database

Migrating from Kinesis DB to External Database:

# Using curl
curl "http://localhost:8080/db/migrate?code=code"

# Using wget
wget -qO - "http://localhost:8080/db/migrate?code=code"

Migrating from External Database back to Kinesis DB:

# Using curl (note the inverted=true parameter)
curl "http://localhost:8080/db/migrate?code=code&inverted=true"

# Using wget
wget -qO - "http://localhost:8080/db/migrate?code=code&inverted=tru

Security Notes

The migration endpoint uses the same security code (init_code) as the initialization endpoint to prevent unauthorized access. Ensure your init_code is:

  • Changed from the default value (code) in production environments
  • Kept secure and not shared publicly
  • Consistent with the value configured in your Configs settings
  • Connection pooling is enabled for all external database backends (SQLite, MySQL, PostgreSQL) with a default pool size of 10 connections
  • Network latency may affect MySQL/PostgreSQL performance compared to embedded databases (Kinesis DB, SQLite)

Best Practices

Before Switching

  1. Create a backup: Always back up your data before switching database backends. See the Backups guide for instructions.
  2. Test in a non-production environment: If possible, test the migration process in a development or staging environment first.
  3. Verify your init code: Ensure you know your initialization code before starting the migration.
  4. Stop write operations: Ensure no data is being written during the migration process to prevent data loss.
  5. Prepare target database: For MySQL/PostgreSQL, ensure the database exists and the connection URL is correct.
  6. Check permissions: Verify that Kinesis API has read/write access to the target database location or server.
  7. Understand migration paths: Remember that you can only migrate FROM Kinesis DB TO external databases, or FROM external databases TO Kinesis DB. Direct migration between external databases requires a two-step process through Kinesis DB.

After Switching

  1. Verify data integrity: Check that all your projects, collections, and data are accessible.
  2. Test functionality: Perform basic CRUD operations to ensure the new backend is working correctly.
  3. Monitor performance: Different backends may have different performance characteristics for your specific workload.
  4. Update backups: Ensure your backup procedures account for the new database backend.

Performance Considerations

  • Kinesis DB may offer better performance for specific Kinesis API operations due to custom optimizations

  • SQLite is well-optimized for general-purpose workloads and benefits from decades of development

  • MySQL provides good performance for multi-user environments and can scale horizontally with replication

  • PostgreSQL offers excellent performance for complex queries and large datasets with advanced indexing

  • The actual performance difference will depend on your specific use case and workload patterns

  • Consider benchmarking different options with your typical workload to make an informed decision

Troubleshooting

Migration Fails

If the migration endpoint returns an error:

  1. Check the Kinesis API logs for detailed error messages
  2. Verify that you’re using the correct initialization code
  3. Ensure you have sufficient disk space for the new database
    • Verify the connection URI is correct and the server is running
    • Ensure the database exists and the user has proper permissions (CREATE, INSERT, UPDATE, DELETE)
    • Check that the MySQL/PostgreSQL server is reachable from the Kinesis API host
    • Verify firewall rules allow connections on the database port
  4. Make sure Kinesis API has been restarted after changing environment variables
  5. For inverted migrations (back to Kinesis DB), verify you’re using inverted=true in the request

Data Missing After Switch

If data appears to be missing after switching backends:

  1. Verify that you called the migration endpoint with the correct parameters:
    • Normal migration: inverted=false (or omitted) with DB_BACKEND set to the target external database
    • Inverted migration: inverted=true with DB_BACKEND set to the source external database
  2. Check that you’re looking at the correct database (file path for SQLite, database name for MySQL/PostgreSQL)
  3. For inverted migrations, ensure you did NOT change DB_BACKEND to kinesis_db before calling the migration endpoint
  4. Review the migration endpoint response for any error messages
  5. Restore from your backup and retry the migration process with the correct parameters
  6. Remember: Direct migration between external databases is not supported - use Kinesis DB as an intermediary

Cannot Access Migration Endpoint

If you receive a 401 or 403 error when calling the migration endpoint:

  1. Double-check that your init_code matches the value configured in Configs
  2. Ensure you’re using the correct URL format with the code query parameter
  3. Verify that the Kinesis API service is running and accessible

Getting Help

If you encounter issues during the database backend switch: