Migrate from SQLite to Neon Postgres

This guide describes how to migrate your SQLite database to Neon Postgres using pgloader

pgloader is an open-source data loading and migration tool that efficiently transfers data from various sources (like CSV, MySQL, SQLite, MS SQL, etc.) into Postgres, handling schema and data transformations on the fly. We'll use it to migrate a sample SQLite database to Neon Postgres

Prerequisites

Before you begin, ensure you have the following:

• A Neon account and a project. If you don't have one, see Sign up.
• A database created in your Neon project. For instructions, see Create a database.
• The file path to your source SQLite database file. If you don't have one, you can create a sample database in the next step.
• Neon's Free plan supports 0.5 GB of data. If your data size is more than 0.5 GB, you'll need to upgrade to one of Neon's paid plans. See Neon plans for more information.

A review of the pgloader SQLite to Postgres Guide is also recommended. It provides a comprehensive overview of pgloader's capabilities.

Understanding SQLite and Postgres data types

Before migrating from SQLite to Postgres, it's helpful to understand a key difference in how they handle data types:

• SQLite uses a flexible typing system called "type affinity". You can store any type of data in any column, regardless of its declared type. For example, you can store the text "hello" in a column declared as INTEGER. The declared type is only a suggestion.
• Postgres uses a strict, static typing system. Data inserted into a column must precisely match the column's declared data type. An attempt to store "hello" in an INTEGER column will result in an error.

When converting a database, SQLite's type affinities are mapped to appropriate Postgres types. Here is a summary of the common mappings:

Create a sample SQLite database (Optional)

If you don't have a database to migrate, you can create a sample database for this tutorial. This requires the sqlite3 command-line tool, typically pre-installed on macOS and Linux.

1. Create a file named seed.sql. This schema defines authors and books tables, including a published_date column stored as TEXT to demonstrate type casting.

   -- Create the authors table
   CREATE TABLE authors (
       id INTEGER PRIMARY KEY,
       name TEXT NOT NULL,
       bio TEXT
   );
   
   -- Create the books table
   CREATE TABLE books (
       id INTEGER PRIMARY KEY,
       author_id INTEGER NOT NULL,
       title TEXT NOT NULL,
       published_date TEXT,
       rating REAL,
       FOREIGN KEY (author_id) REFERENCES authors (id)
   );
   
   -- Insert sample data
   INSERT INTO authors (id, name, bio) VALUES
   (1, 'George Orwell', 'Author of dystopian classics.'),
   (2, 'J.R.R. Tolkien', 'Author of high-fantasy epics.'),
   (3, 'Jane Austen', 'Renowned for her romantic fiction.');
   
   INSERT INTO books (author_id, title, published_date, rating) VALUES
   (1, '1984', '1949-06-08', 4.8),
   (1, 'Animal Farm', '1945-08-17', 4.5),
   (2, 'The Hobbit', '1937-09-21', 4.9),
   (2, 'The Lord of the Rings', '1954-07-29', 5.0),
   (3, 'Pride and Prejudice', '1813-01-28', 4.7);

2. Create the SQLite database sample_library.db from the schema file:

   sqlite3 sample_library.db < seed.sql

You now have a sample_library.db file ready for migration.

turso db shell <database-name> .dump > seed.sql

# Generate a SQLite database file from the SQL dump
sqlite3 sample_library.db < seed.sql

Now that you have your Neon database and SQLite database ready, you can use pgloader to migrate the data. Follow these steps:

1. Retrieve your Neon database connection string

   Log in to the Neon Console. Find the connection string for your database by clicking the Connect button on your Project Dashboard. It should look similar to this:

   postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require

   important

   You will need to remove &channel_binding=require from the connection string, as pgloader does not support channel binding and throws an error when it is present.

   Now, modify this connection string to pass your endpoint ID (ep-cool-darkness-123456 in this example) to Neon with your password using the endpoint keyword, as shown here:

   postgresql://alex:endpoint=ep-cool-darkness-123456;AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require

   note

   Passing the endpoint ID with your password is a required workaround for some Postgres drivers, including the one used by pgloader. For more information, see Connect with an endpoint ID.

   Keep your modified Neon connection string handy.

2. Install pgloader

   Install the pgloader utility using your preferred method:

   • For macOS with Homebrew: brew install pgloader
   • For Debian/Ubuntu: sudo apt-get install pgloader
   • For Docker: Pull the latest image with docker pull dimitri/pgloader:latest

   For other systems, see Installing pgloader.

