Posts tagged: database

I'm All-In on Server-Side SQLite Ben Johnson has joined Fly.io Fly · fly.io [1] SQLite is the next big database trend. with more horizontal scaling, close to user read heavy applications, having your database in the same application stack makes a lot of sense. Tools like litestream are going to enable global distribution in an impressive way. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://fly.io/blog/all-in-on-sqlite-litestream/ [2]: /thoughts/

LiteFS Cloud: Distributed SQLite with Managed Backups Documentation and guides from the team at Fly.io. Fly · fly.io [1] Fly.io’s solution to sqlite managed backups.I definitely want to look into this a bit, but moreso the tech under the hook litestream. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://fly.io/blog/litefs-cloud/ [2]: /thoughts/

[1] sqlite has 3 different tokenizers, porter, ascii, trigram. These can be used with sqlite-utils. sqlite-utils enable-fts --tokenize porter database.db post title message tags And with the python api. db = Database('database.db') db["post"].enable_fts( ["title", "message", "tags"], create_triggers=True, tokenize="trigram" ) posts = list(db["post"].search(search)) Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: /static/https://www.sqlite.org/fts5.html [2]: /thoughts/

sqlite_utils Python library - sqlite-utils sqlite-utils.datasette.io [1] sqlite-utils is primarily a cli tool for sqlite operations such as enabling full text search, and executing searches, but it also has a nice python api that is exposed and pretty straightforward to use. from sqlite_utils import Database db = Database("database.db") db["post"].enable_fts(["title", "message", "tags]) db["post"].search("water") This returns a generator object that you can iterate over the row objects with. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://sqlite-utils.datasette.io/en/stable/python-api.html#full-text-search [2]: /thoughts/

GitHub - simonw/datasette-render-markdown: Datasette plugin for rendering Markdown Datasette plugin for rendering Markdown. Contribute to simonw/datasette-render-markdown development by creating an account on GitHub. GitHub · github.com [1] datasette really does everything doesn’t it! Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://github.com/simonw/datasette-render-markdown [2]: /thoughts/

`ValueError: Constraint must have a name` in alembic 1.10.0 · Issue #1195 · sqlalchemy/alembic Describe the bug ValueError: Constraint must have a name in alembic 1.10.0. Expected behavior Migration succeeds. To Reproduce Please try to provide a Minimal, Complete, and Verifiable example, wit... GitHub · github.com [1] After a nasty time with alembic upgrades, thoughts is about to get a new users table. This may have came from incorrectly setting up alembic for sqlite from the start, but I was able to fix the issue with this GitHub issue. alembic sqlite ValueError: Constraint must have a name The change I needed to make to get my migration to run. + batch_op.create_foreign_key('fk_post_author_id_user', 'user', ['author_id'], ['id']) Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://github.com/sqlalchemy/alembic/issues/1195 [2]: /thoughts/

![[None]] Since using alembic I have been just running out a new revision checking its content and deleting it if its empty, today I learned there is an alembic check command to check for operations that need to be created. ❯ alembic check INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. No new upgrade operations detected. Note This post is a thought [1]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: /thoughts/

DuckDB vs. MotherDuck: When to Move to the Cloud | Kestra DuckDB is fast and free. MotherDuck adds cloud storage, collaboration, and scale. Here kestra.io [1] duckdb is a new in process database that has been making its rounds in analytics for its high performance in those applications. Mother duck is a centeralized server that brings manages storage, data sharing and an ide to duckdb. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://kestra.io/blogs/2023-07-28-duckdb-vs-motherduck [2]: /thoughts/

External Link stackoverflow.com [1] How to sort results from a sqlalchemy based orm. .order_by(model.Entry.amount.desc()) I needed this to enable paging on my thoughts api. @post_router.get("/posts/") async def get_posts( *, request: Request, session: Session = Depends(get_session), hx_request: Annotated[str | None, Header()] = None, accept: Annotated[str | None, Header()] = None, current_user: Annotated[User, Depends(try_get_current_active_user)], page_size: int = 10, page: int = 1, ) -> Posts: "get all posts" statement = ( select(Post) .where(Post.published) .order_by(Post.id.desc()) .limit(page_size) .offset((page - 1) * page_size) ) posts = session.exec(statement).all() posts = Posts(__root__=posts) if isinstance(current_user, RedirectResponse): is_logged_in = False else: is_logged_in = True if hx_request and page == 1 and len(posts.__root__) == 0: return HTMLResponse('<ul id="posts"><li>No posts</li></ul>') if hx_request and len(posts.__root__) == 0: return HTMLResponse("") if not hx_request and len(posts.__root__) == 0: return ["no posts"] if hx_request: return templates.TemplateResponse( "posts.html", { "request": request, "config":...

GitHub - kndndrj/nvim-dbee: Interactive database client for neovim Interactive database client for neovim. Contribute to kndndrj/nvim-dbee development by creating an account on GitHub. GitHub · github.com [1] A neovim database client that I need to check out. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://github.com/kndndrj/nvim-dbee [2]: /thoughts/

sqlite-utils now supports plugins sqlite-utils 3.34 is out with a major new feature: support for plugins. sqlite-utils is my combination Python library and command-line tool for manipulating SQLite databases. It recently celebrated... Simon Willison’s Weblog · simonwillison.net [1] As the title states sqlite-utils now supports plugins. I dug in just a bit and Simon implemented this completely with entrypoints, no framework or library at all. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://simonwillison.net/2023/Jul/24/sqlite-utils-plugins/ [2]: /thoughts/

Column INSERT/UPDATE Defaults — SQLAlchemy 1.4 Documentation docs.sqlalchemy.org [1] sqlalchemy server_defaults end up as defaults in the database when new values are inserted. t = Table( "test", metadata_obj, Column("abc", String(20), server_default="abc"), Column("created_at", DateTime, server_default=func.sysdate()), Column("index_value", Integer, server_default=text("0")), ) CREATE TABLE test ( abc varchar(20) default 'abc', created_at datetime default sysdate, index_value integer default 0 ) Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://docs.sqlalchemy.org/en/14/core/defaults.html#server-invoked-ddl-explicit-default-expressions [2]: /thoughts/

Filter Data - WHERE - SQLModel SQLModel, SQL databases in Python, designed for simplicity, compatibility, and robustness. sqlmodel.tiangolo.com [1] When fetching pydantic models from the database with sqlmodel, and you cannot select your item by id, you probably need to use a where clause. This is the sqlmodel way of doing it. Here is a snippet of how I am using sqlmodel select and where to find a post by link in my thoughts database. @post_router.get("/link/") async def get_post_by_link( *, session: Session = Depends(get_session), link: str, ) -> PostRead: "get one post by link" link = urllib.parse.unquote(link) print(f'link: {link}') post = session.exec(select(Post).where(Post.link==link)).first() if not post: raise HTTPException(status_code=404, detail=f"Post not found for link: {link}") return post Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://sqlmodel.tiangolo.com/tutorial/where/#filter-rows-using-where-with-sqlmodel [2]: /thoughts/

Full-text search - Datasette documentation docs.datasette.io [1] Enable full-text search in sqlite using sqlite-utils. $ sqlite-utils enable-fts mydatabase.db items name description Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://docs.datasette.io/en/latest/full_text_search.html#enabling-full-text-search-for-a-sqlite-table [2]: /thoughts/

sqlite-utils command-line tool - sqlite-utils sqlite-utils.datasette.io [1] I want to like jq, but I think Simon is selling me on sqlite, maybe its just me but this looks readable, hackable, editable, memorizable. Everytime I try jq, and its 5 minutes fussing with it just to get the most basic thing to work. I know enough sql out of the gate to make this work off the top of my head curl https://thoughts.waylonwalker.com/posts/ | sqlite-utils memory - 'select title, message from stdin where stdin.tags like "%python%"' | jq Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://sqlite-utils.datasette.io/en/stable/cli.html#querying-data-directly-using-an-in-memory-database [2]: /thoughts/

sqlite-utils command-line tool - sqlite-utils sqlite-utils.datasette.io [1] insert a json array directly into into sqlite with sqlite-utils. echo '{"name": "Cleo", "age": 4}' | sqlite-utils insert dogs.db dogs - Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-json-data [2]: /thoughts/