SQLite

Add credentials

Create a new pipeline or open an existing pipeline.
Expand the left side of your screen to view the file browser.
Scroll down and click on a file named io_config.yaml.
Enter the following keys and values under the key named default (you can have multiple profiles, add it under whichever is relevant to you)

version: 0.1.1
default:
  sqlite:
    database: /path/to/your/sqlite.db

Notes:

database should be the absolute path to your SQLite database file
The database file will be created automatically if it doesn’t exist
Make sure the directory containing the database file has proper write permissions

Using SQL block

Create a new pipeline or open an existing pipeline.
Add a data loader, transformer, or data exporter block.
Select SQL.
Under the Data provider dropdown, select SQLite.
Under the Profile dropdown, select default (or the profile you added credentials underneath).
Next to the Save to schema label, enter the schema name you want this block to save data to.
Under the Write policy dropdown, select Replace or Append (please see SQL blocks guide for more information on write policies).
Enter in this test query: SELECT 1.
Run the block.

Using raw SQL

You can also use raw SQL with SQLite by enabling the “Use raw SQL” option in the SQL block configuration. This allows you to write complete SQL statements including CREATE TABLE, INSERT, UPDATE, and DELETE statements. Example raw SQL:

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

INSERT INTO users (name, email) VALUES 
    ('John Doe', 'john@example.com'),
    ('Jane Smith', 'jane@example.com');

SELECT * FROM users;

Note: When using raw SQL, you’re responsible for writing the complete SQL statements. Mage won’t automatically create tables or handle data insertion.

Using Python block

Create a new pipeline or open an existing pipeline.
Add a data loader, transformer, or data exporter block (the code snippet below is for a data loader).
Select Generic (no template).
Enter this code snippet (note: change the config_profile from default if you have a different profile):

from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.sqlite import SQLite
from os import path
from pandas import DataFrame

if 'data_loader' not in globals():
    from mage_ai.data_preparation.decorators import data_loader


@data_loader
def load_data_from_sqlite(**kwargs) -> DataFrame:
    query = 'SELECT 1'
    config_path = path.join(get_repo_path(), 'io_config.yaml')
    config_profile = 'default'

    with SQLite.with_config(ConfigFileLoader(config_path, config_profile)) as loader:
        return loader.load(query)

Run the block.

Export a dataframe

Here is an example code snippet to export a dataframe to SQLite:

from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.sqlite import SQLite
from pandas import DataFrame
from os import path

if 'data_exporter' not in globals():
    from mage_ai.data_preparation.decorators import data_exporter


@data_exporter
def export_data_to_sqlite(df: DataFrame, **kwargs) -> None:
    schema_name = 'your_schema_name'  # Specify the name of the schema to export data to
    table_name = 'your_table_name'  # Specify the name of the table to export data to
    config_path = path.join(get_repo_path(), 'io_config.yaml')
    config_profile = 'default'

    with SQLite.with_config(ConfigFileLoader(config_path, config_profile)) as loader:
        loader.export(
            df,
            schema_name,
            table_name,
            index=False,  # Specifies whether to include index in exported table
            if_exists='replace',  # Specify resolution policy if table name already exists
            drop_table_on_replace=False,   # Whether to drop the table when "if_exists" param is set to "replace"
        )

Custom types

To overwrite a column type when running a python export block, simply specify the column name and type in the overwrite_types dict in data exporter config Here is an example code snippet:

from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.sqlite import SQLite
from pandas import DataFrame
from os import path

if 'data_exporter' not in globals():
    from mage_ai.data_preparation.decorators import data_exporter


@data_exporter
def export_data_to_sqlite(df: DataFrame, **kwargs) -> None:
    schema_name = 'your_schema_name'  # Specify the name of the schema to export data to
    table_name = 'your_table_name'  # Specify the name of the table to export data to
    config_path = path.join(get_repo_path(), 'io_config.yaml')
    config_profile = 'default'
    overwrite_types = {'column_name': 'TEXT'}

    with SQLite.with_config(ConfigFileLoader(config_path, config_profile)) as loader:
        loader.export(
            df,
            schema_name,
            table_name,
            index=False,  # Specifies whether to include index in exported table
            if_exists='replace',  # Specify resolution policy if table name already exists
            overwrite_types=overwrite_types,
        )

Method arguments

Field name	Description	Example values
auto_clean_name	Whether to automatically clean the column name (replace the empty space with underscore, avoid using number as the prefix of the column name)	True/False (default: True)
case_sensitive	Whether to support case sensitive columns	True/False (default: False)
drop_table_on_replace	Whether to drop the table when “if_exists” param is set to “replace”.	True/False (default: False)
if_exists	Specify resolution policy if table name already exists	”fail”/“replace”/“append” (default: “replace”)
overwrite_types	Overwrite the column types	`{'column1': 'INTEGER', 'column2': 'TEXT'}`
unique_conflict_method	How to handle the conflict on unique constraints. Use ‘UPDATE’ for UPSERT (update existing rows, insert new ones) or ‘IGNORE’ to skip duplicates.	‘UPDATE’ or ‘IGNORE’ (default: None)
unique_constraints	The unique constraints of the table. Used with unique_conflict_method for UPSERT operations.	[‘col1’, ‘col2’] (default: None)

Example: Using UPSERT (UPDATE or INSERT)

from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.sqlite import SQLite
from pandas import DataFrame
from os import path

if 'data_exporter' not in globals():
    from mage_ai.data_preparation.decorators import data_exporter


@data_exporter
def export_data_to_sqlite(df: DataFrame, **kwargs) -> None:
    """
    Template for exporting data to SQLite with UPSERT support.
    Specify your configuration settings in 'io_config.yaml'.
    """
    schema_name = 'your_schema_name'
    table_name = 'your_table_name'
    config_path = path.join(get_repo_path(), 'io_config.yaml')
    config_profile = 'default'

    with SQLite.with_config(ConfigFileLoader(config_path, config_profile)) as loader:
        loader.export(
            df,
            schema_name,
            table_name,
            index=False,
            if_exists='append',  # Use 'append' with UPSERT
            unique_constraints=['user_id', 'email'],  # Columns that form unique constraint
            unique_conflict_method='UPDATE',  # Update existing rows, insert new ones
        )

SQLite-specific considerations

Database file location

SQLite databases are stored as single files on disk
Ensure the file path is accessible and has proper read/write permissions
Use absolute paths to avoid issues with working directory changes

Schema support

SQLite doesn’t have true schema support like PostgreSQL
The “schema” parameter in Mage is used for table naming conventions
Tables are created in the main database without schema prefixes

Data types

SQLite uses dynamic typing (type affinity)
Common types: INTEGER, REAL, TEXT, BLOB, NULL
Mage will map pandas data types to appropriate SQLite types

Performance tips

SQLite performs best with smaller datasets (< 1GB)
Consider using PRAGMA statements for performance tuning
Use transactions for bulk operations

Infrastructure

3rd party

Add credentials

Using SQL block

Using raw SQL

Using Python block

Export a dataframe

Custom types

Method arguments

SQLite-specific considerations

Database file location

Schema support

Data types

Performance tips

​Add credentials

​Using SQL block

​Using raw SQL

​Using Python block

​Export a dataframe

​Custom types

​Method arguments

​SQLite-specific considerations

​Database file location

​Schema support

​Data types

​Performance tips

Add credentials

Using SQL block

Using raw SQL

Using Python block

Export a dataframe

Custom types

Method arguments

SQLite-specific considerations

Database file location

Schema support

Data types

Performance tips