https://github.com/zknill/sqledge Skip to content Toggle navigation Sign up * Product + Actions Automate any workflow + Packages Host and manage packages + Security Find and fix vulnerabilities + Codespaces Instant dev environments + Copilot Write better code with AI + Code review Manage code changes + Issues Plan and track work + Discussions Collaborate outside of code Explore + All features + Documentation + GitHub Skills + Blog * Solutions For + Enterprise + Teams + Startups + Education By Solution + CI/CD & Automation + DevOps + DevSecOps Resources + Customer Stories + White papers, Ebooks, Webinars + Partners * Open Source + GitHub Sponsors Fund open source developers + The ReadME Project GitHub community articles Repositories + Topics + Trending + Collections * Pricing Search or jump to... Search code, repositories, users, issues, pull requests... Search [ ] Clear Search syntax tips Provide feedback We read every piece of feedback, and take your input very seriously. [ ] [ ] Include my email address so I can be contacted Cancel Submit feedback Saved searches Use saved searches to filter your results more quickly Name [ ] Query [ ] To see all available qualifiers, see our documentation. Cancel Create saved search Sign in Sign up You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session. {{ message }} zknill / sqledge Public * Notifications * Fork 3 * Star 340 Replicate postgres to SQLite on the edge 340 stars 3 forks Activity Star Notifications * Code * Issues 3 * Pull requests 0 * Actions * Projects 0 * Security * Insights More * Code * Issues * Pull requests * Actions * Projects * Security * Insights zknill/sqledge This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. main Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags Name already in use A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch? Cancel Create 1 branch 0 tags Code * Local * Codespaces * Clone HTTPS GitHub CLI [https://github.com/z] Use Git or checkout with SVN using the web URL. [gh repo clone zknill] Work fast with our official CLI. Learn more about the CLI. * Open with GitHub Desktop * Download ZIP Sign In Required Please sign in to use Codespaces. Launching GitHub Desktop If nothing happens, download GitHub Desktop and try again. Launching GitHub Desktop If nothing happens, download GitHub Desktop and try again. Launching Xcode If nothing happens, download Xcode and try again. Launching Visual Studio Code Your codespace will open once ready. There was a problem preparing your codespace, please try again. Latest commit @The-Alchemist @zknill The-Alchemist and zknill improved grammar and punctuation in README.md ... cf03771 Aug 9, 2023 improved grammar and punctuation in README.md cf03771 Git stats * 14 commits Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time cmd/sqledge Add integration test August 2, 2023 12:54 etc Add image to README August 1, 2023 17:45 pkg Add integration test August 2, 2023 12:54 test/integration Add integration test August 2, 2023 12:54 .gitignore Update with README details on how to run and better code separation July 26, 2023 15:19 README.md improved grammar and punctuation in README.md August 9, 2023 16:53 go.mod Proxy write requests to the upstream postgres August 1, 2023 17:29 go.sum Proxy write requests to the upstream postgres August 1, 2023 17:29 View code [ ] SQLedge SQL generation SQL parsing Postgres wire proxy Compatibility Copy on startup Trying it out Config README.md SQLedge [State: alpha] SQLedge uses Postgres logical replication to stream the changes in a source Postgres database to a SQLite database that can run on the edge. SQLedge serves reads from its local SQLite database, and forwards writes to the upstream Postgres server that it's replicating from. This lets you run your apps on the edge, and have local, fast, and eventually consistent access to your data. SQLedge SQL generation The pkg/sqlgen package has an SQL generator in it, which will generate the SQLite insert, update, delete statements based on the logical replication messages received. SQL parsing When the database is started, we look at which tables already exist in the sqlite copy, and make sure new tables are created automatically on the fly. Postgres wire proxy SQLedge contains a Postgres wire proxy, default on localhost:5433. This proxy uses the local SQlite database for reads, and forwards writes to the upstream Postgres server. Compatibility When running, the SQL statements interact with two databases; Postgres (for writes) and SQLite (for reads). The Postgres wire proxy (which forwards reads to SQLite) doesn't currently translate any of the SQL statements from the Postgres query format/functions to the SQLite format/functions. Read queries issued against the Postgres wire proxy need to be compatible with SQLite directly. This is fine for simple SELECT queries, but you will have trouble with Postgres-specific query functions or syntax. Copy on startup SQLEdge maintains a table called postgres_pos, this tracks the LSN (log sequence number) of the received logical replication messages so it can pick up processing where it left off. If no LSN is found, SQLedge will start a postgres COPY of all tables in the public schema. Creating the appropriate SQLite tables, and inserting data. When the replication slot is first created, it exports a transaction snapshot. This snapshot is used for the initial copy. This means that the COPY command will read the data from the transaction at the moment the replication slot was created. Trying it out 1. Create a database create database myappdatabase; 2. Create a user -- must be a super user because we create a publication on all tables create user sqledger with login superuser password 'secret'; 3. Run the example SQLEDGE_UPSTREAM_USER=sqledger SQLEDGE_UPSTREAM_PASSWORD=secret SQLEDGE_UPSTREAM_NAME=myappdatabase go run ./cmd/sqledge/main.go 4. Connect to the postgres wire proxy psql -h localhost -p 5433 $ CREATE TABLE my_table (id serial not null primary key, names text); $ INSERT INTO my_table (names) VALUES ('Jane'), ('John'); $ SELECT * FROM my_table; The read will be served from the local database 5. Connect to the local sqlite db sqlite3 ./sqledge.db .schema Config All config is read from environment variables. The full list is available in the struct tags on the fields in pkg/config/config.go About Replicate postgres to SQLite on the edge Resources Readme Activity Stars 340 stars Watchers 5 watching Forks 3 forks Report repository Releases No releases published Packages 0 No packages published Contributors 2 * @zknill zknill zak * @The-Alchemist The-Alchemist The Alchemist Languages * Go 100.0% Footer (c) 2023 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time.