3. Run a simple migration

   For a basic migration, you can run pgloader directly from the command line. This command uses pgloader's default settings to migrate the sample_library.db schema and data.

   pgloader sqlite://sample_library.db "postgresql://alex:endpoint=ep-cool-darkness-123456;AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require"

   Make sure to enclose the Postgres connection string in quotes to prevent shell interpretation issues.

   The command output will look similar to this:

   2025-06-27T08:26:19.941000Z LOG report summary reset
                table name     errors       rows      bytes      total time
   -----------------------  ---------  ---------  ---------  --------------
                     fetch          0          0                     0.000s
           fetch meta data          0          5                     0.204s
            Create Schemas          0          0                     0.108s
          Create SQL Types          0          0                     0.222s
             Create tables          0          4                     1.307s
            Set Table OIDs          0          2                     0.121s
   -----------------------  ---------  ---------  ---------  --------------
                   authors          0          3     0.1 kB          1.082s
                     books          0          5     0.2 kB          0.993s
   -----------------------  ---------  ---------  ---------  --------------
   COPY Threads Completion          0          4                     1.080s
    Index Build Completion          0          2                     2.342s
            Create Indexes          0          2                     0.662s
           Reset Sequences          0          0                     1.297s
              Primary Keys          0          2                     0.650s
       Create Foreign Keys          0          1                     0.339s
           Create Triggers          0          0                     0.211s
          Install Comments          0          0                     0.000s
   -----------------------  ---------  ---------  ---------  --------------
         Total import time          ✓          8     0.3 kB          6.581s

   This is quick, but it will create primary key columns as bigint rather than serial, and the published_date column will remain text. This is expected behavior, as pgloader uses SQLite's type affinities directly.

Advanced migration with custom casting

For fine-grained control, a pgloader load file is the best approach. Here, we'll create a load file that uses the CAST clause to:

1. Convert INTEGER PRIMARY KEY columns to SERIAL. This makes the Postgres schema cleaner and more idiomatic.
2. Cast the TEXT published_date column to the native DATE type in Postgres.

Create a file named sqlite_advanced.load with the following content. Replace the Neon connection string and file path if necessary.

LOAD DATABASE
    FROM sqlite://sample_library.db
    INTO postgresql://alex:endpoint=ep-cool-darkness-123456;AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require

WITH
    include drop,
    create tables,
    create indexes,
    reset sequences,
    downcase identifiers

CAST
    -- Cast specific primary key columns to SERIAL for auto-incrementing
    column authors.id to serial,
    column books.id to serial,

    -- Cast text column to date; pgloader handles ISO 8601 format ('YYYY-MM-DD') automatically
    column books.published_date to date;

Now, run the migration using this advanced load file:

pgloader sqlite_advanced.load

The migration will now produce a more refined Postgres schema, with SERIAL primary keys and a proper DATE column.

Post-migration verification

After migrating, always verify your data. One critical area is auto-incrementing primary keys.

Verify sequences

The reset sequences option in the load file ensures that auto-incrementing columns start from the correct value. You can verify this manually.

Connect to your Neon database using psql or Neon SQL Editor and check the next value for the books table's sequence:

SELECT nextval(pg_get_serial_sequence('books', 'id'));

This should return a value one higher than the max id in the books table (e.g., 6 for our sample data). If it doesn't, you can reset it manually with this command:

SELECT setval(
    pg_get_serial_sequence('books', 'id'),
    (SELECT MAX(id) FROM books) + 1
);

Troubleshooting

SSL verify error with Docker

If you run pgloader from a Docker container and encounter an SSL verify error: 20 X509_V_ERR_UNABLE_TO_GET_ISSUER_CERT_LOCALLY, you may need to disable SSL certificate verification.

Modify your load file to set sslmode=allow in the Postgres connection string.

LOAD DATABASE
    FROM sqlite:////data/sample_library.db
    INTO postgresql://.../dbname?sslmode=allow;
...

Then, run the Docker command with the --no-ssl-cert-verification flag. Mount your database and load files into the container's /data directory.

docker run --rm -v /path/to/your/files:/data
  dimitri/pgloader:latest
  pgloader --no-ssl-cert-verification /data/sqlite_advanced.load

References

• pgloader Documentation
• pgloader Reference: SQLite to Postgres
• pgloader CLI Reference